Question about finding permutations by substitution 2

[VickyC]

hello to all

I am working an a project that requires me to work with a very large number of tables, each showing ALL of the permutations of of 10 distinct positive integers. Each table has 10! = 3,628,800 records (because the 1st value can be selected in 10 ways, the 2nd in 9 ways, the 3rd in 8 ways, etc.)

I use SQL to generate the records and INSERT them INTO a table, analyse the results, delete the records, then repeat with the next set of 10 distinct positive integers. To fill the table with its 3,628,800 records takes me just over 6 minutes of computing time. For what its worth, I'm using SQL as below. (Because of space limitations, I'm showing how the SQL would look if there were only 5 positive integers to work with, not 10.)

'Actual code uses 10 values, not 5
INSERT INTO tbl_Perms
SELECT DISTINCT Q.V4, Q.V3, Q.V2, Q.V1, Q.V0
FROM 
    (
     SELECT T4.V AS V4, T3.V AS V3, T2.V AS V2, T1.V AS V1, T0.V AS V0
     FROM  tbl_Vals AS T4, tbl_Vals AS T3, tbl_Vals AS T2, tbl_Vals AS T1, tbl_Vals AS T0
     WHERE 
        (
	    (T4.V<>T3.V And T4.V<>T2.V And T4.V<>T1.V And T4.V<>T0.V) And 
	    (T3.V<>T2.V And T3.V<>T1.V And T3.V<>T0.V) And 
	    (T2.V<>T1.V And T2.V<>T0.V) And 
	    (T1.V<>T0.V)
	)
     ) AS Q
ORDER BY Q.V4, Q.V3, Q.V2, Q.V1, Q.V0;

Here is my question. Since every set of permutations is based on 10 distinct values, I should be able to fill one table, then complete ALL other tables by using SUBSTITUTION. For example ...

if Tbl1 is based on the values 2, 6, 23, 1, 78, 33, 44, 55, 66, 77,
and Tbl2 is based on the values 8, 9, 12, 13, 14, 3, 65 ,54 ,43 ,32...

... then I should be able to build Tbl1's 3,628,800 records, then produce Tbl2 by substituting all values of 2 in Tbl1 with 8, all values of 6 with 9, all values of 23 with 12, etc.

This could be a gigantic time saver, but I don't know how to do bulk replacements. In any given table, each value occurs 10! times, so this goes way beyond the limits of a Window's Search/Replace. Does anyone have any suggestions as to a good way to proceed?

Thank you for any thoughts
Vicky C.


A further thought: It might even be best to compute a master permutation table using, say, the 10 values... -9, -8, -7, -6, -5, -4, -3, -2, -1, 0, and then fill all the actual working tables by substitution. But I'm really stuck on how to do bulk replacements???

 

[MajP]

This could be a gigantic time saver

Do you have any data supporting this?, because I doubt this is true. I think it may be slower. I am thinking a new insert query would be faster than update queries.

 

[VickyC]

hi MajP - "Do you have any data on this?" I'm guessing you mean how long does it take when using UPDATE? I don't know yet, but I'll check and post back. I'll be dissapointed if the UPDATE method takes longer, but you may be correct. (The INSERT INTOs take 6m 22s, regardless of the 10 input integers)
I'm doubting this, but I wonder if it could even be done faster using VBA?
Vicky C.

 

[MajP]

I would think VBA would be much slower.

 

[VickyC]

hi again MajP - your suspicion seems to be right. The INSERT qry takes 6m 22s, the UPDATE takes almost exactly 10 minutes, and I bailed on the VBA method after 20 minutes (not sure how long it would take if I let it run to completion.) Thank you for your help.

Vicky C.

I've heard SQL Server has a Replace funtion in SQL. I wonder if that would help in this situation.

 

[MichaelRed]

From your brief discussion, it is not pratical to speculate on the possible varriations of approach.

MS Access (Jet db engine) is/was "notoriusly" slow w/ recordsets. It was largely regarded as a small group / single user db app, and beefed up over the years. On the otherhand it (Ms. Access) offers multiple ways to do almost everthing!! So trying different approaches might gain both some insight into both how it really works and a mnore reasonable soloution.

A thought (NOT a soloution!!) might be to see how to (economically) find the specific values to be replaces in each record.

Also, consider the issue(s) of hardware. Typically, external access to data is approx 1/10 of memory, so having (and using) the capability to maintain multiple recordsets in memory oould be large benefit. Having 3.6 million records * NRecordsets in memory may not be an issue in machines with 8 Gis of memory in current top end PCs, but would be beyond the pale in early verions. IF the multiple recordsets are coexistant within your app, many of the benchmarks would change.

MichaelRed

 

[VickyC]

thank you MichaelRed for your thoughtful comments. I was a little puzzled by "...to see how to (economically) find the specific values to be replaced in each record...". In my case, all sets of 10 replacement values are in a table in the same db application. In each case, all 10 values are replaced with another set of 10 values. So, lets say Tbl1 shows all 3,628,800 permutations of 2,4,6,8,10,12,14,16,18,20. If Tbl2 shows the permutations of 3,5,7,9,11,13,15,17,19,21, then I simply hoped to replace each 2 in Tbl1 with a 3, replace each 4 in Tbl1 with a 5, etc.....

When you mentioned harware issues, I seem to have enough capacity. But, I'm struggling a bit with the fact that the mdb file cannot exceed 2.0 G. I'm finding that I have to break the problem up into parts, running a 'compact' periodically.

Thank you again for your expert thoughts.
Vicky C.