SQL Server Magazine

With this in mind, let’s rewrite the subquery using a logically equivalent expression that’s more convenient for the optimizer to execute and that causes the Seek Predicates to do more logical processing. You might already know that an expression in the form AND typically lends itself to better optimization than one in the form OR. In Listing 3, the subquery’s filter expression involving the sort columns uses an OR operator:

PS.OrderDate < C.OrderDate
  OR (PS.OrderDate = C.OrderDate
  AND PS.SalesOrderID <
  C.SalesOrderID)

You can transform this expression to a logically equivalent one that uses an AND operator:

PS.OrderDate <= C.OrderDate
  AND (PS.OrderDate < C.OrderDate
  OR PS.SalesOrderID <
  C.SalesOrderID)

Listing 4 shows the query that includes this revised subquery filter. This query ran in about 17 seconds on my system. That’s about six times as fast as the preceding query and more than three times the speed of the cursor-based solution. The total I/O cost of this plan is 5,992,708 logical reads—lower than the I/O cost of Listing 3’s query (6,362,250 logical reads), but not substantially so.

Now, look at the execution plan that Figure 3, shows. As you can see, the plans for Listing 3’s and Listing 4’s solutions look similar on the surface—the optimizer deems both to have almost the same cost. But when you look at the Seek Predicates and WHERE Predicate of the Index Seek operator in the plan for Listing 4’s query, you’ll see a difference. The Seek Predicates are

Prefix:
  [tempdb].[Sales].
  [SalesOrderHeader].
  CustomerID =
  [tempdb].[Sales].
  [SalesOrderHeader].
  [CustomerID] as
  [C].[CustomerID],
  End Range:
  [tempdb].[Sales].
  [SalesOrderHeader].
  OrderDate <=
  [tempdb].[Sales].
  [SalesOrderHeader].
  [OrderDate] as [C].[OrderDate]
  and the WHERE Predicate is
  [tempdb].[Sales].
  [SalesOrderHeader].
  [OrderDate] as
  [PS].[OrderDate] <
  [tempdb].[Sales].
  [SalesOrderHeader].
  [OrderDate] as [C].[OrderDate]
  OR
  [tempdb].[Sales].
  [SalesOrderHeader].
  [SalesOrderID] as
  [PS].[SalesOrderID] <
  [tempdb].[Sales].
  [SalesOrderHeader].
  [SalesOrderID] as
  [C].[SalesOrderID]

The plan for Listing 3’s query returned more rows for the WHERE Predicate to evaluate. Those were the rows that satisfied the Seek Predicates PS.CustomerID = C.CustomerID presented here in abbreviated form). The plan for Listing 4’s query left far fewer rows for the WHERE Predicate to evaluate after satisfying the Seek Predicates PS.CustomerID = C.CustomerID, End Range:PS.OrderDate <= C.OrderDate (presented here in abbreviated form).

More to Come
Query tuning and optimization is often more art than science. A small detail can dramatically influence your queries’ performance. The more you know about how the query optimizer works and how to analyze and interpret the information SQL Server’s tools provide—including not being misled by estimation information—the better you can optimize queries.

For this article’s task, I presented a cursor-based solution that ran in 58 seconds, a first attempt at a set-based solution that ran in about 2 minutes, and an optimized version of the set-based query that ran in 17 seconds. But I’m not done yet. There are faster solutions. Next month, I’ll cover even speedier solutions, one of which runs in only 6 seconds. In the meantime, can you think of faster solutions to this T-SQL problem?

End of Article

Prev. page     1 2 [3]     next page -->