Advanced Execution Plan Question

I have a scenario where I have 3 tables joined together. The first is the
"main" table that will define the result set. The second is a lookup table;
the main table has a foreign key to it. The third is a detail table; it has
a foreign key to the main table. Both the second and third tables are on
the right side on a LEFT OUTER JOIN. In this particular query, I only need
the detail data for a particular row if the lookup row exists. The reason
for this is that if the lookup row exists, it will be used to further filter
the result set based upon a value in the detail row. But if the lookup row
doesn't exist, there's no need to lookup the detail row. In order to
optimize the query I attempted to add an additional condition to the ON
clause joining the main and detail table saying only join if the lookup key
is not null:
SELECT
...
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL
AND
F.FilterID
= D.FilterID
My hope was that in the join in the execution plan that there would be an
argument "L.LookupID <> null". What I'm ending up with in the actual
execution plan is the "L.LookupID <> null" is not in the join but its
showing up as an argument in the index s for the inner input, meaning
(I'm thinking) that the s is still getting executed and then the
"L.LookupID <> null" is being applied afterward, which means the s was
wasted if "L.LookupID = null".
Now I put together the 3 tables listed above to test this more closely.
Interestingly enough, the "L.LookupID <> null" argument WAS in the join and
not in the inner input, meaning the index s was not being performed on
the detail table when the lookup was null. HOWEVER, I noticed the inner
input (detail table) was having an index scan because I didn't have an index
on FilterID. Once I put the index on FilterID on the detail table, the
"L.LookupID <> null" moved back to the inner input (detail) index s!
I'm trying to understand the logic of this and also wonder if there is a way
to make the "L.LookupID <> null" always occur in the join. The actual
query I'm doing this for is very vital and regularly executed.
Below is DDL, sample data, and the query. Also is the index that "makes the
difference". If you execute the query without the index and then with the
index, you should see the "L.LookupID <> null" move from the join to the
inner input index s. Note that the sample query simply demonstrates "the
moving WHERE clause" in the execution plan. It does not demonstrate the
condition in my real query that moved me to try and accomplish this.
Thanks,
Mike
----
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestDetail')
DROP TABLE tbFilterTestDetail
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTest')
DROP TABLE tbFilterTest
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestLookup')
DROP TABLE tbFilterTestLookup
GO
CREATE TABLE tbFilterTestLookup
(
LookupID int NOT NULL CONSTRAINT PK_FilterTestLookup PRIMARY KEY
)
GO
INSERT tbFilterTestLookup (LookupID) VALUES (1000)
INSERT tbFilterTestLookup (LookupID) VALUES (2000)
INSERT tbFilterTestLookup (LookupID) VALUES (3000)
INSERT tbFilterTestLookup (LookupID) VALUES (4000)
INSERT tbFilterTestLookup (LookupID) VALUES (5000)
INSERT tbFilterTestLookup (LookupID) VALUES (5001)
INSERT tbFilterTestLookup (LookupID) VALUES (5002)
INSERT tbFilterTestLookup (LookupID) VALUES (5003)
INSERT tbFilterTestLookup (LookupID) VALUES (5004)
INSERT tbFilterTestLookup (LookupID) VALUES (5005)
INSERT tbFilterTestLookup (LookupID) VALUES (5006)
INSERT tbFilterTestLookup (LookupID) VALUES (5007)
INSERT tbFilterTestLookup (LookupID) VALUES (5008)
INSERT tbFilterTestLookup (LookupID) VALUES (5009)
INSERT tbFilterTestLookup (LookupID) VALUES (5010)
INSERT tbFilterTestLookup (LookupID) VALUES (5011)
INSERT tbFilterTestLookup (LookupID) VALUES (5012)
INSERT tbFilterTestLookup (LookupID) VALUES (5013)
INSERT tbFilterTestLookup (LookupID) VALUES (5014)
GO
CREATE TABLE tbFilterTest
(
FilterID int NOT NULL CONSTRAINT PK_FilterTest PRIMARY KEY,
LookupID int NOT NULL
)
GO
INSERT tbFilterTest (FilterID, LookupID) VALUES (100, 10) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (200, 11) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (300, 12) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (400, 1000) -- Match
INSERT tbFilterTest (FilterID, LookupID) VALUES (500, 13) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (600, 14) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (700, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (800, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (900, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (620, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (730, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (840, 20) -- No match
GO
CREATE TABLE tbFilterTestDetail
(
DetailID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_tbFilterTestDetail
PRIMARY KEY,
FilterID int NOT NULL CONSTRAINT FK_tbFilterTestDetail_tbFilterTest
REFERENCES tbFilterTest (FilterID),
Descrip varchar(128) NOT NULL
)
GO
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GO
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-2-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-2-match')
GO
-- Sample Query
SELECT
F.FilterID,
L.LookupID,
D.Descrip
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL -- this is
the condition that moves in the execution plan
AND
F.FilterID =
D.FilterID
-- Magic index: try sample query w/ and w/o
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GOOn Thu, 18 Aug 2005 13:53:36 -0400, Mike Jansen wrote:
(snip)
>Both the second and third tables are on
>the right side on a LEFT OUTER JOIN. In this particular query, I only need
>the detail data for a particular row if the lookup row exists.
Hi Mike,
Then why use a LEFT OUTER JOIN' Outer joins should only be used if
you must retain rows from one table even though there is no match in the
other table. The standard join type (INNER JOIN) will retain only rows
that match (as per your request) and will probably perform faster as
well.
SELECT ...
FROM tbFilterTest AS F
INNER JOIN tbFilterTestLookup AS L
ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail AS D
ON F.FilterID = D.FilterID
If you are sure that there will always be at least one matching row in
the FilterTestDetail table, change the second join to an INNER JOIN as
well. It won't change the results, but it might speed up the
performance.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ADO does not add to Errors collection after the second FETCH NEXT in a SP

I'm having trouble obtaining errors raised in a stored
procedure via the ADO Errors collection after the second
FETCH NEXT statement from within that stored procedure.

Consider the following table created in a SQL Server
database:

CREATE TABLE TestTable
(
TestInt int
)
go

INSERT TestTable(TestInt) values(1)
INSERT TestTable(TestInt) values(2)
INSERT TestTable(TestInt) values(3)

This is a very simple table with one column, and three
rows containing the values 1, 2 and 3.

Consider this stored procedure:
CREATE PROCEDURE TestStoredProc
as
BEGIN
set rowcount 0
Set NoCount ON

declare @.TestInt int
declare @.ErrMsg char(7)
declare TestCursor cursor forward_only for
select * from TestTable

open TestCursor
Fetch next from TestCursor into @.TestInt

While @.@.fetch_status<>-1
Begin
select @.ErrMsg = 'Error ' + convert(char, @.testint)
raiserror(@.ErrMsg, 16, 1)
raiserror(@.ErrMsg, 16, 1)
Fetch next from TestCursor into @.TestInt
end

Close TestCursor
DeAllocate TestCursor
return
END

This stored procedure simply defines a cursor on all rows
in TestTable. For each row fetched from the cursor, the
error message 'Error n' is raised twice, where n is the
integer that had just been fetched from the cursor.

Finally, consider this VB code using ADO to execute the
above stored procedure. After the stored procedure is
executed, the code loops through the errors collection,
and creates a message box for each error in the collection:

Private Sub Form_Load()
Dim cn As Connection
Dim cm As Command
Dim oErr As Error

On Error Resume Next

Set cn = CreateObject("ADODB.Connection")
cn.Open "Data Source=<Some SQL Server>; Initial
Catalog=<Some Database Name>; Provider=SQLOLEDB; Persist
Security Info=False; Integrated Security=SSPI"

Set cm = CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
cm.CommandType = adCmdStoredProc
cm.CommandText = "TestStoredProc"
cm.Execute

For Each oErr In cn.Errors
MsgBox oErr.Description
Next

End
End Sub

When this code is executed, only two message boxes appear
with the message "Error 1".

Any help on this matter would be greatly appreciated :)Does anybody has any suggestions?|||Hi Ivan

You can bet your bottom dollar the focus of any replies will be on the use of the cursor rather than the ADO errors collection.

I know this is just an example - is this curiosity about a quirk you have spotted or a serious problem for you? If the latter, would you mind briefly explaining what your production cursor does as there are limited instances where it is as efficient as a set based solution. It may be that your sproc can be made more effective and your ADO errors issue made irrelevent.|||Of course, it is a serious problem for me. SP where this mechanism is used is a part of the big accounting system. Thus I can’t change it logic.