September 13, 2016 at 6:38 am
All,
I have a insert procedure, which is running very slow and need you advice.
This is SQL2008 version
This procedure is used to "roll-up" different tables and insert values into result table.
Below is the strucure of procedure. Please let me know if any additional information is required.
[font="Comic Sans MS"]
Insert into [di_rollup] ([Diag1]
,[Diag1POI]
,[Diag2]
,[Diag2POI]
,[Diag3]
,[Diag3POI]
........... till 30)
VALUES
, Diag1 = CDE_DIAG_1.CodeValue
, Diag1POI = CDE_DIAG_1.CodePOIInd
, Diag2 = CDE_DIAG_2.CodeValue
, Diag2POI = CDE_DIAG_2.CodePOIInd
, Diag3 = CDE_DIAG_3.CodeValue
, Diag3POI = CDE_DIAG_3.CodePOIInd
.
.
.
.. till Diag30POI
)
FROM CLM
LEFT JOIN CDE_DIAG_1 ON On (Primary key columns join here, And CDE_DIAG_1.CodeType = 'DIAG' And CDE_DIAG_1.CodeSequence = '1')
LEFT JOIN CDE_DIAG_2 ON On (Primary key columns join here, And CDE_DIAG_2.CodeType = 'DIAG' And CDE_DIAG_2.CodeSequence = '2')
LEFT JOIN CDE_DIAG_3 ON On (Primary key columns join here, And CDE_DIAG_3.CodeType = 'DIAG' And CDE_DIAG_2.CodeSequence = '3')
.
.
.
.
LEFT JOIN CDE_DIAG_30 ON On (Primary key columns join here, And CDE_DIAG_30.CodeType = 'DIAG' And CDE_DIAG_30.CodeSequence = '30')
----------and very similer strucure for below four tables.
--------- All the below 4 table has primary key deined on 3 columns (compound PK)
Insert into [pr_rollup]
Insert into [co_rollup]
Insert into [oc_rollup]
Insert into [cl_rollup]
---------Then, below two more inserts having self joins
Insert into TypeRollup (col1,col2........as selected below...)
SELECT DISTINCT
cpt1.col1, cpt1.col2,... ...... cpt1.col10
cpt2.col1, cpt2.col2,... ...... cpt2.col10
cpt3.col1, cpt3.col2,... ...... cpt3.col10
.
.
.
.
.
cpt10.col1, cpt10.col2,... ...... cpt10.col10
FROM Cache.Claims CLM
INNER JOIN PType AS cpt1 ON (Primary key column JOIN AND cpt1.PTypeID = 1)
LEFT JOIN PType AS cpt2 ON (Primary key column JOIN AND cpt2.PTypeID = 2)
LEFT JOIN PType AS cpt3 ON (Primary key column JOIN AND cpt3.PTypeID = 3)
LEFT JOIN PType AS cpt4 ON (Primary key column JOIN AND cpt4.PTypeID = 4)
LEFT JOIN PType AS cpt5 ON (Primary key column JOIN AND cpt5.PTypeID = 5)
LEFT JOIN PType AS cpt6 ON (Primary key column JOIN AND cpt6.PTypeID = 6)
LEFT JOIN PType AS cpt7 ON (Primary key column JOIN AND cpt7.PTypeID = 7)
LEFT JOIN PType AS cpt8 ON (Primary key column JOIN AND cpt8.PTypeID = 8)
LEFT JOIN PType AS PType AS cpt9 ON (Primary key column JOIN AND cpt9.PTypeID = 9)
LEFT JOIN PType AS cpt10 ON (Primary key column JOIN AND cpt10.PTypeID = 10)
LEFT JOIN PType AS cpt11 ON (Primary key column JOIN AND cpt11.ProviderTypeID = 11)
WHERE CLM.PKey = @PartKey (Primary Key column)
[/font]
Attached is the query plan for same.
Kindly suggest what is causing to run this procedure slow and what can be modified to improve the performance of same.
Thank you
Prasad
September 13, 2016 at 6:58 am
DML operations (INSERT, UPDATE, DELETE, and MERGE statements) hold an active transaction until the operation completes. When you have a DML operation that combines a complex SELECT with JOINs, then what you have is a very long transactions with update locks and blocking. It blocks other processes and also itself can be easily blocked. If blocking accounts for the long runtime duration, then consider selecting your aggregate results into temp tables, and then select from the temp tables into the persisted table in a subsequent INSERT operation.
For example:
-- Complicated select into temp table (#T1):
SELECT <...>
INTO #T1
FROM <...>
JOIN <...>
JOIN <...>
JOIN <...>
<...>;
-- Simple select into persisted table (P1):
INSERT INTO P1 < ... >
SELECT < ... >
FROM #T1;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 13, 2016 at 11:16 pm
This should go faster:
insert into TypeRollup (col1,col2........as selected below...)
SELECT cpt.col1, cpt.col2,... ...... cpt.col10
FROM PType cpt
WHERE CLM_PKey = @PartKey
and PTypeID BETWEEN 1 AND 11
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply