SQLCruise Carribean 2015 Hairy Execution Plan Winner

There are two INSERTS in this batch that process hundreds of TRILLIONs of rows.

Unfortunately, they are only estimated plans. But I'd love to hear your thoughts about how to tune the two most demanding statements in the batch. I noticed, for example, that all of the Merge Joins are many-to-many. What else can we tune?

-Kevlink text

avatar image By Kevin 141 asked Feb 20, 2015 at 04:26 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

3 answers: sort voted first

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

avatar image By SQLMickey 16 answered Feb 20, 2015 at 05:41 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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.

avatar image By umair 16 answered Feb 20, 2015 at 06:03 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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

avatar image By Kevin 141 answered Feb 20, 2015 at 05:11 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x114
x109
x12

asked: Feb 20, 2015 at 04:26 PM

Seen: 411 times

Last Updated: Feb 20, 2015 at 06:03 PM