Please help. SQL takes 30 mins to calculate cumulative function. Need suggestion to run faster
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
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.
Maybe something like this?
1-EXP(-(SUM(rate) OVER (ORDER BY eventid))) [Cumulative Rate]
- Your first formula reference is from excel, is Excel at all being used (i.e. to display results or execute the query)
- Is the @int value always in the data being queried or are some missing? how many?
- Are all values in the formula the same data type(to avoid implicit convertions)
- Is the code being execute in a stored procedure or from sql query in ssms?
- Are you using SET NOCOUNT ON
- What kind of hardware is this running on?