December 9, 2004 at 4:22 am
Could someone please explain why the following join is so inefficient?
INNER JOIN dbo.Table_2 T2 (nolock)
ON T1.column = T2.column
AND ( T2.ID = @ID OR @ID = 0)
When running the query containing the join, it takes approx 40 seconds with @ID = 0. Without the AND clause it takes 5 seconds.
Any ideas/thoughts welcome.
Thank you in advance.
Carl.
December 9, 2004 at 5:10 am
Hi Carl
What data type is @ID and why do you have @ID = 0 (do you mean "OR T2.ID = 0" ?) Have you tried to run the query without the OR in your AND.
Try these 2 things first (or combination of both):
1. include (T2.ID = @ID OR T2.ID = 0) in a WHERE clause, something like:
WHERE T2.ID = @ID OR T2.ID = 0
2. Create an index on T2.ID
If the above does not help, bring out the artillery:
3. Create a query hint to use the best index
see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=146295
Pls let us know what happend.
Max
December 9, 2004 at 5:17 am
I would stay away from query hints and begin by examining execution plans, indexes...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 9, 2004 at 7:05 am
Could you post the entire query and is this in a stored procedure?
I assume you are trying to combine 2 queries based on the condition of @ID.
INNER JOIN dbo.Table_2 T2 (nolock)
ON T1.column = T2.column
AND
INNER JOIN dbo.Table_2 T2 (nolock)
ON T1.column = T2.column
AND T2.ID = @ID
You might need to reevaluate your implementation so you have the 2 different queries for best performance.
December 9, 2004 at 7:07 am
>>INNER JOIN dbo.Table_2 T2 (nolock)
>>ON T1.column = T2.column
>>AND ( T2.ID = @ID OR @ID = 0)
Explain the logic of what you are trying to acheive here. Never use an OR in a JOIN expression.
if @ID=0, you are producing a cross join. I have a feeling that is not what you really want.
December 9, 2004 at 10:36 pm
I suggest this
If @ID = 0
BEGIN
...INNER JOIN dbo.Table_2 T2 (nolock)
ON T1.column = T2.column
end
else
begin
INNER JOIN dbo.Table_2 T2 (nolock)
ON T1.column = T2.column
AND ( T2.ID = @ID)
END
That is the simple way to workaround your behavior...
I hope this help
:.::.:.::
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply