0

I have a query that takes one record from #itemtemp, and locates all entries from ReportCSharp that match, inserting those matches into the #link_table. At present, the query runs in 7.5 min, which appears slow for iterating over only 1458 records in the #itemtemp table.

DECLARE 
    @num    int
,   @path   varchar(100)
,   @output varchar(100)
,   @max    int

SET @num = 1

SET @max = (SELECT max(num) FROM #itemtemp)

WHILE @num < @max
BEGIN

    SET @path = (SELECT path from #itemtemp where num = @num)

    INSERT INTO #link_table
    SELECT 
        itemId
    ,   Path
    ,   @path
    FROM ReportCsharp
    WHERE Script like '%"' + @path + '"%' 

    SET @num += 1
END

How can I remove the WHILE loop and replace with more set based operations?

crthompson
  • 15,653
  • 6
  • 58
  • 80
Sauron
  • 6,399
  • 14
  • 71
  • 136

2 Answers2

0
INSERT INTO #link_table
    SELECT rcs.itemId,
           rcs.Path,
           it.Path
        FROM ReportCsharp rcs
            INNER JOIN #itemtemp it
                ON rcs.Script LIKE '%' + it.Path + '%';
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
0

as appears in the code, you could do it directly with insert...select...join:

INSERT INTO #link_table
SELECT 
    P.itemId
,   P.Path
,   I.path
FROM ReportCsharp P
JOIN #itemtemp I ON P.Script like '%' + I.path + '%'
void
  • 7,760
  • 3
  • 25
  • 43