If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Please, your comments on my design
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 |
#2
|
|||
|
|||
Please, your comments on my design
On Wed, 20 Aug 2008 02:44:02 -0700, The Dude f_com2°AT°yahoo°DOT°fr wrote:
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. |
#3
|
|||
|
|||
Please, your comments on my design
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 Seems like there should be a unique index on (establishment_number, transaction_date). One index covering both columns. |
#4
|
|||
|
|||
Please, your comments on my design
On Wed, 20 Aug 2008 04:35:01 -0700, The Dude f_com2°AT°yahoo°DOT°fr wrote:
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 Seems like there should be a unique index on (establishment_number, transaction_date). One index covering both columns. Preventing duplicates and maybe speed. |
#5
|
|||
|
|||
Please, your comments on my design
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 |
#6
|
|||
|
|||
Please, your comments on my design
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 |
#7
|
|||
|
|||
Please, your comments on my design
On Wed, 20 Aug 2008 06:38:26 -0700, The Dude f_com2°AT°yahoo°DOT°fr wrote:
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. |
#8
|
|||
|
|||
Please, your comments on my design
On Wed, 20 Aug 2008 06:38:26 -0700, The Dude f_com2°AT°yahoo°DOT°fr wrote:
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!!!) -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|