hi to all
I have SQL code that works, but slowly. Because I have to run this code very many times, I'm hoping to get it to work more efficiently (ie: faster).
Here's a mini-version of the question that captures my problem:
Table TEST has the following records. [tt]
K KVal
1 10
2 25
3 25
4 25
5 37[/tt]
I want to write SQL to find all DISTINCT PERMUTATIONS of the 5 KVal values.
Here's how I do this now...[tt]
SELECT DISTINCT Q.K0, Q.K1, Q.K2, Q.K3, Q.K4
FROM (
SELECT T0.KVal AS K0, T1.KVal AS K1, T2.KVal AS K2, T3.KVal AS K3, T4.KVal AS K4
FROM TEST as T0, TEST AS T1, TEST AS T2, TEST AS T3, TEST AS T4
WHERE (
((T0.K<>T1.K) AND (T0.K<>T2.K) AND (T0.K<>T3.K) AND (T0.K<>T4.K)) AND
((T1.K<>T2.K) AND (T1.K<>T3.K) AND (T1.K<>T4.K)) AND
((T2.K<>T3.K) AND (T2.K<>T4.K)) AND
((T3.K<>T4.K))
)
) AS Q
ORDER BY Q.K0, Q.K1, Q.K2, Q.K3, Q.K4; [/tt]
The inner SELECT gives 5! = 120 records, but there are 6 of each (because the three values of 25 come in 3! = 6 permutations, which are otherwise indistinguishable). The outer SELECT eliminates these duplicates to give the desired 20 records.
So, what's the problem? My actual table TEST has 10 rows, not 5. This means that the inner SELECT must compute 10! = 3628800 records. If, say, I had a 'triple' and two 'doubles' in the values of KVal, each record would be shown 3!*2!*2! = 24 times. After these duplicates are removed by the outer SELECT, there would be 3628800/24 = 151200 records remaining.
My machine runs this query in just over 4 minutes, but I need to run it for thousands of different sets of 10 KVal values, so I'm looking to improve the SQL's efficiency.
I'd really appreciate some assistance with this!
Thanks, Vicky C.
I have SQL code that works, but slowly. Because I have to run this code very many times, I'm hoping to get it to work more efficiently (ie: faster).
Here's a mini-version of the question that captures my problem:
Table TEST has the following records. [tt]
K KVal
1 10
2 25
3 25
4 25
5 37[/tt]
I want to write SQL to find all DISTINCT PERMUTATIONS of the 5 KVal values.
Here's how I do this now...[tt]
SELECT DISTINCT Q.K0, Q.K1, Q.K2, Q.K3, Q.K4
FROM (
SELECT T0.KVal AS K0, T1.KVal AS K1, T2.KVal AS K2, T3.KVal AS K3, T4.KVal AS K4
FROM TEST as T0, TEST AS T1, TEST AS T2, TEST AS T3, TEST AS T4
WHERE (
((T0.K<>T1.K) AND (T0.K<>T2.K) AND (T0.K<>T3.K) AND (T0.K<>T4.K)) AND
((T1.K<>T2.K) AND (T1.K<>T3.K) AND (T1.K<>T4.K)) AND
((T2.K<>T3.K) AND (T2.K<>T4.K)) AND
((T3.K<>T4.K))
)
) AS Q
ORDER BY Q.K0, Q.K1, Q.K2, Q.K3, Q.K4; [/tt]
The inner SELECT gives 5! = 120 records, but there are 6 of each (because the three values of 25 come in 3! = 6 permutations, which are otherwise indistinguishable). The outer SELECT eliminates these duplicates to give the desired 20 records.
So, what's the problem? My actual table TEST has 10 rows, not 5. This means that the inner SELECT must compute 10! = 3628800 records. If, say, I had a 'triple' and two 'doubles' in the values of KVal, each record would be shown 3!*2!*2! = 24 times. After these duplicates are removed by the outer SELECT, there would be 3628800/24 = 151200 records remaining.
My machine runs this query in just over 4 minutes, but I need to run it for thousands of different sets of 10 KVal values, so I'm looking to improve the SQL's efficiency.
I'd really appreciate some assistance with this!
Thanks, Vicky C.