By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,098 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,098 IT Pros & Developers. It's quick & easy.

Efficient temporary tables

P: n/a
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?
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <ra***@SpamFree.com> wrote in message
news:2%******************@newssvr17.news.prodigy.c om...
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?

Nov 13 '05 #2

P: n/a
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
re******@pcdatasheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:2%******************@newssvr17.news.prodigy.c om...
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?

Nov 13 '05 #3

P: n/a
Per Randy Harris:
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?


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
Nov 13 '05 #4

P: n/a
"Randy Harris" <ra***@SpamFree.com> wrote in
news:2%******************@newssvr17.news.prodigy.c om:
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?


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
Nov 13 '05 #5

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...
"Randy Harris" <ra***@SpamFree.com> wrote in
news:2%******************@newssvr17.news.prodigy.c om:
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?


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


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

This discussion thread is closed

Replies have been disabled for this discussion.