SQL Query activity timeout setting?
I'm sending a fairly complex query to SQL Server using the SQL Query activity. The workflow times out right around 30 seconds, and the log file looks like this:
<Event Timestamp="2013-08-27T13:25:09.7359078-07:00" Level="ERROR" Identity="Guest"><Message>Workflow 'aaa' failed. Cause: Geocortex.Workflow.WorkflowException: Unhandled exception: 'One or more errors occurred.' in activity '1.12: SqlQuery'. ---> System.AggregateException: One or more errors occurred. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Geocortex.Workflow.Activities.SqlQuery.ExecuteSync(IDictionary`2 parameters)
at Geocortex.Workflow.Activities.SqlQuery.<>c__DisplayClass3.<BeginExecute>b__0(Object o)
at System.Threading.Tasks.Task`1.InvokeFuture(Object futureAsObj)
at System.Threading.Tasks.Task.InnerInvoke()
at System.Threading.Tasks.Task.Execute()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at Geocortex.Workflow.Activities.SqlQuery.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
--- End of inner exception stack trace ---</Message></Event>
<Event Timestamp="2013-08-27T13:25:09.737908-07:00" Level="ERROR" Identity="Guest"><Message>Exception has been thrown by the target of an invocation.
Workflow 'aaa' failed
Unhandled exception: 'One or more errors occurred.' in activity '1.12: SqlQuery'.
One or more errors occurred.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</Message></Event>
I can run the SQL query directly in SQL Server just fine (it takes about 2 mins).
Is there a timeout setting that I can change?
Thanks,
Chris
-
Hi Chris
for a start I would consider what the query is doing.
Is it doing a select and waiting to return values to the user? 2 minutes is a long time for them to wait. How much of the query could you "preprocess"? Have you analysed the query in SQL Sever Management Studio and looked to refine it at all?
If it is not waiting to return a value and you are just running a stored procedure then you could use an asynchronous ArcGIS Server geoprocessing task to start it and then the viewer could then just happily carry on with doing something else.
Regards
Ralph
0 -
Hi Ralph. I'm playing around with spatial queries using the native geometry data type in SQL Server. I wanted to create a report that showed how much of each selected taxlot is covered with buildings, is classified as vacant etc. The buildings layer (~22,000 records) in particular slows things down.
I included my query below (I edited the @Where clause to include just two parcels. The workflow uses a parameter to pass in a comma-delimited list of parcels). As you can see, I'm no SQL expert, and I'm sure it's horriby inefficient. I only have SQL Express, so I'm not sure if I have access query optimization tools.
Maybe this isn't the most appropriate methodology for creating a report based on many taxlots. The spatial queries are pretty cool though if you are just running them in a data link for a single record.
Thanks,
Chris
DECLARE @XML as XML
DECLARE @WhereClause varchar(max)
Set @WhereClause='2-2E-29DC-01100,2-2E-32CC-06402'
SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
SELECT TOP 100 PERCENT [APN]
, SITUS
, ZONE
, [Acres] / 43560 as [Total Acres]
, [BLDG] / 43560 AS [Building Acres]
, [BLDG]/[Acres] AS [Building Pct]
, [VA] / 43560 as [Vac Acres]
, [VA]/[Acres] AS [Vac Pct]
, [CVA] / 43560 AS [Constrained Vac Acres]
, [CVA]/[Acres] AS [Constrained Vac Pct]
, [UVA] / 43560 AS [Unconstrained Vac Acres]
, [UVA]/[Acres] AS [Unconstrained Vac Pct]
FROM
(
SELECT APN
, SITUS
, ZONE
,sum(parcels.Shape.STArea()) as [Acres]
, IsNull((
SELECT Sum(vac1.Shape.STIntersection(par1.Shape).STArea())
FROM [SpatialGeometry].[dbo].[PLANNING_VACANT_LANDS_CLIPPED] AS vac1,
[SpatialGeometry].[dbo].[BASE_TAXLOTS] AS par1
WHERE par1.APN = parcels.APN
AND vac1.Shape.STIntersects(par1.Shape) = 1
GROUP BY par1.APN
),0) AS [VA]
, IsNull((
SELECT Sum(vac2.Shape.STIntersection(par2.Shape).STArea())
FROM [SpatialGeometry].[dbo].[PLANNING_VACANT_LANDS_CONSTRAINED] AS vac2,
[SpatialGeometry].[dbo].[BASE_TAXLOTS] AS par2
WHERE par2.APN = parcels.APN
AND vac2.Shape.STIntersects(par2.Shape) = 1
GROUP BY par2.APN
),0) AS [CVA]
, IsNull((
SELECT Sum(vac3.Shape.STIntersection(par3.Shape).STArea())
FROM [SpatialGeometry].[dbo].[PLANNING_VACANT_LANDS_UNCONSTRAINED] AS vac3,
[SpatialGeometry].[dbo].[BASE_TAXLOTS] AS par3
WHERE par3.APN = parcels.APN
AND vac3.Shape.STIntersects(par3.Shape) = 1
GROUP BY par3.APN
),0) AS [UVA]
, IsNull((
SELECT Sum(bldg.Shape.STIntersection(par4.Shape).STArea())
FROM [SpatialGeometry].[dbo].[BASE_BUILDINGS] AS bldg,
[SpatialGeometry].[dbo].[BASE_TAXLOTS] AS par4
WHERE par4.APN = parcels.APN
AND bldg.Shape.STIntersects(par4.Shape) = 1
GROUP BY par4.APN
),0) AS [BLDG]
FROM [SpatialGeometry].[dbo].[BASE_TAXLOTS] AS parcels
WHERE parcels.APN in (SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A))
GROUP BY APN, SITUS, ZONE
) DUMMY_TABLE
ORDER BY APN ASC
0 -
Hi Chris
how about rather than making a 'DummyTable', insert the selection result into a new table, (truncate and re-run each night) and get some performance back?
Then you could do on the fly selections in workflows etc in the data for users to be able to further slice and dice the data.
regards
Ralph
0 -
Ralph, I took your suggestion and created an actual table with the query. Took 4 1/2 hrs to run it on the whole parcel layer (16,000 recs), but now the workflow generates a report very quickly. Thanks for your help!
Chris
0 -
Good morning Chris
sounds like good progress. I am sure the users will prefer quick reports and don't care about a bit of preprocessing. ;-)
Regards
Ralph
0
Du måste logga in om du vill lämna en kommentar.
Kommentarer
5 kommentarer