caosy

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.

Outcomes