September 30, 2009 at 8:56 am
I have the stored procedure listed out below. Running it as is, takes 13 seconds. If I uncomment the "if @Code" block, it took 36 minutes and I didn't bother letting it finish. Can anyone explain why there is such a large discrepancy in time?
I just wanted to get rid of the null values from the final table, and even though I've figured out some other way to do it, this is driving me crazy.
Chris
ALTER procedure [dbo].[PopulateUserView]
as
begin
set nocount on
declare @UserName varchar(10)
declare @LOB varchar(25)
declare @Reg varchar(25)
declare @Dst varchar(25)
declare @Clt varchar(25)
declare @PC varchar(25)
declare @Code varchar(25)
declare @user-id int
select * into #temp from openquery(HMSFIN,
'select USER_ID
,ORG_LVL2 as LOB
,ORG_LVL3 as Region
,ORG_LVL4 as District
,ORG_LVL5 as Client
,PC_NUMBER as ProfitCenter
from USER_SECURITY
where YEAR in (select max(YEAR) from MONTH_CALENDAR
where CLOSED_MONTH = ''Y'')
and ORG_LVL1 = ''30BSU0000'' and FIN_EST_ACCESS in (''W'', ''R'')')
insert into #temp(
USER_ID
,LOB
,REGION
,DISTRICT
,CLIENT
,PROFITCENTER)
select * from openquery(HEDFIN,
'select USER_ID
,ORG_LVL2 as LOB
,ORG_LVL3 as Region
,ORG_LVL4 as District
,ORG_LVL5 as Client
,PC_NUMBER as ProfitCenter
from USER_SECURITY
where YEAR in (select max(YEAR) from MONTH_CALENDAR
where CLOSED_MONTH = ''Y'')
and ORG_LVL1 = ''50BSU0000'' and FIN_EST_ACCESS in (''W'', ''R'')')
declare cUsers cursor fast_forward for
select distinct u.tx_UserName, i.LOB, i.Region, i.District, i.Client, i.ProfitCenter
from dbo.TD_User u join #temp i on u.tx_UserName = i.USER_ID
begin tran
delete from dbo.TD_UserView_XREF
dbcc checkident('dbo.TD_UserView_XREF', reseed, 0)
open cUsers
fetch next from cUsers into @UserName, @LOB, @Reg, @Dst, @Clt, @PC
while @@fetch_status = 0
begin
set @Code = (select case when @LOB is not null then @LOB
when @Reg is not null then @Reg
when @Dst is not null then @Dst
when @Clt is not null then @Clt
else @PC
end)
set @user-id = (select in_UserID from dbo.TD_USER
where tx_UserName = @UserName)
-- if @Code is not null
-- begin
insert into dbo.TD_UserView_XREF
select @user-id, PCValue
from dbo.GetProfitCenters(@Code)
if (@@error!= 0)
begin
raiserror 20000 'PopulateUserView: Error inserting into dbo.TD_UserView_XREF'
rollback tran
return 1
end
fetch next from cUsers into @UserName, @LOB, @Reg, @Dst, @Clt, @PC
-- end
end
close cUsers
deallocate cUsers
commit tran
return 0
end
This is the function definition, in case that's the reason:
ALTER function [dbo].[GetProfitCenters](
@Code varchar(10))
returns @PCList table(
PCValue varchar(10))
as
begin
if charindex('LOB', @Code, 0) = 3 --Line of Business
insert into @PCList(PCValue)
select pc.tx_ProfitCenterValue
from dbo.TL_ProfitCenter pc
join dbo.TL_Client c on pc.in_ClientID = c.in_ClientID
join dbo.TL_District d on c.in_DistrictID = d.in_DistrictID
join dbo.TL_Region r on d.in_RegionID = r.in_RegionID
join dbo.TL_LineOfBusiness l on r.in_LineOfBusinessID = l.in_LineOfBusinessID
where l.tx_LineOfBusinessValue = @Code
else if charindex('REG', @Code, 0) = 3 --Region
insert into @PCList(PCValue)
select pc.tx_ProfitCenterValue
from dbo.TL_ProfitCenter pc
join dbo.TL_Client c on pc.in_ClientID = c.in_ClientID
join dbo.TL_District d on c.in_DistrictID = d.in_DistrictID
join dbo.TL_Region r on d.in_RegionID = r.in_RegionID
where r.tx_RegionValue = @Code
else if charindex('DST', @Code, 0) = 3 --District
insert into @PCList(PCValue)
select pc.tx_ProfitCenterValue
from dbo.TL_ProfitCenter pc
join dbo.TL_Client c on pc.in_ClientID = c.in_ClientID
join dbo.TL_District d on c.in_DistrictID = d.in_DistrictID
where d.tx_DistrictValue = @Code
else if charindex('C', @Code, 0) = 3 --Client
insert into @PCList(PCValue)
select pc.tx_ProfitCenterValue
from dbo.TL_ProfitCenter pc
join dbo.TL_Client c on pc.in_ClientID = c.in_ClientID
where c.tx_ClientValue = @Code
else --ProfitCenter
insert into @PCList(PCValue)
select @Code
return
end
September 30, 2009 at 10:53 am
Your main problem is the complicated table valued function and the cursor does not help.
Try to make the proc set based. As you have not provided DDL or test data it is difficult to tell how to do
this, but the following should get you started:
CREATE PROCEDURE dbo.PopulateUserView2
AS
BEGIN
SET NOCOUNT ON
-- Need to add error handling
SELECT *
INTO #temp
FROM OPENQUERY
(
HMSFIN,
'SELECT USER_ID
,ORG_LVL2 AS LOB
,ORG_LVL3 AS Region
,ORG_LVL4 AS District
,ORG_LVL5 AS Client
,PC_NUMBER AS ProfitCenter
FROM USER_SECURITY
WHERE YEAR IN (SELECT MAX(YEAR) FROM MONTH_CALENDAR
WHERE CLOSED_MONTH = ''Y'')
AND ORG_LVL1 = ''30BSU0000'' AND FIN_EST_ACCESS in (''W'', ''R'')'
)
INSERT INTO #temp
SELECT *
FROM OPENQUERY
(
HEDFIN,
'SELECT USER_ID
,ORG_LVL2
,ORG_LVL3
,ORG_LVL4
,ORG_LVL5
,PC_NUMBER
FROM USER_SECURITY
WHERE YEAR IN (SELECT MAX(YEAR) FROM MONTH_CALENDAR
WHERE CLOSED_MONTH = ''Y'')
AND ORG_LVL1 = ''50BSU0000''
AND FIN_EST_ACCESS IN (''W'', ''R'')'
)
SELECT *
INTO #Temp2
FROM
(
SELECT
D.in_UserID
,CASE N.N
WHEN 1 THEN D.LOB
WHEN 2 THEN D.Region
WHEN 3 THEN D.District
WHEN 4 THEN D.Client
ELSE D.ProfitCenter
END AS Code
,N.N AS CLevel
FROM
(
SELECT DISTINCT
u.in_UserID
,i.LOB
,i.Region
,i.District
,i.Client
,i.ProfitCenter
FROM dbo.TD_User u
JOIN #temp i
ON u.tx_UserName = i.[USER_ID]
) D
JOIN
(
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
) N (N)
) D1
WHERE Code IS NOT NULL
CREATE CLUSTERED INDEX Temp2Index
ON #Temp2 (CLevel)
-- TRUNCATE is better if no FKs
-- DELETE FROM dbo.TD_UserView_XREF
-- DBCC CHECKIDENT('dbo.TD_UserView_XREF', reseed, 0)
TRUNCATE TABLE dbo.TD_UserView_XREF
INSERT INTO dbo.TD_UserView_XREF
SELECT T.in_UserID, pc.tx_ProfitCenterValue
FROM dbo.TL_ProfitCenter pc
JOIN dbo.TL_Client c
ON pc.in_ClientID = c.in_ClientID
JOIN dbo.TL_District d
ON c.in_DistrictID = d.in_DistrictID
JOIN dbo.TL_Region r
ON d.in_RegionID = r.in_RegionID
JOIN dbo.TL_LineOfBusiness l
ON r.in_LineOfBusinessID = l.in_LineOfBusinessID
JOIN #Temp2 T
ON l.tx_LineOfBusinessValue = T.Code
WHERE T.CLevel = 1
INSERT INTO dbo.TD_UserView_XREF
SELECT T.in_UserID, pc.tx_ProfitCenterValue
FROM dbo.TL_ProfitCenter pc
JOIN dbo.TL_Client c
ON pc.in_ClientID = c.in_ClientID
JOIN dbo.TL_District d
ON c.in_DistrictID = d.in_DistrictID
JOIN dbo.TL_Region r
ON d.in_RegionID = r.in_RegionID
JOIN #Temp2 T
ON r.tx_RegionValue = T.Code
WHERE T.CLevel = 2
INSERT INTO dbo.TD_UserView_XREF
SELECT T.in_UserID, pc.tx_ProfitCenterValue
FROM dbo.TL_ProfitCenter pc
JOIN dbo.TL_Client c
ON pc.in_ClientID = c.in_ClientID
JOIN dbo.TL_District d
ON c.in_DistrictID = d.in_DistrictID
JOIN #Temp2 T
ON d.tx_DistrictValue = T.Code
WHERE T.CLevel = 3
INSERT INTO dbo.TD_UserView_XREF
SELECT T.in_UserID, pc.tx_ProfitCenterValue
FROM dbo.TL_ProfitCenter pc
JOIN dbo.TL_Client c
ON pc.in_ClientID = c.in_ClientID
JOIN #Temp2 T
ON c.tx_ClientValue = T.Code
WHERE T.CLevel = 4
INSERT INTO dbo.TD_UserView_XREF
SELECT in_UserID, Code
FROM #Temp2
WHERE CLevel = 5
GO
September 30, 2009 at 11:41 am
Awesome example, I was working on a response and when I saw your response I felt a little bit like an idot without the savant. 🙂
September 30, 2009 at 12:49 pm
The comments about the cursor/complicated TVF are true, and if it's possible for you to re-write this in a set based manner, that would be ideal. As to why this is taking 36+ minutes, it's because you put your fetch next inside the conditional check IF @code is not null. As soon as you hit a null, your cursor is an endless loop that will never advance. Move the fetch next outside of the IF statement.
September 30, 2009 at 12:54 pm
Excellent, thank you all very much.
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply