100 records x10records query

[J. Shrimps Jr.]

If I have two tables with ten columns
(tbla and tbl2) each with an indexed
field (ID), and I run a query matching each
ID as below:
SQL= "SELECT tbl1.*, tbl2.*
FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID;"

How big is the temporary table created before the
query returns the results? Say for example,
tbl1 has 100 records and tbl2 has 10. Wondering
what the math is to determine how big the temp
table gets - in Access 2000.

 

[John Vinson]

If I have two tables with ten columns
(tbla and tbl2) each with an indexed
field (ID), and I run a query matching each
ID as below:
SQL= "SELECT tbl1.*, tbl2.*
FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID;"

How big is the temporary table created before the
query returns the results? Say for example,
tbl1 has 100 records and tbl2 has 10. Wondering
what the math is to determine how big the temp
table gets - in Access 2000.

The JET query optimizer will use the Indexes before it builds the temp
table - if indeed it needs to build a temp table at all, as opposed to
just creating it in memory. It will NOT create a Cartesian join and
then filter it down. If ID is in fact indexed, then you'll get only
the number of records which match.