When retrieve a large amount of data through PI-OLEDB and the query last more than 60 sec, we end up in a time out situation.
Where can we adjust this time limit settings?
Have you tried adding
or similar to the Connection String ?
In PI SQL Commander you'd have to extend the Connect dialog by clicking "Options >>" and add this as Extended Properties
Thanks, gone try this as soon as possible.
I would be interested about why the query is timing out. This usually indicates you are requesting a huge amount of events (rows) in one call. If you query events over larger periods of time for a larger amount of tags, this can be pretty expensive to the PI Server. There exist tuning parameters intended to protect the PI Server from expensive queries. You may run into an error when using a command timeout of 5 minutes as suggested by Michael (thank you about that Michael ) because the amount of events queried exceeds the maximum number of events allowed by tuning parameter for a single call. For sure the tuning parameter can be changed but we consider taking care on the client side to retrieve data in several calls is the better option. This can be achieved by limiting the amount of tags involved and by querying smaller periods. Both is done formulating a proper WHERE clause.
Yes we are pulling a lot of data, we talk about 90 million values. We have developed a export tool based on the PI-SDK, we are able to export several Gb of data over a period of several hours.
We will look into breaking up the query in smaller parts, but still I think we need to tune the parameters for handling this query.
Which parameters do we tune?
Where do we find the time out limit?
The tuning parameter that I was referring to is ArcMaxCollect and specifies the maximum compressed (archived) events that can be returned by a single query.
Can you please describe your use case? What's the purpose of this export tool? What are you doing with that huge amount of data? Where is it going and why? Is this a one time operation or a process repeatedly executed?
The reason I am asking is that someone within the community may have done similar and might be able to provide you with some guidance. Also we may want to add some suggestions based on what we at OSIsoft consider good practice.
This is a development project which has never been done before, the customer need to analyze a huge amount of data to try to understand how this is working. The analysis is done in MATLAB. Based on experience over time the need for export of huge data will decrease.
The export is a daily process the first part of the project, I expect that the export will be done when required when the project are tuned.
From big to small sounds like an unusual approach to me but the fact that I don't understand the use case doesn't mean it's wrong. I however like to refer you to Ahmad's blog post about The Power of Data: Analytics and the discussion about "BIG DATA"; reality or hype?
With regards to your project, I assume you would store / cache the data retrieved somewhere. This still allows you to break one "big" query into smaller ones and execute them one by one. For example you may want to request a list of points (PI tags) first and later retrieve each single PI points history over "reasonable" periods like a year or a month. Also if my assumption is right that you are storing the data somewhere in between PI and MATLAB, why not reusing this data pool or do you expect the PI data to change for historical periods?
The use case is irrelevant, the customer need this huge amount of data that was real matter!
We will try to solve the issue by adjusting the tuning parameter and breaking up the query.
Retrieving data ...