Query processor processes SQL queries specified by user. The processor yields highly efficient query plans that execute using minimal resources, and ensure that results are consistent and correct.
To process a query efficiently, the query processor uses:
SQL Query specified by user.
- Statistics about the tables, indexes, and columns specified in the SQL query.
- Configurable variables defined for the ASE.
The query processor uses several query processor modules to successfully executes several steps of process a query
Query Processor modules:
Above modules works as following:
- The parser transalate the SQL query statement (Query sepecified by a user) to an internal form called a query tree. Parser checks syntax, verifies relations.
- This query tree is normalized. This involves determining column and table names, transforming the query tree into conjugate normal form (CNF), and resolving datatypes.
- The preprocessor transforms the query tree for some types of SQL statements, such as SQL statements with subqueries and views, to a more efficient query tree. Basic functions of preprocessor:The optimizer analyzes the possible combinations of operations (parallelism, join ordering,access and join methods) to execute the SQL statement, and selects an efficient one based on the cost estimates of the alternatives. Amongst all equivalent evaluation plans choose the one with lowest cost. Cost is estimated using statistical information.
- If a relation used in the query is view then each use of this relation in the form-list must replace by parser tree that describe the view.
- It is also responsible for semantic checking mentioned below:
- Checks relation uses : Every relation mentioned in FROM clause must be a relation or a view in current schema.
- Check and resolve attribute uses: Every attribute mentioned in SELECT or WHERE clause must be an attribute of same relation in the current scope.
- Check types: All attributes must be of a type appropriate to their uses.
- The code generator converts the query plan generated by the optimizer into a format more suitable for the query execution engine.
- The procedural engine executes command statements such as create table, execute procedure, and declare cursor directly. For data manipulation language (DML) statements, such as select, insert, delete, and update, the engine sets up the execution environment for all query plans and calls the query execution engine.
- The query execution engine executes the ordered steps specified in the query plan provided by the code generator.
Query Processor Improvements in ASE 15:
Performance of index ‐based data access has been improved. Before ASE 15, the optimizer could not use the index if the join columns were of different datatypes. With ASE 15 there are no more issues surrounding mismatched datatypes and index usage. More than one index per table can be used to execute a query. This feature increases the performance of queries containing ors and star joins.
New optimization techniques now try to avoid creating worktables in the query scenario. Worktables were created in the tempdb to perform various tasks including sorting. The creation of worktables slows performance since they are typically resource intensive. ASE 15’s new
hashing technique performs sorting and grouping in memory, thus avoiding the necessity of a worktable. It is the buffer memory and not the procedure cache that is used for this operation. The elimination of the worktables has improved the performance of the queries containing order by and group by statements.
ASE 15 has enhanced the parallelism to handle large data sets. It now handles both horizontal and vertical parallelism. Vertical parallelism provides the ability to use multiple CPUs at the same time to run one or more operations of a single query. Horizontal parallelism allows the query to access different data located on different partitions or disk devices at the same time.