procedure performance

Question

procedure performance

sathiyan00

SSCommitted

Points: 1879
More actions
February 25, 2013 at 4:32 am

#394249

Hi all,
Following is my stored procedure in that i merge three same structured table with base table.
i created one non clustered index on all five column(col1,col2,col3,col4,col5) in each table(table_1,table_2,table_3,base_table).
each table has around 4 to 5 crore records.
any suggestions to improve the performance in sql server 2008 r2.
PROCEDURE [dbo].[usp_merge_and_insert]
AS
BEGIN
--Decalre all the variable in the begining
DECLARE @error VARCHAR(1024)
BEGIN TRY
MERGE base_table AS tvs
USING (SELECT [col1]
,[col2]
,[col3]
,[col4]
,[col5]
,MAX([col6])
,MAX([col7])
,MAX([col8])
FROM table_1 GROUP BY [col1]
,[col2]
,[col3]
,[col4]
,[col5] ) AS tvs1
ON tvs.col1= tvs1.col1 AND tvs.col2=tvs1.col2 AND tvs.col3=tvs1.col3
AND tvs.col4=tvs.col4 AND tvs.col5=tvs1.col5
WHEN MATCHED THEN
UPDATE SET tvs.co16=tvs1.col6
WHEN NOT MATCHED THEN
INSERT([col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7]
,[col8]
)
VALUES(
tsv1.[col1]
,tsv1.[col2]
,tsv1.[col3]
,tsv1.[col4]
,tsv1.[col5]
,tsv1.[col6]
,tsv1.[col7]
,tsv1.[col8]);
--to merge table_2 with base table
MERGE base_table AS tvs
USING (SELECT [col1]
,[col2]
,[col3]
,[col4]
,[col5]
,MAX([col6])
,MAX([col7])
,MAX([col8])
FROM table_2 GROUP BY [col1]
,[col2]
,[col3]
,[col4]
,[col5] ) AS tvs2
ON tvs.col1= tvs2.col1 AND tvs.col2=tvs2.col2 AND tvs.col3=tvs2.col3
AND tvs.col4=tvs2.col4 AND tvs.col5=tvs2.col5
WHEN MATCHED THEN
UPDATE SET tvs.co16=tvs2.col6
WHEN NOT MATCHED THEN
INSERT([col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7]
,[col8]
)
VALUES(
tsv2.[col1]
,tsv2.[col2]
,tsv2.[col3]
,tsv2.[col4]
,tsv2.[col5]
,tsv2.[col6]
,tsv2.[col7]
,tsv2.[col8]);
--to merge table_3 with base table
MERGE base_table AS tvs
USING (SELECT [col1]
,[col2]
,[col3]
,[col4]
,[col5]
,MAX([col6])
,MAX([col7])
,MAX([col8])
FROM table_3 GROUP BY [col1]
,[col2]
,[col3]
,[col4]
,[col5] ) AS tvs3
ON tvs.col1= tvs3.col1 AND tvs.col2=tvs3.col2 AND tvs.col3=tvs3.col3
AND tvs.col4=tvs3.col4 AND tvs.col5=tvs3.col5
WHEN MATCHED THEN
UPDATE SET tvs.co16=tvs3.col6
WHEN NOT MATCHED THEN
INSERT([col1]
,[col2]
,[col3]
,[col4]
,[col5]
,[col6]
,[col7]
,[col8]
)
VALUES(
tsv3.[col1]
,tsv3.[col2]
,tsv3.[col3]
,tsv3.[col4]
,tsv3.[col5]
,tsv3.[col6]
,tsv3.[col7]
,tsv3.[col8]);
END TRY
BEGIN CATCH
SELECT @error = ERROR_MESSAGE()
RAISERROR(@error,16,2)
END CATCH
END

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you please post the actual execution plans of all three merge statements.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sathiyan00 SSCommitted Points: 1879 More actions · Answer 2

hi ,

i attached actual execution plan of my proc..please find attachment(results.txt)...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sathiyan00 (2/25/2013)
hi ,
i attached actual execution plan of my proc..please find attachment(results.txt)...

Can you save it as a .sqlplan file and post, please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sathiyan00 SSCommitted Points: 1879 More actions · Answer 4

sathiyan00

SSCommitted

Points: 1879

February 25, 2013 at 7:04 am

#1590793

hi

find the same

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Thanks. The plans for each of the MERGE statements are very similar. Here's what I suggest you do; pick one of the statements and work with it in a query window in a test environment. Ensure your statistics are up to date - some of the estimated row counts suggest they are not. Then run the query, capture the actual execution plan and post it here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sathiyan00 SSCommitted Points: 1879 More actions · Answer 6

thanks,

i think clustered index(primary key) in base_table is costing more.

is it good idea to drop and create clustered index after merge statement.

sathiyan00 SSCommitted Points: 1879 More actions · Answer 7

sathiyan00

SSCommitted

Points: 1879

February 26, 2013 at 6:50 am

#1591174

hi chris,

find the updated execution plan.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

It's an estimated plan - the actual plan for one of the queries would be much more informative. However, the following points spring to mind.

Statistics appear to be out of date. Update statistics on the tables involved before running the stored procedure.

The existing indexes on both source and target are not helping. I'd recommend clustering each of the source tables using the columns used for joins to the merge target. The result is a fully-covering index - except it's the table.

If the target clustered index isn't suitable to assist in the joins, then create a non-clustered index which is a better fit than TSV_IX_1. This index is very expensive to maintain and is used in only one of the four merge statements.

When changing this amount of data, you may wish to rebuild rather than reorganize the indexes after the merge operation.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden