During optimization, SQL Optimizer uses a unique Artificial Intelligence engine to execute SQL syntax rules that produce semantically equivalent SQL statement alternatives. The optimization process begins with SQL Optimizer generating alternatives from the original SQL statement. SQL Optimizer then rewrites each alternative to generate additional alternatives. This process continues until SQL Optimizer cannot generate additional alternatives or reaches a set of user-defined quotas. The result is a list of alternatives with unique SQL syntax that provides the same results as the original SQL statement.
Afterwards, SQL Optimizer applies SQL Server optimization hints to each alternative. This process continues until SQL Optimizer applies all the optimization hints to all alternatives or reaches a set of user-defined quotas. See Hints Options for more information.
A syntax transformation rule is illustrated in the following example:
SELECT *
FROM table_a
WHERE table_a.key IN (SELECT table_b.key
FROM table_b)
If table_b.key is an indexed column, the following transformation rule is applied:
SELECT *
FROM table_a
WHERE EXISTS (SELECT 'x'
FROM table_b
WHERE table_b.key = table_a.key)
Although the previous two SQL statements produce the same result, SQL Server may use different execution plans to retrieve the data. The database structure, indexes, and data volume affect execution statistics. Therefore, you need to test the alternatives generated with your database environment in order to find the best alternative.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center
