CODEWITHSUNDEEP
sqlsql-server
sirchristian
sirchristian

Reputation: 521

Cursors vs. While loop - SQLServer

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.

Upvotes: 6

Views: 4218

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

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)

Upvotes: 3

Kevin Stricker
Kevin Stricker

Reputation: 17388

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.

Upvotes: 3

Related Questions