ACL427

Extract Data to SQL Server: PI-OLEDB or SQL?

Discussion created by ACL427 on Feb 23, 2010
Latest reply on Feb 23, 2010 by michaelh

Hi,

 

I have a requirement to write a process that will export the 10 minute averages on about 10,000 points every 10 minutes - to SQL Server.

 

I've been trying to use SQL but not getting very good results. The query runs about 6.5 minutes. I'll include what I'm doing below - any suggestions to improved performance are greatly appreciated.  I'm using a union - is there a better way?

 

I havn't tried doing this with the SDK as I"ve done most things with PI-OLEDB. However, these SQL calls are converted to SDK and back correct? Should I expect a considerable improvement using SDK?

SELECT * FROM OPENQUERY(PI,
'SELECT distinct tag,time,CAST(value As String) AS value
FROM piavg
WHERE tag IN ( Select tag from pipoint..classic WHERE tag LIKE ''%.WT%.WSP'')
   AND time Between ''-10m'' AND ''*''
UNION
SELECT distinct tag,time,CAST(value As String) AS value
FROM piavg
WHERE tag IN ( Select tag from pipoint..classic WHERE tag LIKE ''%.WT%.P_ACT'')
   AND time Between ''-10m'' AND ''*''
UNION
SELECT distinct tag,time,CAST(value As String) AS value
FROM piavg
WHERE tag IN ( Select tag from pipoint..classic WHERE tag LIKE ''%.WT%.POS_NAC'')
   AND time Between ''-10m'' AND ''*''
UNION
SELECT distinct tag,time,CAST(value As String) AS value
FROM piavg
WHERE tag IN ( Select tag from pipoint..classic WHERE tag LIKE ''%.WT%.OS'')
   AND time Between ''-10m'' AND ''*''
'
)

Outcomes