![]() ![]() The test results were less conclusive at 10,000 rows (with one notable exception), but the trends we see at 1,000,000 rows are clearly discernable by the time we have 100,000 rows. Just to make our queries lengthy enough to be nicely measurable, I arbitrarily chose to insert 1,000,000. Now that we know we're dealing with integer data, we'll set up a table with an integer column, then we'll set up a timing and iteration framework for tracking our query times over multiple executions. The original question was accompanied by a snippet of a WHERE clause evaluating the inequality of a column and four integer values: With all that in mind, let's set off to find out if NOT IN is quicker than a "bunch of " and see a few methods that improve in their performance. ![]() ![]() I also discovered several other methods (written by people much smarter than myself) that were significantly quicker then either of the two methods I set out to test (the quickest method I tested was a full 35% faster than the quicker of the original two). In actuality, I did find that one of the queries typically outperformed the other. The query engine takes both of these queries and performs them with the exact same sequence of events. This is because SQL is a declarative language, meaning: you tell the computer what you want, not how to get it. It turns out that they are actually the same query, and it should make absolutely no difference. The answer this time was a bit unexpected. This time, however, I thought I'd do some investigation to see if, in fact, we should prefer one method over the other in this case. A coworker recently asked me which was "more efficient - a bunch of or a NOT IN clause?" The answer, I assumed, like almost all things relating to databases, is "it depends." The nice thing about that answer is that it not only camouflages my vast stores of ignorance, it is also quite often true. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |