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.