Benchmarking

Lukas Eder notes that the best way to compare performance is to…compare performance:

To bust a myth, if you have good reasons to think that a differently written, but semantically equivalent query might be faster (on your database), you should measure. Don’t even trust any execution plan, because ultimately, what really counts is the wall clock time in your production system.

If you can measure your queries in production, that’s perfect. But often, you cannot – but you don’t always have to. One way to compare two queries with each other is to benchmark them by executing each query hundreds or even thousands of times in a row.

Lukas goes on to compare a left join to a correlated subquery in three separate database products and the results are very interesting although absolutely not comparable across products because the DeWitt Clause is still a thing.  Great read.

Related Posts

Separating Data And Log Files

Brent Ozar looks at an old chestnut: So it’s time for a quiz: If you put all of a SQL Server’s data files & logs on a single volume, how many failures will that server experience per year? Bonus question: what kinds of data loss and downtime will each of those failure(s) have? If you […]

Read More

Combinatorics With Joins

Dmitry Zaytsev explains the math behind why query plans can be so inefficient when dealing with a large number of joins: Let’s talk about the sequence of table joins in detail. It is very important to understand that the possible number of table joins grows exponentially, not linearly. Fox example, there are only 2 possible […]

Read More

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031