Normalisation has INCREASED size of Access file

[AndyCotgreave]

Hi,
My company's database is in need of optimisation.

I have a companies table with 75 fields in it and 13,500 records.

I am trying to improve the normalisation in order to increase
efficiency and improve performance (first qu: I assume a correctly
normalised database will perform quicker than a poorly normalised
database?).

In the companies table is an OwnershipType field, text(50). The user
can only enter one of eight options, selected from a drop down. I
created an OwnershipTypes table, entered the eight options into that,
and replaced OwnershipTypes in the Companies table with
OwnershipTypeID, which links to the new table.

It was my understanding that this would reduce the size of the file -
the text field was 50 bytes, the Long Integer link field is only 4.
Repeated over 13,500 records, this should create a significant size
reduction.

But it actually increased the size of the database file, even after
compacting.

Any thoughts? Have I misunderstood the theory?

I'm running Access 2003 on Windows XP.

Andy

 

[KARL DEWEY]

Did you do a Compact and Repair after the database modification?

 

[Rick Brandt]

Hi,
My company's database is in need of optimisation.

I have a companies table with 75 fields in it and 13,500 records.

I am trying to improve the normalisation in order to increase
efficiency and improve performance (first qu: I assume a correctly
normalised database will perform quicker than a poorly normalised
database?).

Not necessarily. Normalization is more about properly modeling the data and
eliminating redundnacy and opportunities for bad data to be introduced.
Performance might be better after proper normalizing, but that would be a
side affect, not the goal. It is not unusual for an improperly normalized
database to perform better in certain areas.

In the companies table is an OwnershipType field, text(50). The user
can only enter one of eight options, selected from a drop down. I
created an OwnershipTypes table, entered the eight options into that,
and replaced OwnershipTypes in the Companies table with
OwnershipTypeID, which links to the new table.

It was my understanding that this would reduce the size of the file -
the text field was 50 bytes, the Long Integer link field is only 4.
Repeated over 13,500 records, this should create a significant size
reduction.

In theory, but you also added a table, a relationship, and one or more
indexes which will make the file larger. You also can only judge size
difference immediately after a fresh CompAct/Repair has been run.

But it actually increased the size of the database file, even after
compacting.

Any thoughts? Have I misunderstood the theory?

13,500 records is a "tiny" database. the smaller the amount of data the
larger the size of the new table, relationship, and index will be relative
to the size of the data. If you had done the same thing on a database with
a million records in it then I imagine it would have gotten significantly
smaller.

I wouldn't worry about it.

 

[Lemark23]

Rick knows what he is talking about. There are a lot of benifits to
what you are doing, but the overhead of what you have added with the
amount of records you have will probably not reduce the size of your
database.

What you are doing will greatly incrase the stability of your
application over time.

You might want to look into those 75 fields. In beginner applications,
I have often found that sometimes people use fields in a main table for
things like Transaction 1, Transaction 2.... etc. Where the number of
fields is the most possible transaction or other item. Those are things
that you get big returns on your investment right away by moving to a
relational design.

 

[Larry Daugherty]

Is your application split? If not, it should be. Then you could
quantify how much your data size has changed as a result of redesign.

HTH

 

[AndyCotgreave]

13,500 records is a "tiny" database. the smaller the amount of data the
larger the size of the new table, relationship, and index will be relative
to the size of the data. If you had done the same thing on a database with
a million records in it then I imagine it would have gotten significantly
smaller.

I wouldn't worry about it.

Easy for you to say! But when my boss is breathing down my neck asking
why the database is running super slowly, I need some answers.

It's interesting what you say about normalisation and performance. I
need to primarily focus on performance over reliability at this stage.
Our database is running really slowly on the network (it *is*
front-end/back-end split with front end copied to users computer,
connecting to single back end). It's only slow when multiple users
connect. I had thought that normalisation was a good tactic to move
towards improving the speed.

Is this not the case?

Andy

 

[Dave F]

If your database is on a network, and it's running slowly, this would appear
to be an issue for your IT department to resolve, not you.

Do other large files, say Excel files, also operate very slowly? If so,
then you're looking at network issues, not Access design issues.

Dave

 

[Rick Brandt]

Easy for you to say! But when my boss is breathing down my neck asking
why the database is running super slowly, I need some answers.

It's interesting what you say about normalisation and performance. I
need to primarily focus on performance over reliability at this stage.
Our database is running really slowly on the network (it *is*
front-end/back-end split with front end copied to users computer,
connecting to single back end). It's only slow when multiple users
connect. I had thought that normalisation was a good tactic to move
towards improving the speed.

Is this not the case?

No. Normalization is about building a proper database, not about
performance.

