SQL LIKE Directive Faster Than LEN() Method
Posted June 2, 2006 at 2:21 PM by Ben Nadel
I was doing some SQL testing with my co-worker, Simon Free, about the speed differences between the SQL function LEN() and the SQL directive LIKE. Take for example the situation where I only want to get users who have a last name. In this case, I don't care the length of the last name, I only care if there is any value. To accomplish this, I used to do:
- SELECT
- u.id
- FROM
- [user] u
- WHERE
- LEN( u.last_name ) > 0
This always worked. Fine. But it turns out this is slow compared to this:
- SELECT
- u.id
- FROM
- [user] u
- WHERE
- u.last_name LIKE '_%'
This is using the wild card "_" to test the name as containing at least one character. This is sufficient for me. Not, now only was this consistently faster that LEN() (usually by half) but it is very consistent in speed. The Len() method would jump all over the place in speed. The LIKE method would always be the same (give or take a few ms).
Thinking about it, it makes sense. It's somewhat similar to the idea behind short-circuit evaluation. With LEN(), the server needs to evaluate EVERY character in a string. With the LIKE method, the server needs to evaluate one character; the second it hits a character, done, true, next record.
