SQL LIKE Performance with only the wildcard (%) as a value

Question

I am wondering what the performance of a query would be like using the LIKE keyword and the wildcard as the value compared to having no where clause at all.

Consider a where clause such as "WHERE a LIKE '%'". This will match all possible values of the column 'a'. How does this compare to not having the where clause at all.

The reason I ask this is that I have an application where there are some fields that the user may specify values to search on. In some cases the user would like all the possible results. I am currently using a single query like this:

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

The values of '%' and '%' can be supplied to match all possible values for a and or b. This is convenient since I can use a single named query in my application for this. I wonder what the performance considerations are for this. Does the query optimizer reduce LIKE '%' to simply match all? I realize that because I'm using a named query (prepared statement), that may also affect the answer. I realize the answer is likely database specific. So specifically how would this work in Oracle, MS SQL Server and Derby.

The alternate approach to this would be to use 3 separate queries based on the user inputting the wildcard.

A is wildcard query:

SELECT * FROM TableName WHERE b LIKE ?

B is wildcard query:

SELECT * FROM TableName WHERE a LIKE ?

A and B are wildcards:

SELECT * FROM TableName

No wildcards:

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

Obviously having a single query is the simplest and easiest to maintain. I would rather use just the one query if performance will still be good.

Answer 1

SQL Server will generally see

WHERE City LIKE 'A%'

and treat it as

WHERE City >= 'A' AND City < 'B'

...and happily use an index seek if appropriate. I say 'generally', because I've seen it fail to do this simplification in certain cases.

If someone's trying to do:

WHERE City LIKE '%ville'

...then an index seek will be essentially impossible.

But something as simple as:

WHERE City LIKE '%'

will be considered equivalent to:

WHERE City IS NOT NULL

Answer 2

I was hoping there would be a textbook answer to this but it sounds like it will largely vary with different database types. Most of the responses indicated that I should run a test so that is exactly what I did.

My application primarily targets the Derby, MS SQL and Oracle databases. Since derby can be run embedded and is easy to set up, I tested the performance on that first. The results were surprising. I tested the worst case scenario against a fairly large table. I ran the test 1000 times and averaged the results.

Query 1:

SELECT * FROM TableName

Query 2 (With values of a="%" and b="%"):

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

Query 1 average time: 178ms

Query 2 average time: 181ms

So performance on derby is almost the same between the two queries.