Conditional statements in WHERE clause - Performance issues

Question

Post reply

Conditional statements in WHERE clause - Performance issues

James D-764131

Say Hey Kid

Points: 684
More actions
June 1, 2008 at 5:50 pm

#182939

Hi,
I wonder could any of you gurus help me with a performance issue I have? I'm not a DBA (far from it) so I'd appreciate it if you could shed any light on the subject from your experiences. I apologise in advance if this has already been posted about.
I'm working on an ASP.NET 2.0/SQL Server 2005 system that has a number of pages allowing users to search for results. These pages can have up to 8 different filters on them that may or may not be set. We have one stored procedure for each search page with conditional statements in the WHERE clause like below:
[font="Courier New"]CREATE Proc SP1
(
@Param1 int,
@Param2 varchar(100)
) AS
BEGIN
SELECT Field1, Field2
FROM Table1
WHERE
(@Param1 = 0 OR Field1 = @Param1)
AND
(@Param2 IS NULL OR Field2 LIKE '%' + @Param2 + '%')
END[/font]
While this is great from a code maintenance point of view, we're getting some pretty shocking performance out of the system. We've added indexes where appropriate and initial research into the problem indicates that we should be using individually defined SELECT statements for each set of defined parameters. As the number of fields being searched inceases though, the nincreases SELECT statements increases exponentially making maintenance a nightmare.
Something else we've noticed recently is that after the system has been running for a number of hours, we start to get a lot of timeouts. Looking at perfmon, the CPU on the SQL box doesn't appear to be doing much. Apologies, but I don't have information on the other counters. My colleague found that running DBCC FreeProcCache seems to sort the problem, so we clearly have an issue here. After some reading, I believe this may be due to SQL caching execution plans, but due to the large number of variations, could it be getting overloaded? I really don't understand execution plans. I've changed the stored proc to WITH RECOMPILE in the hope this will resolve the need to call FreeProcCache at the cost of some CPU time while we get to the bottom of the problem.
So my questions are:
1) What is the most efficient way to write this type of search stored procedure while not incurring maintenance overheads?
2) Does anyone have any idea why we'd need to call FreeProcCache? And do you think the WITH RECOMPILE will reduce the problem temporarily?
Thanks for taking time out to help guys, it's much appreciated! 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

The first issue I see is this:

WHERE

(@Param1 = 0 OR Field1 = @Param1)

AND

(@Param2 IS NULL OR Field2 LIKE '%' + @Param2 + '%')

Prefixing your search criteria with a wildcard will force an index/table scan. This means you will always be searching through every row in the table.

With as many as eight different columns to search on, you may be better off using dynamic SQL or just building up the queries in the application. If you use either method, make sure you are very aware of SQL injection and how to avoid it.

See: http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dyn-search.html

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

James D-764131 Say Hey Kid Points: 684 More actions · Answer 2

Jeff,

Thanks for taking the time to write and also for those excellent links!

You've said that every row in the table gets scanned using the method I'd described; is that only when performing the LIKE search? Would FULL-TEXT INDEXING being a better option here? Or is there something in between the two that I could use?

Kind regards,

James

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Yes - it is with the like statement that would cause an index scan. And, yes - using full text search would be a better solution if you need that kind of search capability.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs