OUTER JOIN issue

The following stored procedure was created by one of our developers and it's
taking too long to be executed, we already identified that the section of th
e
coding with the problem are the outer joins, can anybody suggest something t
o
improve it?
Thanks
-- Populate performance layer table with latest data
----
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 1'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
isnull(isnull(isnull(poobd.charge_cost_centre_did,
unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
sep.charge_cost_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
sday.budget_date_did), sep.budget_date_did) as budget_date_did,
isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
sday.initiative_did), sep.initiative_did) as initiative_did,
isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(poobd.measure_POOBD, 0) as measure_POOBD,
isnull(sday.measure_SDAY, 0) as measure_SDAY,
isnull(sep.measure_SEP, 0) as measure_SEP
INTO #activity_measures_part_1
FROM @.measure_UNKNOWN unknown
FULL OUTER JOIN @.measure_POOBD poobd
ON unknown.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
unknown.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
unknown.cost_centre_did = poobd.cost_centre_did and
unknown.charge_cost_centre_did = poobd.charge_cost_centre_did and
unknown.budget_date_did = poobd.budget_date_did and
unknown.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SDAY sday
ON sday.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sday.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sday.cost_centre_did = poobd.cost_centre_did and
sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
sday.budget_date_did = poobd.budget_date_did and
sday.initiative_did = poobd.initiative_did
FULL OUTER JOIN @.measure_SEP sep
ON sep.clinical_hierarchy_ward_did =
poobd.clinical_hierarchy_ward_did and
sep.clinical_hierarchy_unit_did =
poobd.clinical_hierarchy_unit_did and
sep.cost_centre_did = poobd.cost_centre_did and
sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
sep.budget_date_did = poobd.budget_date_did and
sep.initiative_did = poobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - part 2'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stobd.
clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stobd.
clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(isnull(isnull(soobd.cost_centre_did,
wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
cost_centre_did,
isnull(isnull(isnull(soobd.charge_cost_centre_did,
wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_cos
t_centre_did) as charge_cost_centre_did,
isnull(isnull(isnull(soobd.budget_date_did,
wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
budget_date_did,
isnull(isnull(isnull(soobd.initiative_did,
wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
initiative_did,
isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
isnull(stobd.measure_STOBD, 0) as measure_STOBD
INTO #activity_measures_part_2
FROM @.measure_SOOBD soobd
FULL OUTER JOIN @.measure_WSEP9 wsep9
ON wsep9.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
wsep9.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
wsep9.cost_centre_did = soobd.cost_centre_did and
wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
wsep9.budget_date_did = soobd.budget_date_did and
wsep9.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_PTOBD ptobd
ON ptobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
ptobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
ptobd.cost_centre_did = soobd.cost_centre_did and
ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
ptobd.budget_date_did = soobd.budget_date_did and
ptobd.initiative_did = soobd.initiative_did
FULL OUTER JOIN @.measure_STOBD stobd
ON stobd.clinical_hierarchy_ward_did =
soobd.clinical_hierarchy_ward_did and
stobd.clinical_hierarchy_unit_did =
soobd.clinical_hierarchy_unit_did and
stobd.cost_centre_did = soobd.cost_centre_did and
stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
stobd.budget_date_did = soobd.budget_date_did and
stobd.initiative_did = soobd.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_Abort
select count(*) from #activity_measures_part_1
select count(*) from #activity_measures_part_2
SET @.StepCount = @.StepCount + 1
SET @.StepName = 'Populate performance layer table - final'
EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
@.StepCount
INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
(clinical_hierarchy_ward_did,
clinical_hierarchy_unit_did,
cost_centre_did,
charge_cost_centre_did,
budget_date_did,
initiative_did,
measure_UNKNOWN,
measure_POOBD,
measure_SDAY,
measure_SEP,
measure_SOOBD,
measure_WSEP9,
measure_PTOBDs,
measure_STOBDs,
batch_id)
SELECT isnull(part1.clinical_hierarchy_ward_did,
part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
isnull(part1.clinical_hierarchy_unit_did,
part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
isnull(part1.cost_centre_did, part2.cost_centre_did) as
cost_centre_did,
isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
charge_cost_centre_did,
isnull(part1.budget_date_did, part2.budget_date_did) as
budget_date_did,
isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
isnull(part1.measure_POOBD, 0) as measure_POOBD,
isnull(part1.measure_SDAY, 0) as measure_SDAY,
isnull(part1.measure_SEP, 0) as measure_SEP,
isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
isnull(part2.measure_STOBD, 0) as measure_STOBD,
@.BatchID
FROM #activity_measures_part_1 part1
FULL OUTER JOIN #activity_measures_part_2 part2
ON part1.clinical_hierarchy_ward_did =
part2.clinical_hierarchy_ward_did and
part1.clinical_hierarchy_unit_did =
part2.clinical_hierarchy_unit_did and
part1.cost_centre_did = part2.cost_centre_did and
part1.charge_cost_centre_did = part2.charge_cost_centre_did and
part1.budget_date_did = part2.budget_date_did and
part1.initiative_did = part2.initiative_did
EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR, @.@.ROWCOUNT
IF (@.Error <> 0) GOTO LBL_AbortWithout the table structures, index details and size of tables it is very
difficult to say what is wrong - but it may be worth running it through the
index tuning wizard to see what it comes up with
Have a look at the execution plan and see where the expensive bits are.
May be worth replacing all those nested isnulls with a coalesce of all the
columns in the nested isnulls - makes it much easier to read, less code and
may help.
If no luck try posting the executiuon plan and ddl for all the tables -
someone may then have some more suggestions
Mike John
"FJC" <FJC@.discussions.microsoft.com> wrote in message
news:30F9AE02-48DD-4FC9-BD5C-636224FB5CE3@.microsoft.com...
> The following stored procedure was created by one of our developers and
> it's
> taking too long to be executed, we already identified that the section of
> the
> coding with the problem are the outer joins, can anybody suggest something
> to
> improve it?
> Thanks
> -- Populate performance layer table with latest data
> ----
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 1'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(poobd.clinical_hierarchy_ward_did,
> unknown.clinical_hierarchy_ward_did), sday.clinical_hierarchy_ward_did),
> sep.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(poobd.clinical_hierarchy_unit_did,
> unknown.clinical_hierarchy_unit_did), sday.clinical_hierarchy_unit_did),
> sep.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(poobd.cost_centre_did, unknown.cost_centre_did),
> sday.cost_centre_did), sep.cost_centre_did) as cost_centre_did,
> isnull(isnull(isnull(poobd.charge_cost_centre_did,
> unknown.charge_cost_centre_did), sday.charge_cost_centre_did),
> sep.charge_cost_centre_did) as charge_cost_centre_did,
> isnull(isnull(isnull(poobd.budget_date_did, unknown.budget_date_did),
> sday.budget_date_did), sep.budget_date_did) as budget_date_did,
> isnull(isnull(isnull(poobd.initiative_did, unknown.initiative_did),
> sday.initiative_did), sep.initiative_did) as initiative_did,
> isnull(unknown.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(poobd.measure_POOBD, 0) as measure_POOBD,
> isnull(sday.measure_SDAY, 0) as measure_SDAY,
> isnull(sep.measure_SEP, 0) as measure_SEP
> INTO #activity_measures_part_1
> FROM @.measure_UNKNOWN unknown
> FULL OUTER JOIN @.measure_POOBD poobd
> ON unknown.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> unknown.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> unknown.cost_centre_did = poobd.cost_centre_did and
> unknown.charge_cost_centre_did = poobd.charge_cost_centre_did
> and
> unknown.budget_date_did = poobd.budget_date_did and
> unknown.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SDAY sday
> ON sday.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sday.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sday.cost_centre_did = poobd.cost_centre_did and
> sday.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sday.budget_date_did = poobd.budget_date_did and
> sday.initiative_did = poobd.initiative_did
> FULL OUTER JOIN @.measure_SEP sep
> ON sep.clinical_hierarchy_ward_did =
> poobd.clinical_hierarchy_ward_did and
> sep.clinical_hierarchy_unit_did =
> poobd.clinical_hierarchy_unit_did and
> sep.cost_centre_did = poobd.cost_centre_did and
> sep.charge_cost_centre_did = poobd.charge_cost_centre_did and
> sep.budget_date_did = poobd.budget_date_did and
> sep.initiative_did = poobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - part 2'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> SELECT isnull(isnull(isnull(soobd.clinical_hierarchy_ward_did,
> wsep9.clinical_hierarchy_ward_did),ptobd.clinical_hierarchy_ward_did),stob
d.clinical_hierarchy_ward_did)
> as clinical_hierarchy_ward_did,
> isnull(isnull(isnull(soobd.clinical_hierarchy_unit_did,
> wsep9.clinical_hierarchy_unit_did),ptobd.clinical_hierarchy_unit_did),stob
d.clinical_hierarchy_unit_did)
> as clinical_hierarchy_unit_did,
> isnull(isnull(isnull(soobd.cost_centre_did,
> wsep9.cost_centre_did),ptobd.cost_centre_did),stobd.cost_centre_did) as
> cost_centre_did,
> isnull(isnull(isnull(soobd.charge_cost_centre_did,
> wsep9.charge_cost_centre_did),ptobd.charge_cost_centre_did),stobd.charge_c
ost_centre_did)
> as charge_cost_centre_did,
> isnull(isnull(isnull(soobd.budget_date_did,
> wsep9.budget_date_did),ptobd.budget_date_did),stobd.budget_date_did) as
> budget_date_did,
> isnull(isnull(isnull(soobd.initiative_did,
> wsep9.initiative_did),ptobd.initiative_did),stobd.initiative_did) as
> initiative_did,
> isnull(soobd.measure_SOOBD, 0) as measure_SOOBD,
> isnull(wsep9.measure_WSEP9, 0) as measure_WSEP9,
> isnull(ptobd.measure_PTOBD, 0) as measure_PTOBD,
> isnull(stobd.measure_STOBD, 0) as measure_STOBD
> INTO #activity_measures_part_2
> FROM @.measure_SOOBD soobd
> FULL OUTER JOIN @.measure_WSEP9 wsep9
> ON wsep9.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> wsep9.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> wsep9.cost_centre_did = soobd.cost_centre_did and
> wsep9.charge_cost_centre_did = soobd.charge_cost_centre_did and
> wsep9.budget_date_did = soobd.budget_date_did and
> wsep9.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_PTOBD ptobd
> ON ptobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> ptobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> ptobd.cost_centre_did = soobd.cost_centre_did and
> ptobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> ptobd.budget_date_did = soobd.budget_date_did and
> ptobd.initiative_did = soobd.initiative_did
> FULL OUTER JOIN @.measure_STOBD stobd
> ON stobd.clinical_hierarchy_ward_did =
> soobd.clinical_hierarchy_ward_did and
> stobd.clinical_hierarchy_unit_did =
> soobd.clinical_hierarchy_unit_did and
> stobd.cost_centre_did = soobd.cost_centre_did and
> stobd.charge_cost_centre_did = soobd.charge_cost_centre_did and
> stobd.budget_date_did = soobd.budget_date_did and
> stobd.initiative_did = soobd.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort
>
> select count(*) from #activity_measures_part_1
> select count(*) from #activity_measures_part_2
> SET @.StepCount = @.StepCount + 1
> SET @.StepName = 'Populate performance layer table - final'
> EXEC @.StepID = EDW_Manager.adm.get_Step_Reference @.ProcessID, @.StepName,
> @.StepCount
> INSERT INTO EDW_reporting.whs.PERF_activity_budget_ward_by_measure
> (clinical_hierarchy_ward_did,
> clinical_hierarchy_unit_did,
> cost_centre_did,
> charge_cost_centre_did,
> budget_date_did,
> initiative_did,
> measure_UNKNOWN,
> measure_POOBD,
> measure_SDAY,
> measure_SEP,
> measure_SOOBD,
> measure_WSEP9,
> measure_PTOBDs,
> measure_STOBDs,
> batch_id)
> SELECT isnull(part1.clinical_hierarchy_ward_did,
> part2.clinical_hierarchy_ward_did) as clinical_hierarchy_ward_did,
> isnull(part1.clinical_hierarchy_unit_did,
> part2.clinical_hierarchy_unit_did) as clinical_hierarchy_unit_did,
> isnull(part1.cost_centre_did, part2.cost_centre_did) as
> cost_centre_did,
> isnull(part1.charge_cost_centre_did, part2.charge_cost_centre_did) as
> charge_cost_centre_did,
> isnull(part1.budget_date_did, part2.budget_date_did) as
> budget_date_did,
> isnull(part1.initiative_did, part2.initiative_did) as initiative_did,
> isnull(part1.measure_UNKNOWN, 0) as measure_UNKNOWN,
> isnull(part1.measure_POOBD, 0) as measure_POOBD,
> isnull(part1.measure_SDAY, 0) as measure_SDAY,
> isnull(part1.measure_SEP, 0) as measure_SEP,
> isnull(part2.measure_SOOBD, 0) as measure_SOOBD,
> isnull(part2.measure_WSEP9, 0) as measure_WSEP9,
> isnull(part2.measure_PTOBD, 0) as measure_PTOBD,
> isnull(part2.measure_STOBD, 0) as measure_STOBD,
> @.BatchID
> FROM #activity_measures_part_1 part1
> FULL OUTER JOIN #activity_measures_part_2 part2
> ON part1.clinical_hierarchy_ward_did =
> part2.clinical_hierarchy_ward_did and
> part1.clinical_hierarchy_unit_did =
> part2.clinical_hierarchy_unit_did and
> part1.cost_centre_did = part2.cost_centre_did and
> part1.charge_cost_centre_did = part2.charge_cost_centre_did and
> part1.budget_date_did = part2.budget_date_did and
> part1.initiative_did = part2.initiative_did
> EXEC @.Error = EDW_Manager.adm.log_Step_Results @.StepID, @.@.ERROR,
> @.@.ROWCOUNT
> IF (@.Error <> 0) GOTO LBL_Abort|||Consider using temporary tables instead of table variables. The options to
create indexes on table variables are very limited.
For better help, please post DDL, sample data and expected results.
ML
http://milambda.blogspot.com/|||Only a minor point (not performance related), but
you can use a single "coalesce" instead of multiple
nested "isnull"s. This may make your query easier to
read.