Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unhappy Unanswered: String Concatenation using CURSORs

    Hi all,

    I have a sitaution here where I need to convert some relational data to a flat file. I have a primary record that flatens out pretty well with the exception of two columns that need to have row data converted to strings via concatenation. The column size is Char(146) . I attempted to use 2 cursors to create the strings.

    C1 --outside cursor to pull unique record id (161,000+ records)

    C2 -- SELECTs the top 29 secondary (relational) records for each C1 rec
    (FIELDX as Char(5))

    FIELDX is the concatenated up to 29 times and inserted in to a flat table
    based on record id for flat file export.

    The issue is that this takes FOREVER to run and the 3Ghz XEON w/2GB Ram server weeps.

    Declarations are as follows:

    DECLARE C1 CURSOR FAST_FORWARD READ_ONLY
    FOR
    SELECT Distinct Record_ID
    FROM tblProcedure

    DECLARE C2 CURSOR FAST_FORWARD READ_ONLY
    FOR
    --need only the top 29 relational records to string out
    SELECT TOP 29 Cast(pr_icd1 as Char(5))
    FROM tblProcedure WHERE Record_ID = @RECID --From C1

    OPEN C2

    SET @tmpICDstr = ''

    FETCH NEXT FROM C2 INTO @tmpICDchar

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @tmpICDstr = @tmpICDchar + @tmpICDstr

    FETCH NEXT FROM C2 INTO @tmpICDchar


    END

    --'INSERT INTO [Validation].[dbo].[tmpICDStr] (RECID, sg) VALUES
    (@RECID, @tmpICDstr)
    --'INSERT INTO @tmp (RECID, STRsg) VALUES (@RECID, @tmpICDstr)
    SET @tmpICDchar = ''

    Anybody have a suggestion on how to speed this up. I am looking at about 1min/100 C1 records. Do the math for 161,000+ C1 records. Ugh.

    Any suggestion would be appeciated!

    tnx

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Drop both your cursors.

    Create this function:
    Code:
    create function dbo.Concat_ICD(@RECID int)
    returns varchar(200) as
    begin
    declare	@tmpICDchar varchar(200)
    set	@tmpICDchar = ''
    select	@tmpICDchar = @tmpICDchar + ICD
    from	(select	top 29
    		Cast(pr_icd1 as Char(5)) as ICD
    	from	tblProcedure
    	WHERE	Record_ID = @RECID) TopValues
    return	@tmpICDchar
    end
    Then insert the results of this query to your table:
    Code:
    SELECT	Distinct
    	Record_ID,
    	dbo.Concat_ICD(Record_ID)
    FROM	tblProcedure
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't know about the OP, but I think that's a slick solution. I'm still working out how it concatenates multiple records together.

    Very nice, blindman.
    Paul

  4. #4
    Join Date
    Nov 2003
    Posts
    39

    String Concatenation using CURSORs - Yea

    Thanks blindman! You helped me in more ways that you think. I looked at he RECORD ID in my source table and found the vast majority of my problem. GUIDs and indexing issues. I used your solution along with my fixes to the indexing isseus and got 172456 records to update with the string your function created in about 25 sec. Cook'n with gas now!

    I should of seen that select in your function though.
    I was out of work too long!

    Thanks for the push!

    rmetz

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    From 25 hours to 25 seconds? Gotta like that!
    Go ahead and list that on your annual accomplishments, and maybe you'll get big raise.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •