Calculate the Cost and Determine the Final Access Plan

With the access plan for each permutation established (see Calculate the Table Order Permutations), the Query Processor can begin calculating the cost of each access plan. The cost is the demand the access plan will place on system resources - the access plan with the highest cost is the least efficient and the access plan with the lowest cost is the most efficient (and will be used by the Query Processor for your query).

As part of its calculation for the costs, the Query Processor considers the type of table being searched and the type of access path being used (Scan, Non-Unique Lookup, or Unique Lookup):

When considering the cost and Final Access Plan, you need to be aware that:

• The Query Processor approximates the number of database items or aggregates in a table by using the nearest database index. This number is reduced by a factor of 100 to represent the fact that scanning items is quicker than scanning the same number of historic records.
• If an Id or Source constraint has been applied to an Event Journal scan, the cost is the average number of records per database item multiplied by the number of items that match the constraint. If the constraint includes LIKEs, the number of items that match each LIKE is approximated as 1000.
• If a RecordTime constraint has been applied to an Event Journal, Point Historic or Processed Historic lookup, the cost is reduced by a factor of 100.
• Historic tables cannot be scanned (it is impossible to retrieve every historic record for every database item). Any access plan that contains a historic scan is rejected outright, and if all of the access plan permutations for a query involve a historic scan, the entire query is rejected.

  You can achieve a similar effect to a historic scan by using:

  SELECT ... FROM CDBHistoric WHERE Id IN ( SELECT Id FROM CHistory )

  However, we recommend that you do not attempt this unless you are certain that the database does not contain large amounts of historic data.

• If the cost of a table is calculated as 0 (e.g. there are no objects in that table), that table is not used to calculate the overall cost of the query. If a table with a cost of 0 was used in the calculation, it is possible that an empty table at the beginning of the table order could cause the Query Processor to choose an access plan with a high cost. To avoid this situation, tables with a cost of 0 are ignored.
• If there are no access sub-clauses that reference two tables, or there is only one permutation, then the join order specified in the query is used. In this case, access and link sub-clauses are still assigned to tables to determine access paths, but the cost calculation is skipped.

When the cost of each access plan has been calculated, the Query Processor chooses the access plan with the lowest cost as the Final Access Plan (the access plan that will be used to retrieve the values requested in your Query). If there are multiple access plans with the same lowest cost, the Query Processor will use the first access plan that it calculated with that cost. For example, if there are 4 access plans that have the lowest cost of 1, the Query Processor will use the first access plan that it calculated (out of those 4 permutations).

The cost of each access plan and details of the Final Access Plan can be found in the database log file (see Display a Server Log File in the ClearSCADA Guide to Server Administration).

When the Query Processor has chosen the appropriate access path for the Final Access Plan, it begins the SQL Query Execution Process (see SQL Execution).

You can use the Query Processor section of the ClearSCADA Server Status Tool to ascertain information about the latest, largest, and longest SQL queries. This includes information such as the time taken to parse and verify (validate) a query, along with the type of optimization used, and the number of rows that the query returns.