July 8, 2024 at 6:31 pm
How to optimize below Query, Could you Please guide me.
SELECT selected_sfs.sfName AS 'service_form_name'
,c.agingDays AS 'age'
,documents.document_id
,selected_sfs.contactor
,selected_sfs.lob
,doc_event_orig_actor.creator_full_name
,selected_sfs.originator_full_name
,qic.queue_name
,qic.work_item_status
,CASE WHEN
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
THEN 'Category III'
WHEN actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
THEN 'Category II'
ELSE 'Category I'
END AS 'class'
,CASE WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
)
AND c.agingDays <= 15
THEN 'Open <= 15 Days'
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
)
AND c.agingDays <= 30
THEN 'Open <= 30 Days'
WHEN (
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
)
AND c.agingDays > 30
THEN 'Open > 30 Days'
WHEN ( actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 21
THEN 'Open <= 21 Days'
WHEN ( actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 30
THEN 'Open <= 30 Days'
WHEN ( actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays > 30
THEN 'Open > 30 Days'
WHEN c.agingDays <= 2 THEN 'Open <= 02 Days'
WHEN c.agingDays <= 14 THEN 'Open <= 14 Days'
WHEN c.agingDays > 14 THEN 'Open > 14 Days'
END AS 'age_group'
FROM anchorValues AS selected_sfs
LEFT JOIN cat3Values AS b
ON selected_sfs.document_uid = b.docUID
LEFT JOIN cteSubjectValues SV
ON selected_sfs.document_uid=SV.document_uid
LEFT JOIN #CategoryDetails CD
ON selected_sfs.document_uid=cd.document_uid
LEFT JOIN cteWIPValues AS WIP
ON selected_sfs.document_uid=WIP.document_uid
--OUTER APPLY dbo.rf_UTC2TZ(selected_sfs.origination_date, @TZ) AS OD
--CROSS APPLY dbo.rf_days2(OD.TZ_DateTime, @Today, @daysType) AS c
--CROSS APPLY dbo.rf_days2(selected_sfs.origination_date, @currentDate, @daysType) AS c
OUTER APPLY dbo.rf_UTC2TZ(selected_sfs.creation_date, @TZ) AS OD
CROSS APPLY dbo.rf_days2(OD.TZ_DateTime, @Today, @daysType) AS c
--- Getting Service Form creator from document_events table
INNER JOIN
(
SELECT ddc.document_uid,
ddc.creator_actor_uid AS actor_uid,
v.full_name AS creator_full_name
FROM dbo.dms_doc_created ddc --WITH (FORCESEEK)
INNER JOIN
dbo.v_actors v --WITH (FORCESEEK)
ON (ddc.creator_actor_uid = v.actor_uid)
) as doc_event_orig_actor
ON selected_sfs.document_uid = doc_event_orig_actor.document_uid
LEFT JOIN (SELECT actor_uid,
group_uid
FROM actors_groups WITH (NOLOCK)
WHERE actors_groups.group_uid IN (@nj_groupUID)) as hnjh_sfs
ON doc_event_orig_actor.actor_uid = hnjh_sfs.actor_uid
INNER JOIN documents WITH (NOLOCK)
ON selected_sfs.document_uid=documents.document_uid
LEFT JOIN
(SELECT document_uid,
recipient_actor_uid
FROM actiongrams WITH (NOLOCK)) as --1
actiongrams_1
ON selected_sfs.document_uid = actiongrams_1.document_uid
LEFT JOIN
(SELECT document_uid
FROM actiongrams WITH (NOLOCK)
GROUP BY document_uid) As --2
actiongrams_2
ON actiongrams_1.document_uid = actiongrams_2.document_uid
LEFT JOIN
(SELECT document_uid,
recipient_actor_uid,
groups.group_uid,
groups.name
FROM actiongrams WITH (NOLOCK)
LEFT OUTER JOIN actors_groups WITH (NOLOCK)
ON actiongrams.recipient_actor_uid = actors_groups.actor_uid
LEFT OUTER JOIN groups WITH (NOLOCK)
ON actors_groups.group_uid = groups.group_uid
--WHERE recipient_actor_uid IN (@nj_actorUID) OR groups.group_uid IN (@nj_groupUID)) aS --3
WHERE recipient_actor_uid IN (1604,1609,1610,1648,1729,1732,1733,1734,1735,1738,1739,1759,1760,1761,1762,1763,2202,2405) OR groups.group_uid IN (@nj_groupUID)) aS --3
actiongrams_3
ON selected_sfs.document_uid = actiongrams_3.document_uid
LEFT JOIN
(SELECT document_uid
FROM actiongrams WITH (NOLOCK)
GROUP BY document_uid) AS --4
actiongrams_4
ON actiongrams_3.document_uid = actiongrams_4.document_uid
LEFT JOIN
(SELECT document_uid
FROM wf_job_documents WITH (NOLOCK)) as --5
wf_job_documents_1
ON selected_sfs.document_uid = wf_job_documents_1.document_uid
LEFT JOIN
(SELECT document_uid
FROM wf_job_documents WITH (NOLOCK)
GROUP BY document_uid) As --6
wf_job_documents_2
ON wf_job_documents_1.document_uid = wf_job_documents_2.document_uid
LEFT JOIN
(SELECT document_uid
FROM
(SELECT queue_id, queue_uid, queues.name aS queue_name,
queues.workflow_region_uid AS queue_wf_region_uid
FROM queues WITH (NOLOCK)
--WHERE queue_id IN(@nj_queueID)
WHERE queue_id IN (7,8,155,56,57,58,59,73,170,89,90,91,92,93,94)
and queues.workflow_region_uid IN (@nj_wfRegionUID)) as queue_select -- Can remove "and queues.workflow_region_uid IN (@nj_wfRegionUID)" since it's done at the parameter level
INNER JOIN (SELECT wf_job_uid,
queue_uid,
wfsys_job_event_uid
FROM wf_job_events WITH (NOLOCK)
) As workjob_events_select
ON queue_select.queue_uid = workjob_events_select.queue_uid
INNER JOIN (SELECT wf_job_uid,
document_uid
FROM wf_job_documents WITH (NOLOCK)
)aS workjob_documents_select
ON workjob_events_select.wf_job_uid = workjob_documents_select.wf_job_uid) AS
wf_exclusions_1
ON wf_job_documents_2.document_uid = wf_exclusions_1.document_uid
LEFT JOIN
(SELECT document_uid
FROM wf_job_documents WITH (NOLOCK)
GROUP BY document_uid) as
wf_exclusions_2
ON wf_exclusions_1.document_uid = wf_exclusions_2.document_uid
LEFT OUTER JOIN
queue_info_cte qic
ON (selected_sfs.document_uid = qic.document_uid)
WHERE wf_exclusions_2.document_uid IS NULL
AND actiongrams_4.document_uid IS NULL
AND hnjh_sfs.group_uid IS NULL
and (wip.PromptValue not in ('Mbr Svcs 7700-NonMbr') or wip.PromptValue iS null )
GROUP BY selected_sfs.sfName
-- ,service_form_id
-- ,open_date
-- ,opened
-- ,hnjh_generated_sfs
-- ,closed_date
-- ,closed
,c.agingDays
,documents.document_id
,selected_sfs.contactor
,selected_sfs.lob
,doc_event_orig_actor.creator_full_name
,selected_sfs.originator_full_name
,qic.queue_name
,qic.work_item_status
-- ,ag2_document_uid
-- ,age4_document_uid
-- ,wf2_document_uid
-- ,wfe2_document_uid
,CASE WHEN
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
THEN 'Category III'
WHEN actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
THEN 'Category II'
ELSE 'Category I'
END -- class
,CASE WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
)
AND c.agingDays <= 15
THEN 'Open <= 15 Days'
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
)
AND c.agingDays <= 30
THEN 'Open <= 30 Days'
WHEN (
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN (54,85,90,136,142,146,155,156,175,177,188,202,210,212,217,227,229,256,258,274,288,327,330,332,336,339,343,356,387)
OR b.cat3 = 1
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('dispute'))
OR (selected_sfs.sfName='Contact Center SF' AND SV.SubjectText IN ('inquiry') AND CD.Level_01_prompt IN ('Provider Claim (Dispute)'))
)
AND c.agingDays > 30
THEN 'Open > 30 Days'
WHEN ( actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 21
THEN 'Open <= 21 Days'
WHEN ( actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 30
THEN 'Open <= 30 Days'
WHEN ( actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays > 30
THEN 'Open > 30 Days'
WHEN c.agingDays <= 2 THEN 'Open <= 02 Days'
WHEN c.agingDays <= 14 THEN 'Open <= 14 Days'
WHEN c.agingDays > 14 THEN 'Open > 14 Days'
END -- age_group
OPTION(RECOMPILE, LOOP JOIN);
July 8, 2024 at 8:20 pm
For one thing, don't force only LOOP joins, YIKES!
To tell you anything else, would need much more details on the tables and parameters.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 8, 2024 at 9:27 pm
A formatted version of the OP's query:
SELECT service_form_name = selected_sfs.sfName
,age = c.agingDays
,documents.document_id
,selected_sfs.contactor
,selected_sfs.lob
,doc_event_orig_actor.creator_full_name
,selected_sfs.originator_full_name
,qic.queue_name
,qic.work_item_status
,class = CASE
WHEN
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210, 212, 217, 227
,229, 256, 258, 274, 288, 327, 330, 332, 336, 339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
) THEN
'Category III'
WHEN actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> '' THEN
'Category II'
ELSE
'Category I'
END
,age_group = CASE
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188
,202, 210, 212, 217, 227, 229, 256, 258, 274, 288
,327, 330, 332, 336, 339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays <= 15 THEN
'Open <= 15 Days'
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188
,202, 210, 212, 217, 227, 229, 256, 258, 274, 288
,327, 330, 332, 336, 339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays <= 30 THEN
'Open <= 30 Days'
WHEN (
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188
,202, 210, 212, 217, 227, 229, 256, 258, 274, 288
,327, 330, 332, 336, 339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays > 30 THEN
'Open > 30 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 21 THEN
'Open <= 21 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 30 THEN
'Open <= 30 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays > 30 THEN
'Open > 30 Days'
WHEN c.agingDays <= 2 THEN
'Open <= 02 Days'
WHEN c.agingDays <= 14 THEN
'Open <= 14 Days'
WHEN c.agingDays > 14 THEN
'Open > 14 Days'
END
FROM anchorValues selected_sfs
LEFT JOIN cat3Values b
ON selected_sfs.document_uid = b.docUID
LEFT JOIN cteSubjectValues SV
ON selected_sfs.document_uid = SV.document_uid
LEFT JOIN #CategoryDetails CD
ON selected_sfs.document_uid = CD.document_uid
LEFT JOIN cteWIPValues WIP
ON selected_sfs.document_uid = WIP.document_uid
--OUTER APPLY dbo.rf_UTC2TZ(selected_sfs.origination_date, @TZ) AS OD
--CROSS APPLY dbo.rf_days2(OD.TZ_DateTime, @Today, @daysType) AS c
--CROSS APPLY dbo.rf_days2(selected_sfs.origination_date, @currentDate, @daysType) AS c
OUTER APPLY dbo.rf_UTC2TZ (selected_sfs.creation_date, @TZ) OD
CROSS APPLY dbo.rf_days2 (OD.TZ_DateTime, @Today, @daysType) c
--- Getting Service Form creator from document_events table
INNER JOIN
(
SELECT ddc.document_uid
,actor_uid = ddc.creator_actor_uid
,creator_full_name = v.full_name
FROM dbo.dms_doc_created ddc --WITH (FORCESEEK)
INNER JOIN dbo.v_actors v --WITH (FORCESEEK)
ON(ddc.creator_actor_uid = v.actor_uid)
) doc_event_orig_actor
ON selected_sfs.document_uid = doc_event_orig_actor.document_uid
LEFT JOIN
(
SELECT actor_uid
,group_uid
FROM actors_groups WITH(NOLOCK)
WHERE actors_groups.group_uid IN ( @nj_groupUID )
) hnjh_sfs
ON doc_event_orig_actor.actor_uid = hnjh_sfs.actor_uid
INNER JOIN documents WITH(NOLOCK)
ON selected_sfs.document_uid = documents.document_uid
LEFT JOIN
(
SELECT document_uid
,recipient_actor_uid
FROM actiongrams WITH(NOLOCK)
) --1
actiongrams_1
ON selected_sfs.document_uid = actiongrams_1.document_uid
LEFT JOIN
(
SELECT document_uid
FROM actiongrams WITH(NOLOCK)
GROUP BY document_uid
) --2
actiongrams_2
ON actiongrams_1.document_uid = actiongrams_2.document_uid
LEFT JOIN
(
SELECT document_uid
,recipient_actor_uid
,groups.group_uid
,groups.name
FROM actiongrams WITH(NOLOCK)
LEFT OUTER JOIN actors_groups WITH(NOLOCK)
ON actiongrams.recipient_actor_uid = actors_groups.actor_uid
LEFT OUTER JOIN groups WITH(NOLOCK)
ON actors_groups.group_uid = groups.group_uid
--WHERE recipient_actor_uid IN (@nj_actorUID) OR groups.group_uid IN (@nj_groupUID)) aS --3
WHERE recipient_actor_uid IN ( 1604, 1609, 1610, 1648, 1729, 1732, 1733, 1734, 1735, 1738, 1739, 1759, 1760
,1761, 1762, 1763, 2202, 2405
)
OR groups.group_uid IN ( @nj_groupUID )
) --3
actiongrams_3
ON selected_sfs.document_uid = actiongrams_3.document_uid
LEFT JOIN
(
SELECT document_uid
FROM actiongrams WITH(NOLOCK)
GROUP BY document_uid
) --4
actiongrams_4
ON actiongrams_3.document_uid = actiongrams_4.document_uid
LEFT JOIN
(
SELECT document_uid
FROM wf_job_documents WITH(NOLOCK)
) --5
wf_job_documents_1
ON selected_sfs.document_uid = wf_job_documents_1.document_uid
LEFT JOIN
(
SELECT document_uid
FROM wf_job_documents WITH(NOLOCK)
GROUP BY document_uid
) --6
wf_job_documents_2
ON wf_job_documents_1.document_uid = wf_job_documents_2.document_uid
LEFT JOIN
(
SELECT document_uid
FROM
(
SELECT queue_id
,queue_uid
,queue_name = queues.name
,queue_wf_region_uid = queues.workflow_region_uid
FROM queues WITH(NOLOCK)
--WHERE queue_id IN(@nj_queueID)
WHERE queue_id IN ( 7, 8, 155, 56, 57, 58, 59, 73, 170, 89, 90, 91, 92, 93, 94 )
AND queues.workflow_region_uid IN ( @nj_wfRegionUID )
) queue_select -- Can remove "and queues.workflow_region_uid IN (@nj_wfRegionUID)" since it's done at the parameter level
INNER JOIN
(
SELECT wf_job_uid
,queue_uid
,wfsys_job_event_uid
FROM wf_job_events WITH(NOLOCK)
) workjob_events_select
ON queue_select.queue_uid = workjob_events_select.queue_uid
INNER JOIN
(
SELECT wf_job_uid
,document_uid
FROM wf_job_documents WITH(NOLOCK)
) workjob_documents_select
ON workjob_events_select.wf_job_uid = workjob_documents_select.wf_job_uid
) wf_exclusions_1
ON wf_job_documents_2.document_uid = wf_exclusions_1.document_uid
LEFT JOIN
(
SELECT document_uid
FROM wf_job_documents WITH(NOLOCK)
GROUP BY document_uid
) wf_exclusions_2
ON wf_exclusions_1.document_uid = wf_exclusions_2.document_uid
LEFT OUTER JOIN queue_info_cte qic
ON(selected_sfs.document_uid = qic.document_uid)
WHERE wf_exclusions_2.document_uid IS NULL
AND actiongrams_4.document_uid IS NULL
AND hnjh_sfs.group_uid IS NULL
AND
(
WIP.PromptValue NOT IN ( 'Mbr Svcs 7700-NonMbr' )
OR WIP.PromptValue IS NULL
)
GROUP BY selected_sfs.sfName
-- ,service_form_id
-- ,open_date
-- ,opened
-- ,hnjh_generated_sfs
-- ,closed_date
-- ,closed
,c.agingDays
,documents.document_id
,selected_sfs.contactor
,selected_sfs.lob
,doc_event_orig_actor.creator_full_name
,selected_sfs.originator_full_name
,qic.queue_name
,qic.work_item_status
-- ,ag2_document_uid
-- ,age4_document_uid
-- ,wf2_document_uid
-- ,wfe2_document_uid
,CASE
WHEN
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210, 212, 217, 227
,229, 256, 258, 274, 288, 327, 330, 332, 336, 339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
) THEN
'Category III'
WHEN actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> '' THEN
'Category II'
ELSE
'Category I'
END -- class
,CASE
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210
,212, 217, 227, 229, 256, 258, 274, 288, 327, 330, 332, 336
,339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays <= 15 THEN
'Open <= 15 Days'
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210
,212, 217, 227, 229, 256, 258, 274, 288, 327, 330, 332, 336
,339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays <= 30 THEN
'Open <= 30 Days'
WHEN (
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210
,212, 217, 227, 229, 256, 258, 274, 288, 327, 330, 332, 336
,339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays > 30 THEN
'Open > 30 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 21 THEN
'Open <= 21 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 30 THEN
'Open <= 30 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays > 30 THEN
'Open > 30 Days'
WHEN c.agingDays <= 2 THEN
'Open <= 02 Days'
WHEN c.agingDays <= 14 THEN
'Open <= 14 Days'
WHEN c.agingDays > 14 THEN
'Open > 14 Days'
END -- age_group
OPTION(RECOMPILE, LOOP JOIN);
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 9, 2024 at 1:40 pm
Case statements in Group by Clause taking more CPU, Could you Please let us know how can we optimize above Query.
GROUP BY selected_sfs.sfName
-- ,service_form_id
-- ,open_date
-- ,opened
-- ,hnjh_generated_sfs
-- ,closed_date
-- ,closed
,c.agingDays
,documents.document_id
,selected_sfs.contactor
,selected_sfs.lob
,doc_event_orig_actor.creator_full_name
,selected_sfs.originator_full_name
,qic.queue_name
,qic.work_item_status
-- ,ag2_document_uid
-- ,age4_document_uid
-- ,wf2_document_uid
-- ,wfe2_document_uid
,CASE
WHEN
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210, 212, 217, 227
,229, 256, 258, 274, 288, 327, 330, 332, 336, 339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
) THEN
'Category III'
WHEN actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> '' THEN
'Category II'
ELSE
'Category I'
END -- class
,CASE
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210
,212, 217, 227, 229, 256, 258, 274, 288, 327, 330, 332, 336
,339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays <= 15 THEN
'Open <= 15 Days'
WHEN (
--selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210
,212, 217, 227, 229, 256, 258, 274, 288, 327, 330, 332, 336
,339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays <= 30 THEN
'Open <= 30 Days'
WHEN (
-- selected_sfs.sft_definition_id IN (@category1_sftDefID)
selected_sfs.sft_definition_id IN ( 54, 85, 90, 136, 142, 146, 155, 156, 175, 177, 188, 202, 210
,212, 217, 227, 229, 256, 258, 274, 288, 327, 330, 332, 336
,339, 343, 356, 387
)
OR b.cat3 = 1
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'dispute' )
)
OR
(
selected_sfs.sfName = 'Contact Center SF'
AND SV.SubjectText IN ( 'inquiry' )
AND CD.Level_01_prompt IN ( 'Provider Claim (Dispute)' )
)
)
AND c.agingDays > 30 THEN
'Open > 30 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 21 THEN
'Open <= 21 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays <= 30 THEN
'Open <= 30 Days'
WHEN (
actiongrams_2.document_uid <> ''
OR wf_job_documents_2.document_uid <> ''
)
AND c.agingDays > 30 THEN
'Open > 30 Days'
WHEN c.agingDays <= 2 THEN
'Open <= 02 Days'
WHEN c.agingDays <= 14 THEN
'Open <= 14 Days'
WHEN c.agingDays > 14 THEN
'Open > 14 Days'
END -- age_group
July 9, 2024 at 3:22 pm
As we work on goodwill, please provide some sample data, the formatted query (like Phil did), expected results
DDL: tablestructure/views
Sampledata: insert statements
Query: formatted / human readable ( code tag?)
Expected results
Otherwise: have a look at https://www.brentozar.com/training/think-like-sql-server-engine/
July 9, 2024 at 4:05 pm
An actual execution plan (e.g., from https://www.brentozar.com/pastetheplan/) would probably help us.
July 9, 2024 at 4:52 pm
Consider changing the report from using one embedded monster query into a call to a stored procedure where you can split the original query up into several lean and mean queries, the result of which you then store in temp tables and/or table variables and lastly join those intermediate temp tables into the dataset you want returned.
However, this will require a different approach than using an embedded query if you use multi-value parameters. A stored procedure does require multi-value parameters to be passed as CSV type values that can then be split again internally using the STRING_SPLIT funtion. In the report you can use the join() funtion to assemble the CSV string parameter to pass on to the stored procedure.
I don't know your data or your willingness to try this, but it's my experience that in many cases it's well worth the effort.
July 9, 2024 at 4:56 pm
I don't know your data or your willingness to try this, but it's my experience that in many cases it's well worth the effort.
I agree 100%. It has the added benefit of the business logic remaining within the database, which all of us SQLers know is usually a good thing.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply