Inefficient SQL Queries

SQL Queries can cause a high load on a server. In particular, SQL Queries that request a large amount of unfiltered historic data, for example, all historic data over a year.

You can use the Queries category to check the status of any SQL queries that are requesting data from the server.

To check the status of SQL Queries and attempt to resolve any SQL-related problems:

1. Run the Server Status Tool.
2. Expand the system, then the Query Processor folder, then select the Latest Queries category.
3. Examine the entries in the Access Paths column.

   Queries that are likely to cause high load on the server have the following Access Paths status:

   • <database table>=Scan
   • <database table>=Range

   Where <database table> is the name of the table that contains the data requested by the query, for example, CDBEVENTJOURNAL.

   The Scan status indicates that the query is poorly constructed and usually relates to an unfiltered query, for example, a query that requests historic data without specifying a time range or specific points.

   The Range status also indicates that the query is poorly constructed and relates to a query that defines a time range but does not define specific database items. For example, a query for historic data could have a Range status as although it defines a specific time range, it does not define specific points. This means that the results of the query are based on the historic values of all points in the database.

   The Range status is suitable for Event Journal queries as the Event Journal is intended to show events for all items within a specified time range.

   If there are queries with the Scan status or Range status (except for Event Journal queries), proceed to step 4.

   If there are no queries with the Scan or Range statuses, the SQL queries are unlikely to be causing the high server load. Please refer back to Diagnose and Resolve the Server Load Problems.

4. Identify the source of the query. This is often indicated in the code of the SQL query.
5. Edit the SQL query (using the application in which the SQL query was created) so that:
   • A suitable time range is specified.
   • The query requests data for specific items, not for every item in the database.