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.
- 已编辑 8o43hoieriohjr 2012年7月5日 21:09
2012年7月5日 21:17SQL 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?
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;
>> 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.
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
- 已标记为答案 8o43hoieriohjr 2012年7月6日 19:06
Thanks all. I used joins to a number table, instead of loops, as suggested.
The improvement in speed is crazy!