Subject Re: How to handle a large 'IN' clause?
Author mikcaau
--- In, "Ann W. Harrison"
<aharrison@i...> wrote:
> phil_hhn wrote:
> >
> > It seems creating/inserting the data into the temp table is what kills
> > it (and yes, a prepared statement was used).
> >
> > Should this be so??
> Maybe not, but there's a lot of i/o involved in creating and populating
> a temporary table (not to mention using up table id's and artificially
> aging your database). I/O is expensive.
> Regards,
> Ann

I have a number of temporary tables - temporary in the sense that the
table is permanent but the data is volatile.
Generally access that uses this temporary data occurs within a
transaction and the process
first calls a Fill temp table routine
process - report ...
empty temp table
if the transaction fails for some reason the table is emptied
Each user has own transaction and thus sees own data (report)

An example is a process that determines vehicle run costs for user
chosen period. Run costs record includes annualised capital costs and
a per km charge.
These calculated costs are expensive to determine and need to be
derived once and used many but of course change with the period selected.
Using this method a report that returns run cost by department went
from 4+ hours to 4+ seconds