473,699 Members | 2,302 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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?
Nov 13 '05 #1
5 19795
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%******** **********@news svr17.news.prod igy.com...
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
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******@pcdata sheet.com
www.pcdatasheet.com
"Randy Harris" <ra***@SpamFree .com> wrote in message
news:2%******** **********@news svr17.news.prod igy.com...
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
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.CopyObjec t from
model tables in the app.
--
PeteCresswell
Nov 13 '05 #4
"Randy Harris" <ra***@SpamFree .com> wrote in
news:2%******** **********@news svr17.news.prod igy.com:
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
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.86...
"Randy Harris" <ra***@SpamFree .com> wrote in
news:2%******** **********@news svr17.news.prod igy.com:
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2082
by: Soefara | last post by:
Dear Sirs, I have been developing an application on Windows with MySQL 3.23, making use of temporary tables. Now when I try to port the application to a Unix box running also MySQL 3.23, I keep running into "access denied" errors with the queries which employ temporary tables. This only happens with CREATE TEMPORARY TABLE type queries, all other queries work fine.
11
16293
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the equivalent to the SQL 'with' using TSQL? If there is not one, what is the TSQL solution to creating a temporary table that is associated with an SQL statement? Examples would be appreciated. Thank you!!
1
2603
by: Sampath Reddy | last post by:
Hi Everybody, We are using UDB v8.1 I will explain about my Stored procedures which we are executing in UDB AIX box. We have 3 millions(apporox) of data in 22 tables. By applying the business logic through Stored procedures on 22 tables and writing into 3 new tables. We have used all temporary tables except starting 22 tables. The Stored procedures we have used nearly 6 temporary tables to handle the business logic. Finally we are loading...
2
6975
by: Keith Watson | last post by:
Hi, we are currently implementing an application running on DB2 V7 on Z/OS using largely COBOL stored procedures, managed using WLM. Some of these stored procedures declared global temporary tables, which are declared with ON COMMIT DELETE ROWS to perform work on and then return these temporary tables to the client (which is a message driven EJB connecting via DB2 connect). The client reads the data in the result sets, creates some XML...
3
3269
by: Mike Ridley | last post by:
I have 2 databases called (for example) "progs.mdb" and "files.mdb". Both these databases reside on computer "myserver". The progs database has links to the tables in the files database. "myclient1" runs a shortcut "msaccess.exe \\myserver\progs.mdb /x startmacro". "myclient2" runs his shortcut "msaccess.exe \\myserver\progs.mdb /x startmacro". If I use a temporary table I take it that it will be created on "myserver" in the...
4
2032
by: Maria | last post by:
One of my duties is to record student grades each report period(6). The tables in the database basically look like: TblStudent StudentID Name etc TblStudentGrade StudentGradeID GradePeriod
1
26901
by: Stefan van Roosmalen | last post by:
Hi there, Is there a way to list the TEMPORATY tables? I have tried SHOW TABLES, but this command only list the regular tables. Thank you very much for your answer. Regards, Stefan.
5
2583
by: Alan Little | last post by:
I have affiliates submitting batches of anywhere from 10 to several hundred orders. Each order in the batch must include an order ID, originated by the affiliate, which must be unique across all orders in all batches ever submitted by that affiliate. I'm trying to figure out the most efficient way to check the uniqueness of the order ID. Order data is being submitted to Zen Cart, and also stored in custom tables. I have created a unique...
5
8829
by: Rahul B | last post by:
Hi, I have very little knowledge about creating Procedures/functions in DB2. When i tried to create the test function like CREATE FUNCTION GET_TEST (P_TEST_ID INTEGER, P_SEL_OR_SORT INTEGER,
0
8689
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8618
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9178
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9035
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8916
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8885
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3058
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2010
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.