Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2019 (Phoenix, AZ) with: Matt Vickers and Andy Orsow and Shawn Grigson and Billy Kiely and Jonathan Rowny and Bryan Stanley and Andrew Cullen and Sophy Eung and Scott Markovits and Clark Valberg and Jonathan Hau and Joel Hill and Aaron Stump and David Bainbridge
Ben Nadel at InVision In Real Life (IRL) 2019 (Phoenix, AZ) with: Matt Vickers@envex ) , Andy Orsow , Shawn Grigson@shawngrig ) , Billy Kiely@billykiely ) , Jonathan Rowny@jrowny ) , Bryan Stanley , Andrew Cullen@cullenbyname ) , Sophy Eung@ungsophy ) , Scott Markovits@ScottMarkovits ) , Clark Valberg@clarkvalberg ) , Jonathan Hau , Joel Hill@Jiggidyuo ) , Aaron Stump@aaronstump ) , and David Bainbridge ( @redwhitepine )

SQL LIKE Directive Faster Than LEN() Method

By on
Tags:

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.

Want to use code from this post? Check out the license.

Reader Comments