My table is pretty big...any ideas?

[hatchback]

Hey everyone

In my database, Ive got a table made by a cartesian query joining the primary keys of three tables.

The three tables it connects are quite large - each about 2,000 records. The cartesian query gives me therefore a table of about 8 BILLION records. (It takes access about 20 minutes to run the query on my computer. Even when saved as a table, to jump from the top of the table to the last record takes about 6 minutes to process.)

The table alone, being three fields, all long integers, uses about 1.5 gig of memory.

All the records are needed. I will be looking them up, comparing etc. In short, this table is indispensable in my project.
Of course, it's much too big to be practical.

So my questions:
Will putting the table on SQL Server Express, as a back end, speed things up? Will access, as the front end, be able to work with it faster?
Is speed here just a sacrafice I'll have to make?
What do people with such issues normally do?

Thanks in advance...hoping someone can give me clever ideas!

 

[Cronk]

I wonder, if the primary data is in 3 separate tables, why the need to 'copy' all the data to another table.

If there is a need, use temporary tables to hold only data necessary for the task at hand.

For example, I have a sports grounds booking system with nearly 300,000 bookings over the last 10 years. I need to do a cartesian join to check proposed new bookings for clashes with existing bookings. I run the join after selecting bookings for the dates of proposed new bookings, ie a small subset of the 300K records.

If you do go down this path, put the the temporary table in a separate db on each users C drive and to overcome any bloat, recreate the temporary db every so often. (I have it in the start up procedures to do this every month).

 

[hatchback]

Thanks Cronk

Problem is, I need the table to be permanently there...not sure what to do about it. Anyone else got other ideas?

 

[spikepl]

If you want sensible ideas provide the context. Many turn up here asking how to do X better, whereas quite often Y or Z are more suitable.