1

I'm using SQL Server 2005.

I have a problems with executing SQL statements like this

DECLARE @Param1 BIT
SET @Param1 = 1

SELECT 
    t1.Col1,
    t1.Col2
FROM
    Table1 t1
WHERE
    @Param1=0 OR
    (t1.Col2 in 
        (SELECT  t2.Col4
            FROM 
                Table2 t2
            WHERE 
                t2.Col1 = t1.Col1 AND 
                t2.Col2 = 'AAA' AND 
                t2.t3 <> 0)
    )

This query executes very long time.

But if I replace @Param1 with 1, than query execution time is ~2 seconds.

Any information how to resolve the problem would be greatly appreciated.

1
  • 1
    Please post the XML version of the actual execution plan. Commented Nov 5, 2012 at 14:15

3 Answers 3

1

Well, the explanation seems simple enough. For your current condition, since @Param1=0 is false (you set the parameter to 1 previously), it needs to evaluate your second condition, wich has a subquery and might take a long time. If you change your first filter to @Param1=1, then you are saying that it is true and there is no need to evaluate your second filter, hence making your query faster.

Sign up to request clarification or add additional context in comments.

Comments

0

You seem to be confusing the optimiser with your OR statement. If you remove it, you should find it generates two different execution plans for the SELECT statement - one with a filter, and the other without:

DECLARE @Param1 BIT
SET @Param1 = 1

if @Param1=0
begin
    SELECT 
        t1.Col1,
        t1.Col2
    FROM
        Table1 t1
end
else
begin
    SELECT 
        t1.Col1,
        t1.Col2
    FROM
        Table1 t1
    WHERE
        (t1.Col2 in 
            (SELECT  t2.Col4
                FROM 
                    Table2 t2
                WHERE 
                    t2.Col1 = t1.Col1 AND 
                    t2.Col2 = 'AAA' AND 
                    t2.t3 <> 0)
        )
end

Comments

0

This is commonly referred to as the N+1 problem.
You're doing a select in table 1 and for each record you find you will go look for something in table 2.
By setting your @Param1 to an value which will never be found in your select the sql engine will skip the subquery.
To avoid this behavior you could use a JOIN statement to join both tables together and afterwards filter the results with a where statement. The join statement will be a bit slower then a single subquery because you are matching 2 tables to eachother but because you only need to do the join once(vs N times) you'll gain a serious performance boost.
Example code :

DECLARE @Param1 BIT
SET @Param1 = 1

SELECT t1.Col1,t1.Col2
FROM Table1 t1
INNER JOIN Table2 t2 on t1.Col1 = t2.Col1
WHERE @Param1=0 
OR t2.Col2 = 'AAA' 
AND t2.t3 <> 0

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.