Reduce Execution Time in MYSQL from ASP.NET

Question

i have a table which is relevant GPS application in mysql. whenever i am executing from asp.net application it showing fetal error. so i have decided to run in mysql, it will take more than 1 min(exactly 70 sec) for executing the SP. is it possible to sort out the issues.

Further information:

table 1 : server_gpsdata(it contains gps data. it locks every 10 secs from gps device).

select * from server_gpsdata SD 
INNER JOIN mstcab MC on MC.cabid= SD.cabid 
INNER JOIN server_tblstatus TS on TS.statusid= MC.CabStatusid 
INNER JOIN carmaster CM on CM.carid= MC.carid 
INNER JOIN cabtype CT on CT.cabtypeid= CM.sizeid 
where date(SD.cur_datetime) =current_date and 
      MC.Cabid not in (select D.cabid from trncabdriver D 
                       where date(D.logintime)=current_date) and 
      SD.gpsdataid in (select max(SGD.gpsdataid) from server_gpsdata SGD 
                       where date(SGD.cur_datetime)=current_date 
                       group by SGD.cabid);

We'd need to see the query and the tables involved, probably. — Doug Dawson
– Doug Dawson, Commented Mar 18, 2013 at 12:33
Could you provide more informations like the relevant code and your installation details? — r0-
– r0-, Commented Mar 18, 2013 at 12:33
It would be good try to extend query timeout but you'll have to provide more details about your data access components. — Claudio Redi
– Claudio Redi, Commented Mar 18, 2013 at 12:35
Also the query plan for the query, the structures of the tables involved and any indexes on them. — user359040
– user359040, Commented Mar 18, 2013 at 12:37

score 1 · Accepted Answer · 2013-03-18 14:01:34Z

1

The following should show a significant improvement in performance:

select SD.*, MC.*, TS.*, CM.*, CT.* from 
(select max(SGD.gpsdataid) maxgpsdataid from server_gpsdata SGD 
 where date(SGD.cur_datetime)=current_date 
 group by SGD.cabid) SDM
INNER JOIN server_gpsdata SD ON SDM.maxgpsdataid = SD.gpsdataid
INNER JOIN mstcab MC on MC.cabid= SD.cabid 
INNER JOIN server_tblstatus TS on TS.statusid= MC.CabStatusid 
INNER JOIN carmaster CM on CM.carid= MC.carid 
INNER JOIN cabtype CT on CT.cabtypeid= CM.sizeid 
LEFT JOIN trncabdriver D ON MC.Cabid= D.Cabid AND date(D.logintime)=current_date
where D.Cabid IS NULL

Note that both the existing and proposed queries are returning all columns from the server_gpsdata, mstcab, server_tblstatus, carmaster and cabtype tables - the query is likely to perform better if only the columns that are actually required are selected in the query.

edited Mar 18, 2013 at 14:01

answered Mar 18, 2013 at 13:25

user359040

Sign up to request clarification or add additional context in comments.

3 Comments

Kickstart Over a year ago

Agree with the above, but probably needs the check for SD.cur_datetime adding

user359040 Over a year ago

@Kickstart: Definitely not - the SDM/SGD.cur_datetime check achieves the same effect, while removing the condition from SD should ensure that SD records are accessed via gpsdataid (which should have an index on it).

Kickstart Over a year ago

@ Mark Bannister - mmm. Possibly. It would depend on gpsdataid, and whether it is a unique key for that table. It may not be. For example there could be duplicates with different dates, which would mess up the join from the subselect back onto server_gpsdata. However this would depend entirely on the table design which unfortunately hasn't been specified. If this were the case it could also be fixed by returning the date from the subselect and using that in the join.

Collectives™ on Stack Overflow

Reduce Execution Time in MYSQL from ASP.NET

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related