extreme bloating of mdb with vba - how to reduce?

[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 assignments. 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]

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

 

[david epsom dot com dot au]

Avoid failed updates and appends by always checking
first, and only updating/appending where the update/
append will succeed.

Make sure you are using current versions of Jet/ADO/DAO.

Don't manipulate a shared database.

In some systems some updates are implemented by creating
a new record and deleting the old record. I haven't heard
that it happens in Access, but I'm always learning...

(david)

 

[steve lord]

Hi TC,

Thanks for the info and thanks for your help! But I figured it out
<g>.
For a couple of passes 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 pass 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
pass 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

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

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]

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]

(snip)

In some systems some updates are implemented by creating
a new record and deleting the old record. I haven't heard
that it happens in Access, but I'm always learning...

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]

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
pass through one table. I'm not doing any adds or deletes, but LOTS
of edits. At the very end is the code, fwiw.

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

 

[david epsom dot com dot au]

On a related topic, you can replace rs!ProcCode and rs!HCPC with

dim sProcCode as string
dim fldHCPC as dao.field
....

sProcCode = rs!ProcCode
set fldHCPC = rs!HCPC
....
if sProcCode= "" then fldHCPC = ""
if fldHCPC = "" then fldHCPC = ""



This is a speed optimisation: refering to rs!fieldname is a
slow operation, and if you do it more than once you benefit.

Unfortunately I mostly don't use direct table access, because
I need to be able to connect to SQL Server, but I have had
processing tasks like yours: Now I wonder how they would
have gone with direct table access.

(david)