Exprosoft

Timeout in PI query from our code, but lightening quick response using PI OleDBTester: why?

Discussion created by Exprosoft on Jan 3, 2014
Latest reply on Jan 17, 2014 by RJKSolutions

Hello clever PI people!

 

We have a customer installation that is causing us problems. Data queries from our application to the customers'  PI Server (using PI OleDB) is causing  timeouts. When we try to copy the query and execute through PI OleDBTEster, we get responses very quickly!

 

Here is what is going on as seen in the log file when our application is queriying the PI server:

 

03-Jan-2014 14:14:02.762        INTERFACE_INPUT         webWims           6700     2             4064     Command                ICommandText SetCommandText           pwszCommand =              SELECT tag, time, value FROM piarchive.pisnapshot WHERE tag in ('WIMS_A01_AA','WIMS_A01_AB','WIMS_A01_AC', <lots of tags deleted for clarity>,'WIMS_D16_AC','PI26292/AI1/PV.CV','WIMS_D17_AA','WIMS_D17_AB','WIMS_D17_AC','PI26112/AI1/PV.CV','WIMS_D18_AA','WIMS_D18_AB','WIMS_D18_AC') ORDER By tag                                   ;

 

03-Jan-2014 14:14:02.762        INTERFACE_CALL             webWims           6700     2             4064     Data Source                IDBProperties    GetProperties   

 

03-Jan-2014 14:14:02.762        INTERFACE_INPUT         webWims           6700     2             4064     Data Source                IDBProperties    GetProperties    cPropertySets = 1; i = 0; rgPropertySets.guidPropertySet = DBPROPSET_DATASOURCEINFO; rgPropertySets.cPropertyIDs = 1; j = 0; ulPropertyId = DBPROP_MULTIPLERESULTS;

 

03-Jan-2014 14:14:02.762        INTERFACE_CALL             webWims           6700     2             4064     Command                ICommand         Execute

 

03-Jan-2014 14:14:02.762        SQL_ENGINE_INFO_1   webWims           6700     2             4064     SQL Engine                                         Command to prepare:              SELECT tag, time, value FROM piarchive.pisnapshot WHERE tag in ('WIMS_A01_AA','WIMS_A01_AB','WIMS_A01_AC', <lots of tags deleted for clarity>,'WIMS_D16_AC','PI26292/AI1/PV.CV','WIMS_D17_AA','WIMS_D17_AB','WIMS_D17_AC','PI26112/AI1/PV.CV','WIMS_D18_AA','WIMS_D18_AB','WIMS_D18_AC') ORDER By tag                                  

 

03-Jan-2014 14:14:02.98          SQL_ENGINE_INFO_2   webWims           6700     2             3844     SQL Engine                                         [piarchive..pisnapshot pisnapshot] [PI SDK] Server->GetPoints search: WhereClause = ("tag" = "WIMS_A01_AA") OR ("tag" = "WIMS_A01_AB") OR ("tag" = "WIMS_A01_AC") OR ("tag" = "PI0433/AI1/PV.CV") OR ("tag" = "WIMS_A02_AA") OR ("tag" = "WIMS_A02_AB") OR ("tag" = "WIMS_A02_AC") OR <lots of tags deleted here for clarity> OR ("tag" = "WIMS_D18_AB") OR ("tag" = "WIMS_D18_AC")

 

03-Jan-2014 14:14:44.229        ERROR_RECORD              webWims           6700     2             4064     Command                ICommand         Execute E_FAIL - Timeout has expired.

 

03-Jan-2014 14:14:44.229        INTERFACE_ERROR         webWims           6700     2             4064     Command                ICommand         Execute E_FAIL

 

THat is, an error caused by a timeout after around 40 seconds.

 

And here is the log from the identical query, using PI OleDb Tester:

 

03-Jan-2014 14:09:28.795        INTERFACE_INPUT         PIOLEDBTester  6812     2             6816     Command                ICommandText SetCommandText           pwszCommand = SELECT tag, time, value FROM piarchive.pisnapshot WHERE tag in ('WIMS_A01_AA','WIMS_A01_AB','WIMS_A01_AC','PI0433/AI1/PV.CV','WIMS_A02_AA','WIMS_A02_AB','WIMS_A02_AC',<lots of tags deleted for clairity>,'WIMS_D18_AB','WIMS_D18_AC') ORDER By tag;

 

03-Jan-2014 14:09:28.795        INTERFACE_CALL             PIOLEDBTester  6812     2             6816     Command                ICommand         Execute

 

03-Jan-2014 14:09:28.795        SQL_ENGINE_INFO_1   PIOLEDBTester  6812     2             6816     SQL Engine                                         Command to prepare: SELECT tag, time, value FROM piarchive.pisnapshot WHERE tag in ('WIMS_A01_AA','WIMS_A01_AB','WIMS_A01_AC','PI0433/AI1/PV.CV','WIMS_A02_AA','WIMS_A02_AB','WIMS_A02_AC','PI0431/AI1/PV.CV','WIMS_A03_AA','WIMS_A03_AB', <lots of tags deleted for clarity>,'WIMS_D18_AB','WIMS_D18_AC') ORDER By tag

 

03-Jan-2014 14:09:28.795        SQL_ENGINE_INFO_2   PIOLEDBTester  6812     2             5856     SQL Engine                                         [piarchive..pisnapshot pisnapshot] [PI SDK] Server->GetPoints search: WhereClause = ("tag" = "WIMS_A01_AA") OR ("tag" = "WIMS_A01_AB") OR ("tag" = "WIMS_A01_AC") OR ("tag" = "PI0433/AI1/PV.CV") OR ("tag" = "WIMS_A02_AA") OR ("tag" = "WIMS_A02_AB") OR ("tag" = "WIMS_A02_AC") OR ("tag" = "PI0431/AI1/PV.CV") OR ("tag" = "WIMS_A03_AA") OR ("tag" = "WIMS_A03_AB") OR ("tag" = "WIMS_A03_AC") OR ("tag" = "PI0432/AI1/PV.CV") OR <lots of tags deleted here for clarity> OR ("tag" = "WIMS_D18_AC")

 

03-Jan-2014 14:09:29.2             SQL_ENGINE_INFO_1   PIOLEDBTester  6812     2             5856     SQL Engine                                         Execution time: 00:00:00

 

03-Jan-2014 14:09:29.2             INTERFACE_WARNING  PIOLEDBTester  6812     2             6816     Command                ICommand         Execute DB_S_ERRORSOCCURRED

 

I can not see why the same query is behaving so very differently.

 

Are there any connection string properties we need to set? Are there any other logs that can help us investigate this further? This query is working fine at other customer sites - and also at our own PI test servers.

 

All and any help would be greatly appreciated!

 

Regards, 

 

Eystein at Exprosoft

Outcomes