none
Alternative to WHILE for trying different variables! too slow! help please!

    问题

  • declare @variable1 int = 0	
    declare @variable2 int = 0 
    declare @variable3 int = 0	
    declare @variable4 int = 0
    declare @variable5 int = 0 
    
    WHILE 	@variable5 < 10	BEGIN	set @variable5 = 1 + @variable5
    WHILE 	@variable4 < 10	BEGIN	set @variable4 = 1 + @variable4
    WHILE 	@variable3 < 10	BEGIN	set @variable3 = 1 + @variable3
    WHILE 	@variable2 < 10	BEGIN	set @variable2 = 1 + @variable2
    WHILE 	@variable1 < 10	BEGIN	set @variable1 = 1 + @variable1
    
    insert into #Results
    select X   from Y
    where 1=1
    and variable1 = @variable1
    and variable2 = @variable2
    and variable3 = @variable3
    and variable4 = @variable4
    and variable5 = @variable5
    
    END set @variable1 = 0
    END set @variable2 = 0
    END set @variable3 = 0
    END set @variable4 = 0
    END set @variable5 = 0
    
    select * from #Results

    I made a greatly simplified version of my SQL query above.  I have 5 different variables and need to try every single one to find which is best.

    I have been using WHILE LOOP to insert the results into the table. 

    The problem is - I worked out that there are 57,000 combinations on my real life example.... and I have to do it to 120 different items....so it would take 13.2 days to run best case. 

    Is there a better way to do this?  Or do I have to use nested WHILE loops?

    Thanks!!!

    PS:  When SQL is performing this calculation CPU usage, disk and memory usage is slow.  It is running on a 8 core CPU, 8GB ram and all on a sata3 ssd...so the bottleneck does not seem to be the hardware.


    2012年7月5日 21:04

答案

  • SQL is meant to be a set based language, not iterative.  As such, cursors and loops tend to be pretty slow.  Can you explain the business rules a bit, and maybe someone here can help you do this without the loop?
    2012年7月5日 21:17
  • I agree with dgjohnson, giving us more information would help us help you.  But a set based solution might look something like

    ;With n2 As (Select 1 As Number Union All Select 1),
    n4 As (Select n2a.Number From n2 n2a Cross Join n2 n2b),
    n16 As (Select n4a.Number From n4 n4a Cross Join n4 n4b),
    Numbers As (Select Row_Number() Over(Order By n16.Number) As Number From n16)
    Insert Into #Results
    Select X From Y
    Inner Join Numbers v1 On variable1 = v1.Number
    Inner Join Numbers v2 On variable2 = v2.Number
    Inner Join Numbers v3 On variable3 = v3.Number
    Inner Join Numbers v4 On variable4 = v4.Number
    Inner Join Numbers v5 On variable5 = v5.Number
    Where v1.Number <= 10
      And v2.Number <= 10
      And v3.Number <= 10
      And v4.Number <= 10
      And v5.Number <= 10;

    Tom

    2012年7月5日 21:42
  • >> The problem is - I worked out that there are 57,000 combinations on my real life example. <<

    That is a small table today. You will build it once with CROSS JOINs and keep it. Do you know the difference between permutations and combinations? A combination has no order and a permutation does. 

    WITH Ten(d)

    AS (SELECT d FROM (VALUES ...) AS X(d) )

    SELECT V1.d,  V2.d,  V3.d,  V4.d,  V5.d

       FROM Ten AS V1,  Ten AS V2,  Ten AS V3,  Ten AS V4,  Ten AS V5



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2012年7月6日 5:10

全部回复

  • SQL is meant to be a set based language, not iterative.  As such, cursors and loops tend to be pretty slow.  Can you explain the business rules a bit, and maybe someone here can help you do this without the loop?
    2012年7月5日 21:17
  • I agree with dgjohnson, giving us more information would help us help you.  But a set based solution might look something like

    ;With n2 As (Select 1 As Number Union All Select 1),
    n4 As (Select n2a.Number From n2 n2a Cross Join n2 n2b),
    n16 As (Select n4a.Number From n4 n4a Cross Join n4 n4b),
    Numbers As (Select Row_Number() Over(Order By n16.Number) As Number From n16)
    Insert Into #Results
    Select X From Y
    Inner Join Numbers v1 On variable1 = v1.Number
    Inner Join Numbers v2 On variable2 = v2.Number
    Inner Join Numbers v3 On variable3 = v3.Number
    Inner Join Numbers v4 On variable4 = v4.Number
    Inner Join Numbers v5 On variable5 = v5.Number
    Where v1.Number <= 10
      And v2.Number <= 10
      And v3.Number <= 10
      And v4.Number <= 10
      And v5.Number <= 10;

    Tom

    2012年7月5日 21:42
  • >> The problem is - I worked out that there are 57,000 combinations on my real life example. <<

    That is a small table today. You will build it once with CROSS JOINs and keep it. Do you know the difference between permutations and combinations? A combination has no order and a permutation does. 

    WITH Ten(d)

    AS (SELECT d FROM (VALUES ...) AS X(d) )

    SELECT V1.d,  V2.d,  V3.d,  V4.d,  V5.d

       FROM Ten AS V1,  Ten AS V2,  Ten AS V3,  Ten AS V4,  Ten AS V5



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    2012年7月6日 5:10
  • Thanks all.  I used joins to a number table, instead of loops, as suggested.

    The improvement in speed is crazy!

    2012年7月6日 19:07