Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Evaluating How Filtering Options Affect T-SQL Performance  : Page 2

The many popular rules concerning T-SQL filtering operators can't be trusted implicitly; instead, you should evaluate your options explicitly.


advertisement
Testing a Sample Query
To illustrate the testing process, this article uses the AdventureWorks sample database, testing several variations of a query that retrieves sales details for three specific products. You can assume that the results will be displayed in a GridView object in a web application. The GridView object gets populated when a postback occurs on the web application; therefore, the query must perform quickly and (assuming a busy web site) the frequency of execution will be high as well.

This example uses three constant hard-coded ProductID values to filter the query.

ProductID = '762' ProductID = '754' ProductID = '770'

What You Need
To reproduce the testing environment used in this article, you'll need:


Although it's generally best practice to avoid hard-coded values, their use in this example serves to isolate the effect of the operators on the execution plans from any influence by sub-queries that aren't related to the operators themselves.

The sample uses two tables: SalesOrderDetail and Product, each of which has several defined indexes:

The Product table has four indexes:

  • PK_Product_ProductID (Clustered index through a Primary Key)
  • AK_Product_rowguid (Unique non-clustered index)
  • AK_Product_ProductNumber (Unique non-clustered index)
  • AK_Product_Name (Unique non-clustered index)
The SalesOrderDetail table has three indexes:

  • PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID (Clustered index through a Primary Key)
  • IX_SalesOrderDetail_ProductID (Non-clustered index)
  • AK_SalesOrderDetail_rowguid (Unique non-clustered index)
Operator Types
For filtering, there are two types of operators: comparator operators and logical operators.

Comparator operators evaluate the two values being compared to determine if they meet the operator's criteria. T-SQL's comparator operators are:

  • Equal (=)
  • Greater Than (>)
  • Less Than (<)
  • Greater Than Equal To (>=)
  • Less Than Equal To (<=)
  • Not Equal To (<>)
Logical operators evaluate a condition and return a Boolean value that lets you know whether the condition was met. T-SQL's logical operators are:

  • ALL
  • ANY
  • BETWEEN
  • EXISTS
  • IN
  • LIKE
  • NOT
  • SOME
You can use comparator and logical operators together in complex filtering. When using multiple operators the order of operations may potentially affect the performance of the query. T-SQL treats operators according to both position and type: When the multiple operators being used are the same type (for example, two comparator operators), operations occur from left to right; when the operators are different, comparator operators take precedence over logical operators.

The examples described in the rest of this article evaluate four of the most commonly used operators: the Equal comparator operator (=), and the IN, ANY, and EXISTS logical operators.

The Equal Comparator Operator and the IN Logical Operator
As noted above, you need a query that retrieves sales details for three specific products. You can accomplish the task by using either a WHERE clause that filters with a comparator operator via equality (=) or with a logical operator—using IN.

Comparator operators are designed to be used for only two values: the value being evaluated and the value being sought. To test for equality against three specific ProductIDs, you also have to use the OR operator. Here's a query that uses the Equal comparator operator:

SELECT SalesOrderID, CarrierTrackingNumber, ProductID, UnitPrice FROM AdventureWorks.Sales.SalesOrderDetail WHERE (ProductID = '762') or (ProductID = '754') or (ProductID = '770')

In contrast, the IN logical operator lets you use a list of arguments without also having to use OR or AND.



SELECT SalesOrderID, CarrierTrackingNumber, ProductID, UnitPrice FROM AdventureWorks.Sales.SalesOrderDetail WHERE ProductID IN ('762','754','770')

Author's Note: Although this particular example uses explicit values as arguments to the IN operator, you can use a sub-query to obtain the argument values.

 
Figure 1. Execution Plan for Equal and IN: For these two queries and this particular database, the Equal comparator operator and the IN logical operator result in identical execution plans.
Despite the distinct differences in the query syntax, when executed against the AdventureWorks database and these specific tables, both sample queries produce identical execution plans (see Figure 1).

The ANY and EXISTS Logical Operators
The ANY logical operator returns a Boolean true when any of the items in its arguments are met, while the EXISTS logical operator returns a Boolean true if the sub-query in its argument returns any rows. Testing the performance of these operators can be tricky because the sub-queries used often contain other operators, which directly affect the results. To minimize such effects, the following ANY and EXISTS query samples both contain a sub-query that uses the Equal comparator operator.

Here's the query to test the ANY logical operator:

SELECT SalesOrderID, CarrierTrackingNumber, ProductID, UnitPrice FROM AdventureWorks.Sales.SalesOrderDetail WHERE ProductID = ANY( SELECT ProductID FROM AdventureWorks.Production.Product WHERE (ProductID = '762') OR (ProductID = '754') OR (ProductID = '770') )

And here's the query to test the EXISTS logical operator:

SELECT a.SalesOrderID, a.CarrierTrackingNumber, a.ProductID, a.UnitPrice FROM AdventureWorks.Sales.SalesOrderDetail a WHERE

 
Figure 2. Execution Plan for ANY and EXISTS: The ANY and EXISTS logical operators offer an execution plan that introduces nested loops.
EXISTS ( SELECT b.ProductID FROM AdventureWorks.Production.Product b WHERE a.ProductID = b.ProductID AND ( (b.ProductID = '762') or (b.ProductID = '754') or (b.ProductID = '770') ) )
Again, and perhaps unexpectedly, when executed against the AdventureWorks database, both the IN and EXISTS queries above produce identical execution plans (see Figure 2).



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap