Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-09-2018, 12:38 AM   #1
rede96
Newly Registered User
 
Join Date: Apr 2004
Posts: 129
Thanks: 5
Thanked 1 Time in 1 Post
rede96 is on a distinguished road
Correct way to deal with temp tables & bloat?

I have a back-end / multiple front-end database set up. I need to regularly update the BE with data from an external source. The tables in the external source are linked through SQL server.

Updating directly from the external tables doesnít really work as it is extremely slow and the tables donít have natural primary keys I link to. So,

Iíve found the quickest way is to create temp tables and update from them.
Iíve created an Auto Update DB that runs over night however the problem is that it bloats in file size very quickly due to the temp tables. So, I must repair / compact each morning, or the update DB fails due to file size.

As a work around I set the database repair / compact on close and was going to use a third DB application to open the update DB each night, let the update process run, then close it and compact.

However, Iíve read this isnít the right way to deal with this issue, as I should be able to do all this without the bloat / temp tables.

Can anyone help with a way to do my updates without the bloat and regular compact /repair? Then I can just set it to update each night and forget about it!

__________________
It's simple... only when you know how!
rede96 is offline   Reply With Quote
Old 08-09-2018, 12:49 AM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,327
Thanks: 80
Thanked 1,416 Times in 1,336 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Correct way to deal with temp tables & bloat?

Use a "Side End", a separate local database just for the temporary tables. Copy a fresh one each time it is needed and delete it afterwards.

However I first would look closer at why your processes with the SQL tables are not performing well.
Galaxiom is offline   Reply With Quote
Old 08-09-2018, 01:05 AM   #3
rede96
Newly Registered User
 
Join Date: Apr 2004
Posts: 129
Thanks: 5
Thanked 1 Time in 1 Post
rede96 is on a distinguished road
Re: Correct way to deal with temp tables & bloat?

Quote:
Originally Posted by Galaxiom View Post
Use a "Side End", a separate local database just for the temporary tables. Copy a fresh one each time it is needed and delete it afterwards.
Thanks for the reply. Sorry, I probably wasn't very clear. The update DB I have is in effect a side end. It is the side end that grows in file size, even with deleting the temp tables. The file size can only be returned to normal via compact and repair.

If I want this side end to run automatically, then I have to have a way for it to compact and repair each night OR find a different way to do the update.

Quote:
Originally Posted by Galaxiom View Post
However I first would look closer at why your processes with the SQL tables are not performing well.
Not much I can do here. The update is slow because our network is slow, the SQL tables are really large and I have to create ID's links so I can update my tables where none exist in the SQL tables. So kind of stuck with this.

__________________
It's simple... only when you know how!
rede96 is offline   Reply With Quote
Old 08-09-2018, 01:44 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,464
Thanks: 40
Thanked 3,381 Times in 3,276 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Correct way to deal with temp tables & bloat?

you might want to look at your sql design - if you are using vba functions such as nz, iif, etc then it will be slow because all the data needs to be brought across the network by the ODBC driver so the functions can be applied. Would be better to use a passthrough query and utilise the TSQL equivalents. Or perhaps the sql can be redesigned to avoid vba functions.

Similarly if using aggregate queries, ensure any criteria is applied to the WHERE section of the code rather than the HAVING section (HAVING should only apply to any aggregated fields, WHERE is applied to the grouped fields).

Don't think you understood what Galaxiom is suggesting (or perhaps I'm not understanding what you are doing). If I've understood you correctly you have three 'sets' of tables, the ones in SQL server, the destination tables in your access back end and the temporary tables.

Galaxiom is suggesting creating a temporary database for the temporary tables - your front end can link to them, populate them from SQL server then using update/append queries to update the access backend from the temporary tables. Then delete the temporary database. Only bloat you would then get is if you are deleting data from the destination tables - which in many cases would not be happening.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-09-2018, 02:34 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,793
Thanks: 139
Thanked 1,563 Times in 1,535 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Correct way to deal with temp tables & bloat?

The side end database should be created and populated from your application database.

Once the updates are done you delete it completely. All it would hold are your temporary tables, nothing else.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 08-09-2018, 07:48 AM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,287 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by rede96 View Post
It is the side end that grows in file size, even with deleting the temp tables. The file size can only be returned to normal via compact and repair.
The side end itself, the entire file, should be deleted, not just the tables it contains.
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 08-10-2018, 01:58 AM   #7
rede96
Newly Registered User
 
Join Date: Apr 2004
Posts: 129
Thanks: 5
Thanked 1 Time in 1 Post
rede96 is on a distinguished road
Re: Correct way to deal with temp tables & bloat?

Quote:
Originally Posted by CJ_London View Post
Don't think you understood what Galaxiom is suggesting (or perhaps I'm not understanding what you are doing). If I've understood you correctly you have three 'sets' of tables, the ones in SQL server, the destination tables in your access back end and the temporary tables.

Galaxiom is suggesting creating a temporary database for the temporary tables - your front end can link to them, populate them from SQL server then using update/append queries to update the access backend from the temporary tables. Then delete the temporary database. Only bloat you would then get is if you are deleting data from the destination tables - which in many cases would not be happening.
Quote:
Originally Posted by Minty View Post
The side end database should be created and populated from your application database.

Once the updates are done you delete it completely. All it would hold are your temporary tables, nothing else.
Quote:
Originally Posted by MarkK View Post
The side end itself, the entire file, should be deleted, not just the tables it contains.
Mark

Right, understood. Sorry had my mind somewhere else.

Thanks for the help.


__________________
It's simple... only when you know how!
rede96 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting Temp Tables Mari111 Tables 6 03-15-2018 07:15 PM
Temp tables...who needs them? NauticalGent Queries 9 03-29-2017 08:45 PM
Temp Tables jsic1210 Tables 18 12-17-2014 06:23 AM
how to deal with an exception to my Related Tables sitrav Tables 7 08-28-2013 07:03 AM




All times are GMT -8. The time now is 05:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World