SQL Execution

When the Query Processor has completed the SQL Join Optimization phase, it proceeds to the execution phase, which is the final phase that is undertaken. During the execution phase, the Query Processor:

1. Evaluates the ‘special condition’ for any no-dependency sub-clauses (sub-clauses that do not reference a table):
   • If your query contains no-dependency sub-clauses and they have ‘special conditions’ that evaluate to true, your query is executed. The Query Processor will perform the relevant search and return the appropriate result set.
   • If your query contains no-dependency sub-clauses, and one or more of those sub-clauses has a ‘special condition’ that evaluates to false, your query is not executed. Your search is not performed and an empty result set is returned (steps 2-4 inclusive of the execution phase are not applied to your query).
   • If your query does not contain no-dependency sub-clauses, your query is executed. The Query Processor will perform the relevant search and return the appropriate result set.
2. Next, the Query Processor reads the rows of the referenced table(s). The access path of the referenced table is used to determine which of the rows are read:
   • If the access path is Scan, each of the rows are read. However, if constraints are in place, the scan may be limited to rows that match certain criteria, for example, a filter may limit the scan to rows in a specified time range.
   • If the access path is Non-Unique Lookup by Value, the Query Processor reads those rows that have an index value that matches the value specified in the condition.
   • If the access path is Unique Lookup by Value, the Query Processor reads the single row that has an index value that matches the value specified in the condition.
3. With the rows read, the Query Processor proceeds by merging the row sets (this only applies if your query references multiple tables).
4. Finally, the Query Processor reads each row in the row set. For each row in the row set, it creates a row that is added to the result set. The row that is added to the row set has the appropriate values for that row (the values that were requested in your query).
5. Any features that affect a row’s place in the result set are applied independently for each row. So, ordering, grouping, TOP etc., are applied to each row as it is added to the result set. This is much more efficient than applying ordering after the rows have been added to the result set.

When the Query Processor has added a row to the Result Set for each row in the Row Set, the execution phase is completed and the results are returned.

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.