 | |
Severe backend bloat while appending data
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Severe backend bloat while appending data -
11-07-2010
, 02:10 PM
#2
| |||
| |||
|
|
Hi all, For those who are interested..: Last week I posted a question: 'Slow multi-user application, 1 user is fine...' Problem (slowness) occurred most noticeable while appending data in a procedure. My conclusion is that I need to drop the Secondary key on three fields. This will speed up the process, and it will bring down database bloat. It will also bring in some danger... I hate do have to go this route...!! I did some testing this weekend: (I was mostly interested in multi-user speed but found out about db bloat  Trying to append data (with existing key) ==> Scenario: Backend opened *twice* on my very own workstation. I created an append query qryVulWeekTest.(appending some 1100 records of existing data from a temptable) Code: Function TestAppend() Dim I As Integer, lngTimer As Long DoCmd.SetWarnings False lngTimer = Timer For I = 1 To 10 CurrentDb.Execute "qryVulWeekTest" Next I DoCmd.SetWarnings True End Function Unchanged backend with an autonumber PK and a secondary key on three fields: Result: First run results in a backend of 639 Mb and it takes about 57 seconds on my machine Second run results in a backend of 1,2 Gb and takes another 57 seconds. Third run results in a backend of 1,8 Gb !! (almost hits the limit...) and takes another 57 seconds. Fourth run fails with a message' invalid arg'... (I do NOT trust error messages...) So I changed the backend: Creating a PRIMARY key of the same three fields that were secondary key.... (I really would like to keep this key...) The initial PK (autonumber) is now indexed (no duplicates) ==> This changed results and speed... First run results in a backend of 255 Mb (and it only takes 6 seconds on my machine) Second run results in a backend of 639 Mb. Third run results in a backend of 0,99 Gb. Fourth run results in a backend of 1,2 Gb. Fifth run: 1,4 Gb, and run six leads to a backend of 1,8 Gb So this is almost exactly half the bloat as is the case with the first unchanged backend time. So there seems to be a BIG difference between primary keys and secondary key with respect to this scenario... BUT this bloat is ridiculous in my opinion!! This bloat alone is degrading performance on a network !! FYI: Solo-user means NO bloat! So my conclusion is that I need to drop the PK or Secondary key on these three fields. I need to take care that no records will be appended that already exist... I always relied on Access (RI) in the previous years... So to be sure I tested again the same scenario with both Access 2.0 and Access '97 I could run the *same* procedure (even on a 4 field PK or seckey) a hundred times with NO increase of the backend size... Any ideas? Regards, Arno R |
#3
| ||||
| ||||
|
|
DoCmd.SetWarnings False lngTimer = Timer For I = 1 To 10 CurrentDb.Execute "qryVulWeekTest" Next I DoCmd.SetWarnings True |
should be removed.. Also you should use ", dbfailonerrors" on the
..execute line.
Quote:
|
Fourth run fails with a message' invalid arg'... (I do NOT trust error messages...) |
Quote:
|
FYI: Solo-user means NO bloat! |
Quote:
|
I need to take care that no records will be appended that already exist... |
causing a lot of your bloated.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#4
| |||
| |||
|
|
On Sun, 7 Nov 2010 21:10:13 +0100, "Arno R" arracomn_o_s_p_a_m (AT) planet (DOT) nl> wrote: DoCmd.SetWarnings False lngTimer = Timer For I = 1 To 10 CurrentDb.Execute "qryVulWeekTest" Next I DoCmd.SetWarnings True SetWarnings shouldn't make a different with currentdb.execute and should be removed.. Also you should use ", dbfailonerrors" on the .execute line. Fourth run fails with a message' invalid arg'... (I do NOT trust error messages...) Invalid arguments is the standard message when you exceed the 2 gb. FYI: Solo-user means NO bloat! I also expected this. Or rather greatly reduced bloat with solo user. I need to take care that no records will be appended that already exist... As far as I'm concerned that's good practice anyhow. As that is causing a lot of your bloated. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ |
#5
| |||
| |||
|
|
In general, indexes increase append time and file size. The do greatly decrease retrieval time. Try dropping all indexes on the table being imported into, then recreate the indexes after the import. You can use Drop Index and Create Index DDL language in your code with the Execute method that you are using. You should see both the speed improve and the bloat diminish. -- Arvin Meyer, MCP, MVP |
#6
| |||
| |||
|
|
I Should have used Docmd.OpenQuery instead of CurrentDb.execute here |
errors.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#7
| |||
| |||
|
|
I Should have used Docmd.OpenQuery instead of CurrentDb.execute *here* |
I did not need the errrors reported *here*
Thanks anyway, your opinion is much appreciated!
Arno R
"Tony Toews" <ttoews (AT) telusplanet (DOT) net> schreef in bericht news
nljd697ldrq9eomokt3pdo10566k2mh5u (AT) 4ax (DOT) com...Quote:
|
On Tue, 9 Nov 2010 10:20:54 +0100, "Arno R" arracomn_o_s_p_a_m (AT) planet (DOT) nl> wrote: I Should have used Docmd.OpenQuery instead of CurrentDb.execute here No, you should be using currentdb.execute as it will report back any errors. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ |
#8
| |||
| |||
|
|
In my testing-code I was adding duplicate records en relying at Jet (secondary key) to prevent dupes. I did not need the errrors reported *here* |
that it didn't attempt to add the dupe records in the first place.
But we've had this discussion in another thread.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#9
| |||||||||
| |||||||||
|
|
On Sunday, November 07, 2010 3:10 PM Arno R wrote: Hi all, For those who are interested..: Last week I posted a question: 'Slow multi-user application, 1 user is fine...' Problem (slowness) occurred most noticeable while appending data in a procedure. My conclusion is that I need to drop the Secondary key on three fields. This will speed up the process, and it will bring down database bloat. It will also bring in some danger... I hate do have to go this route...!! I did some testing this weekend: (I was mostly interested in multi-user speed but found out about db bloat Trying to append data (with existing key) ==> Scenario: Backend opened *twice* on my very own workstation. I created an append query qryVulWeekTest.(appending some 1100 records of existing data from a temptable) Code: Function TestAppend() Dim I As Integer, lngTimer As Long DoCmd.SetWarnings False lngTimer = Timer For I = 1 To 10 CurrentDb.Execute "qryVulWeekTest" Next I DoCmd.SetWarnings True End Function Unchanged backend with an autonumber PK and a secondary key on three fields: Result: First run results in a backend of 639 Mb and it takes about 57 seconds on my machine Second run results in a backend of 1,2 Gb and takes another 57 seconds. Third run results in a backend of 1,8 Gb !! (almost hits the limit...) and takes another 57 seconds. Fourth run fails with a message' invalid arg'... (I do NOT trust error messages...) So I changed the backend: Creating a PRIMARY key of the same three fields that were secondary key.... (I really would like to keep this key...) The initial PK (autonumber) is now indexed (no duplicates) ==> This changed results and speed... First run results in a backend of 255 Mb (and it only takes 6 seconds on my machine) Second run results in a backend of 639 Mb. Third run results in a backend of 0,99 Gb. Fourth run results in a backend of 1,2 Gb. Fifth run: 1,4 Gb, and run six leads to a backend of 1,8 Gb So this is almost exactly half the bloat as is the case with the first unchanged backend time. So there iseems to be a BIG difference between primary keys and secondary key with respect to this scenario... BUT this bloat is ridiculous in my opinion!! This bloat alone is degrading performance on a network !! FYI: Solo-user means NO bloat! So my conclusion is that I need to drop the PK or Secondary key on these three fields. I need to take care that no records will be appended that already exist... I always relied on Access (RI) in the previous years... So to be sure I tested again the same scenario with both Access 2.0 and Access '97 I could run the *same* procedure (even on a 4 field PK or seckey) a hundred times with NO increase of the backend size... Any ideas? Regards, Arno R |
Quote:
|
On Monday, November 08, 2010 8:02 AM Arvin Meyer wrote: In general, indexes increase append time and file size. The do greatly decrease retrieval time. Try dropping all indexes on the table being imported into, then recreate the indexes after the import. You can use Drop Index and Create Index DDL language in your code with the Execute method that you are using. You should see both the speed improve and the bloat diminish. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access Co-author: "Access Solutions", published by Wiley |
Quote:
|
On Monday, November 08, 2010 3:56 PM Tony Toews wrote: SetWarnings should not make a different with currentdb.execute and should be removed.. Also you should use ", dbfailonerrors" on the .execute line. Invalid arguments is the standard message when you exceed the 2 gb. I also expected this. Or rather greatly reduced bloat with solo user. As far as I am concerned that is good practice anyhow. As that is causing a lot of your bloated. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ |
Quote:
|
On Tuesday, November 09, 2010 4:20 AM Arno R wrote: Hi Tony, You are right.. I mixed up. I Should have used Docmd.OpenQuery instead of CurrentDb.execute here When I make SURE no records to be appended exist yet, I can drop the secondary index. In that case speed is ok and no bloat. Arno R |
Quote:
|
On Tuesday, November 09, 2010 4:21 AM Arno R wrote: Thanks Arvin, Dropping and recreating indexes fot this -very-often-used-procedure seems like a no-go to me. I would still have to make sure that no records exist yet... I might as well delete the secondary index and let it be... But you are right that speed increases and bloat diminishes Arno R |
Quote:
|
On Tuesday, November 09, 2010 6:19 PM Tony Toews wrote: No, you should be using currentdb.execute as it will report back any errors. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ |
Quote:
|
On Wednesday, November 10, 2010 4:22 AM Arno R wrote: Hi Tony, Please note the word *here* In my testing-code I was adding duplicate records en relying at Jet (secondary key) to prevent dupes. I did not need the errrors reported *here* Thanks anyway, your opinion is much appreciated! Arno R |
Quote:
|
On Wednesday, November 10, 2010 4:11 PM Tony Toews wrote: Well, I can see your point. However I would have designed my query such that it did not attempt to add the dupe records in the first place. But we have had this discussion in another thread. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ |
Quote:
|
Submitted via EggHeadCafe C# In Depth Second Edition - An Interview with Jon Skeet http://www.eggheadcafe.com/tutorials...jon-skeet.aspx |
#10
| |||
| |||
|
|
How do I drop and re-establish indexes in 2007. I'm not sure of the sytax?? |
Open the Indexes dialog (toobar/ribbon.)
Delete the indexes you don't need.
In code, you can use DAO code like this:
http://allenbrowne.com/func-DAO.html#DeleteIndexDAO
or you could execute a DROP query (DDL), or
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.