Prevent searching logic from executing

Question

I am trying to implement a sql query for a search and there are a number of fields that I need to be able to search across but they are all optional. I have implemented the query in the following way:

SELECT --<some fields>
FROM --<some tables>
WHERE   ((@param1 IS NULL) OR ([FirstName] = @param1))
        AND((@param2 IS NULL) OR ([Surname] = @param2))
        AND((@param3 IS NULL) OR ([CompanyName] = @param3))

What I am finding though is that now I have about 6 params, even when they are all null, the query takes far too long to execute.

I prefer not to be executing dynamic sql and only adding the sql conditions that are needed based on the parameters. Is there any way that I can optimise this query to make sure that it is not executing all of the conditional logic?

Just as an FYI, I don't think that the speed issue is down to indexes as I have look at the execution plans and it is running of Index Scans rather than Table Scans or Lookups.

BTW, does it take significantly less time if you execute the query without where clause? — Khadim Ali, Aug 19 '13 at 11:01
@Ali.NET Yeah, it comes back in less than a second without the where clause. — Richard Hooper, Aug 19 '13 at 11:05

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

Sounds like parameter sniffing.

Try rebuilding your indexes and updating statistics.

If the problem persists you could add an OPTION(RECOMPILE) to the entire statement, or use OPTIMIZE FOR to hint at what the procedure should do in terms of query plan.

[BTW: A clustered index scan IS a table scan.]

The canonical reference is: Dynamic Search Conditions in T-SQL

edited May 23 '17 at 12:28

Community

1
1

answered Aug 19 '13 at 11:06

Mitch Wheat

295,962
43
465
541

Actually [Dynamic Search Conditions in T-SQL](http://www.sommarskog.se/dyn-search-2008.html) seems more relevant here. The OP is attempting a catch all query. `OPTION(RECOMPILE)` should do the trick though. – Martin Smith Aug 19 '13 at 11:18
sorry that was the link I meant to paste! – Mitch Wheat Aug 19 '13 at 11:48

Prevent searching logic from executing

1 Answers1