Hi,

I have a query which run thousand of times within the loop with randomised data. Basically, the loop randomises the data/column on each iteration and then performs some calculations. The below query is what I came up with but it takes too slow
when compared with non-randomised way. I have commented out the line with non-randomisation, which joins to the same row_numbers. The table only contains about few thousand rows. Is there a way to re-structure the randomisation to speed up
?

WHILE @counter <= 10
BEGIN
--Some SELECT Queries eliminated for clarity
--Randomise and Delete temp tables
DELETE @tmpReCalcBloomberg
DELETE @tmpAnniversaryTemp
UPDATE @tmpBloomberg
SET randomNo = FlOOR(RAND(CHECKSUM(NEWID())) * (@upperNO-@lowerNO)+@lowerNO)
--Randomise Returns AND Calculate Prev products
INSERT INTO @tmpReCalcBloomberg
SELECT b2.bl_date, b2.bl_rowNo, b1.bl_rowNo, b1.bl_und1, b1.bl_und2, b1.bl_und3, b1.bl_und4, b1.bl_und5,
ROUND(EXP(SUM(LOG(b1.bl_und1)) OVER(ORDER BY b2.bl_rowNo)), 17),
ROUND(EXP(SUM(LOG(b1.bl_und2)) OVER(ORDER BY b2.bl_rowNo)), 17),
ROUND(EXP(SUM(LOG(b1.bl_und3)) OVER(ORDER BY b2.bl_rowNo)), 17),
ROUND(EXP(SUM(LOG(b1.bl_und4)) OVER(ORDER BY b2.bl_rowNo)), 17),
ROUND(EXP(SUM(LOG(b1.bl_und5)) OVER(ORDER BY b2.bl_rowNo)), 17)
--FROM @tmpBloomberg AS b1 JOIN @tmpBloomberg AS b2 ON b1.bl_rowNo = b2.bl_rowNo
FROM @tmpBloomberg AS b1 JOIN @tmpBloomberg AS b2 ON b1.bl_rowNo = b2.randomNo
SET @counter = @counter + 1
END