Keeping size down

[cgilmour2]

I have a database for a museum inventory, which will eventually end up with a
very large number of records - in the hundred thousands. Does anyone have any
tips on keeping the size down? There will be images as well, but these will
be linked, rather than shown.

I'm using Access 2003.

Thanks very much.

 

[Danny J. Lesandrini]

Hundreds of thousands of records doesn't have to mean large. The best
advice anyone can give is to pay attention to field size. If you are saving
a state code, make the field 2 characters. Don't accept the 50 char size
for all fields, like first name, etc. Think and plan.

Avoid memo fields if a text (255) will suffice. Keep it relational and
normalize it to maximize efficiencies.

As I said, the record count you describe doesn't have to mean "too big"

 

[Fred]

Not sure what you meant by "linked" images.....if you meant an OLE, field,
don't!! .....that will fatally bloat your databases before you get even 1% of
your records in. Store path and file name in text fields and use code to
display in forms and reports.

 

[John W. Vinson]

Not sure what you meant by "linked" images.....if you meant an OLE, field,
don't!! .....that will fatally bloat your databases before you get even 1% of
your records in. Store path and file name in text fields and use code to
display in forms and reports.

Just FYI, Fred, A2007 is much more efficient with storing images than prior
versions. I'd agree though that it would be best to avoid storing images -
even a .jpg file with compaction is still an awfully big chunk to store in a
table when your database has a 2GByte limit.

 

[Tony Toews [MVP]]

Hundreds of thousands of records doesn't have to mean large. The best
advice anyone can give is to pay attention to field size. If you are saving
a state code, make the field 2 characters. Don't accept the 50 char size
for all fields, like first name, etc. Think and plan.

That's not applicable for standard Jet text fields. Access will only
use as much of the disk space as it needs. You can test this yourself
by creating a table with more than 20 255 character text fields.
When you try to fill the fields with 255 data you will run out of room
at some point along the way. I forget the error message now.

I vaguely require that there is a means of declaring a physical field
size for text fields but it requires using DAO code and I can't find
any notes on how to do it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Fred]

John,

Thanks. I heard that (own 4 seats of 2007 but have never used it) and got
tired of writing the "at least through Access 2003, can't speak for 2007 but
I heard that it was better" qualifier on these.

Thanks again.

 

[David W. Fenton]

Hundreds of thousands of records doesn't have to mean large. The
best advice anyone can give is to pay attention to field size. If
you are saving a state code, make the field 2 characters. Don't
accept the 50 char size for all fields, like first name, etc.
Think and plan.

Avoid memo fields if a text (255) will suffice. Keep it
relational and normalize it to maximize efficiencies.

I think your advice is correct, i.e., choose the smallest possible
field size, but it's not right for the reason you say. Jet uses
variable-length storage, so having a field size that is larger than
the longest-possible value in the field does not actually increase
the space taken up by the data.

But it *is* important if the field is indexed. You want the shortest
possible field value so that the index is as efficient as possible.

 

[Danny J. Lesandrini]

Hmm. Who'd a thunk it?

Guess I never asked why use the smallest. In SQL Server 7, you used to
be warned if the sum of the size of your fields exceeded 8k, which was
one page, so I assumed that SQL Server (at any rate) did keep track of
actual field size in as much as it had to plan for it.

I haven't seen the warning since 1999 because I never create records
larger than 8k anymore, so I'm not sure if that's still true, and even if
it were, it wouldn't apply to Access, except for how it's modified my
own personal behavior.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


"David W. Fenton wrote ...

 

[David W. Fenton]

In SQL Server 7, you used to
be warned if the sum of the size of your fields exceeded 8k, which
was one page, so I assumed that SQL Server (at any rate) did keep
track of actual field size in as much as it had to plan for it.

Did it prohibit you from defining the field lengths such that they
exceeded 8K, or was it that you encountered the problem only if the
actual data in a record exceeded 8K? The latter is the case in Jet,
with the 4KB limit on record length, because that's the size of the
data page, and a record has to fit inside a single data page.

 

[Danny J. Lesandrini]

No, I don't believe it was prohibited. The warning was enough to make
me reconsider sizes. There's just a performance issue when a record
exceeds a SQL Server page, or so I was led to believe.

 

[Kent]

Tips for images -
Always store you images externally and store the path to the images in the
database. Fill an image control at runtime.eg
Me.imgControl.Picture = Me.txtPicturePath

DO NOT use OLE Images. Not only will they bloat your database but you will
also have other problems.

Save external images as BMPs ONLY - Access experiences memory leaks with
JPGs. GIFs etc. This leak causes long reports to fail with mysterious error
messages. eg if you try to print an inventory report of more than 600 pages
with say 5 images per page.