Composite Keys and Performance
Experts,
I inherited an Access database with a main table of ledger balances that was setup as such:
DataPullNo, Company, Year, Cost Center, Account, ComboKey, Amount
The ComboKey is a text field and...
1. the Primary Key (even though Ive yet to find that field in any joins)
2. is populated with a concatenation of (DataPullNo & Company & Cost Center & Account)
3. is indexed (no duplicates)
-The table has about 2mil records.
-There are indexes on DataPullNo (DuplicatesOk) , and ComboKey(no duplicates)
-There are multiple data pulls in any given year (actuals, forecasts, etc) hence the datapull number field
-looking at the data, I can get a unique record on just (DataPullNo, CostCenter, Account)
A professional developer set this database up years ago. Why would anyone create this lengthy ComboKey field instead of just creating a Composite Primary Key? Ive read that the Primary Key sets up an ordered index in the background to facilitate lookups. Is that why he did it?
Would I get a performance increase if I create a composite key?
Thanks
I inherited an Access database with a main table of ledger balances that was setup as such:
DataPullNo, Company, Year, Cost Center, Account, ComboKey, Amount
The ComboKey is a text field and...
1. the Primary Key (even though Ive yet to find that field in any joins)
2. is populated with a concatenation of (DataPullNo & Company & Cost Center & Account)
3. is indexed (no duplicates)
-The table has about 2mil records.
-There are indexes on DataPullNo (DuplicatesOk) , and ComboKey(no duplicates)
-There are multiple data pulls in any given year (actuals, forecasts, etc) hence the datapull number field
-looking at the data, I can get a unique record on just (DataPullNo, CostCenter, Account)
A professional developer set this database up years ago. Why would anyone create this lengthy ComboKey field instead of just creating a Composite Primary Key? Ive read that the Primary Key sets up an ordered index in the background to facilitate lookups. Is that why he did it?
Would I get a performance increase if I create a composite key?
Thanks
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You can delete it, as Access easily can operate with a compound unique index on the fields DataPullNo, Company, Cost Center, Account
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Gentlemen, why or when would I want a multicolumn index instead of just indexing individual fields? and
Do developers add multicolumn indexes to fields that are already individually indexed in situations where some queries use individual indexed fields and other queries use multiple indexed fields?
Thanks
Do developers add multicolumn indexes to fields that are already individually indexed in situations where some queries use individual indexed fields and other queries use multiple indexed fields?
Thanks
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thank you all
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
For a normal transactional table this is uncommon at least.
The biggest problem with this approach is, that Company & Cost Center & Account does not guarantee unique keys. E.g. AA & BB & 12 and A & ABB1 & 2 are different keys, but have the same composite value.
Most probably. As access is normally filtered by date, having composite key over (Date, other columns) as primary key will speed up data access.
Only when the used date format is lexical sortable.