Please, your comments on my design

[The Dude]

Hello all,

I would really appreciate any comments on how to improve the following
design, and if possible the speed.

I have a table with three fields : an establishment number (text), a date of
transaction and the amount (currency, euros). Each establishment has one
transaction a month, and the table is about 3 millions rows now.

Indexes are on Establishment number (with duplicates), and transaction date
(with duplicates). Would it improve speed to set one on the amount?

Should I put a primary key (knowing that I refer to other tables with the
establishment number, on a one to many basis)?

Would it improve the speed if I set the text field from 50 to 16?

Should I make separate tables in regards of the transaction year?

Thanks in advance
T_D

 

[Michael Gramelspacher]

Hello all,

I would really appreciate any comments on how to improve the following
design, and if possible the speed.

I have a table with three fields : an establishment number (text), a date of
transaction and the amount (currency, euros). Each establishment has one
transaction a month, and the table is about 3 millions rows now.

Indexes are on Establishment number (with duplicates), and transaction date
(with duplicates). Would it improve speed to set one on the amount?

Should I put a primary key (knowing that I refer to other tables with the
establishment number, on a one to many basis)?

Would it improve the speed if I set the text field from 50 to 16?

Should I make separate tables in regards of the transaction year?

Thanks in advance
T_D

Seems like there should be a unique index on (establishment_number, transaction_date).
One index covering both columns.

 

[The Dude]

Thank you Michael. It is an interesting suggestion.
I guess you can assign that in the View/Indexes window?

What benefit would I gain from setting one multiple index instead of two
single field indexes?

Thanks

 

[Michael Gramelspacher]

Thank you Michael. It is an interesting suggestion.
I guess you can assign that in the View/Indexes window?

What benefit would I gain from setting one multiple index instead of two
single field indexes?

Thanks

Preventing duplicates and maybe speed.

 

[The Dude]

Wow Michael this works amazingly well!

First of all, it's the perfect way to prevent duplicates in the coupled
values... priceless.

Secondly, it does work faster. It is so fast that I will have to reboor my
PC to make sure that the table is not loaded in memory... it now takes a few
seconds for a short sample instead of a couple of minutes before. Will try,
but it's definitely a plus.

Thanks again Michael
T_D

 

[The Dude]

So, Michael, could you please tell me if setting these multiple indexes is
interesting in a table for non financial information?

For instance, you have a name, address, zip code... etc... Is it better to
set one index for each field you usually filter, or set then again a multiple
filter?

Thanks
T_D

 

[Michael Gramelspacher]

So, Michael, could you please tell me if setting these multiple indexes is
interesting in a table for non financial information?

For instance, you have a name, address, zip code... etc... Is it better to
set one index for each field you usually filter, or set then again a multiple
filter?

Thanks
T_D

You always need a unique index covering the columns that represent the natural key of a table. This
is needed to prevent duplicates. It is very common to use an autonumber column to insure
uniqueness, the it will not prevent duplicates.

I do not know of any rule covering multiple column non-unique indexes. I would think the
(last_name,first_name) is a good choice for an index, because we normally are interested in full
names. It would not help searching for just first_name. I do not know about addresses.
(city_name,state_name) could be indexed. (city_name,street_name) might be good, but only if you
table maintains street_name separate from house_num. My suggestion is to try things and see how it
affects the speed of your queries.

Maybe others have better guidance to add.

 

[John W. Vinson]

So, Michael, could you please tell me if setting these multiple indexes is
interesting in a table for non financial information?

For instance, you have a name, address, zip code... etc... Is it better to
set one index for each field you usually filter, or set then again a multiple
filter?

Thanks
T_D

It only makes sense to create a multifield index if there is a logical need to
do so. In your example, the combination of establishment and date is a
"candidate primary key" - it needs to be unique by the business logic, and
both fields will be used for referencing to other tables. I'd be inclined NOT
to add a new primary key field but instead to make these two fields the joint,
composite PK (by ctrl-clicking both in table design view and selecting the Key
icon).

A multikey index will speed searching and sorting *in the order in which the
indexes are defined* - e.g. an index on LastName, FirstName, AddressNo, Street
and Postcode will make it possible to sort a query by those five fields *in
that order*, and will make searching the table by those fields much faster -
but NOT if you skip a field. Searching or sorting just by AddressNo and Street
will not make any use of the index and will not benefit.

In addition, what you win on the swings you lose on the roundabouts: indexing
will make data retrieval faster, but will make updating the table slower
(since Access must update both the table and all the affected indexes).

Getting an optimal set of correctly designed indexes is a difficult art, and
is a part of the reason that a good DBA is worth every penny of his or her
exorbitant salary (don't I wish!!!)