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 the New York ColdFusion User Group (Jan. 2009) with:

SQL IN Directive Much Faster Than Multiple OR Clauses

By Ben Nadel on
Tags: ColdFusion, SQL

I use the IN directive a lot in SQL, especially in conjunction with ValueList()'s of IDs. I was working on a query today that was dealing with thousands of IDs and I got curious as to how the IN directive compared to a more labor-intensive grouping or OR clauses. To me, the IN directive is elegant, but if it's slow, I wouldn't mind writing out the ORs.

To test this, I queried the page-hit IDs from a web statistics database and then used that list of IDs to basically re-query the database for the same records:

  • <!--- Query for the hit IDs. --->
  • <cfquery name="qIDs" datasource="..." username="..." password="...">
  • SELECT
  • h.id
  • FROM
  • web_stats_hit h
  • </cfquery>
  •  
  •  
  • <!--- Query for the ID records using the IN directive. --->
  • <cfquery name="qInTest" datasource="..." username="..." password="...">
  • SELECT
  • h.id,
  • h.date_created
  • FROM
  • web_stats_hit h
  • WHERE
  • <!---
  • In the ValueList(), I am passing in comments in an attempt
  • to make the amount of SQL larger so that it compares to the
  • next query which physically has more character data.
  • --->
  • h.id IN ( #ValueList( qIDs.id, ", /*..*/" )# )
  • </cfquery>
  •  
  •  
  • <!--- Query for the ID records using an OR clause for every ID. --->
  • <cfquery name="qOrTest" datasource="..." username="..." password="...">
  • SELECT
  • h.id,
  • h.date_created
  • FROM
  • web_stats_hit h
  • WHERE
  • 1 = 1
  • <!---
  • Loop over IDs. I am doing a 'sloppy' loop here to create
  • as little white space as possible so as to not have to push
  • so much darn data to the SQL server.
  • --->
  • <cfloop query="qIDs">OR id=#qIDs.id# </cfloop>
  • </cfquery>

The web stats database returns about 3,500 records. In the first query, I am using the IN directive and am just passing in a comma-delimited list of IDs. I am also throwing some bunk comments into the delimiter list so that the actually, physical size of the first query is comparable to that of the second. Value lists just take up less character data.

It turns out that the IN directive is MUCH faster. For the examples above, the IN test runs on average at just under 1,000 ms where as the OR test runs in just over 6,000 ms. If I take away the comments in the ValueList() call (ex. #ValueList( qIDs.id )#), then the IN test query drops to about 300 ms - a third the parsing and execution time.

So, it seems that raw character data accounts for a little bit of the speed of the query as the removing of the comments (/*..*/) demonstrates, but it looks like IN is just much faster than multiple ORs. This of course, only tests numeric data. I would have to come up with a slightly different test to see about string list data. Still, as I am using ID lists more often, this is very very interesting.



Reader Comments

Ben,

The "1=1 OR" in your where cause will return ALL records and probably explains the large difference in the run times.

I'm sure the IN is faster than multiple OR's anyway, but probably not to the degree that your test showed.

Reply to this Comment

@John,

Good catch! That was supposed to be "1 = 0". I probably just copied it over incorrectly as the tests came back with the same results.

Reply to this Comment

Oh, but I guess they would have anyway since there was no filtering on the original query.

Hmmm, nice catch.

Reply to this Comment

I've been wondering it myself and want to find out about it.
But in my test, the Or clause is faster than the In clause.

The number of records is millions (all data in the table not the result set. the result set should be hundreds to thousands)

But there's only 2 condition. Not many.

I suppose that if the case is few, Or will be better, while if the case is many, In will be better.

But, i'm not in condition to prove it though.
I haven't test it with many cause myself.

PS: i'm not sure there's will be response, since it's already very old.

Reply to this Comment

In most RDBMS, IN statements are rewritten at parse time as a list of OR predicates. So there should be no difference in performance.

Reply to this Comment

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.