SQL Server Performance

Slow cursor and failing replacement program

Discussion in 'T-SQL Performance Tuning for Developers' started by nickiH, May 19, 2005.

  1. nickiH New Member

    Hi- I am trying to replace a program that I wrote with a cursor that runs way too slow with this below- unfortunately it truncates the variable that I am building with a function also below (the cursor does not do that). Maybe I should go back to the cursor method and figure out why it's too slow? I'll give this one another shot in case there is something glaringly wrong...
    It truncates my string variable @s down to 4000 characters- Here is the code:

    CREATE PROCEDURE list_table2 AS
    declare @s varchar(8000)
    set @s = ''
    select @s = @s + ', dbo.List_SIProcs(prID,''' + ItemName + ''')' from
    (select distinct ItemName from tblSIProcs2 where ItemName is not null) t
    --print Len(@s) --4000
    print @s
    set @s = 'select prID, ' + substring(@s,3,8000) + ' from (select PRID from tblSIProcs2 where PRID is not null) A
    GROUP BY PRID'
    --print (@s)
    --exec (@s)
    GO

    The function is :
    CREATE function List_SIProcs(@prID int, @ItemName varchar(10))
    returns varchar(100)
    as
    begin
    declare @tmpStr varchar(100)
    set @tmpStr=''
    Select @tmpStr = isnull(@tmpStr+', ','') + ltrim(rtrim(ItemData)) From tblSIProcs2
    where prID=@prID and ItemName = @ItemName
    order by ItemData
    return @tmpStr
    end

    Thanks a lot

    Oh- here is the table structure- the problem with this sample data is that it works- the larger amound of data is what is being truncated.
    create table tblSProcs2 (PrID int,ItemName varchar(10), ItemData varchar(10))
    insert into tblSProcs2
    select 1, 'meds','asprin'
    union all select 2,'meds','asprin'
    union all select 2,'meds','ibuprofen'
    union all select 3,'meds','bayer'
    union all select 3,'meds','asprin'
    union all select 3,'meds','ibuprofen'
    union all select 3,'area','arm'
    union all select 2,'area','leg'

Share This Page