Someone mentioned to me that the speed pf a big query will be improved if I include the primary key index field in the query. Just I am wondering if any of the people that program lots know if this is right.
Thank you.
Thank you.
Query speed question
- Thread starter Sess
- Start date
MSAccessRookie
AWF VIP
- Local time
- Today, 04:44
- Joined
- May 2, 2008
- Messages
- 3,428
That depends on the query.
If it is on a single Table, and the Primary Key is not required as part of the dataset, then it should not be needed in an index. As a matter of fact, depending on the number of records involved, using a Primary Key based Index might slow it down if there is an index on a Secondary Key that is used as well. A better approach would be to evaluate the Columns that are being used and create an appropriate index that speeds up the Query.
Thanks I think I get it. So that means I have to look at the tables and make the indexes on the fields in the tables, they can have more then just primary, secondary, tertiary? I do not want to index all the fields but it is possible to have more then just a few fields indexed?
Will the extra indexes impact the performance of the database, not going overboard with indexing but if there is more then a couple to each table?
Thank You
Will the extra indexes impact the performance of the database, not going overboard with indexing but if there is more then a couple to each table?
Thank You
MSAccessRookie
AWF VIP
- Local time
- Today, 04:44
- Joined
- May 2, 2008
- Messages
- 3,428
You can have as many as you need, but too many is a problem as well, since they need to be sorted and resorted for each index that is used.
Thank you. That is making sense to me.
Do I really need the autonumber key index that is offered with the wizard when I create a table or is it just same if I have a unique identifier that is part of the dataset?
I did not notice a difference in performance when I said NO to the offer. Just wondering before the database becomes populated with a lot of data.
Do I really need the autonumber key index that is offered with the wizard when I create a table or is it just same if I have a unique identifier that is part of the dataset?
I did not notice a difference in performance when I said NO to the offer. Just wondering before the database becomes populated with a lot of data.
The key can be any field, or combination of fields (composite key), that uniquely define a record. They can be natural (a field from the real data) or artificial (the autonumber field offered by Access). Do use the artificial key for anything that is seen by the user.
It is a common myth that every table should have a key field.
A key field is only essential if you need to refer to the individual records from other tables.
It is a common myth that every table should have a key field.
A key field is only essential if you need to refer to the individual records from other tables.
MSAccessRookie
AWF VIP
- Local time
- Today, 04:44
- Joined
- May 2, 2008
- Messages
- 3,428
I respectfully disagree. Adding a Primary Key field creates little or no overhead, and while you may not see a need to refer to individual records today, there may be a need the day after tomorrow. If you do not already have one at the time, then you will need to add one then, so why not get it over with in the first place?
Primary Key fields are indexed four byte integers. In a table with millions of records the maintenance of an unnecessary index is a substantial overhead.
Interesting Post:
I have never read up on the algorithm used when access runs a query however I would assume it is based on the 1/2 principle - If this is the case having any column indexed other than the one in the criteria would not speed up the query.
Case "select * from table WHERE ID = 25" Assuming 100 records and ID being indexed or autonumber :
Search point 1
100/2 = 50 which is > 25
Search point 2
50/2 = 25 == HIT
I use the best case scenario here to explain the idea.....
Thus my questions are:
Would an index speed up a select query with no criteria?
How are the indices stored: It is mentioned that two many indexes can work opposite to the intended however when would this occur
If the index for each column for each table is stored as a seperate entity then a select query should see no difference between the table with 25 or 1 index as long as the criteria matches an index
Where I would imagine an impact is on an insert or update as the db engine would have to reindex thus having a large number of indexes surely would impact update, insert and delete. Even then does it impact on the time taken to run the query or rather the processing after the query has been executed?
I have never read up on the algorithm used when access runs a query however I would assume it is based on the 1/2 principle - If this is the case having any column indexed other than the one in the criteria would not speed up the query.
Case "select * from table WHERE ID = 25" Assuming 100 records and ID being indexed or autonumber :
Search point 1
100/2 = 50 which is > 25
Search point 2
50/2 = 25 == HIT
I use the best case scenario here to explain the idea.....
Thus my questions are:
Would an index speed up a select query with no criteria?
How are the indices stored: It is mentioned that two many indexes can work opposite to the intended however when would this occur
If the index for each column for each table is stored as a seperate entity then a select query should see no difference between the table with 25 or 1 index as long as the criteria matches an index
Where I would imagine an impact is on an insert or update as the db engine would have to reindex thus having a large number of indexes surely would impact update, insert and delete. Even then does it impact on the time taken to run the query or rather the processing after the query has been executed?
I don't believe so.
Definitely makes more work on an update and this has to be balanced against the benefits especially where records are added individually. However in the case of appending, say, daily batches it is clearly in favour of an index.
The extent of the problem varies. An indexed field with duplicates allowed can be incredibly efficient. For example a table may have millions of records but perhaps only thousands of different dates in a date field. Retreiving records on an indexed date field in this case is very fast.
However adding a key (indexed no duplicates) results in an enormous index that is as slow to look up as the table itself both during select and append queries.
The index on a key field is not about speed but about avoiding duplictes. Using an unnecessary key just bogs down the processing for no gain.
This is really where access blurs the lines somewhat. The difference between an index and a constraint. However in the case of a Key field I would argue that this would also effect speed as an update and delete query would have to find the record first. However this is just about the only example worth mentioning....
Indexed no Duplicates slowing down a update query lends itself to the arguement for serial or autonumber Keys as the lookup is then no longer required. The question again is architecture of the engine, does it do the lookup anyway even though it has itself produced the number. The answer to this is Yes so therefore it voids any logical saving.
You also state that the use of an unnessesary key would bog down the processing, would the engine not be doing that in a completely seperate thread therefore (as long as you dont cause 50% cpu usage) the data access time would/should not be effected?
I assume an index is ordered so the engine stops looking when it reaches the last index record matching the enquiry. With the constraint I presume it looks where it would expect to find the value as one does consulting a dictionary.
The speed would be increased only if the criteria field is indexed. Any extra key field would need to be added to its index and hence slow down. I suppose the real question is how long it takes to append a value to an index.
Access wouldn't use any of the fields to identify the record in the index. I believe the address of the record is held in the index as the page and record number.
Perhaps so but the index might not be rebuilt until the append is complete.
Would love to hear from an expert.
The field I want to use as a key index is a 13 character number. This is a unique number related to batch numbers being run in manufacturing based on a compilation of work order number, workorder line number and a sequence number.
If it uses a 1/2 principal to index will this cause a speed issue or is it same as if I used the autonumber key index that Access will supply?
If it uses a 1/2 principal to index will this cause a speed issue or is it same as if I used the autonumber key index that Access will supply?
Basically we discussing things on a very theoretical level here - as the index can logically sort it will speed things up alot. Dont get fooled by all out bantering the index is a good thing as long as its used in moderation and logically
boblarson
Smeghead
- Local time
- Today, 01:44
- Joined
- Jan 12, 2001
- Messages
- 32,059
I'm not an indexing Expert by any means, so I use posts like this:
http://www.access-programmers.co.uk/forums/showpost.php?p=886247&postcount=5
http://www.access-programmers.co.uk/forums/showpost.php?p=761425&postcount=2
etc.
http://www.access-programmers.co.uk/forums/showpost.php?p=886247&postcount=5
http://www.access-programmers.co.uk/forums/showpost.php?p=761425&postcount=2
etc.
Rabbie
Super Moderator
- Local time
- Today, 09:44
- Joined
- Jul 10, 2007
- Messages
- 5,906
How is the field you want a idex defined. Is it a long integer or is it a text field? This will have a big effect on its effciency as an index
- Local time
- Today, 09:44
- Joined
- Sep 12, 2006
- Messages
- 16,118
technically, i assume indexes are maintained in a sort of balanced b-tree type basis, with say 100 items per node this gives a large item capacity in a relatively flat structure. retrieving items via pointer chains is presumably less efficient than searching the items at each node.
tree balancing algorithms are interesting to examine, and a look at them would will certainly give some insight into the innards of access
----------
with regard to the original point, access will examine the available indexes when deciding how to retrieve items in a query, and hopefully will come up with the fastest way of achieving this. it is not worth indexing every field, but is worthwhile for those fields or combinations of fields that you often use in searches/queries
tree balancing algorithms are interesting to examine, and a look at them would will certainly give some insight into the innards of access
----------
with regard to the original point, access will examine the available indexes when deciding how to retrieve items in a query, and hopefully will come up with the fastest way of achieving this. it is not worth indexing every field, but is worthwhile for those fields or combinations of fields that you often use in searches/queries
Thank you for those links, I passed the bottom part of the second one to the network guy. Hope I am not getting over my head in this. lol
What I called my unique number actually must be a text because I am informed that the workorder 'number' as I called it can actually be alpha-numeric. I was calling it a number in the 'generic' term but obviously I need to be more accurate in my choice of words.
I suppose a numeric would be faster then alpha-numeric?
I suppose a numeric would be faster then alpha-numeric?
