SQL Performance Question (no where clause vs where column like '%')

in a query, is there any performance hit by doing a where clause like so

select bunchofstuff from sometable where column like '%'

vs

开发者_开发知识库select bunchofstuff from sometable

Building some dynamic queries, just curious. Thanks.

On SQL 2005, SP3 running the following

set statistics io on
select * from sys.columns where name like '%'
set statistics io off

set statistics io on
select * from sys.columns
set statistics io off

suggests that the cost will be almost identical, since both return the same result set:

(1134 row(s) affected)
Table 'syscolpars'. Scan count 1, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Switching on the query plans and/or statistics IO shows that select * from sys.columns where name like '%' is marginally more expensive (51% of query cost vs. 49% for select * from sys.columns), since it appears that the filter is parsed and applied even though it has no effect.

those are different queries and will return different results if the column in question allows for NULL!

if you want all rows, just remove the WHERE

Any time you filter results, performance is going to be a little slower (unless, of course, the query optimizer optimizes out an empty like)

My assumption would be that since this is a dynamic query, there's going to be something after the wildcard.

Depending on the size of the table, the wildcard like clause could cause a slight slowdown or something on the order of seconds/minues.

When I'm code-generating SQL and it's easier to try to insert a no-op than try to get all the parentheses and leading and trailing things right - I usually use something like WHERE (0 = 1) OR ... or WHERE (1 = 1) AND ...

There are a bunch of factors to be considered:

1. If you have to use "%" then make sure at least you give something like 'a%'
2. Next if you want to use dynamic queries try using Optional Parameters instead
3. Make sure that the column(s) you are searching on is indexed (non-clustered) properly.

HTH

What Cade Roux wrote... in case of SP, you would have a parameter: @ApplyFilter int

and in your select statement you would have:

SELECT ....
WHERE 
(@ApplyFilter = 1) and ....

This way - app level would decide if filter is applicable and pass it to the SP. The above would return 0 rows; if your logic was to return all rows when no filter applied you would have:

SELECT ....
WHERE 
(@ApplyFilter = 0) and ....

You are asking us a question that you are in the best position to answer. We have no idea what your specific environment looks like (which database, OS, hardware configuration, front-end setup, etc.).

You should run this query both ways and check the performance yourself, either by timing each query or using your database's profiling tools/options.

It's true that database performance testing can be tricky. If you are unsure about how to test, you could post a new question asking how to test for performance. Make sure that you are specific about your DBMS version, as this is usually highly database specific.

The best part about this approach is that you will be better equipped to answer these kinds of questions for yourself in the future.