The results regularly go back and forth, much like the heads and tails of a coin toss. So, I have managed to convince myself that, despite the two execution times listed above, these two queries are, indeed, the same query as far as SQL Server is concerned - at least on this day, on this server, for these queries (I still gravitate toward the comforting ambiguity of "it depends"). I mentioned earlier that there were several queries that outperform our basic AND and NOT IN queries (on this server on this day). Let's take a look at some of those queries and their execution results. The first alternative technique doesn't use a WHERE clause to filter out our integer values. ON tbl_IN_VS_AND.filterCriterion_sv = tbl.filterValue_val It places the integer values into a UNION query and does a LEFT OUTER JOIN against that to filter out unequal rows. "derived UNION table LEFT OUTER JOIN" ET: 34360 ms On this test run of 100 executions, this odd query was consistently outperforming the quicker of our original two queries by about 19%: It definitely feels odd, placing things you would normally put in a WHERE clause into a derived table then looking for absent values, but the performance benefit gives us a compelling reason to consider doing this. ![]() Our last query happened to be our best performing (on this server, on this day). Like the previous query, this one uses a derived table. ![]() "NOT EXISTS from derived UNION table" ET: 27920 ms WHERE tbl.filterValue_val = tbl_IN_VS_AND.filterCriterion_sv )Īnd here is the time it took for 100 executions: However, it takes it one step further and nests that inside an IF NOT EXISTS(). On this day, on this server, this query runs a full 35% faster than the quicker of our two original queries. Are there even faster ways to run this query? I would say the odds are pretty good that there are.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |