Database bloating by 10Mb each time

  • Thread starter Thread starter GeorgeMar
  • Start date Start date
G

GeorgeMar

I have a procedure that populates a table from another
table. The populated table has 4500 records and the other
table has 8000 records.

Using DAO recorset, I SET the recordset and explicitly
close the recordset each time and SET db = nothing before
exiting.

However, each time the procedure is run, it adds 10 Mb to
the database.

Is there anything else I have to do to release the
recorsets?
 
However, each time the procedure is run, it adds 10 Mb to
the database.

Is there anything else I have to do to release the
recorsets?
Click to expand...

It's not about recordsets. Databases get bigger when you add stuff but
usually don't get smaller when you delete it; until you do a compact and
repair. That is what you need to do.

B Wishes


Tim F
 
Thanks Tim

The literature on database bloating talks about explicitly
closing the recordsets to prevent them remaining open
after the procedure.

In general whereever I have used recordsets, the database
increases by a fraction of a Mb. This time it is
increasing by 10Mb which is excessive.

regards
george
 
There are number of things that can cause bloating.

Of course..any routines that copy and then delete reocrds will of couse not
recover used space until a cmpact (this does NOT seem to be your case).

I also seend that procesing a table in table order as opposed to some sort
order by a query can also have a huge differnce. (so, process data in page
saved order if you can).

Also, some of the updates to JET can/do fix bloating also (you do have all
the updates to jet installed in addition to the office updates..right?).

There is some great hints and pointers and things to check at:

http://www.granite.ab.ca/access/bloatfe.htm
 
GeorgeMar said:
I have a procedure that populates a table from another
table. The populated table has 4500 records and the other
table has 8000 records.

Using DAO recorset, I SET the recordset and explicitly
close the recordset each time and SET db = nothing before
exiting.

However, each time the procedure is run, it adds 10 Mb to
the database.

Is there anything else I have to do to release the
recorsets?
Click to expand...

Is this procedure one that could be done with SQL alone? You might find
that to bloat less. Would you care to post the code of the procedure?
Maybe we can see what's going on to cause the excessive bloat.
 
This is the culprit. Hope you can see the problem.

Set db = CurrentDb
Set rstBullet = db.OpenRecordset("SELECT * FROM
tblReportsBullet WHERE GradeType='" & strType & "'")

Select Case strType

Case "outcome"
With rstBullet
Do Until .EOF

Select Case !gradevalue
Case "0"

Case "1"
.Edit
rstBullet!Grade1 = "l"
.Update
Case "2"
.Edit
rstBullet!Grade5 = "l"
.Update
Case "3"
.Edit
rstBullet!Grade4 = "l"
.Update
Case "4"
.Edit
rstBullet!Grade3 = "l"
.Update
Case "5"
.Edit
rstBullet!Grade2 = "l"
.Update
End Select

.MoveNext
Loop
End With

Case "VET"
With rstBullet
Do Until .EOF
.Edit
Select Case !gradevalue
Case "0"

Case "1"
rstBullet!Grade1 = "l"
Case "2"
rstBullet!Grade2 = "l"
Case "3"
rstBullet!Grade3 = "l"
Case "4"
rstBullet!Grade4 = "l"
Case "5"
rstBullet!Grade5 = "l"
End Select
.Update
.MoveNext
Loop
End With
End Select
rstBullet.Close



Set rstBullet = db.OpenRecordset("SELECT * FROM
tblReportsBullet WHERE GradeType='Profile'")
'Set rstBullet = db.OpenRecordset("tblReportsBullet")
With rstBullet
Do Until .EOF
.Edit
Select Case !gradevalue
Case "0"

Case "1"
rstBullet!Grade1 = "l"
Case "2"
rstBullet!Grade2 = "l"
Case "3"
rstBullet!Grade3 = "l"
Case "4"
rstBullet!Grade4 = "l"
Case "5"
rstBullet!Grade5 = "l"
End Select
.Update
.MoveNext
Loop
End With

rstBullet.Close
Set db = Nothing
 
GeorgeMar said:
This is the culprit. Hope you can see the problem.

Set db = CurrentDb
Set rstBullet = db.OpenRecordset("SELECT * FROM
tblReportsBullet WHERE GradeType='" & strType & "'")

Select Case strType

Case "outcome"
With rstBullet
Do Until .EOF

Select Case !gradevalue
Case "0"

Case "1"
.Edit
rstBullet!Grade1 = "l"
.Update
Case "2"
.Edit
rstBullet!Grade5 = "l"
.Update
Case "3"
.Edit
rstBullet!Grade4 = "l"
.Update
Case "4"
.Edit
rstBullet!Grade3 = "l"
.Update
Case "5"
.Edit
rstBullet!Grade2 = "l"
.Update
End Select

.MoveNext
Loop
End With

Case "VET"
With rstBullet
Do Until .EOF
.Edit
Select Case !gradevalue
Case "0"

Case "1"
rstBullet!Grade1 = "l"
Case "2"
rstBullet!Grade2 = "l"
Case "3"
rstBullet!Grade3 = "l"
Case "4"
rstBullet!Grade4 = "l"
Case "5"
rstBullet!Grade5 = "l"
End Select
.Update
.MoveNext
Loop
End With
End Select
rstBullet.Close



Set rstBullet = db.OpenRecordset("SELECT * FROM
tblReportsBullet WHERE GradeType='Profile'")
'Set rstBullet = db.OpenRecordset("tblReportsBullet")
With rstBullet
Do Until .EOF
.Edit
Select Case !gradevalue
Case "0"

Case "1"
rstBullet!Grade1 = "l"
Case "2"
rstBullet!Grade2 = "l"
Case "3"
rstBullet!Grade3 = "l"
Case "4"
rstBullet!Grade4 = "l"
Case "5"
rstBullet!Grade5 = "l"
End Select
.Update
.MoveNext
Loop
End With

rstBullet.Close
Set db = Nothing
Click to expand...

You might try something like this (be warned -- this will be "air SQL",
so I don't guarantee its correctness):

'----- start of revised code -----
Set db = CurrentDb

Select Case strType

Case "outcome"

db.Execute _
"UPDATE tblReportsBullet SET Grade1 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '1'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade5 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '2'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade4 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '3'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade3 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '4'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade2 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '5'", _
dbFailOnError

Case "VET"

db.Execute _
"UPDATE tblReportsBullet SET Grade1 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '1'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade2 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '2'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade3 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '3'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade4 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '4'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade5 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '5'", _
dbFailOnError

End Select

db.Execute _
"UPDATE tblReportsBullet SET Grade1 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '1'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade2 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '2'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade3 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '3'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade4 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '4'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade5 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '5'", _
dbFailOnError

'----- end of revised code -----

That's 10 update queries that could potentially be executed to perform
your process, if I've correctly identified the updates you need, but
each one should update all the qualifying records in one go. The
queries will be *much* more efficient if the fields [GradeType] and
[gradevalue] are indexed. I don't know for sure if this will have an
effect on your bloating, but it's worth a try.

I'd like to verify, by the way that the fields [Grade1] through
[Grade5], and [gradevalue], are all text fields. You're enclosing their
values in quotes, so I assume so, but if they aren't actually text
fields the quotes around the values will force, at best, unnecessary
conversions.
 
Dirk

Thank you for your suggestion. It worked. The database is
no longer bloating.

What intrigues me is that I have similar procedures in
that database and they don't cause this bloating.

Any idea why? Should I minimise the use of recordset from
now on and use SQL where ever possible?

many thanks
george
-----Original Message-----
This is the culprit. Hope you can see the problem.

Set db = CurrentDb
Set rstBullet = db.OpenRecordset("SELECT * FROM
tblReportsBullet WHERE GradeType='" & strType & "'")

Select Case strType

Case "outcome"
With rstBullet
Do Until .EOF

Select Case !gradevalue
Case "0"

Case "1"
.Edit
rstBullet!Grade1 = "l"
.Update
Case "2"
.Edit
rstBullet!Grade5 = "l"
.Update
Case "3"
.Edit
rstBullet!Grade4 = "l"
.Update
Case "4"
.Edit
rstBullet!Grade3 = "l"
.Update
Case "5"
.Edit
rstBullet!Grade2 = "l"
.Update
End Select

.MoveNext
Loop
End With

Case "VET"
With rstBullet
Do Until .EOF
.Edit
Select Case !gradevalue
Case "0"

Case "1"
rstBullet!Grade1 = "l"
Case "2"
rstBullet!Grade2 = "l"
Case "3"
rstBullet!Grade3 = "l"
Case "4"
rstBullet!Grade4 = "l"
Case "5"
rstBullet!Grade5 = "l"
End Select
.Update
.MoveNext
Loop
End With
End Select
rstBullet.Close



Set rstBullet = db.OpenRecordset("SELECT * FROM
tblReportsBullet WHERE GradeType='Profile'")
'Set rstBullet = db.OpenRecordset ("tblReportsBullet")
With rstBullet
Do Until .EOF
.Edit
Select Case !gradevalue
Case "0"

Case "1"
rstBullet!Grade1 = "l"
Case "2"
rstBullet!Grade2 = "l"
Case "3"
rstBullet!Grade3 = "l"
Case "4"
rstBullet!Grade4 = "l"
Case "5"
rstBullet!Grade5 = "l"
End Select
.Update
.MoveNext
Loop
End With

rstBullet.Close
Set db = Nothing
Click to expand...

You might try something like this (be warned -- this will be "air SQL",
so I don't guarantee its correctness):

'----- start of revised code -----
Set db = CurrentDb

Select Case strType

Case "outcome"

db.Execute _
"UPDATE tblReportsBullet SET Grade1 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '1'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade5 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '2'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade4 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '3'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade3 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '4'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade2 = 'l' " & _
"WHERE GradeType='outcome' AND gradevalue = '5'", _
dbFailOnError

Case "VET"

db.Execute _
"UPDATE tblReportsBullet SET Grade1 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '1'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade2 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '2'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade3 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '3'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade4 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '4'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade5 = 'l' " & _
"WHERE GradeType='VET' AND gradevalue = '5'", _
dbFailOnError

End Select

db.Execute _
"UPDATE tblReportsBullet SET Grade1 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '1'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade2 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '2'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade3 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '3'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade4 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '4'", _
dbFailOnError

db.Execute _
"UPDATE tblReportsBullet SET Grade5 = 'l' " & _
"WHERE GradeType='Profile' AND gradevalue = '5'", _
dbFailOnError

'----- end of revised code -----

That's 10 update queries that could potentially be executed to perform
your process, if I've correctly identified the updates you need, but
each one should update all the qualifying records in one go. The
queries will be *much* more efficient if the fields [GradeType] and
[gradevalue] are indexed. I don't know for sure if this will have an
effect on your bloating, but it's worth a try.

I'd like to verify, by the way that the fields [Grade1] through
[Grade5], and [gradevalue], are all text fields. You're enclosing their
values in quotes, so I assume so, but if they aren't actually text
fields the quotes around the values will force, at best, unnecessary
conversions.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



.
Click to expand...
 
GeorgeMar said:
Dirk

Thank you for your suggestion. It worked. The database is
no longer bloating.
Excellent!

What intrigues me is that I have similar procedures in
that database and they don't cause this bloating.

Any idea why? Should I minimise the use of recordset from
now on and use SQL where ever possible?
Click to expand...

I can't really guess why, except that this time you're doing a *lot* of
edits. But I'm not really knowledgeable about all that Access -- or
more likely DAO and Jet -- do behind the scenes to support this kind of
processing. It is generally accepted that executing action queries will
always -- or almost always -- be more efficient than doing the
equivalent processing with recordsets. That's because, given a
declarative statement of the task to be performed, the Jet database
engine can figure out the most efficient way to accomplish it. So I
would recommend using action queries instead of recordsets when you can.
 
Many, many thanks, Dirk

George
-----Original Message-----


I can't really guess why, except that this time you're doing a *lot* of
edits. But I'm not really knowledgeable about all that Access -- or
more likely DAO and Jet -- do behind the scenes to support this kind of
processing. It is generally accepted that executing action queries will
always -- or almost always -- be more efficient than doing the
equivalent processing with recordsets. That's because, given a
declarative statement of the task to be performed, the Jet database
engine can figure out the most efficient way to accomplish it. So I
would recommend using action queries instead of recordsets when you can.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
I have a bloating problem too, however mine first appeared after moving from Access97 to 2002/2003. It is a rather simple update function, which updates 2 fields in a 1.58 million record table with a sequential number in each of the two fields. This used to work fine in Access97 with minimal growth, but in later versions of Access the database bloats out to around 2 gig before crashing after updating only 403,000 records (x 2 fields). The database actually grows by approx 1.6 gig.
I have tried both DAO and ADO with the same results. Any suggestions here would be great.
Attached is the DAO function which is called from a form.
Thanks
Chris

Function append_seq_num(ByVal strTableName As String)

Dim db As Database
Dim rs As Recordset
Dim lngCounter As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset(strTableName, dbOpenTable)
lngCounter = 1

Do Until rs.EOF
rs.Edit
rs.Fields("Seq_ID") = lngCounter
rs.Fields("Seq_num") = lngCounter
rs.Update
rs.MoveNext
lngCounter = lngCounter + 1
Loop
rs.Close
db.Execute "CREATE UNIQUE INDEX idx_Seq_ID ON " & strTableName & "(Seq_ID) WITH DISALLOW NULL;"
db.Close

' release objects
Set rs = Nothing
Set db = Nothing

End Function
 
First, a table has no inherent sort-order. So it really doesn't make much
sense, to assign an incrementing number to each record in that (unsorted)
recordset! The only benefit of doing that, AFAICS, is getting a unique # for
each record. Is that what you're trying to do? If so, doesn't the table have
some other (better) primary key? Couldn;t you use an autonumber primary key?

If you do need those incrementing numbers, and the order of the records >is<
important, you must >define< the order you want, eg. by basing the recordset
on a SELECT query with a suitable ORDER BY clause.

Ignoring all that, if you want to continue with what you currently have, you
may get better results if you do it from an update query:

UPDATE TheTable
SET Seq_ID = NextNum(),
Seq_Num = NextNum()

NextNum() would be a public function using a static variable to allocate the
next sequential number. You'd need to see how many times that function was
called, per record, in the query shown. It might be once, twice, or maybe
several times, depending on how Jet processed it. It doesn't matter how many
times it is called, as long as that # is >predictable<, so that you can code
for it.

If the function is only called once >for the whole query<, just change it to
reference a fieldname. This will force it to be called for every record:

UPDATE TheTable
SET Seq_ID = NextNum(<some field>),
Seq_Num = NextNum(<some field>)

HTH,
TC
(off for the day)


Chris VanderSchaaf said:
I have a bloating problem too, however mine first appeared after moving
Click to expand...
from Access97 to 2002/2003. It is a rather simple update function, which
updates 2 fields in a 1.58 million record table with a sequential number in
each of the two fields. This used to work fine in Access97 with minimal
growth, but in later versions of Access the database bloats out to around 2
gig before crashing after updating only 403,000 records (x 2 fields). The
database actually grows by approx 1.6 gig.
 
Thanks for your response TC. Maintaining the order is critical. This one module is part of a series of steps that sorts a database for export into a sequenced text file. By the time the process has reached this step, the data has already been sequenced with a SELECT INTO / ORDER BY query. The Seq_Num field is actually a text field (later padded out with leading zeros) for display on a printed article, whilst Seq_ID is used to maintain the sorted order for the final queries that run after this module. Has always worked fine in Access97, I just can't figure out what has changed in Access 200x / Jet 4.0 that is causing this massive bloat.
 
Back
Top