Maybe the query optimize is not so smart as you imagine

I had a database which stores huge amount of performance counter values. The table with counter values has a clustered index with column timekey in the first sequence. And there is time dimension table with column timekey as its clustered index.

One day I issued a query to find out the earliest date in the table, which looks like:
SELECT MAX(Date) FROM dbo.FactPerformance_History p
INNER JOIN dbo.DimTime t on p.TimeKey = t.TimeKey

I found the result was not returned immediately. So I checked the execution plan. I was quite surprised the execution plan showed two big tables FactPerformance_History and the DimTime were joined first then be aggregated for the max value.

It looks like query optimizer didn’t notice two tables were joined with two physical sorted columns. It decided to start the aggregation after the entire merge join was finished. Actually the query only needs the first match record with the two tables merged reversely.

Anyway, the query optimizer didn’t do it. So I can only rewrote the query:
SELECT Date FROM (SELECT MAX(TimeKey) AS TimeKey FROM Array.[FactDevicePerformance_History]) AS p
INNER JOIN ETL.DimTime t on p.TimeKey = t.TimeKey

Two execution plans here:

First query

query 1

Second query

It took 12 seconds to return with the result the first query, but only 6ms the new one.

OK. I agree semantics of the two queries are not exact the same. But if query optimizer can make the merge and stream aggregate run in asynchronously, then I believe the execution time of two plan above will be same.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: