Query layer performance is very slow

2119
0
03-03-2016 09:14 AM
AzizaParveen2
New Contributor III

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   

on       

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.

0 Kudos
0 Replies