Help converting Cursor to CTE

[Pho3nix]

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.

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]

Try starting with this:

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

 

[Pho3nix]

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]

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]

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 )

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

 

[XennoX]

@Pho3nix - What version of MSSQL server are you using?

 

[CT_Biker]

@Pho3nix - What version of MSSQL server are you using?

One with a lot of unnecessary joins.