sqlperformancesql-server-2008sql-tuningquery-tuning

How to tune the following query?


This query gives me the desired result but i can't run this query every time.The 2 loops is costing me.So i need to implement something like view.But the logic has temp tables involved which isn't allowed in views as well.so, is there any other way to store this result or change the query so that it will cost me less.

DECLARE @Temp TABLE (
    [SiteID] VARCHAR(100)
    ,[StructureID] INT
    ,[row] DECIMAL(4, 2)
    ,[col] DECIMAL(4, 2)
    )
DECLARE @siteID VARCHAR(100)
    ,@structureID INT
    ,@struct_row INT
    ,@struct_col INT
    ,@rows_count INT
    ,@cols_count INT
    ,@row INT
    ,@col INT

DECLARE structure_cursor CURSOR
FOR
SELECT StructureID
    ,SiteID
    ,Cols / 8.5 AS Cols
    ,Rows / 11 AS Rows
FROM Structure
WHERE SellerID = 658 --AND StructureID = 55

OPEN structure_cursor

FETCH NEXT
FROM structure_cursor
INTO @structureID
    ,@siteID
    ,@struct_col
    ,@struct_row

SELECT @rows_count = 1
    ,@cols_count = 1
    ,@row = 1
    ,@col = 1

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @row <= @struct_row
    BEGIN
        WHILE @col <= @struct_col
        BEGIN
            --PRINT 'MEssage';
            INSERT INTO @Temp (
                SiteID
                ,StructureID
                ,row
                ,col
                )
            VALUES (
                @siteID
                ,@structureID
                ,@rows_count
                ,@cols_count
                )

            SET @cols_count = @cols_count + 1;
            SET @col = @col + 1;
        END

        SET @cols_count = 1;
        SET @col = 1;
        SET @rows_count = @rows_count + 1;
        SET @row = @row + 1;
    END

    SET @row = 1;
    SET @col = 1;
    SET @rows_count = 1;

    FETCH NEXT
    FROM structure_cursor
    INTO @structureID
        ,@siteID
        ,@struct_col
        ,@struct_row
END

CLOSE structure_cursor;

DEALLOCATE structure_cursor;

SELECT * FROM @Temp

Image 1 Image 2


Solution

  • You can generate the number of rows and columns and then CROSS APPLY with those, like below. I've left out your SellerID condition.

    ;WITH Cols
    AS
    (
        SELECT StructureID, SiteID, CAST(Cols / 8.5 AS INT) AS Col
        FROM Structure
        UNION ALL
        SELECT s.StructureID, s.SiteID, Col - 1
        FROM Structure s
            INNER JOIN Cols c ON s.StructureID = c.StructureID AND s.SiteID = c.SiteID
        WHERE Col > 1
    )
    , Rows
    AS
    (
        SELECT StructureID, SiteID, CAST(Rows / 11 AS INT) AS Row
        FROM Structure
        UNION ALL
        SELECT s.StructureID, s.SiteID, Row - 1
        FROM Structure s
            INNER JOIN Rows r ON s.StructureID = r.StructureID AND s.SiteID = r.SiteID
        WHERE Row > 1
    )
    --INSERT INTO @Temp (SiteID, StructureID, row, col)
    SELECT s.SiteID, s.StructureID, r.Row, c.Col 
    FROM Structure s
        CROSS APPLY Cols c
        CROSS APPLY Rows r
    WHERE s.StructureID = c.StructureID AND s.SiteID = c.SiteID
        AND s.StructureID = r.StructureID AND s.SiteID = r.SiteID