none
Self Join with Randomisation too slow

    Question

  • 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


    Wednesday, July 02, 2014 2:04 PM

Answers

  • Thanks Patrick, sorry for not responding earlier.  I have fixed it by introducing primary key and using primary key for JOIN rather than row_number and it works fine now.

    thanks.

    Thursday, July 03, 2014 4:22 PM

All replies

  • Can you provide some data for us to test with, as well as a description of the tables and indices?
    Wednesday, July 02, 2014 2:41 PM
  • Thanks Patrick, sorry for not responding earlier.  I have fixed it by introducing primary key and using primary key for JOIN rather than row_number and it works fine now.

    thanks.

    Thursday, July 03, 2014 4:22 PM