Sorry for not including this sooner. The query statement and plan are
below. Hopefully the text will be readable in your newsreader or you can
easily cut and paste it.
There is a clustered index on Lead_Action_History.report_date.
Lead_Master, Report_Group, Report_Month, Import_Batch, and
Project_Dispositions are all joined on primary keys.
Project_Disp_Config_Detail is joined using columns that make up the
first two columns of the primary key, which is also clustered.
Lead_Master has about 4.6M records. Lead_Action_History has about 22.5M
records. For the report_date in question there are just over 65K
records. All of the other tables are very small.
As a reminder... the code only uses the plan below when it is run within
the stored procedure. Running it directly in QA uses a more efficient
query plan, also included below.
I tried setting the MAXDOP option to 1 and there was no substantial
change. The parallelism in the below plan was on a pretty insignificant
part of the query.
Thanks!
QUERY:
SELECT RM.report_month_abrev + ' ' + RG.report_Group_descr [Lead
Type],
LAH.calldisp AS [Call Disposition],
CASE WHEN LEN(IsNull(PD.fdisp_id, '')) = 0 THEN ' ' ELSE
IsNull(PD.fdisp_id, '') END AS [FDisp],
CASE WHEN LEN(IsNull(PDCD.output_code, '')) = 0 THEN ' '
ELSE IsNull(PDCD.output_code, '') END AS [Output Code],
COUNT(*) AS [Count],
CAST(PD.calldisp_descr AS VARCHAR(30)) AS [Calldisp
Description]
FROM Lead_Action_History LAH WITH (NOLOCK)
INNER JOIN Lead_Master LM WITH (nolock) ON LAH.lead_seq = LM.lead_seq
INNER JOIN Mercury.dbo.Report_Group RG WITH (nolock) ON
RG.report_group_seq = LM.report_group_seq
INNER JOIN Mercury.dbo.Report_Month RM WITH (nolock) ON
RG.report_month = RM.report_month_seq
INNER JOIN Import_Batch IB WITH (nolock) ON IB.import_batch_seq =
LM.import_batch_seq AND IB.batch_type = 'P'
INNER JOIN Mercury.dbo.Project_Dispositions PD WITH (nolock) ON
PD.project_seq = 10440
AND
PD.calldisp = LAH.calldisp
INNER JOIN Mercury.dbo.Project_Disp_Config_Detail PDCD WITH (nolock)
ON PDCD.project_seq = 10440
AND PDCD.calldisp = LAH.calldisp
WHERE report_date = @in_report_date
AND LAH.calldisp IS NOT NULL
GROUP BY RM.report_month_abrev + ' ' + RG.report_Group_descr,
LAH.calldisp,
PD.fdisp_id,
PD.calldisp_descr,
PDCD.output_code
PLAN WHEN RUN WITHIN STORED PROCEDURE:
StmtText
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1009]=If
(len(isnull([PD].[FDISP_ID], ''))=0) then ' ' else
Convert(isnull([PD].[FDISP_ID], '')), [Expr1010]=If
(len(isnull([PDCD].[OUTPUT_CODE], ''))=0) then ' ' else
isnull([PDCD].[OUTPUT_CODE], ''),
[Expr1011]=Convert([PD].[CALLDISP_DESCR])))
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Expr1032])))
|--Stream Aggregate(GROUP BY:([Expr1007],
[LAH].[calldisp], [PD].[FDISP_ID], [PD].[CALLDISP_DESCR],
[PDCD].[OUTPUT_CODE]) DEFINE:([Expr1032]=Count(*)))
|--Sort(ORDER BY:([Expr1007] ASC, [LAH].[calldisp]
ASC, [PD].[FDISP_ID] ASC, [PD].[CALLDISP_DESCR] ASC,
[PDCD].[OUTPUT_CODE] ASC))
|--Compute
Scalar(DEFINE:([Expr1007]=[RM].[report_month_abrev]+'
'+[RG].[report_group_descr]))
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([PDCD].[CALLDISP]))
|--Filter(WHERE:([LAH].[calldisp]=[PDCD].[CALLDISP]))
| |--Bookmark
Lookup(BOOKMARK:([Bmk1000]), OBJECT:([ALWOB].[dbo].[Lead_Action_History]
AS [LAH]))
| |--Nested
Loops(Inner Join, OUTER REFERENCES:([LM].[lead_seq]) WITH PREFETCH)
| |--Nested
Loops(Inner Join)
| |
|--Filter(WHERE:([IB].[batch_type]='P'))
| | |
|--Bookmark Lookup(BOOKMARK:([Bmk1004]),
OBJECT:([ALWOB].[dbo].[Import_Batch] AS [IB]))
| | |
|--Nested Loops(Inner Join, OUTER REFERENCES:([LM].[import_batch_seq]))
| | |
|--Bookmark Lookup(BOOKMARK:([Bmk1001]),
OBJECT:([ALWOB].[dbo].[Lead_Master] AS [LM]))
| | |
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([RG].[report_group_seq]))
| | |
| |--Hash Match(Inner Join,
HASH:([RM].[report_month_seq])=([RG].[report_month]),
RESIDUAL:([RG].[report_month]=[RM].[report_month_seq]))
| | |
| | |--Parallelism(Broadcast)
| | |
| | | |--Table Scan(OBJECT:([Mercury].[dbo].[Report_Month]
AS [RM]))
| | |
| | |--Parallelism(Distribute Streams)
| | |
| | |--Clustered Index
Scan(OBJECT:([Mercury].[dbo].[Report_Group].[PK_REPORT_GROUP] AS [RG]))
| | |
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1029],
[Expr1030], [Expr1031]))
| | |
| |--Compute
Scalar(DEFINE:([Expr1029]=Convert([RG].[report_group_seq])-1,
[Expr1030]=Convert([RG].[report_group_seq])+1, [Expr1031]=If
(Convert([RG].[report_group_seq])-1=NULL) then 0 else 6|If
(Convert([RG].[report_group_seq])+1=NULL) then 0 else 10))
| | |
| | |--Constant Scan
| | |
| |--Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Master].[LeadMaster_IDX04] AS [LM]),
SEEK:([LM].[report_group_seq] > [Expr1029] AND [LM].[report_group_seq] <
[Expr1030]),
WHERE:([RG].[report_group_seq]=Convert([LM].[report_group_seq])) ORDERED
FORWARD)
| | |
|--Index Seek(OBJECT:([ALWOB].[dbo].[Import_Batch].[pk_import_batch] AS
[IB]), SEEK:([IB].[import_batch_seq]=[LM].[import_batch_seq]) ORDERED
FORWARD)
| |
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISP_CONFIG_DETAIL].[ProjectDispCo
nfigDetail_PK] AS [PDCD]), SEEK:([PDCD].[PROJECT_SEQ]=10440) ORDERED
FORWARD)
| |--Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Action_History].[LeadActionHistory_IDX0
3] AS [LAH]), SEEK:([LAH].[lead_seq]=[LM].[lead_seq] AND
[LAH].[report_date]=[@in_report_date]) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISPOSITIONS].[PK_PROJECT_DISPOSIT
IONS] AS [PD]), SEEK:([PD].[CALLDISP]=[PDCD].[CALLDISP] AND
[PD].[PROJECT_SEQ]=10440) ORDERED FORWARD)
-----------------------------------------------------------
PLAN WHEN RUN OUTSIDE STORED PROCEDURE:
StmtText
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-
|--Compute Scalar(DEFINE:([Expr1009]=If (len(isnull([PD].[FDISP_ID],
''))=0) then ' ' else Convert(isnull([PD].[FDISP_ID], '')),
[Expr1010]=If (len(isnull([PDCD].[OUTPUT_CODE], ''))=0) then ' '
else isnull([PDCD].[OUTPUT_CODE], ''),
[Expr1011]=Convert([PD].[CALLDISP_DESCR])))
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Expr1027])))
|--Stream Aggregate(GROUP BY:([Expr1007], [LAH].[calldisp],
[PD].[FDISP_ID], [PD].[CALLDISP_DESCR], [PDCD].[OUTPUT_CODE])
DEFINE:([Expr1027]=Count(*)))
|--Sort(ORDER BY:([Expr1007] ASC, [LAH].[calldisp] ASC,
[PD].[FDISP_ID] ASC, [PD].[CALLDISP_DESCR] ASC, [PDCD].[OUTPUT_CODE]
ASC))
|--Compute
Scalar(DEFINE:([Expr1007]=[RM].[report_month_abrev]+'
'+[RG].[report_group_descr]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([PDCD].[CALLDISP]))
|--Filter(WHERE:([IB].[batch_type]='P'))
| |--Bookmark
Lookup(BOOKMARK:([Bmk1004]), OBJECT:([ALWOB].[dbo].[Import_Batch] AS
[IB]))
| |--Nested Loops(Inner Join,
OUTER REFERENCES:([LM].[import_batch_seq]))
| |--Hash Match(Inner Join,
HASH:([RM].[report_month_seq])=([RG].[report_month]),
RESIDUAL:([RG].[report_month]=[RM].[report_month_seq]))
| | |--Table
Scan(OBJECT:([Mercury].[dbo].[Report_Month] AS [RM]))
| | |--Nested
Loops(Inner Join, OUTER REFERENCES:([LM].[report_group_seq]))
| | |--Nested
Loops(Inner Join, OUTER REFERENCES:([LAH].[lead_seq]) WITH PREFETCH)
| | | |--Hash
Match(Inner Join, HASH:([PDCD].[CALLDISP])=([LAH].[calldisp]),
RESIDUAL:([LAH].[calldisp]=[PDCD].[CALLDISP]))
| | | |
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISP_CONFIG_DETAIL].[ProjectDispCo
nfigDetail_PK] AS [PDCD]), SEEK:([PDCD].[PROJECT_SEQ]=10440) ORDERED
FORWARD)
| | | |
|--Clustered Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Action_History].[LeadActionHistory_IDX0
2] AS [LAH]), SEEK:([LAH].[report_date]=[@in_report_date]) ORDERED
FORWARD)
| | |
|--Clustered Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Master].[pk_lead_master] AS [LM]),
SEEK:([LM].[lead_seq]=[LAH].[lead_seq]) ORDERED FORWARD)
| | |--Clustered
Index Seek(OBJECT:([Mercury].[dbo].[Report_Group].[PK_REPORT_GROUP] AS
[RG]), SEEK:([RG].[report_group_seq]=Convert([LM].[report_group_seq]))
ORDERED FORWARD)
| |--Index
Seek(OBJECT:([ALWOB].[dbo].[Import_Batch].[pk_import_batch] AS [IB]),
SEEK:([IB].[import_batch_seq]=[LM].[import_batch_seq]) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISPOSITIONS].[PK_PROJECT_DISPOSIT
IONS] AS [PD]), SEEK:([PD].[CALLDISP]=[PDCD].[CALLDISP] AND
[PD].[PROJECT_SEQ]=10440) ORDERED FORWARD)
-----------------
Thomas R. Hummel
Manager, Data Systems
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!