Cartesian Queries

Resolved · Medium Priority · Version 2003

Mon 7 Sep 2009, 16:20 Delegate Mike said...

Mike has attended:
Access VBA course

Cartesian Queries

Do you have any tips/tricks on speeding up Cartesian queries between two unrelated tables.

Example.

TblTransactions contains Cardnumber

TblCard contains the "range" (a Low number and High number) and CardType

SELECT tblTransactions.Cardnumber, tblCard.CardType
FROM tblTransactions, tblCard
WHERE tblTransactions.CardNumber Between [Low] And [High];

Thu 10 Sep 2009, 10:07 Trainer Stephen said...

RE: Cartesian Queries

Hi Mike

Thank you for your question

A Cartesian query combines every row in the first table with every row in the second table, and consequently can take a considerable length of time to run. In addition the data is in many cases meaningless.

Can you clarify the purpose of your query as I might be able to suggest an alternative approach

Regards

Stephen

Thu 10 Sep 2009, 10:33 Delegate Mike said...

RE: Cartesian Queries

I have approximately 5 million card numbers in a table that I need to determine the card type. Visa supply a list containing a range of cardnumbers and the card type, eg Low 453978000 High 453978999 CardType "D", this table of ranges is approximately 600000 rows.

So my query is 5 million x 60,000 and as you can imagine takes a hell of a long time. Is there any way of speeding it up using recordsets or any other method?

Thu 17 Sep 2009, 14:20 Trainer Stephen said...

RE: Cartesian Queries

Hi Mike

Sorry for the delay in getting back to you, I have been away from the office for a few days

One possibility would be to first write the contents of the 2 tables into 2 Arrays.

You could then write a procedure that goes through the card numbers array a row at a time, retrieves the card number and then cycles through the card type array until it finds a match. It then retrieves the card type.

The skills covered on the VBA course should be sufficient for you to do this. If however you require specific help we could provide a more formal consultancy service. If this is required, please do not hesitate to contact our enquiries team.

Regards

Stephen

Thu 24 Sep 2009: Automatically marked as resolved.

Training courses