Adding Additional Where Clause in SQL Takes Big Pe

问题:

I have an SQL Query that runs through multiple views and tables.

The query runs fine, but when I added another condition in the WHERE clause, it started to have tremendous hit on the performance.

The query is structured like...

         SELECT a.*
           FROM vw_myView a
LEFT OUTER JOIN tbl1 b ON a.ID = b.ID
LEFT OUTER JOIN vw_OtherView c ON a.ID = c.ID
LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID
          WHERE a.Column1 = 'VALUE'
            AND a.Column2 LIKE ISNULL(@parameter, a.Column2)

From the query above, when I add the other condition in the WHERE clause, my query now takes a lot of time (more than 3 minutes) to execute and return 1000 records. Removing the added conditional clause and query returns the records in less than 7 secs.

Thanks. What should I be checking to see where the performance hit is tremendous

回答1:

Your query clause AND a.Column2 LIKE ISNULL(@parameter, a.Column2) involves calling a function, so SQL Server has to do a table scan (ie test that function against every row) in your table to see if it satisfies the query.

Even if you had an index on a.Column2, Sql Server would not be able to use it.

[EDIT] This is one way to speed it up in most cases

-- Only this part executes when the @parameter has a value.
-- It returns nothing and executes fast when @parameter is NULL

SELECT a.*
           FROM vw_myView a
LEFT OUTER JOIN tbl1 b ON a.ID = b.ID
LEFT OUTER JOIN vw_OtherView c ON a.ID = c.ID
LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID
          WHERE a.Column1 = 'VALUE'
            AND a.Column2 LIKE @parameter

UNION ALL

-- This part does not execute when the @paramter has a value
-- This will leave the 2nd query clause out and run faster when @parameter is not specified, avoiding a table scan

SELECT a.*
           FROM vw_myView a
LEFT OUTER JOIN tbl1 b ON a.ID = b.ID
LEFT OUTER JOIN vw_OtherView c ON a.ID = c.ID
LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID
          WHERE a.Column1 = 'VALUE'
            AND @parameter IS NULL

回答2:

Look at your query's execution plan. I'll bet dollars to donuts that your additional search argument is causing a table scan, a very costly procedure, performance-wise.

回答3:

Dynamic SQL is probably your best bet. Create one statement if the parameter is null and different one if it is not. This is the common way that searches are made faster with lots of possible parameters.

Since you only have the two conditions, I might try to write it like this: IF @parameter IS NULL

BEGIN
    SELECT a.*            
    FROM vw_myView a 
    LEFT OUTER JOIN tbl1 b 
    ON a.ID = b.ID 
    LEFT OUTER JOIN vw_OtherView c 
    ON a.ID = c.ID 
    LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID           
    WHERE a.Column1 = 'VALUE'             
    AND a.Column2 LIKE a.Column2 
END
ELSE 
BEGIN
    SELECT a.*            
    FROM vw_myView a 
    LEFT OUTER JOIN tbl1 b 
    ON a.ID = b.ID 
    LEFT OUTER JOIN vw_OtherView c 
    ON a.ID = c.ID 
    LEFT OUTER JOIN tbl2 d ON c.OtherID = d.OtherID           
    WHERE a.Column1 = 'VALUE'             
    AND a.Column2 LIKE  @parameter
END

Of course you should not use SELECT * ever in production code and use = if possible instead of LIKE. And if those views reference the same tables, directly querying the tables might be abetter choice for performance.

回答4:

try this.

AND (a.Column2 = @parameter OR @parameter is NULL)

Don't get why you are using like?

do you want to do something like this

AND (a.Column2 like @parameter+'%' OR @parameter is NULL)

回答5:

Why do this? AND a.Column2 LIKE ISNULL(NULL, a.Column2)
That is always going to check a.Column2 LIKE a.Column2
Are you trying to just check a.Column2 IS NULL?

Can you pls post the query as it was before you modified?

[Edit]
I don't know your target database, but if it is Sybase I would caution you against using @parameter + '%' which will do a table scan. You can read more at To LIKE or not to LIKE.