Please help. SQL takes 30 mins to calculate cumulative function. Need suggestion to run faster

Monalisa 2014-01-03 11:16:25

Have a table Like below
EVENTID RATE
2858907 0.00018858%
2094305 0.00000023%
2094313 0.00000023%
2094297 0.00000019%

Want to calculate Cumulative rate Formula is =1-EXP(-SUM(B$2:Bx)):
EVENTID RATE Cumulative Rate
2858907 0.00018858% 0.0001885796%
2094305 0.00000023% 0.0001888047%
2094313 0.00000023% 0.0001890342%
2094297 0.00000019% 0.0001892286%
2094969 0.00000015% 0.0001893752%

Using below query:
DECLARE @rateSum float
DECLARE @Count int
DECLARE @int int

SET @rateSum = 0
SET @int = 1
SELECT @Count = COUNT(*) FROM PORTFOLIO_ELT

WHILE @int <= @Count BEGIN SELECT @rateSum = @rateSum + RATE FROM TABLE WHERE id = @int UPDATE table SET [Cumulative Rate]= 1-EXP(-@rateSum) WHERE id = @int SET @int = @int + 1 END

GokhanVarol 2014-01-03 12:37:00
Can you get rid of the cursor and use set base instead?
You can open a recordset in C# (or VB etc, not SQL), do the calculation on a read only forward recordset, dump the output into a temp table using bulk insert (or similar). Create the index that matches the source table and join and update with a single command.
JasonHall 2014-01-03 13:03:14
I think you might have better luck using a windowing function for this.

Maybe something like this?

select
eventid,
rate,
1-EXP(-(SUM(rate) OVER (ORDER BY eventid))) [Cumulative Rate]
from
rates

D. Ames 2014-01-05 20:44:24
Food for Thought:

  1. Your first formula reference is from excel, is Excel at all being used (i.e. to display results or execute the query)
  2. Is the @int value always in the data being queried or are some missing? how many?
  3. Are all values in the formula the same data type(to avoid implicit convertions)
  4. Is the code being execute in a stored procedure or from sql query in ssms?
  5. Are you using SET NOCOUNT ON
  6. What kind of hardware is this running on?
January 3, 2014
No Comments