Redundant Query Plan Branches

Consider the following “basic_member” view definition from the Credit database:

CREATE VIEW [dbo].[basic_member]
AS
SELECT  [member].[member_no],
[member].[lastname],
[member].[firstname],
[member].[middleinitial],
[member].[street],
[member].[city],
[member].[state_prov],
[member].[mail_code],
[member].[phone_no],
[member].[region_no],
[member].[expr_dt],
[member].[member_code]
FROM    [dbo].[member]
WHERE   [member].[member_no] NOT IN (SELECT    [corp_member].[member_no]
FROM      [dbo].[corp_member]);

GO

A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I’m boxing in an “areas of interest” via SQL Sentry Plan Explorer’s rendering of the plan):

We see that the view has a predicate on member_no NOT IN the corp_member table.  But what happens if the original report writer doesn’t look at the view definition and decides they need this same predicate applied at the the view reference scope (not realizing this was already taken care of)?  For example:

SELECT  [basic_member].[member_no],
[basic_member].[lastname],
[basic_member].[firstname],
[basic_member].[middleinitial],
[basic_member].[street],
[basic_member].[city],
[basic_member].[state_prov],
[basic_member].[mail_code],
[basic_member].[phone_no],
[basic_member].[region_no],
[basic_member].[expr_dt],
[basic_member].[member_code]
FROM    [dbo].[basic_member]
WHERE   [basic_member].[member_no] NOT IN
(SELECT   [corp_member].[member_no]
FROM     [dbo].[corp_member]);

Like the previous query against the view, we see 8,498 rows.  But unlike the previous query, we see the following plan:

Notice the redundancy – even though the result set is identical between the two versions.  And the tables I’m using are small, but you can still see the difference in scan count and logical reads.

Query Against View

Table ‘member’. Scan count 2, logical reads 305, physical reads 2, read-ahead reads 294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘corporation’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query with Redundant Predicate

Table ‘member’. Scan count 3, logical reads 325, physical reads 2, read-ahead reads 294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘corporation’. Scan count 2, logical reads 16, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

And there is additional I/O overhead associated with the second plan of course.  For the Credit database, the scale is small, but imagine the difference for a very large table.

You cannot always count on easily identifying redundant areas.  The Query Optimizer may find an optimal plan in spite of the way it was written – but if you do see repeating branches in a query execution tree associated with a performance issue, you may want to explore the possibility of overlapping/redundant logic further.