Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cursors vs. While loop - SQLServer

Tags:

sql

sql-server

Say I have a bunch of rows in a DB (SQLServer 2008 in this case) that can be used to create equations.

 -----------------------------------------------------
 OperationID | EquationID | Operation | Amount | Order
 -----------------------------------------------------
     1       |     1      |     +     |   12   |  1 
     2       |     1      |     +     |   12   |  2 
     3       |     2      |     /     |   2    |  3 
     4       |     2      |     +     |   12   |  1 
     5       |     2      |     -     |   2    |  2 
 -----------------------------------------------------

I need come up with a way to evaluate the equations in this table.

Equation 1: 12 + 12 = 24
Equation 2: (12 - 2)/2 = 5

I cannot think of a way to get these results without iterating through the rows. The only ways I know how to do this is with a cursor or through the use of a temp table and a while loop. Are there any better ways to do this? If not generally what will perform better cursors or while loops?

Note: This is somewhat simplified and at this stage in the project we can only conjecture about what the data will look like. The assumption is that each 'equation' will have around 100 to 1000 operations and that there will be a few thousand 'equations' each day that will need to be processed.

like image 530
sirchristian Avatar asked Oct 26 '10 19:10

sirchristian


People also ask

What is difference between cursor and loop?

Cursors in sql server allow you to fetch a set of data, loop through each record, and modify the values as necessary; then, you can easily assign these values to variables and perform processing on these values. While loop also same as cursor to fetch set of data and process each row in sql server.

What is better than cursor in SQL?

Alternative 2: Temporary Tables We can also use temporary tables instead of SQL cursors to iterate the result set one row at a time. Temporary tables have been in use for a long time and provide an excellent way to replace cursors for large data sets.

Is it good to use cursor in SQL?

Cursors could be used in some applications for serialized operations as shown in example above, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.


2 Answers

It's been demonstrated that a recursive CTE performs better than a loop for coming up with running totals. This is just a running total with a variable operator really, so the performance benefit should apply here.

The way to create a recursive CTE which behaves like a loop is like so:

        ;WITH cte AS (
        SELECT equation, number, order FROM table WHERE order = 1
        UNION ALL
        SELECT table.equation, 
            CASE WHEN table.operation = '+' THEN cte.number + table.number
                 WHEN table.operation = '-' THEN cte.number - table.number END AS number, --etc.
table.order FROM table INNER JOIN cte ON table.order = cte.order + 1 AND table.equation = cte.equation
        )
    SELECT equation, number, order 
    FROM cte
    OPTION (MAXRECURSION 1000);

The first SELECT grabs your leftmost number, and the UNION all does the following operations on the number returned by it. The maxrecursion option limits the number of operations in one equation to 1000. You can, of course, set this higher.

This answer is somewhat incomplete, because the final select query would return intermediate results. That's fairly simple to filter though.

like image 54
Kevin Stricker Avatar answered Sep 21 '22 12:09

Kevin Stricker


I've cleaned up/fleshed out mootinator's answer a bit and am presenting that code here. I've marked this answer community wiki because mootinator deserves the credit for the answer. This was just the simplest way to present that code without editing his answer.

declare @equations table (
    OperationID int,
    EquationID int,
    Operation char(1),
    Amount int,
    [Order] int
)

insert into @equations
    (OperationID, EquationID, Operation, Amount, [Order])
    values 
    (1, 1, '+', 12, 1),
    (2, 1, '+', 12, 2),
    (3, 2, '/',  2, 3),
    (4, 2, '+', 12, 1),
    (5, 2, '-',  2, 2)

;with cteCalc as (
    select EquationID, Amount, [Order] 
        from @equations 
        where [Order] = 1
    union all
    select e.equationid, 
           case when e.Operation = '+' then c.Amount + e.Amount
                when e.Operation = '-' then c.Amount - e.Amount
                when e.Operation = '*' then c.Amount * e.Amount
                when e.Operation = '/' then c.Amount / e.Amount
           end AS Amount, 
           e.[Order] 
        from @equations e 
            inner join cteCalc c 
                on e.EquationID= c.EquationID
        where e.[Order] = c.[Order] + 1
),
cteMaxOrder as (
    select EquationID, MAX([Order]) as MaxOrder 
        from cteCalc 
        group by EquationID
)
select c.EquationID, c.Amount
    from cteMaxOrder mo
        inner join cteCalc c
            on mo.EquationID = c.EquationID
                and mo.MaxOrder = c.[Order]
    order by c.EquationID
    option (maxrecursion 1000)
like image 31
Daniel Andujar Avatar answered Sep 19 '22 12:09

Daniel Andujar