How to reduce query excution time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to reduce query excution time

Author

Topic

baktha.thalapathy
Starting Member

7 Posts

Posted - 2010-04-28 : 09:46:01

SELECT SQL_CALC_FOUND_ROWS GpsTime ,
MAX(StopTime) as mx,
MIN(StartTime) AS mn,
SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(StopTime, StartTime)))) AS TotalWorkTime
FROM ( SELECT GpsTime , GpsTime AS StopTime,
COALESCE(( SELECT MAX(b.GpsTime) FROM xydata b
WHERE ObjectId='17'
AND ClientId='1'
AND GpsTime > '2010-04-20 08:22:27'
AND GpsTime < '2010-04-26 10:22:27'
AND b.ObjectId = a.ObjectId
AND b.GpsTime < a.GpsTime), GpsTime) AS StartTime
FROM xydata a Inner Join fm4features f on f.id=a.id
where ObjectId='17'
and ClientId='1'
AND GpsTime > '2010-04-20 08:22:27'
AND GpsTime < '2010-04-26 10:22:27'
AND f.dataid='1' AND f.value='1' ) derived
group by DATE_FORMAT(GpsTime, '%Y-%m-%d')
order by GpsTime asc

it will take 63 sec i want reduce the sec,

please help which one i want to change

Bakthavachalam E

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-28 : 10:01:51

First we want to see formatted code:

SELECT sql_calc_found_rows                                          gpstime, 
       MAX(stoptime)                                                AS mx, 
       MIN(starttime)                                               AS mn, 
       Sec_to_time(SUM(Time_to_sec(Timediff(stoptime, starttime)))) AS totalworktime 
FROM   (SELECT gpstime, 
               gpstime                                               AS stoptime, 
               Coalesce((SELECT MAX(b.gpstime) 
                         FROM   xydata b 
                         WHERE  objectid = '17' 
                                AND clientid = '1' 
                                AND gpstime > '2010-04-20 08:22:27' 
                                AND gpstime < '2010-04-26 10:22:27' 
                                AND b.objectid = a.objectid 
                                AND b.gpstime < a.gpstime), gpstime) AS starttime 
        FROM   xydata a 
               INNER JOIN fm4features f 
                 ON f.id = a.id 
        WHERE  objectid = '17' 
               AND clientid = '1' 
               AND gpstime > '2010-04-20 08:22:27' 
               AND gpstime < '2010-04-26 10:22:27' 
               AND f.dataid = '1' 
               AND f.VALUE = '1') derived 
GROUP  BY Date_format(gpstime, '%Y-%m-%d') 
ORDER  BY gpstime ASC

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-28 : 10:03:16

Is this MS SQL Server???

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 10:06:00

quote:
Originally posted by webfred

Is this MS SQL Server???

No, you're never too old to Yak'n'Roll if you're too young to die.

It is MySQL

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 10:19:28

I presume there's an index on xydata (objectid, clientid, gpstime)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

baktha.thalapathy
Starting Member

7 Posts

Posted - 2010-04-30 : 03:50:46

Yes i am using Index for ObjectId, ClientId, GpsTime in xydata

Index


Name:        FieldNames                           Index Type
id_objid     ObjectId, ClientId, GpsTime          Normal

Bakthavachalam E

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources