CODEWITHSUNDEEP
c#asp.netmysqldatabase
VetriJith
VetriJith

Reputation: 9

Reduce Execution Time in MYSQL from ASP.NET

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);

Upvotes: 0

Views: 314

Answers (1)

user359040
user359040

Reputation:

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.

Upvotes: 1

Related Questions