Database Performance Problem

[blackout]

Hi,

In my project, I am using WinCC OA database in order to keep logged entries.
In the current system, I have log entries deserialized to datapoints and it is archived by state.
I use a single datapoint with some defined elements and all of them are archived over one "archive" config under the datapoint node. (and I check timestamp to understand if two different dp elements archived values belong to same log item or not)

Currently, for example in order to show filtered log entries in a table view, I use some unique id value in SQL queries but as long as the number of entries increases, it takes more time to get the SQL query results.

Does anyone have any suggestions for me to improve WinCC OA SQL query performance ? (like how to prepare a relational database structure in wincc oa for log entries or using something like a hash table to hold some log-db relation to improve query performance)

-> LogEntry
- _archive
- _common
- _lock
+ Category(string)
+ Owner (string)
+ Description (string)
+ Text (string)
+ KeyWords (dyn_string)
+ OpenTime (stime)



Best Regards https://www.winccoa.com/fileadmin/image ... les/dp.png

[leoknipp]

Can you please describe what the use case is.
What is a "log entry" in this case?
How often is such a log entry written?
How does the query look like you are currently using?
Is there a need to read this data again in WinCC OA?

If you want to use a relational database you can use Oracle archiving with the WinCC OA RDB manager.

Best Regards
Leopold Knipp
Senior Support Specialist

[blackout]

Hi,

You can consider the use case as a logging mechanism I have created.. for example, you can consider it like a user can enter some log entries from a panel, and another user can apply some filtering (which is an sql query being executed) and get some relevant log entries from a database..
I also use the very same mechanism in order to archive message exchanges (-over a custom communication protocol-) between WinCC OA and field devices. So in this context, all of the archived values can be considered as "log items".

Log entries (archived values) are not planned to be deleted from the database, only the evacuation of historical data will be handled by archive manager.

A simple query looks like :

"SELECT 'ID:_original.._value','Category:_original.._value', 'Owner:_original.._value', 'Text:_original.._value','OpenTime:_original.._value', 'Keywords:_original.._value','Description:_original.._value',"
" 'ID:_original.._stime', 'Owner:_original.._stime' FROM 'LogEntry*' WHERE _DPT = \\"LogEntry\\" AND 'ID:_original.._stime' == 'Owner:_original.._stime' AND 'Category:_original.._stime' == 'Owner:_original.._stime'"+ "Some sql condition like .. AND .. LIKE ...." +
" TIMERANGE(\\""some valid timerange start"\\",\\"" + some valid timerange end + "\\",1,0)";

[Gertjan van Schijndel]

The WinCC OA database is not very suited to run complex queries against.
Is it not an option to store these log entries in an external database?