SQL Performance Tuning

Discussion created by caosy on Aug 6, 2013
Latest reply on Aug 6, 2013 by caosy

I get the raw data for the same time period each day over a period of time in the following SQL.


select pc.*


from (SELECT time+time('1:1:0') dtFrom, time+time('10:30:0') dtTo


      FROM [piarchive].[piinterp2]


      WHERE tag = 'XXXX'


      and time between '2013/7/1' and '2013/7/31'


      AND timestep = '1d') t,


   picomp2 pc


where pc.tag = 'XXXX'


and pc.time between t.dtFrom and t.dtTo


option (force order)




Is executed in PISQLCommander by setting in the following three patterns the search period.


If set to (time between '2013 / 7/31 'and '2013 / 7/31')  one day, it takes 0.031 seconds.


If set to (time between '2013 / 7/30 'and '2013 / 7/31')  two days, it takes 3.166 seconds.


If set to (time between '2013 / 7/1 'and '2013 / 7/31')  one month, fail with a timeout.




Would you have a way to improve the SQL above.