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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy