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:
Launch code in new window » Download code as text file »
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.
Download Code Snippet ZIP File
Comments (0) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Ask Ben: Averaging Date/Time Stamps In SQL
Problems With Storing Date And Time Separately In SQL Server
There are no comments posted for this web log entry.