Using Temp Table Design

Hello,

Is there any performance gain by using a temp table to store the
transactions entered that day by employees instead of storing them in the
main transactions table which holds the historical transactions? Both the
main and the temp tables will be same (fields, properties, etc.).

I am interested in this design concept as I am thinking it is easier for the
database to add a new record to the temp table when it has a couple hundred
rows versus a couple thousand rows. The records in the temp table would then
be automatically appended to the main table and then deleted from the temp
table at the end of the day. How to do this will probably be another posting
though.

I haven't met with my client to discuss volumes so I am unsure if I will
need this method, but I wanted to see if this is a plausible solution. The
system would be used within a multi-user environment (12 - 25 but again, I
don't know the current and future state of the headcount) using Access 2003.
Any advice would be appreciated.

Regards,

Paul

Adding records to a table isn't going to require a temp table. Querying the
table for reporting may necessitate the use of a temp table depending on what
you are doing but for just adding records, no.

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________


"Paul" wrote:

Hello,

Is there any performance gain by using a temp table to store the
transactions entered that day by employees instead of storing them in the
main transactions table which holds the historical transactions? Both the
main and the temp tables will be same (fields, properties, etc.).

I am interested in this design concept as I am thinking it is easier for the
database to add a new record to the temp table when it has a couple hundred
rows versus a couple thousand rows. The records in the temp table would then
be automatically appended to the main table and then deleted from the temp
table at the end of the day. How to do this will probably be another posting
though.

I haven't met with my client to discuss volumes so I am unsure if I will
need this method, but I wanted to see if this is a plausible solution. The
system would be used within a multi-user environment (12 - 25 but again, I
don't know the current and future state of the headcount) using Access 2003.
Any advice would be appreciated.

Regards,

Paul

On Thu, 17 Jul 2008 12:37:06 -0700, boblarson
wrote:

Adding records to a table isn't going to require a temp table. Querying the
table for reporting may necessitate the use of a temp table depending on what
you are doing but for just adding records, no.

Hi Bob,

I think the question is a bit different - that is, would it be more
efficient to add the new records to a local temp table, and then
append them all at once into the main table at the end of the day. As
opposed to just adding the new records one at a time into the main
table.

Paul,

Adding a new record is a fairly lightweight operation for Access. Even
if you have a lot of records, with a lot of indexes, new records can
usually be added as quickly as a human can type them in. If you are
already seeing performance issues, then there might be other reasons
to explore.

Your approach would work, and in theory it might be slightly more
efficient to add the records in bulk. But it would take more coding
to ensure a reliable daily append of those records, plus making sure
that no errors would allow them to be appended twice. Given the
limited information you've provided, I don't see a benefit.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com