IN Statement

Discussion created by jonmorris on Mar 3, 2010
Latest reply on Mar 4, 2010 by michaelh

I’m using this select statement to query data from about 200 pi tags at once:

SELECT pi1.tag, pi1.time, pi1.value 
FROM [PI-SERVER].[piarchive]..[piinterp2] pi1 
WHERE pi1.tag IN (SELECT SA.Pitag FROM Sources S
INNER JOIN SourceAnalyte SA ON S.ID = SA.SourceID) 
AND pi1.time BETWEEN ‘1/23/2010’ AND ‘1/23/2010 1:00 AM’ 
AND pi1.timestep = @timestep

This query works fine on our developmental PI Server but at the client's site it times out.  I am only requesting about 200 records.  Interestingly, if I change the select statement for the IN clause so that it only returns one value (IE one PI Tag), the query works fine.  If the select for the IN clause returns 2 values (IE two pi tags) or more, the query times out.  What could be different between the PI-SERVER and OLEDB on our development site verses the client site which could explain this?  The alternative is to build a literal string with 200 individual PI tags, which will probably work but is undesirable.