CODEWITHSUNDEEP
sqlsql-servert-sqloptimizationwhere-clause
Nemanja Vujacic
Nemanja Vujacic

Reputation: 935

TSQL Optimization - WHERE clause without LIKE

What is the most optimized solution for this situation:

WHERE clause of my query is like this:

WHERE columnX LIKE @Account + '%'

I use '%' because @Account could be ' ' and in that case I want to get all values. In all other cases I could use equals (=).

This solution is not optimized because I have index on columnX and I would like to use equals (=) because of that. Also, solution with OR is not acceptable because of performance.

Do you have some other recommendation? I tried with CASE in WHERE clause, but didn't find good solution that is optimized. Our compatibility level is 80, so cannot use newer syntax. (don't ask why :-) )

TnX in advance!

Nemanja

Upvotes: 1

Views: 230

Answers (5)

rs.
rs.

Reputation: 27467

Try this

DECLARE @var VARCHAR(10) = '' 
DECLARE @table TABLE ( ID VARCHAR(10) ) 

INSERT INTO @table 
SELECT '11' 
UNION 
SELECT '2' 

SELECT * 
FROM @table 
WHERE ( Len(@var) = 0 OR ID = @var ) 

SET @var = '11' 

SELECT * 
FROM @table 
WHERE ( Len(@var) = 0 OR ID = @var )

Upvotes: 0

James Curtis
James Curtis

Reputation: 794

You are attempting to execute two semantically different queries; one to pull all rows, and one to pull a single row. These two queries will by definition have two different query plans. Your attempt to combine them into one query to avoid "code duplication" is misguided. "Code duplication" is not the enemy, complexity is. The answer is for you to create a stored proc for each scenario. This will not only allow each stored proc to represent a single query plan; you will also simplify your application by following the single responsibility principle.

Upvotes: 1

erikkallen
erikkallen

Reputation: 34411

On some versions of SQL server, the OR is not a performance issue if you use OPTION(RECOMPILE). For more information, see http://sommarskog.se/dyn-search-2008.html

Upvotes: 0

Diego
Diego

Reputation: 36166

this is a situation we all face :)

There is nothing much you can do really if you what a select with a Like. Your situation is not the worst, if you only add the % in the end, there is a good chance the index will be used for a seek. The real problem is when you have % in the begining where the index, if used, will be on a scan

also, bear in mind that the index usage is also related with the columns you have on your select. If you have a table with ID and Name, with an index on name, and select * from it the index probably wont be used.

To finish, you could consider fulltext search, but it's implementation is rather complicated

Upvotes: 0

roman
roman

Reputation: 117510

you can try this

where columnX = isnull(nullif(@Account, ''), columnX)

Upvotes: 2

Related Questions