SQLCruise Carribean 2015 Hairy Execution Plan Winner

Since we're talking trillions of rows, I wonder if a columnstore index would make sense on some of these columns?

Hi,

I didn't get to spend a ton of time looking at the plan, but this is what I saw first.

For the two insert statements I saw the same problem. They are both using the substring function in the WHERE clause. This means that an index scan needs to occur instead of an index seek. I would investigate to see if it is possible to use persisted computed columns in that table to break up the ORD_RSN_OCR field into the parts you need. Then you can add those parts to an index.

I would also change @tblProductValues.LookupValues field to nvarchar(125). An implicit conversion is occuring when that table is being used in the first INSERT statement.

I don't know the data, but I would investigate to see if the DISTINCT is actually needed. It is very expensive. If there is a way to join to another table, or use some other logic that will make usre you have distinct rows with out using that operator, then you will be much better off.

Mickey

At a quick glance DISTINCT on the subquery for the INSERT INTO t_WSTDiscosStaging looks redundant as you have an aggregate on the outer query anyway.