Atkin’s Query Plan

One of our customer’s warehouses threw a fit over an extraction query this week. The query ran for two hours before we cancelled. We expected the query to run for about 5 minutes, but the database turned out to be subtly different then the testbed we use for optimizations. Here is a zoomed out view of the estimated query plan we started with:

Notice the line along the top where SQL is working hard between join steps to prepare data for the next join. After every little change to the query and indexes, I’d revisit the zoomed out plan to see if it was getting any skinner. I was thinking of calling this technique “big picture optimization”, or the “Atkins query plan”, but that’s just silly talk isn’t it? Here is a plan that finished in 4 minutes:

And with this picture of success, I’ll wish everyone reading a merry Christmas.