PI Datalink Queries - long execution time

Discussion created by caffreys_col on Jan 8, 2020

I have a user that is using an Excel sheet to do some analysis using about 24 PI tags. He is calculating averages using the PI Datalink function, but for some reason they are taking longer than usual. I'm just wanting to understand how the Datalink query is processed, so I can try and fault find the real issue.



We have 2 servers with a PI-to-PI link; 1 connected to the DCS, the other on the IT network.

We have recently moved to scanning all digital and analogues tags at 1 second on the DCS facing PI server, which means we are getting more data. Compression and Exception values are typically 1/0.5% of span respectively.

The PI-to-PI interface runs at 1second scan rates - no exception or compression takes place (the data on both servers should be almost identical).

The user is querying the IT facing PI server, using datalink on a client PC.


My guess is that the increased scan rates (and hence more data), is resulting in the query having to process more data points. I'm not sure if the Datalink query is processed on the PI server, or whether the client PC requests the raw data and then the actual calculation is performed using the client PC's resources. The user is blaming the speed of the client PC, but I think it's due to the increase in data points.

I have set Excel not to auto recalculate, and also set the Datalink update rate to 0 (only recalculate on F9). I'm thinking that when new data is added to the sheet, that the entire workbook is being recalculated resulting on long execution times.


Are there any PI server settings I can change to improve the performance of the queries?

I have asked the user what calculations can be performed on the PI server, which should take the loading of the client PC. It seems this is a report that is run weekly so I see no reason why the calcs can't be done on the PI server. 


All recommendations welcome!