Problem AES Screen, query take long time

[miks]

I'm using WinCC OA Version 3.11 and I have problems on accessing historical data by AES screen.

I save strings to a value archives to store events.
Value changes, Commands, Attribute changes like alert limit changes; all are written to a special DPEs (.msg) which every DP have and they got an archive config.

I have about 29.000 elements in the value archive configured for 1580 entries (giving the maximum file size of 2GB).
Of course this are big archives and the query may take long time; but if I query the same information by SystemManagment/Reports/SQL-Query the result arrives much faster.

The SQL query string "SELECT ALL '_online.,._value' FROM '*.*.*.msg' TIMERANGE("2013.10.15 12:00:00","2013.10.15 13:00:00",1,0)
It returns 26104 lines within query time 5.044 seconds, total time 6.251 seconds
The same query in AES screen returns Too much data error.

If I query the same but only a timerange of 5 minutes SQL returns 14866 lines within query time 4.884 seconds, total time 5.579 seconds
The same query in AES screen take nearly one minute.

The customer like to query a whole day or even wider timeranges.

Why is SQL query so much faster and how I can speed up AES screen to reach answer times like SQL query?

How can I speed up thing generally (quering a whole day by SQL-Query also give a to much data error)?

All these string are written in one value archive. It should not be necessary to query all archives.
Can I specify to query only this special value archive, not all?

Thank You.

[leoknipp]

Hello,

if you want to read historical information saved in the value archives you have to use SELECT '' instead of SELECT ALL.
You also should use the config _offline and its attributes. With the keyword ARCHIVENUM at the SQL-statement you can define which archive shall be queried.

If you get an amount of 26104 lines for one hour you will have about 600.000 lines for one day.
Please note that reading this amount of data may be very difficult.

Saving all changes at the configuration at a datapoint-element is uncommon.
Please check with your customer if it is really necessary to store all this information.

How long shall this information be available in your project?

The size of queries in WinCC OA is limited to avoid an overload situation caused by huge queries.
The AEScreen is reading several attributes for the _offline-config and therefore the limits may be exceeded. If a query-limit is exceeded you should get a log-message at the server and/or the the client.
Also the AESceen is formatting the result and displayes it in the table, therefore it takes longer than a simple SQL-command.

At the chapter "Installation" --> "Error tolerance and stability" in the online help it is described which functions are used to react on an overload situation.

Best Regards
Leopold Knipp
Senior Support Specialist

[miks]

Dear Leopold, thank you for this informations.

I'm trying the ARCHIVENUM Option in SQL queries.
Unfortunately this option does not have a big impact on query times.

The following statements deliver about 29.000 lines (time is average of 6 executions):
SELECT '_offline.._value' FROM '*.*.*.msg' TIMERANGE("2013.10.15 11:00:00","2013.10.15 11:05:00",1,0) ARCHIVENUM 7 -> ~5.53 seconds
SELECT '_offline.._value' FROM '*.*.*.msg' TIMERANGE("2013.10.15 11:00:00","2013.10.15 11:05:00",1,0) -> ~5.55 seconds
SELECT ALL'_offline.._value' FROM '*.*.*.msg' TIMERANGE("2013.10.15 11:00:00","2013.10.15 11:05:00",1,0) ~5.47
SELECT ALL '_offline.._value' FROM '*.*.*.msg' TIMERANGE("2013.10.15 11:00:00","2013.10.15 11:05:00",1,0) ARCHIVENUM 7 -> ~5.52

I'm aware that there are many other influences on the query time, but at least it looks like it does not speed up significant. :dry:

I will evaluate the use of 3.11+SP1 and patch P019 as there are some data manager and historical alarm query improvements.

[leoknipp]

Hello,

the data-manager has the information in which archive the dp-elements are saved which fit the query-statement. Therefore it send the data request only to the archives which contain the requested information.
If the datapoint elements which fit the FROM-statement are saved in several archives you might get greater differences for the query times when using the ARCHIVENUM-option.

As described in my previous posting SELECT ALL shall not be used.
If you want to read historical data you have to use the keyword SELECT, to read historical alerts SELECT ALERT has to be used.

Please check again with your customer if it is really necessary to store all changes.
Getting 29000 results for a query for 5 minutes is very uncommon.

Best Regards
Leopold Knipp
Senior Support Specialist