Goodtech

Huge query responds time - Multi treading problem?

Discussion created by Goodtech on Nov 16, 2011
Latest reply on May 21, 2012 by josef`04

We have implemented a .NET application (a service) that will query PI records from piarchive..picomp2 periodically. We use PI OleDB interface towards PI. The service, i.e  OleDb client, is running on a computer different from the PI server. The number of tags that will be queried are approx. 3000. They will be queried for records automatically about every 60 seconds. We are grouping the tags in groups of 10-500 tags and each group of tags is queried in a separate thread. We use the multithreaded connection string option  "Session ID=-1;"

 

 A query typically looks like the following:

 
SELECT tag, time, value, status, DIGSTRING(status) statusText 
FROM piarchive..picomp2 
WHERE tag IN ( '18DIP0291/Y/10SSAMP' , '18DIQ0291/Y/10SSAMP' , '18ZTR2221/Y/10SSAMP' , '18ZTR4005/Y/10SSAMP' , '18ZTR4221/Y/10SSAMP' ) 
AND time > CAST('2011-11-07 18:30:00.0000000' as DateTime) 
AND time <= CAST('2011-11-07 19:30:00.0000000' as DateTime) 
ORDER BY tag, time ASC

 

 

 

 

The connection string is like:

 

Provider = PIOLEDB; Data Source = BE-W110; User ID = piadmin; Password =; Time Zone = UTC; Session ID = -1; Command Timeout = 180;

 

 

 

We have tested this solution successfully running the OleDB client on OS: Windows Server 2003 R2

 

 

 

Problem:

 

Installed the Oledb (32bit .Net service) on a new computer with OS: Windows Server 2008 R2.

 

We have installed both 32 and 64bit version of PI-OleDB/SDK on the new computer. How can you find out what version is actually in use by our OleDB client service?

 

The Oledb client is running successfully with one tag of groups, but if 2 groups of tags are queried - in separate threads - the response time from the ExecuteReader operation is about several times longer than running only queries for one group (i.e one thread).

 

Numbers:

 

Query responds for a query with 852 tag names.

 

Running the system with one group only: 15 seconds. This is acceptable!!

 

Running the system with two groups - i.e. multi-threading: 120 seconds

 

Anyone have an idea why the responds time is increasing?

Outcomes