Hi Stan,
Below is the result, without going into indexes etc, using the query you suggested, on client machine it is taking hours, this time I
ran on server.
With your suggestion--Full Load on server ran for 28.00 Min by loading 40,547,768 --
Original query as posted--Full load on server ran for 19.35 Min by loading 28,996,268
Looks like not much difference, may need to create indexes.
Neil
ok. are both queries are returining the same data.. how many output rows are you excepting?
can you check the session wait while the query is running.
do set statistics IO on ans Set Statistics Time On -- so, we get some metrics on logical vs physical reads and cpu cost.
also, i do not see any suggestions being made for missing indexes in the execution plan..
but, Try creating the below indexes, you might need all of them, but let's start with all of them and monitor how the index is being used - using dmv - index_usager --
CREATE NONCLUSTERED INDEX [NC_DEXT_EngProfile] ON [DEXT].[EngagementProfile]
(
[sourceid] ASC,
[extractdateid] ASC,
[engfileid] ASC
)
INCLUDE ( [periodenddate],[engfilename]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NC_ExtTransLoad_RRR] ON [ExtTransLoad].[ruleresultrow]
(
[sourceid] ASC,
[extractdateid] ASC,
[engfileid] ASC
)
INCLUDE ( [ruleid]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NC_ExtTransLoad_RD] ON [ExtTransLoad].[ruledefinition]
(
[ruleid] ASC,[ruletype] ASC,[isactive] ASC,[ruleresultid] ASC
)
INCLUDE ([rulenumber]],[category],[subcategory] ) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NC_DEXT_TeamMember] ON [DExt].[teammember]
(
[sourceid] ASC,
[extractdateid] ASC,
[engfileid] ASC
)
INCLUDE ( [teammemberlastname],[teammemberfirstname]) WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
SELECT [EP].[engfileid], [EP].[engfilename],[EP].[periodenddate],
[TM].[teammemberfirstname] + ' ' + [teammemberlastname] AS [TeamMember],[TM].[engrole],
[RRR].[ruleid],
[RuleDef].[rulenumber], [RuleDef].[ruletype], [RuleDef].[category], [RuleDef].[subcategory]
FROM [DExt].[engagementprofile] [EP]
INNER JOIN [ExtTransLoad].[ruleresultrow] [RRR]
ON [EP].[sourceid] = [RRR].[sourceid] AND [EP].[extractdateid] = [RRR].[extractdateid] AND [EP].[engfileid] = [RRR].[engfileid]
INNER JOIN [ExtTransLoad].[ruledefinition] [RuleDef]
ON [RuleDef].[ruleid] = [RRR].[ruleid] AND [RuleDef].[ruletype] = 'Documentation Diagnostics' AND [RuleDef].[isactive] = 1 AND [RRR].[ruleresultid] = RuleDef.ruleresultid
INNER JOIN [DExt].[teammember] [TM]
ON [EP].[sourceid] = [TM].[sourceid] AND [EP].[extractdateid] = [TM].[extractdateid] AND [EP].[engfileid] = [TM].[engfileid]