May 15, 2014 at 11:28 am
Hi All,
I have a insert query which is a long runing , it is complex where it reads and then insert data.
PLease provide your input to tune it. I have attached it below.
Cheers,
Got an idea..share it !!
DBA_Vishal
May 15, 2014 at 11:34 am
Does the query plan show scans instead of seeks? Are the predicates (JOINS and WHERE clauses) indexed in the source tables? Are there many indexes on the target table (Consider dropping or disabling them, then rebuilding them later)?
How many rows does this subquery return?
(SELECT ASSIGNED_AMBEST
FROM DEP_DW.DW_REF_CV_CARRIER
WHERE LOAD_FLG = 'Y'
AND EXCLUDE_FLG = 'Y'
AND ACTIVE_FLAG = 'Y')
This may cause the complexity to go to O(n^2)
May 15, 2014 at 11:40 am
If you reformat your big query as a collection of smaller CTEs and one final insert, it will be easier to read (for you, me and whoever has to support it after you win the lottery!)
e.g.
;with cte1 as (...subquery1...),
cte2 as (...subquery2...),
...
cten as (...subqueryn...
insert into ...
select cte1
join cte2 on ...
join cte3 on ...
...
join cten on ...
May 15, 2014 at 12:17 pm
You've got a bunch of stuff that's going to lead to scans. Adding columns like this:
(prev_stat.BIZ_YEAR + prev_stat.BIZ_MONTH)
For a comparison will prevent statistics use and lead to scans. Functions on columns like this:
ISNULL(prev_stat.pol_cancel_dt, prev_stat.POLICY_TERM_END_DT)
Will prevent statistics use and lead to scans.
Are the tables you're reading from read/write or are they read_only? If they're read_only, why not just use read_uncommitted at the head of the query rather than NO_LOCK? It makes for much cleaner code and you don't have to remember to put NO_LOCK everywhere. If they're read/write, you do know that NO_LOCK can lead to duplicate and missing rows? I sure hope you're not dealing with financial data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2014 at 12:52 pm
Adding to what Grant said, there are some HORRIBLY bad design and coding choices here. Some of them you will likely have no (easy) hope of overcoming.
In addition, there are some SORT items that could be crushing tempdb, in addition to likely HASH JOINS too.
Have you done a wait stats and file IO stall analysis while this is running? That could reveal some hardware that is not up to the task either.
Oh, and has this run fine in the past and then all of a sudden started tanking, or has it always been bad?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 16, 2014 at 1:01 am
Thank you all for the input , I am proud to be member of this forum.
We do truncate load to the staging table( source) , rebuild all in indexes on stage and the insert to DW tables. All the indexes dissables except cluster. We have paritioned these tables on BIZ_dt column.
so If I use CTE..as suggested and join will that help ?
Cheers,
Got an idea..share it !!
DBA_Vishal
May 16, 2014 at 5:06 am
A CTE mostly just addresses formatting in this case. It's not going to change performance. You still have to address the things that I brought up or you'll just see scans and things will only go as fast as your disks and memory can support.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2014 at 6:02 am
Sure I will see if those comparison and scan are reduced.
Cheers,
Got an idea..share it !!
DBA_Vishal
May 16, 2014 at 7:16 am
The only difference I can spot between the two UNIONed queries is this filter:
prev_stat.DW_POLICY_STATUS IN (7) or prev_stat.DW_POLICY_STATUS IN (8)
If this is the case, then combine the two queries:
SELECT prevsub_pol.d_entity_id AS Prev_d_entity_id
,prev_stat.DW_POLICY_STATUS AS Prev_policy_status
,prev_stat.TRNS_EFF_DT AS Prev_TRNS_EFF_DT
,prev_stat.POLICY_TERM_BEGIN_DT AS Prev_trm_bgn_dt
,prev_stat.POLICY_INCEPTION_DT AS Prev_incp_dt
,prev_stat.POLICY_TERM_END_DT AS Prev_trm_end_dt
,prev_stat.POLICY_NUM AS Prev_pol_num
,prev_stat.CARRIER_ID AS Prev_assigned_ambest_num
,prev_stat.pol_cancel_dt AS Prev_pol_cancel_dt
,pol_trans.I_CV AS Prev_I_CV
,CASE WHEN prevsub_pol.C_GEND IN ('M','F') THEN prevsub_pol.C_GEND ELSE 'U' END AS PREV_C_GEND
,prevsub_pol.[D_DRIVER_AGE] AS PREV_driver_age
,pol_details.C_ST_ALPH AS PREV_C_ST_ALPH
,pol_details.C_ZIP AS PREV_C_ZIP
,pol_details.M_COUNTY_CODE AS PREV_M_COUNTY_CODE
,pol_details.M_COUNTY_NAME AS PREV_M_COUNTY_NAME
,prev_stat.BIZ_DT AS PREV_BIZ_DT
,prev_stat.BIZ_MONTH AS PREV_BIZ_MONTH
,prev_stat.BIZ_YEAR AS PREV_BIZ_YEAR
,ROW_NUMBER() OVER (
PARTITION BY prev_stat.POLICY_NUM,prev_stat.CARRIER_ID
ORDER BY prev_stat.POLICY_TERM_BEGIN_DT DESC,pol_trans.I_CV DESC,pol_trans.h_trns DESC,prevsub_pol.d_entity_id DESC
) AS ROWNO_PREV
,CASE
WHEN isnull(prev_stat.CARRIER_ID, 00000) = (SELECT ASSIGNED_AMBEST
FROM DEP_DW.DW_REF_CV_CARRIER
WHERE LOAD_FLG = 'Y' AND EXCLUDE_FLG = 'Y' AND ACTIVE_FLAG = 'Y')
THEN 'T'
ELSE 'F'
END AS 'PREV_FLAG'
FROM [DEP_DW].[DW_CV_Policy_Status] prev_stat WITH (NOLOCK)
INNER JOIN [DEP_DW].[DW_CV_SubjectDetails] prevsub_pol WITH (NOLOCK)
ON prevsub_pol.n_pol = prev_stat.POLICY_NUM
AND prev_stat.CARRIER_ID = prevsub_pol.I_AMBEST_CO_NUM
AND prevsub_pol.C_REL_POLHLD IN (@C_REL_POLHLD_PP,@C_REL_POLHLD_SP)
INNER JOIN [DEP_DW].[DW_CV_PolicyTransDetails] pol_trans WITH (NOLOCK)
ON prevsub_pol.n_pol = pol_trans.n_pol
AND prev_stat.POLICY_TERM_END_DT = pol_trans.D_POL_EXPIR
AND prev_stat.CARRIER_ID = pol_trans.I_AMBEST_CO_NUM
AND pol_trans.REJECT_FLG = 'N'
INNER JOIN [DEP_DW].[DW_CV_AutoPolicyDetails] pol_details WITH (NOLOCK)
ON pol_trans.AutoPolicy_Checksum = pol_details.AutoPolicy_Checksum
WHERE 1 = 1
AND prev_stat.C_LOB = @C_LOB
AND (prev_stat.BIZ_YEAR + prev_stat.BIZ_MONTH) <= cast(YEAR(@StartDate) AS VARCHAR) + RIGHT(('0' + cast(MONTH(@StartDate) AS VARCHAR)), 2)
AND prev_stat.DW_POLICY_STATUS IN (8)
AND prev_stat.POLICY_TERM_BEGIN_DT != ISNULL(prev_stat.pol_cancel_dt, prev_stat.POLICY_TERM_END_DT)
UNION ALL
SELECT prevsub_pol.d_entity_id AS Prev_d_entity_id
,prev_stat.DW_POLICY_STATUS AS Prev_policy_status
,prev_stat.TRNS_EFF_DT AS Prev_TRNS_EFF_DT
,prev_stat.POLICY_TERM_BEGIN_DT AS Prev_trm_bgn_dt
,prev_stat.POLICY_INCEPTION_DT AS Prev_incp_dt
,prev_stat.POLICY_TERM_END_DT AS Prev_trm_end_dt
,prev_stat.POLICY_NUM AS Prev_pol_num
,prev_stat.CARRIER_ID AS Prev_assigned_ambest_num
,prev_stat.pol_cancel_dt AS Prev_pol_cancel_dt
,pol_trans.I_CV AS Prev_I_CV
,CASE WHEN prevsub_pol.C_GEND IN ('M','F') THEN prevsub_pol.C_GEND ELSE 'U' END AS PREV_C_GEND
,prevsub_pol.[D_DRIVER_AGE] AS PREV_driver_age
,pol_details.C_ST_ALPH AS PREV_C_ST_ALPH
,pol_details.C_ZIP AS PREV_C_ZIP
,pol_details.M_COUNTY_CODE AS PREV_M_COUNTY_CODE
,pol_details.M_COUNTY_NAME AS PREV_M_COUNTY_NAME
,prev_stat.BIZ_DT AS PREV_BIZ_DT
,prev_stat.BIZ_MONTH AS PREV_BIZ_MONTH
,prev_stat.BIZ_YEAR AS PREV_BIZ_YEAR
,ROW_NUMBER() OVER (
PARTITION BY prev_stat.POLICY_NUM,prev_stat.CARRIER_ID
ORDER BY prev_stat.POLICY_TERM_BEGIN_DT DESC,pol_trans.I_CV DESC,pol_trans.h_trns DESC,prevsub_pol.d_entity_id DESC
) AS ROWNO_PREV
,CASE
WHEN isnull(prev_stat.CARRIER_ID, 00000) = (SELECT ASSIGNED_AMBEST
FROM DEP_DW.DW_REF_CV_CARRIER
WHERE LOAD_FLG = 'Y' AND EXCLUDE_FLG = 'Y' AND ACTIVE_FLAG = 'Y')
THEN 'T'
ELSE 'F'
END AS 'PREV_FLAG'
FROM [DEP_DW].[DW_CV_Policy_Status] prev_stat WITH (NOLOCK)
INNER JOIN [DEP_DW].[DW_CV_SubjectDetails] prevsub_pol WITH (NOLOCK)
ON prevsub_pol.n_pol = prev_stat.POLICY_NUM
AND prev_stat.CARRIER_ID = prevsub_pol.I_AMBEST_CO_NUM
AND prevsub_pol.C_REL_POLHLD IN (@C_REL_POLHLD_PP,@C_REL_POLHLD_SP)
INNER JOIN [DEP_DW].[DW_CV_PolicyTransDetails] pol_trans WITH (NOLOCK)
ON prevsub_pol.n_pol = pol_trans.n_pol
AND prev_stat.TRNS_EFF_DT = pol_trans.D_TRNS_EFF
AND prev_stat.CARRIER_ID = pol_trans.I_AMBEST_CO_NUM
AND pol_trans.REJECT_FLG = 'N'
INNER JOIN [DEP_DW].[DW_CV_AutoPolicyDetails] pol_details WITH (NOLOCK)
ON pol_trans.AutoPolicy_Checksum = pol_details.AutoPolicy_Checksum
WHERE 1 = 1
AND prev_stat.C_LOB = @C_LOB
AND (prev_stat.BIZ_YEAR + prev_stat.BIZ_MONTH) <= cast(YEAR(@StartDate) AS VARCHAR) + RIGHT(('0' + cast(MONTH(@StartDate) AS VARCHAR)), 2)
AND prev_stat.DW_POLICY_STATUS IN (7)
AND prev_stat.POLICY_TERM_BEGIN_DT != ISNULL(prev_stat.pol_cancel_dt, prev_stat.POLICY_TERM_END_DT)
-- =======================================================================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT prevsub_pol.d_entity_id AS Prev_d_entity_id
,prev_stat.DW_POLICY_STATUS AS Prev_policy_status
,prev_stat.TRNS_EFF_DT AS Prev_TRNS_EFF_DT
,prev_stat.POLICY_TERM_BEGIN_DT AS Prev_trm_bgn_dt
,prev_stat.POLICY_INCEPTION_DT AS Prev_incp_dt
,prev_stat.POLICY_TERM_END_DT AS Prev_trm_end_dt
,prev_stat.POLICY_NUM AS Prev_pol_num
,prev_stat.CARRIER_ID AS Prev_assigned_ambest_num
,prev_stat.pol_cancel_dt AS Prev_pol_cancel_dt
,pol_trans.I_CV AS Prev_I_CV
,CASE WHEN prevsub_pol.C_GEND IN ('M','F') THEN prevsub_pol.C_GEND ELSE 'U' END AS PREV_C_GEND
,prevsub_pol.[D_DRIVER_AGE] AS PREV_driver_age
,pol_details.C_ST_ALPH AS PREV_C_ST_ALPH
,pol_details.C_ZIP AS PREV_C_ZIP
,pol_details.M_COUNTY_CODE AS PREV_M_COUNTY_CODE
,pol_details.M_COUNTY_NAME AS PREV_M_COUNTY_NAME
,prev_stat.BIZ_DT AS PREV_BIZ_DT
,prev_stat.BIZ_MONTH AS PREV_BIZ_MONTH
,prev_stat.BIZ_YEAR AS PREV_BIZ_YEAR
,ROW_NUMBER() OVER (
PARTITION BY prev_stat.DW_POLICY_STATUS, prev_stat.POLICY_NUM,prev_stat.CARRIER_ID
ORDER BY prev_stat.POLICY_TERM_BEGIN_DT DESC,pol_trans.I_CV DESC,pol_trans.h_trns DESC,prevsub_pol.d_entity_id DESC
) AS ROWNO_PREV
,CASE
WHEN isnull(prev_stat.CARRIER_ID, 00000) = (SELECT ASSIGNED_AMBEST
FROM DEP_DW.DW_REF_CV_CARRIER
WHERE LOAD_FLG = 'Y' AND EXCLUDE_FLG = 'Y' AND ACTIVE_FLAG = 'Y')
THEN 'T'
ELSE 'F'
END AS 'PREV_FLAG'
FROM [DEP_DW].[DW_CV_Policy_Status] prev_stat WITH (NOLOCK)
INNER JOIN [DEP_DW].[DW_CV_SubjectDetails] prevsub_pol
ON prevsub_pol.n_pol = prev_stat.POLICY_NUM
AND prev_stat.CARRIER_ID = prevsub_pol.I_AMBEST_CO_NUM
AND prevsub_pol.C_REL_POLHLD IN (@C_REL_POLHLD_PP,@C_REL_POLHLD_SP)
INNER JOIN [DEP_DW].[DW_CV_PolicyTransDetails] pol_trans
ON prevsub_pol.n_pol = pol_trans.n_pol
AND prev_stat.TRNS_EFF_DT = pol_trans.D_TRNS_EFF
AND prev_stat.CARRIER_ID = pol_trans.I_AMBEST_CO_NUM
AND pol_trans.REJECT_FLG = 'N'
INNER JOIN [DEP_DW].[DW_CV_AutoPolicyDetails] pol_details
ON pol_trans.AutoPolicy_Checksum = pol_details.AutoPolicy_Checksum
WHERE 1 = 1
AND prev_stat.C_LOB = @C_LOB
AND (prev_stat.BIZ_YEAR + prev_stat.BIZ_MONTH) <= cast(YEAR(@StartDate) AS VARCHAR) + RIGHT(('0' + cast(MONTH(@StartDate) AS VARCHAR)), 2)
AND prev_stat.DW_POLICY_STATUS IN (7,8)
AND prev_stat.POLICY_TERM_BEGIN_DT != ISNULL(prev_stat.pol_cancel_dt, prev_stat.POLICY_TERM_END_DT)
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply