Coding Challenge

[NauticalGent]

Disclaimer, I had to use AI to generate the code I got work. But I thought it would make a good thread anyway.

I have a 5 digit code that I use a lot in my line of work. It's called a Commercial And Government Entity (CAGE) code and the Master file on the DoD website holds over 3 million records. The CAGE code is alpha-numeric and I wondered how close 3 mil was to maxing it out. Not being a mathematician I asked ChatGPT what the total number of possible code were and the answer was 36^5, or 60,466,176 records.

Obviously the US Gov was safe from having to revamp the process.

For fun I tried to figure out a way to generate a table with VBA that would hold all the possible CAGE codes...again, I am NO computer scientist so I quickly gave up and asked Chatty to generate the code, Although the code worked, there were other issues - 60m records is a MASSIVE table so a lot of tweaking was needed to get this to work.

Before I show "my" work, I thought it might be interesting to see if anyone here would be able and willing to take on the challenge without cheating like I did.

Anyone?

 

[NauticalGent]

Also, the time it took as about 943 seconds or 16 min. If anyone was access to SQL server, I would be interested to see how long it would take...

 

[pbaldy]

My first thought isn't code, it's a query against a table with the 36 values, getting a Cartesian product. A quick example using a table with the 24 hours:

SELECT tblHours.StartHour, tblHours_1.StartHour
FROM tblHours, tblHours AS tblHours_1

This produced the expected 576 records.

 

[MajP]

I discussed this here to include both combinations and permutations and with or without replacement.

Determine all zip code combinations - Microsoft: Access Queries and JET SQL

Hi, I am trying to determine all possible zip code combinations for the first 3 digits. They then must be combined into a 6 character field used in queries based on zip codes. Ex: 111xx and 765xx would be combined into 111765 and so on thru all possible combinations. The zip codes are used...

www.tek-tips.com

 

[Minty]

A table with the 36 characters cross joined to itself 5 times:

select  
A.CharVal, b.CharVal, c.CharVal, d.CharVal , e.CharVal
fROM 
zzTable_Source A
CROSS JOIN
(select CharVal
fROM 
zzTable_Source B
) B
CROSS JOIN
(
select 
CharVal
fROM 
zzTable_Source c) c
CROSS JOIN
(
select 
CharVal
fROM 
zzTable_Source c) D
CROSS JOIN
(
select 
CharVal
fROM 
zzTable_Source c) E

Running it with four values returns 1,679,616 records and took 11 seconds on my Azure SQL server .
With the added fifth column it took 7Min 22 seconds and returned 60,466,176 rows.

Row CharVal CharVal CharVal CharVal CharVal
60466169 H 3 H H H
60466170 H 4 H H H
60466171 H 5 H H H
60466172 H 6 H H H
60466173 H 7 H H H
60466174 H 8 H H H
60466175 H 9 H H H
60466176 H H H H H

 

[NauticalGent]

I just tried the Cartesian join theory and it took only about 3 min. Of course I didn't believe it and ran it again and it took even less time. I would have bet GOOD money against a "bad" query outperforming clean VBA - and would have lost every penny.

THIS is why I will never be as cool as you guys...

 

[NauticalGent]

Just in case anyone wanted to play with both methods, here is the db I played around with. Appreciate the lesson today!

 

[Pat Hartman]

I would have bet GOOD money against a "bad" query outperforming clean VBA - and would have lost every penny.

Always strive to build a query to solve your problem and only use a VBA loop as a last resort.

 

[Mark_]

I just tried the Cartesian join theory and it took only about 3 min. Of course I didn't believe it and ran it again and it took even less time. I would have bet GOOD money against a "bad" query outperforming clean VBA - and would have lost every penny.

THIS is why I will never be as cool as you guys...

In theory, the entire query returned can reside in memory. Table entries by nature are always stored to disk. Your betting money that a disk some place is faster that the computer's memory...