Hi Tom,

About the text field. Making it's size smaller won't reduce the database
size UNLESS it also truncated data. If you put one character in a text field
set to 255, Access only stores one character. No waste. Now if you had 255
characters in that field and reduce the size to 5, the database file would
get smaller, but then you've lost 250 characters from that record.

Then why not just set all text fields to 255 characters? (1) The wizards use
the field size to attempt to size controls on forms and reports. It could get
ugly fast if your zipcode fields were set to 255! (2) Speaking of zipcode
fields, by setting the field size to 5 it would prevent someone from typing
in something grossly incorrect or prevent the Zip+4 format. Not as good as an
input mask, but it works.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom Ellison" wrote:

I have the assignment to imporve a database's performance.

The data is in a separate back end database that has just under 3 million
rows. It has no index. All queries are running as table scans, of course.

I want to add some indexes that should fix this nicely. But there's no
room. It's almost 2 gigabytes now.

I've found that several columns are much larger than necessary. Here are
the stats:

Datatype Length Number Change
text 50 1 15
decimal 18 27 7
decimal 18 2
date/time 1
integer 2

The above Number column is the number of such columns in the table. I am
expecting to impact the size of the table by changing a 50 character text
column to 15 characters, and by reducing the precision of the 29 decimal
columns to 7.

I could not do this by just changing the datatypes on a full table.
Something about not enough memory. (I like Jet less and less. I would
never have this kind of mess with MSDE!)

So, I made a copy of the table, cleared out all the records, compacted this,
and made the changes to the 27 decimal columns. The table was just over
100K bytes in size. I then linked to the database containing the copy of
the data and created an append query to put the data in.

When this finished, I had saved less than 100K bytes, a fraction of 1% of
the data size. Does not the size of a Decimal datatype depend on the
precision? If not, then why is there a precision. If you're going to store
10 bytes all the time, why not allow them all to be used to hold data?
Allowing two digits (nibbles) per byte, a decimal of precision 18 should
require 10 bytes to store (add one nibble for a sign and divide by two,
rounding up). I chose 7, although 6 would have been sufficient for all
existing data, since I expect it's going to add one for a sign anyway, so 6
+ 1 = 7 would be 4 bytes, and 7 + 1 = 8 would be 4 bytes anyway, so there
would be no savings there. Just my theory.

Anyway, after completing the append, the table had reduced in size by less
than 100K bytes (out of just under 2 GB). By my calculation, I was
expecting a reduction of just over 50%. Does Jet not store the decimal
datatype in varying sized pieces, according to the precision? Is my formula
of (Precision + 1) / 2 not what you would expect?

I then repeated all the steps, reducing the text column from 50 to 15. This
I expected would save 105MB (35 bytes X 3 million). Bottom line: the file
is now larger than ever!

Does anyone know how a database can be reduced in size to accomplish this?

Tom Ellison