help in avoiding loop

  • 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

    Wheretdr.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

    SELECTDCMResourceID,

    --EMPID,

    ReportsToID,

    ManagerID,

    PeriodID

    FROMResourceManagerLevelReportHeirarchy

    WHEREDCMResourceID <> @ManagerID

    End

    END

    I appreciate your patience and help...

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • 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...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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

  • 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

    SELECTDCMResourceID,

    --EMPID,

    ReportsToID,

    ManagerID,

    PeriodID

    FROMResourceManagerLevelReportHeirarchy

    --

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • 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

  • 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

  • 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

  • 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