bjsower

Query Optimization for PI-OLEDB Classic

Discussion created by bjsower on Mar 5, 2013
Latest reply on Mar 6, 2013 by bjsower

I have what I think is a very simple query, but can include up to about 70 tags and may look at a time range anywhere from 30 days to 2 years.  The issue I am having is that the older data we try to query the slower the performance.  A query of 30 days from 2 years ago is much slower than a query of the last 30 days.  I suspect it is because it has to load the data from the older archives into memory and the newer archives are already in memory due to users and other apps actively retrieving data from them.  What I am looking for is some assistance to help me optimize this query or recommendations of a more efficient way to retrieve this data.  This is just an example query below.  Ultimately, we would be wanting to use the PIAverage or PITotal tables.  Any help/advice  appreciated.

 

SELECT tag, time, value

 

FROM piarchive..picount

 

WHERE tag

 

IN ('Tag1','Tag2','Tag3', ..., 'TagN')

 

AND time BETWEEN DATE('t-120d') AND DATE('t')

 

AND timestep = '1mo'

Outcomes