Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

procedure performance Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 4:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
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
Post #1423535
Posted Monday, February 25, 2013 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1423577
Posted Monday, February 25, 2013 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
hi ,
i attached actual execution plan of my proc..please find attachment(results.txt)...


  Post Attachments 
results.txt (5 views, 116.84 KB)
Post #1423593
Posted Monday, February 25, 2013 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1423594
Posted Monday, February 25, 2013 7:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
hi
find the same


  Post Attachments 
result_1.sqlplan (7 views, 1.01 MB)
Post #1423598
Posted Monday, February 25, 2013 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1423624
Posted Monday, February 25, 2013 11:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
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.
Post #1423895
Posted Tuesday, February 26, 2013 6:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
hi chris,
find the updated execution plan.


  Post Attachments 
latest_execution_plan.sqlplan (3 views, 1.01 MB)
Post #1424065
Posted Tuesday, February 26, 2013 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1424077
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse