March 23, 2010 at 7:11 am
Guys:
I am tendering my apologies upfront for not providing any sort of DDLs and DMLs for this post.
I need a clear direction to remove loops from the following code...I repeat, am not looking for exact solution as i am not providing DDL or DML.
There are two while loops in the following code...i need some sort of help in improving the performance by replacing with set based operations.....
Declare @DCMResourceID varchar(max)
Declare @Periodids varchar(max)
Declare @TBA_FLAGS varchar(max)
Set DCMResourceID ='2604, 2606,2607,2610'
Set Periodids = '6,7,8'
set @TBA_FLAGS = 'N,N,N,N'
WHILE (patindex('%,%', @DCMResourceID) > 0 )
BEGIN
set @ManagerID = Cast(substring(@DCMResourceID, 1, patindex('%,%', @DCMResourceID)-1) as INT)
Set @DCMResourceID = Substring(@DCMResourceID, patindex('%,%', @DCMResourceID) + 1, len(@DCMResourceID))
Set @TBA_FLAG = substring(@TBA_FLAGS, 1, patindex('%,%', @TBA_FLAGS)-1)
Set @TBA_FLAGS = Substring(@TBA_FLAGS, patindex('%,%', @TBA_FLAGS) + 1, len(@TBA_FLAGS))
WHILE (patindex('%,%', @Periodids) > 0 )
Begin
set @PeriodID = Cast(substring(@Periodids, 1, patindex('%,%', @Periodids)-1) as INT)
Set @Periodids = Substring(@Periodids, patindex('%,%', @Periodids) + 1, len(@Periodids)) ;
WITH ResourceManagerLevelReportHeirarchy as
(
SELECT tdr.DCMResourceID,
tdr.EMPID,
tdr.ReportsToID,
@ManagerID AS ManagerID,
tdr.TBA_FLAG,
@PeriodID AS PeriodID
FROM #tblDCMResources tdr
Where tdr.DCMResourceID = @ManagerID
AND tdr.IsActive = 'Y'
And tdr.TBA_FLAG = @TBA_FLAG
UNION ALL
SELECT tdbd.DCMResourceID,
tdr.EMPID,
ISNULL(tdbd.PositionReportsToID,tdbd.CurrentReportsToID),
@ManagerID AS ManagerID,
tdr.TBA_FLAG,
@PeriodID AS PeriodID
FROM tblDepartmentbyDate tdbd
INNER JOIN tblPeriod tp
ON ((tdbd.StartDate <= tp.PeriodEndDate) AND (tdbd.EndDate >= tp.PeriodStartdate))
INNER JOIN #tblDCMResources tdr
ON tdr.DCMResourceID = tdbd.DCMResourceID
INNER JOIN ResourceManagerLevelReportHeirarchy h
ON ISNULL(tdbd.PositionReportsToID,tdbd.CurrentReportsToID) = h.EMPID
and tdr.LEAF_LEVEL_TBA = h.TBA_FLAG
WHERE tdr.IsActive='Y'
AND tp.PeriodID = @PeriodID and tdr.ReportsToID = h.EMPID
)
INSERT INTO #tbl
SELECT DCMResourceID,
-- EMPID,
ReportsToID,
ManagerID,
PeriodID
FROM ResourceManagerLevelReportHeirarchy
WHERE DCMResourceID <> @ManagerID
End
END
I appreciate your patience and help...
March 23, 2010 at 1:40 pm
I would look into a Tally table solution with CROSS APPLY or a split string function together with an intermediate table and a quirky update or any combination of those "tools".
That's just wild guessing since there is not a single start value nor an expected result...
I'm sorry but I'm not going to reverse engineer that code to figure out what exactly would be the best replacement method...
March 23, 2010 at 2:44 pm
You might want to check out the 15 ways to lose your cursor[/url] articles by RBarryYoung.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 23, 2010 at 5:25 pm
You don't have to reverse-engineer much to figure out that the code posted could never work.
DECLARE @DCMResourceID varchar(max)
Set @DCMResourceID ='2604, 2606,2607,2610'
WHILE (patindex('%,%', @DCMResourceID) > 0 )
begin
select (patindex('%,%', @DCMResourceID)) -- replaces all the code from the OPs example
end
That, gentleman, is a never-ending loop.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 23, 2010 at 5:38 pm
Seeing how you are passing parameters which are strings of comma-separated values. I can suggest this approach without trying to guess at the structure and content of your tables.
I often take such delimited strings, parse them into individual elements and use them to populate indexed #temporary tables, which are then JOINed to the primary tables, so that only matching values are returned from a single query.
Hope that helps. For more detail we really need to see some sample tables with scripts to populate them, and expected output.
The data doesn't need to be production data which we all understand is confidential. It does have to be fictional examples sufficient to let us understand the result set you want to produce. The structure doesn't have to be identical to production either. Omit some columns that aren't necessary to the query and replace the column names with fictional names. That's fine with us. One row id is like any other, one date is like any other, and one amount is like any other. It's really not a trade secret to track dates and amounts, and maybe even to group things by departments and managers and such.
Please understand. We're all volunteers and our time is as valuable to us as yours is to you. In the absence of specifics, we just aren't highly motivated to puzzle out what you're trying to do.
Best of luck to you. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 23, 2010 at 11:08 pm
Thanking all for responses..
I did some work on this procedure..rewritten the code without using while loops...tried having tally table to insert the values from comma separated string into temp tables and then rewriting recursive CTE by cross applying with temp tables (populated splitting CSV with help of tally)...but the result is not as expected..may be iam over looking something..
Declare @Periodids varchar(max)
Declare @TBA_FLAGS varchar(max)
Set DCMResourceID ='2604, 2606,2607,2610'
Set Periodids = '6,7,8'
set @TBA_FLAGS = 'N,N,N,N'
CREATE TABLE #ResourceListTable
(
Row_number int IDENTITY(1,1),
DCMResourceID int ,
TBA_FLAG CHAR(1)
)
CREATE TABLE #Periods
(
Periodid int
)
insert into #Periods
SELECT
SUBSTRING(','+@Periodids+',',N +1,CHARINDEX(',',','+@Periodids+',',N +1)-N -1) AS PeriodId
FROM
dbo.Tally
WHERE
N < LEN(','+@Periodids+',')
AND SUBSTRING(','+@Periodids+',',N ,1) = ','
INSERT INTO #ResourceListTable (DCMResourceID,TBA_FLAG)
SELECT
p1.Value,
p2.Value
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY N ) AS 'id',
SUBSTRING(','+@DCMResourceID+',',N +1,CHARINDEX(',',','+@DCMResourceID+',',N +1)-N -1) AS Value
FROM
dbo.Tally
WHERE
N < LEN(','+@DCMResourceID+',')
AND SUBSTRING(','+@DCMResourceID+',',N ,1) = ','
) p1
JOIN
(
SELECT
ROW_NUMBER () OVER (ORDER BY N ) AS 'id',
SUBSTRING(','+@TBA_FLAGs+',',N +1,CHARINDEX(',',','+@TBA_FLAGs+',',N +1)-N -1) AS Value
FROM
dbo.Tally
WHERE
N < LEN(','+@TBA_FLAGs+',')
AND SUBSTRING(','+@TBA_FLAGs+',',N ,1) = ','
) p2 ON p2.id = p1.id
CREATE TABLE #tbl
(
DCMResourceID Int,
--EMPID Varchar(20),
ReportsToID Varchar(20),
ManagerID int,
PeriodID int
)
;WITH ResourceManagerLevelReportHeirarchy as
(
SELECT tdr.DCMResourceID,
tdr.EMPID,
tdr.ReportsToID,
tdr.DCMResourceID AS ManagerID,
tdr.TBA_FLAG,
pd.PeriodID AS PeriodID
FROM #tblDCMResources tdr
inner join #ResourceListTable rtl on tdr.DCMResourceID = rtl.DCMResourceID
cross apply #Periods pd
where tdr.IsActive = 'Y'
And tdr.TBA_FLAG = rtl.TBA_FLAG
UNION ALL
SELECT tdbd.DCMResourceID,
tdr.EMPID,
ISNULL(tdbd.PositionReportsToID,tdbd.CurrentReportsToID),
tdr.DCMResourceID AS ManagerID,
tdr.TBA_FLAG,
h.PeriodID AS PeriodID
FROM tblDepartmentbyDate tdbd
INNER JOIN tblPeriod tp
ON ((tdbd.StartDate <= tp.PeriodEndDate) AND (tdbd.EndDate >= tp.PeriodStartdate))
INNER JOIN #tblDCMResources tdr
ON tdr.DCMResourceID = tdbd.DCMResourceID
--INNER JOIN #ResourceListTable rtl
--ON tdr.DCMResourceID = rtl.DCMResourceID
INNER JOIN ResourceManagerLevelReportHeirarchy h
ON ISNULL(tdbd.PositionReportsToID,tdbd.CurrentReportsToID) = h.EMPID
--and tdr.DCMResourceID = h.DCMResourceID
and tdr.LEAF_LEVEL_TBA = h.TBA_FLAG
WHERE tdr.IsActive='Y'
AND tp.PeriodID = h.PeriodID and tdr.ReportsToID = h.EMPID
)
INSERT INTO #tbl
SELECT DCMResourceID,
--EMPID,
ReportsToID,
ManagerID,
PeriodID
FROM ResourceManagerLevelReportHeirarchy
--
March 24, 2010 at 6:07 am
We still don't know what results you are expecting, so we are unlikely to be of any more assistance. At this point, I don't even know why you would be using a recursive CTE.
Good luck with the rewrite though!
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2010 at 5:55 am
I will add that this certainly looks like this could be a quite complex problem. Forums, as someone else mentioned, are 'staffed' by volunteers and they are best used for short, quick, relatively simple assistance. This one may be outside that definition. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2010 at 2:17 pm
The Dixie Flatline (3/23/2010)
You don't have to reverse-engineer much to figure out that the code posted could never work.
DECLARE @DCMResourceID varchar(max)
Set @DCMResourceID ='2604, 2606,2607,2610'
WHILE (patindex('%,%', @DCMResourceID) > 0 )
begin
select (patindex('%,%', @DCMResourceID)) -- replaces all the code from the OPs example
end
That, gentleman, is a never-ending loop.
It is indeed, but the original contains no never ending loop. Your replacement does not include the line which makes it a terminating loop:-
Set @DCMResourceID = Substring(@DCMResourceID, patindex('%,%', @DCMResourceID) + 1, len(@DCMResourceID))
which reduces the number of commas in @DCMResourceID by 1 each time round the loop, until there are none left and the loop terminates.
Tom
April 7, 2010 at 4:34 pm
My eyes walked right past that. Time for new glasses. Thank you for pointing out my error, Tom. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply