July 19, 2020 at 11:27 pm
Is it possible to optimize this statement to get rid of the case named [aging]?
specifically, the two highlighted statements that cause 90% of all waits in the entire select statement.
4 attachments: entire SP, the CULPRIT SQL, and the full Stored Proc where it is called +screenshot that shows fully confirmed problem statements that cause 90 % of all waits here. That subquery is executed 300K times (number of resultset rows). both tables are over 100 000 000 rows and 300 columns.
THANK YOU!
Likes to play Chess
July 19, 2020 at 11:43 pm
Here is the query plan.
Likes to play Chess
July 20, 2020 at 12:51 am
This is what we came up with so far.
But it is not confirmed that I will have a list of ClaimNumbers to pre-populate like this...
/* --------------- D.A- Solution starts here ------------------*/
declare @claim_number int,
@max_paid_date datetime,
@min_date_submitted datetime,
@min_date_submitted_X datetime
set
@claim_number = 1123456 -- Claim number needs to be assigned from the previous statement
SELECT
@max_paid_date = MAX(MaxPdDate1.paid_date)
FROM
Application.dbo.Claims_Log_V2 MaxPdDate1 (NOLOCK)
WHERE
MaxPdDate1.claim_number = @claim_number
SELECT
@min_date_submitted_X = MIN(date_submitted) -- aging = difference between
FROM
Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- (1st adjustment after the last Check-Run date)
WHERE
MinDtSub.claim_number = @claim_number -- and (current date)
AND MinDtSub.date_submitted >= @max_paid_date
AND SUBSTRING(MinDtSub.support_codes, 8, 1) = 'X'
SELECT
@min_date_submitted = MIN(date_submitted) -- if claim has been through a check-run but has
FROM
Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- no adjustments, then aging = difference between
WHERE
MinDtSub.claim_number = @claim_number -- (1st activity of any kind after the last Check-Run date)
AND MinDtSub.date_submitted >= @max_paid_date
/* --------------- - Solution ends here ------------------*/
SELECT
DISTINCT C.user_id,
C.date_submitted,
gl.[group_name] AS client,
C.claim_number,
C.service_date,
C.receive_date,
SUBSTRING(C.support_codes, 8, 1),
SUM(C.submitted_cost)
/*-------------------------- Aging Logic ---------------------------------------------------------------------*/
/* */
/* WHEN claim has paid more than once */
/* WHEN there was an adjustment (status = X) after the last check date */
/* THEN aging = (reporting date) minus (1st adjustment date after the last paid date) */
/* ELSE */
/* claim was pended without an adjustment (this is procedurally wrong, but occasionally happens */
/* aging = (reporting date) minus (1st date of any kind of activity after the last paid date) */
/* ELSE (claim has never been through the Check-Run */
/* aging = (reporting date) minus (received date) */
/*----------------------------------------------------------------------------------------------------------------*/,
CASE
WHEN (
SELECT
COUNT (DISTINCT(countPdDt.paid_date)) -- if there are any paid dates indicating
FROM
Application.dbo.Claims_Log_V2 countPdDt (NOLOCK) -- that the claim has been through a
WHERE
countPdDt.claim_number = C.claim_number -- Check-Run
AND countPdDt.paid_date > '1/1/1901'
AND countPdDt.paid_date < '12/31/9999'
) > 0 THEN CASE
WHEN (
SELECT
COUNT(SUBSTRING(stat.support_codes, 8, 1)) -- if the claim has any
FROM
Application.dbo.Claims_Log_V2 stat (NOLOCK) -- any adjustments
WHERE
stat.claim_number = C.claim_number
AND SUBSTRING(stat.support_codes, 8, 1) = 'X'
) > 0 THEN (
DATEDIFF(
DD,
@min_date_submitted_X, -- temp variable replaces the subquery
GETDATE()
)
)
ELSE (
DATEDIFF(
DD,
@min_date_submitted, -- temp variable replaces the subquery
GETDATE()
)
)
END
ELSE -- if claim has never been through a check-run
(DATEDIFF(DD, C.receive_date, GETDATE())) -- aging = difference between (claim receive date)
END [Aging] -- and (current date)
,CASE
C.benefit_type
WHEN 'V' THEN 'V'
WHEN 'D' THEN CASE
(SUBSTRING(C.submitted_procedure_id, 1, 2))
WHEN 'D8' THEN 'O'
ELSE 'D'
END
WHEN 'M' THEN 'M'
ELSE '?'
END [benefit_type],
C.group_gid,
G.group_id,
G.group_name,
W.Date_Originated,
R.Role_ID,
R.Role_Description,
T.Task_Description,
C.submitted_procedure_id
FROM
Application.dbo.Claims_Log_v2 C WITH (NOLOCK)
INNER JOIN (
SELECT
claim_number,
line_number,
MAX(date_submitted) AS date_submitted
FROM
Application.dbo.Claims_Log_v2 WITH (NOLOCK)
GROUP BY
claim_number,
line_number
) X ON C.claim_number = X.claim_number
AND C.line_number = X.line_number
AND C.date_submitted = X.date_submitted
LEFT JOIN (
SELECT
DISTINCT [group_gid],
[group_name] --FROM [BAUtilities].[dbo].[group_name_LU]) gl
FROM
[AVESIS_EDI].[dbo].[group_name_LU]
) gl ON gl.group_gid = c.group_gid
LEFT JOIN [Application].[dbo].[Groups] G (NOLOCK) ON C.[group_gid] = G.[group_gid]
AND G.[record_status] = 'A'
/********** Added this section to bring in workflow info **********/
LEFT JOIN Application.dbo.WorkFlow_Process W WITH (NOLOCK) ON C.claim_number = W.Supporting_Data
AND W.record_status = 'A'
LEFT JOIN Application.dbo.WorkFlow_Roles R WITH (NOLOCK) ON W.Role_GID = R.Role_GID
AND R.record_status = 'A'
LEFT JOIN Application.dbo.WorkFlow_Task_Definition T WITH (NOLOCK) ON W.Task_GID = T.Task_GID
AND T.record_status = 'A'
WHERE
SUBSTRING(C.support_codes, 8, 1) = 'Z'
AND SUBSTRING(C.support_codes, 4, 1) NOT IN ('3', '5')
AND C.service_date <= GETDATE()
AND C.claim_sid = (
select
max(maxSid.claim_sid)
from
application.dbo.claims_log_v2 maxSid (NOLOCK)
where
maxSid.claim_number = c.claim_number
AND maxSid.line_number = c.line_number
)
AND LEN(C.Claim_number) = 15
GROUP BY
C.user_id,
C.date_submitted,
gl.group_name,
C.claim_number,
C.service_date,
C.receive_date,
SUBSTRING(C.support_codes, 8, 1),
C.benefit_type,
C.group_gid,
G.group_id,
G.group_name,
W.Date_Originated,
R.Role_ID,
R.Role_Description,
T.Task_Description,
C.submitted_procedure_id;
Likes to play Chess
July 20, 2020 at 12:56 am
Where is the query plan?
July 20, 2020 at 2:02 am
add .sqlplan extension..
Likes to play Chess
July 20, 2020 at 2:02 am
how can i add it here?
Likes to play Chess
July 20, 2020 at 7:06 am
rename it to .txt
and please put the full original code so we can look at it.
and get rid of those "with (nolock)" and distinct when using a group by
July 20, 2020 at 2:13 pm
attached .sqlplan as .txt.
Original code:
SELECT DISTINCT
C.user_id
,C.date_submitted
,gl.[group_name] AS client
,C.claim_number
,C.service_date
,C.receive_date
,SUBSTRING(C.support_codes,8,1)
,SUM(C.submitted_cost)
/*-------------------------- Aging Logic ---------------------------------------------------------------------*/
/* */
/* WHEN claim has paid more than once */
/* WHEN there was an adjustment (status = X) after the last check date */
/* THEN aging = (reporting date) minus (1st adjustment date after the last paid date) */
/* ELSE */
/* claim was pended without an adjustment (this is procedurally wrong, but occasionally happens */
/* aging = (reporting date) minus (1st date of any kind of activity after the last paid date) */
/* ELSE (claim has never been through the Check-Run */
/* aging = (reporting date) minus (received date) */
/*----------------------------------------------------------------------------------------------------------------*/
, CASE WHEN (SELECT COUNT (DISTINCT(countPdDt.paid_date))-- if there are any paid dates indicating
FROM Application.dbo.Claims_Log_V2 countPdDt (NOLOCK)-- that the claim has been through a
WHERE countPdDt.claim_number = C.claim_number-- Check-Run
AND countPdDt.paid_date > '1/1/1901'
AND countPdDt.paid_date < '12/31/9999'
)
> 0
THEN CASE WHEN (SELECT COUNT(SUBSTRING(stat.support_codes,8,1)) -- if the claim has any
FROM Application.dbo.Claims_Log_V2 stat (NOLOCK) -- any adjustments
WHERE stat.claim_number = C.claim_number
AND SUBSTRING(stat.support_codes,8,1) = 'X'
)
> 0
THEN (DATEDIFF(DD,(SELECT MIN(date_submitted) -- aging = difference between
FROM Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- (1st adjustment after the last Check-Run date)
WHERE MinDtSub.claim_number = C.claim_number -- and (current date)
AND MinDtSub.date_submitted >= (SELECT MAX(MaxPdDate1.paid_date)
FROM Application.dbo.Claims_Log_V2 MaxPdDate1 (NOLOCK)
WHERE MaxPdDate1.claim_number = C.claim_number)
AND SUBSTRING(MinDtSub.support_codes,8,1) = 'X')
,GETDATE()
))
ELSE (DATEDIFF(DD,(SELECT MIN(date_submitted) -- if claim has been through a check-run but has
FROM Application.dbo.Claims_Log_V2 MinDtSub (NOLOCK) -- no adjustments, then aging = difference between
WHERE MinDtSub.claim_number = C.claim_number -- (1st activity of any kind after the last Check-Run date)
AND MinDtSub.date_submitted >= (SELECT MAX(MaxPdDate1.paid_date) -- and (current date)
FROM Application.dbo.Claims_Log_V2 MaxPdDate1 (NOLOCK)
WHERE MaxPdDate1.claim_number = C.claim_number))
,GETDATE()
))
END
ELSE -- if claim has never been through a check-run
(DATEDIFF(DD, C.receive_date, GETDATE())) -- aging = difference between (claim receive date
END [Aging] -- and (current date)
,CASE C.benefit_type
WHEN 'V' THEN 'V'
WHEN 'D' THEN CASE (SUBSTRING(C.submitted_procedure_id,1,2))
WHEN 'D8' THEN 'O'
ELSE 'D'
END
WHEN 'M' THEN 'M'
ELSE '?'
END [benefit_type]
,C.group_gid
,G.group_id
,G.group_name
,W.Date_Originated
,R.Role_ID
,R.Role_Description
,T.Task_Description
,C.submitted_procedure_id
FROM Application.dbo.Claims_Log_v2 C WITH (NOLOCK)
INNER JOIN
( SELECT claim_number
,line_number
,MAX(date_submitted) AS date_submitted
FROM Application.dbo.Claims_Log_v2 WITH (NOLOCK)
GROUP BY claim_number,line_number ) X
ON C.claim_number = X.claim_number
AND C.line_number = X.line_number
AND C.date_submitted = X.date_submitted
LEFT JOIN (SELECT DISTINCT [group_gid]
,[group_name]
--FROM [BAUtilities].[dbo].[group_name_LU]) gl
FROM [AVESIS_EDI].[dbo].[group_name_LU]) gl
ON gl.group_gid = c.group_gid
LEFT JOIN [Application].[dbo].[Groups]G (NOLOCK)
ON C.[group_gid] = G.[group_gid] AND G.[record_status] = 'A'
/********** Added this section to bring in workflow info **********/
LEFT JOIN Application.dbo.WorkFlow_Process W WITH (NOLOCK)
ON C.claim_number = W.Supporting_Data AND W.record_status = 'A'
LEFT JOIN Application.dbo.WorkFlow_Roles R WITH (NOLOCK)
ON W.Role_GID = R.Role_GID AND R.record_status = 'A'
LEFT JOIN Application.dbo.WorkFlow_Task_Definition T WITH (NOLOCK)
ON W.Task_GID = T.Task_GID AND T.record_status = 'A'
WHERE
SUBSTRING(C.support_codes,8,1) = 'Z'
AND SUBSTRING(C.support_codes,4,1) NOT IN ('3','5')
AND C.service_date <= GETDATE()
AND C.claim_sid = ( select max(maxSid.claim_sid)
from application.dbo.claims_log_v2 maxSid (NOLOCK)
where maxSid.claim_number = c.claim_number
AND maxSid.line_number = c.line_number )
AND LEN(C.Claim_number) = 15
GROUP BY
C.user_id
,C.date_submitted
,gl.group_name
,C.claim_number
,C.service_date
,C.receive_date
,SUBSTRING(C.support_codes,8,1)
,C.benefit_type
,C.group_gid
,G.group_id
,G.group_name
,W.Date_Originated
,R.Role_ID
,R.Role_Description
,T.Task_Description
,C.submitted_procedure_id;
Likes to play Chess
July 20, 2020 at 3:22 pm
still no plan
July 20, 2020 at 4:05 pm
i tried to rename the sqlplan file to txt and other and no extension... but SSC keeps saying
Upload Errors:
execplanInsert-2.txt: Sorry, this file type is not permitted for security reasons.
Likes to play Chess
July 20, 2020 at 5:43 pm
running query with SET STATISTICSPROFILE ON, will attach as Excel.
it now takes 3 hours to run and before was only 30 minutes. No other changes in anything, same data volumes, same everything..
Likes to play Chess
July 20, 2020 at 6:30 pm
you are hitting application.dbo.claims_log_v2 alot in this query. Looks like everything is using a join on claim_number. Can you put that hit into a cte, then join to it? Something like below. There was on DDL or sample data, so it might not compile and there might be typos, but you get the jist
;with cte as (SELECT Claim_Number,
Count (DISTINCT( countPdDt.paid_date )) as countPdDt,
- sum(case when Substring(stat.support_codes, 8, 1) = 'X' then 1 else 0 end) as stat,
Min(Case when Substring(support_codes, 8, 1) = 'X' then date_submitted else null end) as MinDtSub1,
Max(Case when Substring(support_codes, 8, 1) = 'X' then MaxPdDate1.paid_date else null end) as MaxPdDate1,
Min(date_submitted) as MinDtSub,
Max(MaxPdDate1.paid_date) as MaxPdDt
from application.dbo.claims_log_v2
group by Claim_Number)
SELECT DISTINCT C.user_id,
C.date_submitted,
gl.[group_name] AS client,
C.claim_number,
C.service_date,
C.receive_date,
Substring(C.support_codes, 8, 1),
Sum(C.submitted_cost)
/*-------------------------- Aging Logic ---------------------------------------------------------------------*/
/* */
/* WHEN claim has paid more than once */
/* WHEN there was an adjustment (status = X) after the last check date */
/* THEN aging = (reporting date) minus (1st adjustment date after the last paid date) */
/* ELSE */
/* claim was pended without an adjustment (this is procedurally wrong, but occasionally happens */
/* aging = (reporting date) minus (1st date of any kind of activity after the last paid date) */
/* ELSE (claim has never been through the Check-Run */
/* aging = (reporting date) minus (received date) */
/*----------------------------------------------------------------------------------------------------------------*/
,
case when countPdDt > 0 then
case when stat > 0 then
Datediff(dd, MinDtSub1, MaxPdDate1)
else
Datediff(dd, MinDtSub, MaxPdDt)
end
else
Datediff(dd, C.receive_date, Getdate())
end as [Aging]
-- CASE
-- WHEN (SELECT Count (DISTINCT( countPdDt.paid_date )) -- if there are any paid dates indicating
-- FROM application.dbo.claims_log_v2 countPdDt (nolock) -- that the claim has been through a
-- WHERE countPdDt.claim_number = C.claim_number -- Check-Run
-- AND countPdDt.paid_date > '1/1/1901'
-- AND countPdDt.paid_date < '12/31/9999') > 0 THEN
-- CASE
-- WHEN (SELECT Count(Substring(stat.support_codes, 8, 1)) -- if the claim has any
-- FROM application.dbo.claims_log_v2 stat (nolock) -- any adjustments
-- WHERE stat.claim_number = C.claim_number
-- AND Substring(stat.support_codes, 8, 1) = 'X') > 0
--THEN
-- ( Datediff(dd, (SELECT Min(date_submitted) -- aging = difference between
-- FROM application.dbo.claims_log_v2 MinDtSub (nolock)
-- -- (1st adjustment after the last Check-Run date)
-- WHERE MinDtSub.claim_number = C.claim_number -- and (current date)
-- AND MinDtSub.date_submitted >= (SELECT Max(MaxPdDate1.paid_date)
-- FROM application.dbo.claims_log_v2 MaxPdDate1 (nolock)
-- WHERE MaxPdDate1.claim_number = C.claim_number)
-- AND Substring(MinDtSub.support_codes, 8, 1) = 'X'), Getdate()) )
-- ELSE ( Datediff(dd, (SELECT Min(date_submitted) -- if claim has been through a check-run but has
-- FROM application.dbo.claims_log_v2 MinDtSub (nolock) -- no adjustments, then aging = difference between
-- WHERE MinDtSub.claim_number = C.claim_number -- (1st activity of any kind after the last Check-Run date)
-- AND MinDtSub.date_submitted >= (SELECT Max(MaxPdDate1.paid_date) -- and (current date)
-- FROM application.dbo.claims_log_v2 MaxPdDate1 (nolock)
-- WHERE MaxPdDate1.claim_number = C.claim_number)), Getdate()) )
-- END
-- ELSE -- if claim has never been through a check-run
-- ( Datediff(dd, C.receive_date, Getdate()) ) -- aging = difference between (claim receive date
-- END [Aging] -- and (current date)
,
CASE C.benefit_type
WHEN 'V' THEN 'V'
WHEN 'D' THEN
CASE ( Substring(C.submitted_procedure_id, 1, 2) )
WHEN 'D8' THEN 'O'
ELSE 'D'
END
WHEN 'M' THEN 'M'
ELSE '?'
END [benefit_type],
C.group_gid,
G.group_id,
G.group_name,
W.date_originated,
R.role_id,
R.role_description,
T.task_description,
C.submitted_procedure_id
FROM application.dbo.claims_log_v2 C WITH (nolock)
join cte ct
on c.Claim_Number = ct.Claim_Number
INNER JOIN (SELECT claim_number,
line_number,
Max(date_submitted) AS date_submitted
FROM application.dbo.claims_log_v2 WITH (nolock)
GROUP BY claim_number,
line_number) X
ON C.claim_number = X.claim_number
AND C.line_number = X.line_number
AND C.date_submitted = X.date_submitted
LEFT JOIN (SELECT DISTINCT [group_gid],
[group_name]
--FROM [BAUtilities].[dbo].[group_name_LU]) gl
FROM [AVESIS_EDI].[dbo].[group_name_lu]) gl
ON gl.group_gid = c.group_gid
LEFT JOIN [Application].[dbo].[groups]G (nolock)
ON C.[group_gid] = G.[group_gid]
AND G.[record_status] = 'A'
/********** Added this section to bring in workflow info **********/
LEFT JOIN application.dbo.workflow_process W WITH (nolock)
ON C.claim_number = W.supporting_data
AND W.record_status = 'A'
LEFT JOIN application.dbo.workflow_roles R WITH (nolock)
ON W.role_gid = R.role_gid
AND R.record_status = 'A'
LEFT JOIN application.dbo.workflow_task_definition T WITH (nolock)
ON W.task_gid = T.task_gid
AND T.record_status = 'A'
WHERE Substring(C.support_codes, 8, 1) = 'Z'
AND Substring(C.support_codes, 4, 1) NOT IN ( '3', '5' )
AND C.service_date <= Getdate()
AND C.claim_sid = (SELECT Max(maxSid.claim_sid)
FROM application.dbo.claims_log_v2 maxSid (nolock)
WHERE maxSid.claim_number = c.claim_number
AND maxSid.line_number = c.line_number)
AND Len(C.claim_number) = 15
GROUP BY C.user_id,
C.date_submitted,
gl.group_name,
C.claim_number,
C.service_date,
C.receive_date,
Substring(C.support_codes, 8, 1),
C.benefit_type,
C.group_gid,
G.group_id,
G.group_name,
W.date_originated,
R.role_id,
R.role_description,
T.task_description,
C.submitted_procedure_id;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 21, 2020 at 1:40 am
Thank you! Will give it a try tomorrow.
Likes to play Chess
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply