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.
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.
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.
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.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With