Table with 789.6 million records taking 5 mins to execute the select stmt

Question

Post reply

Table with 789.6 million records taking 5 mins to execute the select stmt

harris32

Valued Member

Points: 70
More actions
June 25, 2015 at 2:50 pm

#305399

Thanks all for your help. Here is my query(ofcourse table names have been changed) which I'm trying to tune and currently taking 4-5 mins to finish . Also, you can see the tables details and other info below with the attachments on exe plan, etc.. .
Database1.Schema1.Object5:
Total Records : 789.6 million
of records between 01/01/2014 and 01/31/2014 : 28.2 million
My table has around 789 million records and it is partitioned on "Column19" by month and year .
Clustered index on Column19
Database1.Schema1.Object6:
Total Records : 24791
Database1.Schema1.Object7:
Total Records : 311
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Database1.Schema1.Object6'. Scan count 9, logical reads 1082, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Database1.Schema1.Object7'. Scan count 4, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Database1.Schema1.Object5'. Scan count 9, logical reads 280072, physical reads 283, read-ahead reads 130274, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(17064 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 697975 ms, elapsed time = 254160 ms.
INSERT INTO Object1
(
Column3,
Column4,
Column5,
Column6,
Column7,
Column8,
Column9,
Column10,
Column11,
Column12,
Column13,
Column14,
Column15,
Column16,
Column17,
Column18
)
SELECT
Column3,
Column4,
Column5,
Isnull(right(Column4,9), Isnull),
Isnull(right(Column4,9), Isnull),
Object2.Column8,
Column9= Object2.Column8,
Column10 = Object3.Column19,
Column11 =
Sum (
CASE
WHEN Object3.Column8 IS NOT NULL
THEN Object3.Column20
ELSE 0
END
),
Column12 =
Sum (
CASE
WHEN Object3.Column8 IS NOT NULL AND Datediff(Column21,Object2.Column22,Object3.Column19) < 365
THEN Object3.Column20
ELSE 0
END
),
Column13 =
Sum (
CASE
WHEN Object3.Column23 = ?
THEN Object3.Column20
ELSE 0
END
),
NULL,
Column15 =
Sum (
CASE
WHEN Object3.Column8 IS NOT NULL AND Datediff(Column21,Object2.Column22,Object3.Column19) < 365
THEN Object3.Column20
ELSE 0
END
),
Column16 =
Sum (
CASE
WHEN Object4.Column24 IN ('abc', 'xyz', 'lmn' )
THEN Object3.Column20
ELSE 0
END
),
Column17 = 0,
Object2.Column18
FROM
Database1.Schema1.Object5 Object3
INNER JOIN Database1.Schema1.Object6 Object2
ON Object2.Column25 = Object3.Column25
AND Object2.Column26 = Object3.Column26
AND Object2.Column27 = Object3.Column27
AND Object2.Column28 = 'Y'
INNER JOIN Database1.Schema1.Object7 Object4
ON Object4.Column29 = Object3.Column29
WHERE
Object3.Column19 BETWEEN '01/01/2014' AND '01/31/2014'
GROUP BY
Object3.Column30,
Object3.Column31,
Object3.Column32,
Object3.Column25,
Object3.Column26,
Object3.Column19,
Object2.Column33,
Object2.Column22,
Object2.Column8
,Object3.Column4
,Object3.Column3
,Object3.Column5
,Object2.Column18

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

harris32 Valued Member Points: 70 More actions · Answer 1

harris32

Valued Member

Points: 70

June 25, 2015 at 2:56 pm

#1808246

And here are other attachments as well...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

What would really help is the actual execution plan as a *.sqlplan file instead of pictures of the execution plan.

harris32 Valued Member Points: 70 More actions · Answer 3

Sorry. I can't post the actual exec plan as it will have the table names.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

harris32 (6/25/2015)
Sorry. I can't post the actual exec plan as it will have the table names.

Okay. I can't really help looking at the pictures even though they are pretty.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

How many indexes do you have on the HEAP that you're inserting to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

And, like I said [font="Arial Black"]here[/font], it would seem that you need to do a little "Divide'n'Conquer" and well as a bit of preaggregation although the final insert is looking really painful according to the graphic execution plan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

harris32 Valued Member Points: 70 More actions · Answer 7

I'm inserting the data into the temp table and how does the index matter on the table to which the data is loading ?

Thanks for your reply though .

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

harris32 (6/25/2015)
I'm inserting the data into the temp table and how does the index matter on the table to which the data is loading ?
Thanks for your reply though .

Speed. It takes time to update indexes to contain new rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)