Calculate Run time Query not Working

hi experts

I m working at GPS project and i want to calculate the total vehicle working time . The time selected by client or user.

I m using below query and its working but its very slow it is taking 3-4 minutes to calculate only 1 day vehicle driven time....

please help me to improve this query performance and time ...
SELECT SQL_CALC_FOUND_ROWS Lat,Longi, ID, Speed,ObjectId,GpsTime   , MAX(StopTime) as mx, MIN(StartTime) AS mn, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(StopTime, StartTime)))) AS TotalWorkTime  FROM ( sELECT Lat,Longi, ID, Speed,ObjectId,GpsTime    , GpsTime AS StopTime    , COALESCE((  SELECT MAX(b.GpsTime)   FROM xydata b   WHERE ObjectId='" + Objectid + "' and ClientId='" + id + "'  AND GpsTime > '" + from+"  " +result + "' AND  GpsTime < '" + to+"  " +result1 + "' AND  b.ObjectId = a.ObjectId  AND b.GpsTime < a.GpsTime  ),GpsTime) AS StartTime  fROM xydata a where ObjectId='" + Objectid + "' and ClientId='" + id + "' AND GpsTime > '01-08-2010 00:00:01' AND  GpsTime < '01-08-2010 11:59:59' AND Speed >  '3' ) derived Group by Lat  order by GpsTime asc  limit 1,700

Open in new window

Sachin_AjmaniAsked:
Who is Participating?
 
Zlatko KuzmanovskiConnect With a Mentor programmerCommented:
Hi,

This should work:

Add an index on xydata table, on these fields:
-ObjectId
-ClientId

Please preserve the column ordering in the index.
Please let me know...

Note: If such index is already present, please add column: GpsTime. last to the above.

Regards,
Zlatko.
0
 
Sachin_AjmaniAuthor Commented:
hi ZLATKO:

ObjectId and GpsTime is already indexes and they both present in  xydata table but ClientId is not index ...

i will check my query after changes ClientId into Index and i will tell you and please reply me about your note...

Note: If such index is already present, please add column: GpsTime. last to the above.

I m not getting this...


Thanks for reply....
0
 
Zlatko KuzmanovskiConnect With a Mentor programmerCommented:
Hi,

In order to have your query fastened, multi-column index must be present, not separate index on ObjectId, and/or GpsTime. The index should include the two (or three) columns in itself.

Regards,
Zlatko.
slika.JPG
0
 
Sachin_AjmaniAuthor Commented:
hi ZLATKO:

Thank you so much...
now my query is executing very fast ....

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.