Query taking long time-perf
-
Hi All,
One of the SELECT query is taking a long time to execute.
Its is a view and it is calling another view. dev team says,its a simple SELECT, why it takes so much time. I see this query is filling up entire tempdb i.e. more than 2TB and it takes 7 hours time and still runs.
Looking for some suggestions in improving the query performance.
Attaching the estimated query plan. Rowcounts, index info.
Estimated plan.
https://www.brentozar.com/pastetheplan/?id=rkRFSvc-a
offending query
================
SELECT * FROM CONTACT_MULTIPLE_C_LEVEL_DETAILS_V;
View definitions
===================
CREATE VIEW [dbo].[CONTACT_MULTIPLE_C_LEVEL_DETAILS_V]
AS
SELECT C_VIEW.ACCOUNT_MDM_ID,
P_ACCT.PARTY_NM AS ACCOUNT_NAME,
C_VIEW.ACCOUNT_SURF_ID,
C_VIEW.C_LEVEL_CONTACT_MDM_ID,
C_VIEW.C_LEVEL_CONTACT_FULL_NM,
C_VIEW.C_LEVEL_CONTACT_EMAIL,
C_VIEW.C_LEVEL_CONTACT_JOB_TITLE,
C_VIEW.JOB_FUNCTION,
C_VIEW.CONTACT_PHONE,
C_VIEW.C_LEVEL_CONTACT_SOURCE_URL,
C_VIEW.LAST_UPDATE_DATE,
C_VIEW.DELETE_FLAG
FROM [CONTACT_C_LEVEL_DETAILS_V] C_VIEW, ---///view and old ansi join
C_B_PARTY P_ACCT,
(
SELECT ACCOUNT_MDM_ID,
JOB_FUNCTION
FROM [CONTACT_C_LEVEL_DETAILS_V]
WHERE DELETE_FLAG = '0'
GROUP BY ACCOUNT_MDM_ID,
JOB_FUNCTION
HAVING COUNT(1) > 1
) C_MULTI_VIEW
WHERE C_VIEW.ACCOUNT_MDM_ID = C_MULTI_VIEW.ACCOUNT_MDM_ID
AND C_VIEW.ACCOUNT_MDM_ID = P_ACCT.MDM_ID
AND C_VIEW.JOB_FUNCTION = C_MULTI_VIEW.JOB_FUNCTION
AND C_VIEW.DELETE_FLAG = '0'
GO--nested view
CREATE view [dbo].[CONTACT_C_LEVEL_DETAILS_V] as
SELECT distinct
QUERY.CNT_PARTY_ROWID_OBJECT,
QUERY.ACCT_PARTY_ROWID_OBJECT,
QUERY.ACTV_FLG,
QUERY.ORG_LEVEL,
QUERY.VALIDATION_FLAG,
CONCAT(RTRIM(QUERY.CNT_PARTY_ROWID_OBJECT),'|',RTRIM(QUERY.ACCT_PARTY_ROWID_OBJECT),'|',SPLIT.ITEM) as PRIMARY_KEY,
QUERY.ACCOUNT_MDM_ID,
QUERY.ACCOUNT_SURF_ID,
QUERY.C_LEVEL_CONTACT_MDM_ID,
QUERY.C_LEVEL_CONTACT_FULL_NM,
QUERY.C_LEVEL_CONTACT_EMAIL,
QUERY.C_LEVEL_CONTACT_JOB_TITLE,
SPLIT.ITEM as JOB_FUNCTION,
QUERY.CONTACT_PHONE,
QUERY.C_LEVEL_CONTACT_SOURCE_URL,
QUERY.LAST_UPDATE_DATE,
QUERY.DELETE_FLAG AS DELETE_FLAG
FROM (
SELECT
P_CNCT.ROWID_OBJECT AS CNT_PARTY_ROWID_OBJECT,
P_ACCT.ROWID_OBJECT AS ACCT_PARTY_ROWID_OBJECT,
P_CNCT.ACTV_FLG AS ACTV_FLG,
CNT.ORG_LEVEL AS ORG_LEVEL,
CNT.VALIDATION_FLAG AS VALIDATION_FLAG,
P_ACCT.MDM_ID AS 'ACCOUNT_MDM_ID',
ACCT.SURF_ID AS 'ACCOUNT_SURF_ID',
P_CNCT.MDM_ID AS 'C_LEVEL_CONTACT_MDM_ID',
CASE WHEN CNT.MID_NM IS NULL
THEN P_CNCT.PARTY_NM
ELSE CONCAT(CNT.FRST_NM, ' ',CNT.MID_NM,' ',CNT.LAST_NM)
END AS 'C_LEVEL_CONTACT_FULL_NM',
COMM.COMM_VAL AS 'C_LEVEL_CONTACT_EMAIL',
CNT.C_LEVEL_JOB_TITLE AS 'C_LEVEL_CONTACT_JOB_TITLE',
CNT.C_LEVEL_JOB_FUNCTION AS 'C_LEVEL_CONTACT_JOB_FUNCTION',
/*
(SELECT TOP 1 COMM_X.COMM_VAL FROM C_B_PARTY_COMM COMM_P
INNER JOIN C_B_PARTY_COMM_XREF COMM_X ON COMM_P.ROWID_OBJECT = COMM_X.ROWID_OBJECT AND COMM_X.HUB_STATE_IND = 1 AND COMM_X.ROWID_SYSTEM = 'IDD' AND COMM_X.EVENT_TYP = 'IDD_CONT_UPLOAD'
WHERE COMM_X.COMM_TYP_CD = 'PHONE' AND COMM_X.COMM_USG = 'BUSINESS' AND COMM_P.PARTY_ID = P_CNCT.ROWID_OBJECT) AS 'CONTACT_PHONE',
*/
CASE WHEN
(SELECT TOP 1 COMM_X.COMM_VAL FROM C_B_PARTY_COMM COMM_P
INNER JOIN C_B_PARTY_COMM_XREF COMM_X ON COMM_P.ROWID_OBJECT = COMM_X.ROWID_OBJECT AND COMM_X.HUB_STATE_IND = 1 AND COMM_X.ROWID_SYSTEM = 'IDD' AND COMM_X.EVENT_TYP = 'IDD_CONT_UPLOAD'
WHERE COMM_X.COMM_TYP_CD = 'PHONE' AND COMM_X.COMM_USG = 'BUSINESS' AND COMM_P.PARTY_ID = P_CNCT.ROWID_OBJECT) IS NULL
THEN
(SELECT TOP 1 COMM_X.COMM_VAL FROM C_B_PARTY_COMM COMM_P
INNER JOIN C_B_PARTY_COMM_XREF COMM_X ON COMM_P.ROWID_OBJECT = COMM_X.ROWID_OBJECT
AND COMM_X.HUB_STATE_IND = 1 --AND COMM_X.ROWID_SYSTEM = 'IDD' AND COMM_X.EVENT_TYP = 'IDD_CONT_UPLOAD'
WHERE COMM_X.COMM_TYP_CD = 'PHONE' AND COMM_X.COMM_USG = 'BUSINESS' AND COMM_P.PARTY_ID = P_CNCT.ROWID_OBJECT
AND COMM_X.REF_PHONE_VERBOSE_STS IN (SELECT REF_PHONE_VLDTN_STATUS FROM C_B_LU_PHONE_VLDTN_STS WHERE SNOW_VLDTN_MESSAGE IN ('Valid','Unknown'))
ORDER BY COMM_X.LAST_UPDATE_DATE DESC) ---//hmm order by
ELSE
(SELECT TOP 1 COMM_X.COMM_VAL FROM C_B_PARTY_COMM COMM_P
INNER JOIN C_B_PARTY_COMM_XREF COMM_X ON COMM_P.ROWID_OBJECT = COMM_X.ROWID_OBJECT AND COMM_X.HUB_STATE_IND = 1 AND COMM_X.ROWID_SYSTEM = 'IDD' AND COMM_X.EVENT_TYP = 'IDD_CONT_UPLOAD'
WHERE COMM_X.COMM_TYP_CD = 'PHONE' AND COMM_X.COMM_USG = 'BUSINESS' AND COMM_P.PARTY_ID = P_CNCT.ROWID_OBJECT
ORDER BY COMM_X.LAST_UPDATE_DATE DESC)
END AS 'CONTACT_PHONE',
CNT.C_LEVEL_DATA_SOURCE_URL AS 'C_LEVEL_CONTACT_SOURCE_URL',
CASE WHEN P_CNCT.LAST_UPDATE_DATE > COMM.LAST_UPDATE_DATE AND P_CNCT.LAST_UPDATE_DATE > CNT.LAST_UPDATE_DATE
AND P_CNCT.LAST_UPDATE_DATE > REL.LAST_UPDATE_DATE THEN
P_CNCT.LAST_UPDATE_DATE
WHEN COMM.LAST_UPDATE_DATE IS NOT NULL AND COMM.LAST_UPDATE_DATE > P_CNCT.LAST_UPDATE_DATE AND COMM.LAST_UPDATE_DATE > CNT.LAST_UPDATE_DATE
AND COMM.LAST_UPDATE_DATE > REL.LAST_UPDATE_DATE THEN
COMM.LAST_UPDATE_DATE
WHEN CNT.LAST_UPDATE_DATE IS NOT NULL AND CNT.LAST_UPDATE_DATE > P_CNCT.LAST_UPDATE_DATE AND CNT.LAST_UPDATE_DATE > COMM.LAST_UPDATE_DATE
AND CNT.LAST_UPDATE_DATE > REL.LAST_UPDATE_DATE THEN
CNT.LAST_UPDATE_DATE
WHEN REL.LAST_UPDATE_DATE IS NOT NULL AND REL.LAST_UPDATE_DATE > P_CNCT.LAST_UPDATE_DATE AND REL.LAST_UPDATE_DATE > COMM.LAST_UPDATE_DATE
AND REL.LAST_UPDATE_DATE > CNT.LAST_UPDATE_DATE THEN
REL.LAST_UPDATE_DATE
ELSE
P_CNCT.LAST_UPDATE_DATE
END LAST_UPDATE_DATE
,CASE WHEN REL.HUB_STATE_IND = 1 THEN 0 WHEN REL.HUB_STATE_IND = -1 THEN 1 END AS DELETE_FLAG
FROM C_B_PARTY(nolock) P_CNCT
INNER JOIN C_B_PARTY_REL(nolock) REL ON P_CNCT.ROWID_OBJECT = REL.PARENT_PARTY_ID AND REL.HIERARCHY_CODE = 'Account-Contact Hierarchy'
AND REL.HUB_STATE_IND=1 AND REL.consolidation_ind = 1
AND P_CNCT.PARTY_TYP_CD = 'Contact' and P_CNCT.consolidation_ind = 1 AND P_CNCT.HUB_STATE_IND = 1 AND ISNULL(P_CNCT.ACTV_FLG, '1') ='1'
--INNER JOIN C_B_PARTY_REL_XREF REL_X ON REL_X.ROWID_OBJECT = REL.ROWID_OBJECT AND REL_X.ROWID_SYSTEM = 'IDD' AND REL_X.EVENT_TYP = 'IDD_CONT_UPLOAD'
INNER JOIN C_B_ACCOUNT(nolock) ACCT ON ACCT.PARTY_ID = REL.CHILD_PARTY_ID AND ACCT.SURF_ID LIKE 'ACCT%' AND ACCT.HUB_STATE_IND = 1
INNER JOIN C_B_PARTY(nolock) P_ACCT ON P_ACCT.ROWID_OBJECT = ACCT.PARTY_ID AND P_ACCT.BO_CLASS_CODE = 'Organization' AND P_ACCT.HUB_STATE_IND = 1
INNER JOIN C_B_CONTACT(nolock) CNT ON P_CNCT.ROWID_OBJECT = CNT.PARTY_ID AND CNT.HUB_STATE_IND = 1 AND CNT.consolidation_ind = 1
--INNER JOIN C_B_CONTACT_XREF CNT_X ON CNT_X.ROWID_OBJECT = CNT.ROWID_OBJECT AND CNT_X.ROWID_SYSTEM = 'IDD' AND CNT_X.EVENT_TYP in ('IDD_BULK_CONT_UPLOAD','IDD_CONT_UPLOAD') AND CNT_X.HUB_STATE_IND = 1
--INNER JOIN C_B_PARTY_XREF P_CNCT_X ON P_CNCT.ROWID_OBJECT = P_CNCT_X.ROWID_OBJECT AND P_CNCT_X.ROWID_SYSTEM='IDD'
-- AND P_CNCT_X.EVENT_TYP ='IDD_CONT_UPLOAD' --AND P_CNCT_X.ACTV_FLG!=0
INNER JOIN C_B_PARTY_COMM(nolock) COMM ON COMM.PARTY_ID = P_CNCT.ROWID_OBJECT AND COMM.COMM_TYP_CD = 'EMAIL' AND COMM.HUB_STATE_IND=1
AND COMM.consolidation_ind = 1
--INNER JOIN C_B_PARTY_COMM_XREF COMM_E_X ON COMM.ROWID_OBJECT = COMM_E_X.ROWID_OBJECT AND COMM_E_X.HUB_STATE_IND = 1 AND COMM_E_X.ROWID_SYSTEM = 'IDD' AND COMM_E_X.EVENT_TYP = 'IDD_CONT_UPLOAD'
--AND COMM_E_X.COMM_TYP_CD = 'EMAIL'
--where P_CNCT.PARTY_TYP_CD = 'Contact' and P_CNCT.consolidation_ind = 1 --and P_CNCT.mdm_id='11191487'
) QUERY
CROSS APPLY DBO.SPLITSTRINGS(QUERY.C_LEVEL_CONTACT_JOB_FUNCTION, ',') AS SPLIT;
GO--function definition
ALTER FUNCTION [dbo].[SplitStrings]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS
RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
WHERE Number <= CONVERT(INT, LEN(@List))
AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
) AS y);--Row Counts
select count(*) from C_B_PARTY;--8561556
select count(*) from C_B_PARTY_COMM;--17229583
select count(*) from C_B_PARTY_COMM_XREF;--28316745
select count(*) from C_B_PARTY_REL;--5300472
select count(*) from C_B_PARTY_REL_XREF;--9162486
select count(*) From C_B_ACCOUNT;--133965
select count(*) from C_B_CONTACT;--8269099
select count(*) from C_B_CONTACT_XREF;--22952114
select count(*) from C_B_PARTY_XREF;--25511535
Regards,
Sam
-
did you implement the indexes I mentioned on https://www.sqlservercentral.com/forums/topic/query-filling-up-tempdb ?
some of them would help on this particular issue as well.
and while not necessarily the issue on this case replace that "bad" stringsplit with a performant one in the cases where your input is guaranteed to be less than varchar(8000) or nvarchar(4000).
one thing you can do immediately is to replace the line below as it messes up some join predicates
- CASE WHEN REL.HUB_STATE_IND = 1 THEN 0 WHEN REL.HUB_STATE_IND = -1 THEN 1 END AS DELETE_FLAG
with
0 as DELETE_FLAG -- zero
as your INNER join filters rows so that only the first condition is EVER true
FROM C_B_PARTY(nolock) P_CNCT
INNER JOIN C_B_PARTY_REL(nolock) REL ON P_CNCT.ROWID_OBJECT = REL.PARENT_PARTY_ID AND REL.HIERARCHY_CODE = 'Account-Contact Hierarchy'
AND REL.HUB_STATE_IND=1
-
This was removed by the editor as SPAM
-
Starting with the execution plan, you have a lot of issues. This is in no way a simple query. And views that join other views calling other views and functions, etc., may look like a simple way to avoid code reuse, but instead it's a simple way to kill the optimizer. Let's talk about it.
One of MANY things the optimizer does is go through a simplification process, meaning, do you really need all the objects referenced in your query. That process, especially as it grows in complexity with views calling views, etc., takes processing cycles away from the main part of the optimization process, how the heck do we make this query run fast. And, in fact, in your execution plan, if you look at the properties of the first operator, the SELECT operator, you'll see that the plan you have is the result of a timeout in optimization.
Next problem, you have six Plan Affecting Convert warnings (you can see the little exclamation point on the first operator, letting you know) all of which are listed, again, in the first operator. Here's one: CONVERT_IMPLICIT(nvarchar(max),rtrim([P_CNCT].[ROWID_OBJECT]),0). These can make it so that if you have good indexes with up to date statistics, it won't matter because you'll get a scan anyway.
As it is, looking through the plan, you've got a whole slew of scans, meaning your indexes are not filtering well (or, the plan affecting converts are doing what I said).
You have a large number of key lookups, suggesting that more work can be done on either choosing better clustered indexes, or building out your non-clustered indexes differently. These things lead to three reads for every page involved plus the processing power to do the join. Frequently, this is a great target for performance tuning.
You're also missing a join predicate on one of the queries. This [QA6_ORS].[sys].[sysschobjs].[clst] [o] and [QA6_ORS].[sys].[sysschobjs].[nc1] [o] are not joined properly. Oh, and it's using a lazy spool to deal with the fact.
I recommend tearing this down into it's component parts and then ignoring any attempts at code reuse. Write a query that returns the data you need, even if chunks of that code are duplicated from other queries. Also, use the appropriate data types so you avoid so many conversions. Make sure your indexes are in place to avoid so many key lookups.
I know what you want. A single, "change the location of this comma" and everything is magically fixed without adjusting code, structures or methodology. I'm here to tell you the bad news. It's not there. If you want this to perform, you need to rearchitect how you're running this query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
possible changes - in addition to what I mentioned on the other thread.
table C_B_CONTACT - likely the clustered key should be changed to be based on PARTY_ID - in dept analysys of remaining of system required.
some of the other tables MAY also benefit from the same type of change e.g. cluster on the most commonly used field(s) throughout the system instead of the Primary Key field
change SVR1_20X ON C_B_PARTY
index columns:
BO_CLASS_CODE
HUB_STATE_IND
include columns:
ROWID_OBJECT
MDM_ID
change NI_C_B_ACCOUNT_2 ON C_B_ACCOUNT
index columns:
PARTY_ID
HUB_STATE_IND
SURF_ID
include columns:
change NI_C_B_PARTY_COMM_6 on C_B_PARTY_COMM
index columnns:
HUB_STATE_IND
PARTY_ID
COMM_TYP_CD
CONSOLIDATION_IND
include columns:
COMM_VAL
LAST_UPDATE_DATE - frederico_fonseca wrote:
did you implement the indexes I mentioned on https://www.sqlservercentral.com/forums/topic/query-filling-up-tempdb ?
some of them would help on this particular issue as well.
This is a different instance. Will create those supporting indexes.
-
Looking at first glance with lot of scans , I felt the same. Thanks for the pointers. Now, I am getting a doubt, are they even getting the desired output or not. I will suggest to start over with small data and keep building the final query. Again, thank a lot sir for going through the plan.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply