How to tune the following query?

Question

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

i am actually converting the table 1 as in image above to table 2.So that i can use the second table further in my query to join and get the output — Subek Shakya, Apr 15 '15 at 12:05

score 2 · Answer 1 · answered Apr 15 '15 at 11:46

Do this with a set-based operation. I think you just want insert . . . select:

INSERT INTO @Temp (SiteID, StructureID, row, col)
    SELECT StructureID, SiteID, Cols / 8.5 AS Cols, Rows / 11 AS Rows
    FROM Structure
    WHERE SellerID = 658;

You should avoid cursors, unless you really need them for some reason (such as calling a stored procedure or using dynamic SQL on each row).

EDIT:

Reading the logic, it looks like you want to insert rows for based on the limits in each row. You still don't want to use a cursor. For that, you need a number generator and master..spt_values is a convenient one, if it has enough rows. So:

with n as (
      select row_number() over (order by (select null)) as n
      from master..spt_values
     ) 
INSERT INTO @Temp (SiteID, StructureID, row, col)
    SELECT StructureID, SiteID, ncol.n / 8.5 AS Cols, nrow.n / 11 AS Rows
    FROM Structure s JOIN
         n ncol
         ON ncol.n <= s.struct_col CROSS JOIN
         n nrow
         ON nrow <= s.struct_row
    WHERE SellerID = 658;

score 1 · Accepted Answer · answered Apr 15 '15 at 12:18

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

Thanks!! this helped me to create view as well..can you elaborate how can you do this Col - 1 in CTE above .. — Subek Shakya, Apr 16 '15 at 06:55
That's a recursive CTE. You set in the anchor the highest number for col/row (Rows/11 and Cols/8.5), then in the recursive member you keep on subtracting until you get to 1. — George T, Apr 16 '15 at 07:33

score 0 · Answer 3 · answered Apr 15 '15 at 12:41

We can do this by using CROSS APPLY and CTE.

CREATE TABLE Structure(SiteID varchar(20), StructureID int, 
Cols decimal(18,2), [Rows] decimal(18,2))

INSERT INTO Structure (SiteID, StructureID, Cols, [Rows])

VALUES

('MN353970', 51,17,22),
('MN272252', 52,17,11)


;WITH RowCTE([Rows]) AS
(
    SELECT 1 
    UNION ALL
    SELECT 2
),
ColCTE(Cols) AS
(
    SELECT 1
    UNION ALL
    SELECT 2
)

SELECT SiteID, StructureID, R.Rows, C.Cols
FROM Structure s
CROSS APPLY
    (
        SELECT Cols FROM ColCTE
    ) C
CROSS APPLY
    (
        SELECT [Rows] FROM RowCTE
    ) R

Sql Fiddle Demo

Hi selva..i looked at your code..Logic seems ok but looking at siteID MN272252..it should just create 2 rows not 4 rows because its ,Cols / 8.5 AS Cols and Rows / 11 AS Rows.Which means 2x1 should be the output.The output should be dynamic. — Subek Shakya, Apr 16 '15 at 05:10

How to tune the following query?

3 Answers3