Help converting Cursor to CTE

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
27,342
#1
Hi,

Stuck converting this to a CTE and figured I'd ask if anyone has some thoughts.
Also have the problem of the below showing me seperate results per iteration of the cursor but I'm sorting that out.

Code:
Declare @json as varchar(MAX);
    Declare @id as int;
    DECLARE @RecordCursor as CURSOR;
    SET @RecordCursor = CURSOR FOR
    SELECT system_id,record
     FROM dbo.set_records;
    OPEN @RecordCursor;
    FETCH NEXT FROM @RecordCursor INTO @id,@json;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        Select @id as id,
        max(case when NAME ='recorduuid' then convert(Varchar(50),StringValue) else '' end) as [recorduuid]
        FROM [dbo].[parseJSON]
        (
            (select @json)
        )
    FETCH NEXT FROM @RecordCursor INTO  @id,@json;
    END

    CLOSE @RecordCursor;
    DEALLOCATE @RecordCursor;
Thoughts?
 

GoB

Expert Member
Joined
Jan 7, 2008
Messages
1,241
#2
Try starting with this:

SELECT system_id, record, name, stringvalue FROM dbo.set_records CROSS APPLY parseJSON(record)
 

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
27,342
#3
At 80k records, running at 7 minutes..

Try starting with this:

SELECT system_id, record, name, stringvalue FROM dbo.set_records CROSS APPLY parseJSON(record)
Hmm.. my arch nemesis, cross apply. Let me look at this.
 

GoB

Expert Member
Joined
Jan 7, 2008
Messages
1,241
#4
I don't know it, just Googled to find how to pass table-function parameters in as part of a query. :)

But either approach will run a function on your 80k records... that's the problem with accessing data inside stored blobs.
 

retromodcoza

Well-Known Member
Joined
Mar 4, 2006
Messages
242
#5
The given query points to bad table design,improper usage of SQL Server functions and bad use of cursors.

I would redo or circumvent [dbo].[parseJSON] in this query. That would be the better solution. Since you haven't posted this function - heres a workaround which uses a semi-set base method :

(NOTE - This is very hacked up just to give you an idea of whats possible. It wont execute and I haven't tested it)
(NOTE FURTHER - This will take up some memory for 80k records , and is not optimal The double pass through the function here should be way faster than the overhead with the cursor doing it )


SQL:
DECLARE @tempTable TABLE (id INT , record VARCHAR (250), Name VARCHAR(50), JSON VARCHAR(250)

INSERT INTO @tempTable
    SELECT FirstTable.id , FirstTable.record , (SELECT Name FROM [dbo].[parseJSON] (FirstTable.record)) AS Name , (SELECT StringValue FROM [dbo].[parseJSON] (FirstTable.record)) AS StringValue
    FROM
    (
    SELECT system_id AS id,record
        FROM dbo.set_records
    ) AS FirstTable


SELECT id, max(case when Name ='recorduuid' then convert(Varchar(50),StringValue) else '' end) FROM @tempTable
 
Last edited:
Top