I am kicking off the new year with a quick jaunt back in time through Mr. Peabody's WABAC Machine. The blog entry below was published on April 30, 2008, during my pre-Simple-Talk blogging days. The topic below is something that I have found to be a valuable consideration in optimizing queries. Enjoy!

As we work with other Professionals in our industry, we quickly learn that there are many ways to accomplish a given task. When presented with multiple options in a situation, there are times where one option stands out as the optimal choice based upon our general knowledge of the database engine.

There are also times where the optimal choice may not be so obvious and will vary depending on many considerations such as database architecture, level of use, indexing, hardware configuration or general best practices. Any given approach may perform superbly with one database and drag on another.

A sample case might be that we are given two versions of a seemingly simple query that filters the data. One option is the common use of a WHERE clause. The other is utilizing the filters within the JOIN clause. The examples below illustrate the syntax differences between these options.

WHERE Filter Option

SELECT
    [TABLE1].[FIELD1]
FROM
    [TABLE1]
    INNER JOIN [TABLE2]
        ON [TABLE1].[FIELD1] = [TABLE2].[FIELD1]
    INNER JOIN [TABLE3]
    ON [TABLE2].[FIELD2] = [TABLE3].[FIELD2]
WHERE
    [TABLE1].[FIELD1] = 700
    AND [TABLE2].[FIELD2] > 1000
    AND [TABLE3].[FIELD2] > 1000

JOIN Filter Option

SELECT
    [TABLE1].[FIELD1]
FROM
    [TABLE1]
    INNER JOIN [TABLE2]
        ON [TABLE1].[FIELD1] = [TABLE2].[FIELD1]
        AND [TABLE1].[FIELD1] = 700
        AND [TABLE2].[FIELD2] > 1000
    INNER JOIN [TABLE3]
        ON [TABLE2].[FIELD2] = [TABLE3].[FIELD2]
        AND [TABLE3].[FIELD2] > 1000

When these are executed, the Query Optimizer evaluates the query and determines the best execution plan for the statement. In this case, Query Optimizer determined that both options should utilize the same execution plan; but since these statements are syntactically unique they are cached separately.

Execution Plan

Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([TABLE2].[FIELD2]) WITH PREFETCH)| |--Clustered Index Seek(OBJECT:([TABLE2].[TABLE2_P]),SEEK:([TABLE2].[FIELD1]=700 AND [TABLE2].[FIELD2] > 1000) ORDERED FORWARD)| |--Clustered Index Seek(OBJECT:([TABLE3].[TABLE3_P]), SEEK:([TABLE3].[FIELD2]=[TABLE2].[FIELD2]), WHERE:([TABLE3].[FIELD2]>1000) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([TABLE1]))
|--Index Seek(OBJECT:([TABLE1].[IX_TABLE1_2]), SEEK:([TABLE1].[FIELD1]=700) ORDERED FORWARD)

In review of SQL Server Profiler, the duration of the T-SQL batch appear identical (both reflected the value of 20 in my sample). A review of the sysprocesses system table reveals some interesting information about these two options. The CPU, Physical IO (Reads) and Memory usage are significantly different.

JOIN Option
CPU: 200
PIO: 5
MEM: 15

WHERE Option
CPU: 19110
PIO: 707
MEM: 38

According to this information, the performance of the filtering that occurred within the JOIN clause performed much better than the WHERE clause option. To ensure that the initial differences in the performance was not unique to the first execution, I ran the same queries multiple times, obtaining a cache hit for each execution. The results were consistent.

It is important to note that these numbers and results are specific to my test environment. The results reflected in your specific environment may differ; but through the sample above the process of evaluating and selecting the most optimal solution is illustrated.