登录
 
主页技术资源库学习下载支持社区论坛
IT 专业人士的资源 >
> 
> 
Alternative to WHILE for trying different variables! too slow! help please!

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

  • 2012年7月5日 21:04
     
      包含代码
    登录进行投票
    0
     
    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:17
     
     已答复
    登录进行投票
    1
    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:42
     
     已答复 包含代码
    登录进行投票
    1

    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月6日 5:10
     
     已答复
    登录进行投票
    0

    >> 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日 19:07
     
     
    登录进行投票
    0

    Thanks all.  I used joins to a number table, instead of loops, as suggested.

    The improvement in speed is crazy!