Query layer performance is very slow

AzizaParveen2 — Thu, 03 Mar 2016 17:14:26 GMT

Dear GeoNet Users,

I am working on a web application where I need to show current AVL locations of each our fire engines/trucks from a regular sql table which is managed by third party, so we don't have the luxury to add a geometry column into the table, so I am doing everything on the fly using a inner join. here is my query

Select VehDataLog.StudentName, vehDataLog.VDLogNo, vehDataLog.TimeLog,

status =

CASE

WHEN (DATEDIFF(MINUTE, tm.LatestTimeRec,getutcdate()) <= 5) THEN 1

ELSE 2

END,

Geometry::Point(VehDataLog.CoX,VehDataLog.CoY, 4326) AS SHAPE from VehDataLog

inner join(select StudentName, max(TimeLog) as LatestTimeRec

from VehDataLog

where VehDataLog.TimeLog <= getutcdate() and DATEDIFF(day,VehDataLog.TimeLog, getutcdate()) between 0 and 30 and StudentName not like 'AA%' and StudentName not like 'AT%' group by StudentName) tm

VehDataLog.StudentName = tm.StudentName

and VehDataLog.TimeLog = tm.LatestTimeRec

If I run this query in SQL server management studio, it takes about 4 secs , I published it as map service and it take about 30 sec to draw, Zoom in/ out is painfully slow. Do you guys have any suggestion to improve the performance? thanks

Aziza.

topic Query layer performance is very slow in ArcGIS JavaScript Maps SDK Questions

Query layer performance is very slow