How does Access handle huge dynasets

[Tim Cali]

Hi. In my suvey database, there is a query that creates a cartesian product
used as the source for another query, that latter of which is used for
report output. I need the cartesian product in order to aggregate properly.

Since there are so many respondents for this survey, and also since there
are so many questions, this "master" query is going to be much bigger than I
am used to. I think in a previous survey I had a maximum of 3K records in
the dynaset, and I aggregated from that. This new survey is going to yield
30-40K records, or 10x bigger than my last survey results for the dynaset.

Is a dynaset stored in RAM? I was thinking about it earlier, and this
cartesian product, which also uses an outer join, is incredibly useful.
However I can see the initial size of it being huge, and I am wondering how
Access handles its resources to be able to handle these huge select queries
at first, even when the final result may be an aggregation 1/10 or less the
size of the starting point.

Does this make sense? In other words, what if I had a table with one million
records, and I needed some kind of outer join to "prep" the data from which
to aggregate from. How does Access handle it. Do we need huge RAM
requirements? Does Access write this to a temporary table on disk, and then
erase it? What's going on "behind the scenes"?

Thanks for any insight.

Tim

 

[Jeff Boyce]

Tim

It seems like you may be asking a "what if" question. Have you already
build a dummy, sample table with a large number of rows, and tested? Is
your inquiry about "whether or not", or about "how long will it take", or
???

More info, please...

Jeff Boyce
<Access MVP>

 

[Tim Cali]

My question is "what goes on behind the scenes"? I am going to be creating a
cartesian product select query from tables that are going to be larger than
ever before. This got me to thinking about how Access handles certain
things.

I will have 350 respondents x 200 answers x 3 times the survey is given =
210,000 records possible. I need to create this intermediary table (qryAlll)
and then do my grouping and summarizing from this.

So if I run qryAll just by itself, I see 210,000 records. Where are these
values for this dynaset stored? In RAM? On disk? Is the temporary query
table a duplicate of the source, requiring a lot of hardware resources?

Now my end goal is not to run qryAll but itself, but to summarize it. So I
create another query (qryGroup) which uses qryAll as its source. qryGroup
might end up providing me with only 50 rows.

If I run qryAll by itself, what happens behind the scenes? Do I need an
appropriate amount of RAM to handle the temp table, whereupon, after qryGoup
has finished its grouping, does Access "release" the memory? Or is there
another internal mechanism that doesn't require RAM resources for this, but
perhaps is able to construct the grouping at a lower level, maybe only
requiring RAM for the final set of outputted data? Or, is all this written
to disk temporarily and then erased?

If I run qryGroup (50 records), does Access require the same number of
resources as it would if I ran qryAll (210,000 records)? Since qryGroup
looks to qryAll, I would think that it would require the resources to handle
210,000 records, but I don't know.

The reason I am asking this is because I find this cartesian product very
useful. Now that my base tables are going to be bigger than ever before, I
am wondering if my system can handle this. Or, since this survey will
eventually be used on other machines, will I need to consider other
approaches?

Hope that makes sense.

 

[Phobos]

Why not make your cartesian product query a MAKE TABLE query?

I would imagine it would be much faster than than running a cartesian
product query if you just query a table.

P