Performance is first and foremost about the design of the application
interfacing with the database and the proper use of indexes. The highest
priority should be given to pulling the least amount of data for any given
task. Building proper indexes is one of the most important aspects of
achieving that. When a needed index is not present Access will need to pull
LOTS more data over the LAN to do the same job.

Tony Toews has some good information about Access performance at the link
below...

http://www.granite.ab.ca/access/performancefaq.htm

 

[Tony Toews]

Our database is running really slowly on the network (it *is*
front-end/back-end split with front end copied to users computer,
connecting to single back end). It's only slow when multiple users
connect.

Aha. Multiple users! Like Rick stated see the Access Performance
FAQ page at http://www.granite.ab.ca/access/performancefaq.htm

In particular the LDB locking problem. which a persistent recordset
connection or an always open bound form corrects (multiple users)

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

 

[AndyCotgreave]

Thanks for all the replies.

Dave F wrote:

(To the OP) did the database perform better prior to the revisions?

Yes. It was slowing down over several revisions, and then the most
recent revision seems to have tipped it over the edge in terms of
performance.

THe most recent revision took many controls off the main forms, had a
lot of code efficiency improvements (closing all recordsets, etc), and
also added about four more fields to the main, monster Companies table.

Andy

 

[AndyCotgreave]

Performance is first and foremost about the design of the application
interfacing with the database and the proper use of indexes. The highest
priority should be given to pulling the least amount of data for any given
task. Building proper indexes is one of the most important aspects of
achieving that. When a needed index is not present Access will need to pull
LOTS more data over the LAN to do the same job.

I've come across similar recommendations before. "Building proper
indexes" is a term that has concerned me. A proper index is one that is
on a field that is used as a search criteria for common purposes has
always been my assumption.

If that's correct, isn't it easy to design proper indexes? What kind of
things would represent incorrect indexes (index on every field? not
enough indexes?)

I'll definitely look into where I can bring back the least amount of
data.

Tony Toews has some good information about Access performance at the link
below...

http://www.granite.ab.ca/access/performancefaq.htm

Thanks.

Andy

 

[AndyCotgreave]

(To the OP) did the database perform better prior to the revisions?

Further to this, the biggest reduction in speed is when the user types
a search term in the Search box to look for a company. With multiple
users this now takes up to fifteen seconds to return. Here's the
process:

1. User types in a company name or company number (does not need to be
the full name or number)
2. VBA code checks if it is numeric or alpha
3. If numeric, executes SQL string:

SELECT DISTINCT Companies.Name, Companies.CompanyNumber FROM Companies
WHERE (Companies.CompanyNumber LIKE '*<number>*')
[CompanyNumber is a Text field (legitimately so: CompanyNumbers may
contin letters in the UK!]

4. If alpha, executes this string:

SELECT DISTINCT Companies.Name, Companies.CompanyNumber FROM Companies
WHERE (Name LIKE '*<name>*' OR LegalName LIKE '*<name>*')"

5. When the values returns, the results are displayed in a list box.

This process used to take a few seconds, but since the last revision,
now takes fifteen. Indexes don't help becuase of the use of LIKE, but
still, it's hardly a complicated query? ANd it's fast with a single
user.

Cheers,

Andy

 

[Rick Brandt]

I've come across similar recommendations before. "Building proper
indexes" is a term that has concerned me. A proper index is one that
is on a field that is used as a search criteria for common purposes
has always been my assumption.

If that's correct, isn't it easy to design proper indexes? What kind
of things would represent incorrect indexes (index on every field? not
enough indexes?)

I'll definitely look into where I can bring back the least amount of
data.

For example. Your search that uses a wildcard on both the left and right side
of the value being searched cannot utilize an index. The entire table must be
scanned. If possible you should look for exact matches or use wildcards only on
the right side as those both can use an index.

Next to your search criteria input you could provide an option for "Equals",
"Starts With" or "Contains" and your code could use that to decide where to use
wildcard search characters. Any search using the first two options should be
faster (assuming an index exists on that field).

 

[Tony Toews]

This process used to take a few seconds, but since the last revision,
now takes fifteen. Indexes don't help becuase of the use of LIKE, but
still, it's hardly a complicated query? ANd it's fast with a single
user.

If it's fast with a single user and slow with multiple users then you
need to keep a bound form always open while your users are in the FE.
Or you keep a persistent recordset open at all times.

For more information on performance problems visit my Access
Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

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

 

[AndyCotgreave]

Next to your search criteria input you could provide an option for "Equals",
"Starts With" or "Contains" and your code could use that to decide where to use
wildcard search characters. Any search using the first two options should be
faster (assuming an index exists on that field).

Ah-ha! They're great ideas, and so obvious, once you point them out...

I'll sort that out, also the persistent forms recommending by others,
and we'll see if that makes a difference!

(not for two weeks, though, I'm off on holiday, so my users will have
to suffer a while longer..!)
Andy