extreme bloating of mdb with vba - how to reduce?
Related Topics
slord_nosp ...@circlesofcare.org (Steve Lord)
Greetings Group, I have a smallish mdb, about 40 mb.
I have a table that I do some (I wouldn't say "a lot", but it's not trivial either) manipulations on. The table's about 150,000 records with about 25 columns.
With VBA, I have several Subs. Each one opens the table with the openrecordset method and does its processing. The processing is mostly nested if/thens and ***ignments. Each sub is called sequentially, making sure to close the recordset before the next sub is called.
The whole process takes about 4 minutes on a 1.2 ghz P4-M with a slow hard drive (which chatters just about the whole time).
Once I'm done, the mdb is over 500 mb (!!!) and takes about 15-20 mins to compact.
On the options, advanced tab I set the Default Open to 'Exclusive', kept the Default Record Locking on 'No Locks', and turned off the 'Open databases using record-level locking.
No luck - still bloats to half a gig!
Any ideas what/how I can keep this mdb from bloating so badly??
Thanks very much for any help!
-Steve Lord
"TC" n...@email.here
It depends on how you are changing the table. Repeated additions, edits & deletions will eventually cause database bloat: this is inevitable. It is not really related to the locking strategy, so that won't help you. The db size won't go back down until you compact the database. (You >are< compacting it, yes?) In my experience, repeated edits, deletions & general shuffling-arounds are often not required in a properly designed relational database. For example, people might archive records my moving them off into an archive table; but you could achieve the same result, with less bloat, by setting an "archive" flag in the relevant records.
Perhaps give some (simple) examples of how you are changing the data.
HTH, TC ...
sl ...@circlesofcare.org (steve lord)
Hi TC, Thanks for the info and thanks for your help! But I figured it out <g>.
For a couple of p***es through the table, I needed it sorted and filtered so I opened the recordset with a query, for example, Set dbs = CurrentDb StrCriteria = "SELECT * From Services where (services.SourceFile='Residential' and Services.Dept_Provider = 13 and Services.InsType = '10' and (Services.ProcCode = 'W1044' or Services.ProcCode = 'W1046' or Services.ProcCode = 'H2014' or Services.ProcCode = 'H2017')) order by ServDate, MIS;" Set rs = dbs.OpenRecordset(StrCriteria) However, the monster p*** through the table required no such filters or sorting. So instead of opening with a query, I simply opened the table: Set dbs = CurrentDb ' StrCriteria = "SELECT * From Services;" ' Set rs = dbs.OpenRecordset(StrCriteria) Set rs = dbs.OpenRecordset("Services", dbOpenTable) VOILA: No more bloat!
Now, can you enlighten as to why there's so much more activity with a recordset opened via query vs a straightforward opening of the table?
Again, it balloons from a 40 mb mdb to over 500 mb just after this one p*** through one table. I'm not doing any adds or deletes, but LOTS of edits. At the very end is the code, fwiw.
Thanks again for all your help!
-Steve Lord CODE: Public Sub OutpatientServices() Dim dbs As Database Dim rs As Recordset Dim StrCriteria As String ' Open a recordset of Services to process the records Set dbs = CurrentDb ' StrCriteria = "SELECT * From Services;" ' Set rs = dbs.OpenRecordset(StrCriteria) Set rs = dbs.OpenRecordset("Services", dbOpenTable) ' Loop through the recordset and set HCPC values for the Outpatient services rs.MoveFirst ' start with the first record Do Until rs.EOF rs.Edit '---- MEDICAL OUTPATIENT AND OUTPATIENT SERVICES ---- regardless of SourceFile If rs!Prov_Type = 8 Or rs!Prov_Type = 9 Then ' ARNP/MD Staff 'Psych Evals If rs!ProcCode = "90801" Or rs!ProcCode = "W1030" Or _ (rs!ProcCode = "H2010" And (rs!Modifier = "HP" Or rs!Modifier = "HO")) Then rs!HCPC = "H2010" If rs!Prov_Type = 9 Then ' MD rs!Mod1 = "HP" ElseIf rs!Prov_Type = 8 Then ' ARNP rs!Mod1 = "HO" End If rs!ElapsedTime = 60 'Med Checks - include No Mod, with both 2 units and 1 unit with a StartTime ElseIf rs!ProcCode = "W1050" Or rs!ProcCode = "90862" Or _ (rs!ProcCode = "H2010" And rs!Units = 2 And (IsNull(rs!Modifier) = True)) Or _ (rs!ProcCode = "H2010" And rs!Units = 1 And (IsNull(rs!Modifier) = True) And (IsNull(rs!starttime) = False)) Then rs!HCPC = "H2010" rs!Mod1 = Null rs!ElapsedTime = 15 ' Miscellaneous or errant codes for medical staff - Recoded as Med Checks ElseIf rs!ProcCode = "90802" Or rs!ProcCode = "90804" Or _ rs!ProcCode = "90805" Or rs!ProcCode = "90806" Or _ rs!ProcCode = "90807" Or rs!ProcCode = "90845" Or _ rs!ProcCode = "90853" Or rs!ProcCode = "90857" Or _ rs!ProcCode = "90889" Or rs!ProcCode = "96100" Or _ rs!ProcCode = "99221" Or rs!ProcCode = "99242" Or _ rs!ProcCode = "99371" Or _ rs!ProcCode = "W1027" Or rs!ProcCode = "W1032" Or _ rs!ProcCode = "W1033" Or rs!ProcCode = "W1035" Or _ rs!ProcCode = "W1037" Or rs!ProcCode = "W1038" Or _ rs!ProcCode = "W1044" Or rs!ProcCode = "W1049" Or _ rs!ProcCode = "W1059" Or rs!ProcCode = "W1070" Or _ rs!ProcCode = "W1073" Or rs!ProcCode = "W1074" Or _ rs!ProcCode = "W1075" Or _ rs!ProcCode = "H0002" Or rs!ProcCode = "H0004" Or _ rs!ProcCode = "H0031" Or rs!ProcCode = "H0046" Or _ rs!ProcCode = "H2010" And rs!Modifier = "HM" Or _ rs!ProcCode = "H2010" And rs!Modifier = "HE" Then rs!HCPC = "H2010" rs!Mod1 = Null rs!ElapsedTime = 15 End If rs!CostCenter = "12" ' Set Cost Center to 12 for Medical Staff ElseIf rs!Prov_Type = 7 Then ' NURSING Staff ' Office and Outpatient Visit If rs!ProcCode = "W1038" Or rs!ProcCode = "H0002" Then rs!HCPC = "H0002" rs!Mod1 = Null rs!CostCenter = "12" ' Erroneous Nursing Service coded as Clinic Visit ElseIf rs!ProcCode = "W1030" Or _ rs!ProcCode = "W1035" Or _ rs!ProcCode = "W1050" Or _ rs!ProcCode = "90862" Then rs!HCPC = "H0046" rs!Mod1 = "HE" rs!CostCenter = "12" ' Outpatient - Open Clinic -- Treat as COST CENTER 14 ElseIf (rs!ProcCode = "H0004" And rs!Modifier <> "HQ") Or _ rs!ProcCode = "W1074" Then rs!HCPC = "H0004" rs!Mod1 = Null rs!CostCenter = "14" End If ElseIf rs!Prov_Type = 14 Or rs!Prov_Type < 7 Then ' Clinical Staff rs!test = "Provider " & rs!Prov_Type ' Individual Therapy If rs!ProcCode = "90806" Or rs!ProcCode = "90808" Or _ rs!ProcCode = "W1074" Or _ (rs!ProcCode = "H0004" And (rs!Modifier <> "HQ" Or IsNull(rs!Modifier = True))) Then rs!HCPC = "H0004" rs!Mod1 = Null rs!CostCenter = "14" ' Psychological Testing ElseIf rs!ProcCode = "96100" Or _ (rs!ProcCode = "H0031" And (IsNull(rs!Modifier) = True)) Then rs!HCPC = "H0031" rs!Mod1 = Null rs!CostCenter = "14" ' BioPsychosocial Exam ElseIf rs!ProcCode = "W1027" Or _ (rs!ProcCode = "H0031" And rs!Modifier = "HN") Then rs!HCPC = "H0031" rs!Mod1 = "HN" rs!CostCenter = "14" ' Office and Outpatient Visit - probably errors ElseIf rs!ProcCode = "W1038" Or rs!ProcCode = "H0002" Then rs!HCPC = "H0002" rs!Mod1 = Null rs!CostCenter = "14" ' Erroneous coding billed as therapy ElseIf rs!ProcCode = "80019" Or rs!ProcCode = "90801" Or _ rs!ProcCode = "90804" Or rs!ProcCode = "90805" Or rs!ProcCode = "90807" Or _ rs!ProcCode = "90812" Or rs!ProcCode = "90819" Or _ rs!ProcCode = "90862" Or rs!ProcCode = "99371" Or _ rs!ProcCode = "W1030" Or rs!ProcCode = "W1033" Or _ rs!ProcCode = "W1034" Or rs!ProcCode = "W1050" Or _ rs!ProcCode = "W1064" Or rs!ProcCode = "W1070" Or _ rs!ProcCode = "W1071" Then rs!HCPC = "H0004" rs!Mod1 = Null rs!ElapsedTime = 45 rs!CostCenter = "14" End If End If ' ------ End of MEDICAL OUTPATIENT AND OUTPATIENT SERVICES rs.Update rs.MoveNext Loop rs.Close MsgBox ("Completed Processing of Outpatient Services") End Sub
"test" n...@email.here
Hi Steve Not sure why it would bloat more when opened with a query vs. a straightforward opening of the table. I'd have to check the whole process, in context. That might be a bit much work, since you have solved the problem now.
Hope it doesn't happen again! Also, the other responded gave good info.
Cheers, TC ...
<>...
"test" n...@email.here
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message ...
(snip) AFAIK, Jet will certainly do that if the record is sandwiched between other (existing) records in a data page, and the update causes the record to grow.
Jet will delete the old record, and allocate a new (bigger) data slot (from that, or some other page) for the updated record.
Cheers, TC
"Albert D. Kallal" kal...@msn.com
This does hint that processing in table (disk) order is better then processing in a sort order. You get to process the data buckets in order.
Interesting!
It is also very possible that when using a query simply much more temp data space must be used to work on that query. I mean, where does the reocrdset data get saved during processing? You load up a reocrdset..it has to be saved somewhere?
Also, note that a common cause of bloating is not closing your recordsets correctly. and setting them to nothing (you need to do both!!).
I see you have rs.Close But are missing set rs = nothing So, always use: rs.Close set rs = nothing You also want to chekc out the folwing reasons for bloating: http://www.granite.ab.ca/access/bloatfe.htm
--
Albert D. Kallal (MVP) Edmonton, Alberta Canada pleasenonoSpamKal...@msn.com http://www.attcanada.net/~kallal.msn
| To Top |