Efficient temporary tables

An application that I develop uses a lot of temporary tables. I very
frequently empty the tables (using DELETE From), then fill them back up.
Anyone have comments on what effects I might expect to see on performance
and bloating by deleting the tables, then using SELECT INTO to recreate,
instead of the way I have been doing it?

re: Efficient temporary tables

You might take a look at the Temporary Table demo Tony Toews has at
http://www.granite.ab.ca/access/temptables.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Randy Harris" <randy@SpamFree.com> wrote in message
news:2%bId.11841$Vj3.9867@newssvr17.news.prodigy.c om...[color=blue]
> An application that I develop uses a lot of temporary tables. I very
> frequently empty the tables (using DELETE From), then fill them back up.
> Anyone have comments on what effects I might expect to see on performance
> and bloating by deleting the tables, then using SELECT INTO to recreate,
> instead of the way I have been doing it?
>
>[/color]

re: Efficient temporary tables

You will see very noticeable bloating but probably little effect on
performance.

A suggested way to do this to avoid the bloating problem is to create a
"template" external mdb file with all the tables you are now using as temp
tables. Make a copy of this file and put it in an appropriate folder. Link
to the tables in this file from your front end. Where you are filling your
temp tables now, change and fill the tables in the external mdb file. Where
you are deleting records in your temp tables, eliminate all deleting and
just use the Kill statement and delete the external file. Replace the
external file by copying the "template' file and naming the copy with the
name you chose for the external mdb file using the FileCopy command.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com


"Randy Harris" <randy@SpamFree.com> wrote in message
news:2%bId.11841$Vj3.9867@newssvr17.news.prodigy.c om...[color=blue]
> An application that I develop uses a lot of temporary tables. I very
> frequently empty the tables (using DELETE From), then fill them back up.
> Anyone have comments on what effects I might expect to see on performance
> and bloating by deleting the tables, then using SELECT INTO to recreate,
> instead of the way I have been doing it?
>
>[/color]

re: Efficient temporary tables

Per Randy Harris:[color=blue]
>An application that I develop uses a lot of temporary tables. I very
>frequently empty the tables (using DELETE From), then fill them back up.
>Anyone have comments on what effects I might expect to see on performance
>and bloating by deleting the tables, then using SELECT INTO to recreate,
>instead of the way I have been doing it?[/color]

I don't use the app or back end for temp tables.

Instead, I create a new DB in C:\TEMP and populate it via DoCmd.CopyObject from
model tables in the app.
--
PeteCresswell

re: Efficient temporary tables

"Randy Harris" <randy@SpamFree.com> wrote in
news:2%bId.11841$Vj3.9867@newssvr17.news.prodigy.c om:
[color=blue]
> An application that I develop uses a lot of temporary tables. I
> very frequently empty the tables (using DELETE From), then fill
> them back up. Anyone have comments on what effects I might expect
> to see on performance and bloating by deleting the tables, then
> using SELECT INTO to recreate, instead of the way I have been
> doing it?[/color]

If you don't need indexes, it saves a step, but I don't know that
it's going to be much of a performance issue unless you're doing
thousands of these in a loop.

I almost always use an existing table because I don't want to take
the time to SELECT INTO and then add the indexes, which I almost
always need for performance reasons.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Efficient temporary tables

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns95E6C99988551dfentonbwaynetinvali@24.168.1 28.86...[color=blue]
> "Randy Harris" <randy@SpamFree.com> wrote in
> news:2%bId.11841$Vj3.9867@newssvr17.news.prodigy.c om:
>[color=green]
> > An application that I develop uses a lot of temporary tables. I
> > very frequently empty the tables (using DELETE From), then fill
> > them back up. Anyone have comments on what effects I might expect
> > to see on performance and bloating by deleting the tables, then
> > using SELECT INTO to recreate, instead of the way I have been
> > doing it?[/color]
>
> If you don't need indexes, it saves a step, but I don't know that
> it's going to be much of a performance issue unless you're doing
> thousands of these in a loop.
>
> I almost always use an existing table because I don't want to take
> the time to SELECT INTO and then add the indexes, which I almost
> always need for performance reasons.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

Oh wow! I hadn't even thought about the indexes. They would indeed need to
be recreated.