For the specific scenario discussed in my previous post, we observed that using an intermediate Cartesian product was about three times faster than doing joins with multiple lookup tables. To keep the discussions manageable, we limited the max degree of parallelism to 1. In other words, we did not allow query parallelism. For any query that touches a large amount of data, that was silly, especially when it’s on a server with 80 logical processors.
To be more realistic, I then looked at the following four scenarios:
- No parallelism, using the Cartesian product materialized into a temp table
- No parallelism, using joins with the six lookup tables
- Maxdop set to 20, using the Cartesian product materialized into a temp table
- Maxdop set to 20, using joins with the six lookup tables
The following chart summarizes the test results:
It’s clear that the queries can benefit greatly from parallelism. In fact, in the two cases where a Cartesian product was used, setting maxdop to 20 sped the query up ~13 times. The query duration dropped from 248 seconds to 19 seconds!
It’s intuitive that enabling parallelism would give us a boost.
It’s also interesting to note that the query with multi-table joins did not receive the same kind of performance boost from parallelism as did the query with the single table join using the Cartesian product temp table. The query duration dropped from 740 seconds to 80 seconds when parallelism was enabled on the query joining directly with the six lookup tables, about ~9 times speed up (compared with ~13 times speed up in case of the Cartesian product).
Even when maxdop was set to 20, going from multi-table joins to a single table join using the Cartesian product temp table still gave us a 4 times performance boost. The duration went from 80 seconds to 19 seconds.
At least for this specific scenario, parallelism plus the Cartesian product temp table gave us the best performance. In the next post, I’ll look at the impact of parallelism more closely, in particular, how different degrees of parallelism affect performance.