Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2010 (Landsdown, VA) with: Cara Beverage

SQL LIKE Directive Faster Than LEN() Method

By Ben Nadel on
Tags: SQL

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.

Tweet This Groovy post by @BenNadel - SQL LIKE Directive Faster Than LEN() Method Thanks my man — you rock the party that rocks the body!

Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.