I’ve been so busy with the move to Singapore I haven’t had time to blog, let alone think! So tonight I blog, tomorrow I’ll try to think…or not.
Anyhoo, Query Fusion. Good Stuff. Makes you go fast. Let’s talk about it. Let’s see how it works under the covers.
As you may already know, Query Fusion is a performance enhancing technique through which multiple queries at the same level of detail can be combined and fired as a single unit. In almost all cases, executing fewer queries is better. In its current 9.0 incarnation, the feature works with live data sources (translation: not against extracts).
Lets take a look at a dashboard, which contains 3 views and a quick filter.
In Tableau 8.3 this sucker should execute 4 queries against a live data source assuming an empty cache:
• One query each for our 3 views
• A single query to establish and display the domain (range) for the quick filter
If we view the logs in 8.3, we can see this is exactly what happens. Here is the log file – note the 4 begin query / end query pairs:
The Data Interpreter executes our 3 “viz queries” in 2.185 seconds, .95 seconds, and .96 seconds respectively. The Quick Filter domain is calculated within .033 seconds.
Viewing the tabprotosrv log, we can see the exact queries being executed against my database and the time it took to get a result:
It took SQL Server 2.11 seconds, .92 seconds, .92 seconds and .007 seconds to return rows to us…the deltas you see between this log and the previous one is us “thinking”.
When all is said and done, our little dashboard takes just north of 4 seconds (about 4.25) to render in 8.3 according to Performance Recorder:
What happens in 9? Let’s start with Performance Recorder again (and by the way, I cleared SQL Server’s buffer cache between runs and ran Desktop 9 on the same machine to make sure we have an apples-to-apples comparison):
The exact same report took just a tiny bit more than 3 seconds to complete. Bingo – nearly 1.2 seconds saved.
The difference is pretty clear – we’re executing one query for our vizzes instead of three. The logs are very interesting to look at on this one.
First, we see that Tableau is going to consider the execution of 4 “questions”, just like last time.
However, we see that queries 0, 1 and 3 can be combined:
Only query 2 needs to be executed on it’s own. This is the query for our quick filter:
We generate a new query (query 4, which actually is the 5th query since we’re counting with a base of zero) to take care of combining queries 0, 1, and 3 and we execute it:
This is clearly a winner because it only takes 2.6 seconds for SQL Server to execute vs. (2.11 + .92 + .92) = 3.95 we saw previously.
Hooray! Query Fusion! My hero!
