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
|
|||
|
|||
improving performance by indexing query criteria fields
In Access Help under Performance, it says that you can improve query
performance indexing any field used to set criteria for the query. It also says to index fields on both sides of a join. However, elsewhere I have also read that indexed fields can also slow down certain operations like adding or deleting records, because Access has to update the index when records containing those fields are added or deleted. So evidently there is some performance benefit to be derived from indexing criteria fields, but you also have to suffer a disadvantage. If I only have a few such fields, I suppose I could answer this question with some experimentation. However, I have about 10 databases in production with about 900 query criteria and join fields. (I printed out a list of them in the Immediate Window with some VB code). Can anyone give me some guidelines for deciding when to index query criteria and join fields, and when it's best to leave them unindexed, if you want to improve the overall performance of your database? Thanks in advance, Paul |
#2
|
|||
|
|||
Personally, I index any field that I use frequently (whatever I define as
frequent) in searches or sorts or that seems to take a long time to my users. If I've set up relationships in the relationship window I don't index the fields involved in the relationship, because as far as I know Access automatically builds these indexes. If you don't use the relationship window to set up the relations, but just do it in queries then you do need to set up the indexes. There is a small hit when adding or changing records, but usually unless you are adding a lot of records at one time through an Insert query or some loop in VBA code, the extra time is not noticeable to the user. Let's say for argument's sake, that adding a records with 5 indexes takes an extra second - the user won't notice, but if you were doing that through an insert query that added 600 records, that would cause 10 minutes of added time. In real life, you are not talking a second, but more like a few milliseconds. In other words, add indexes when it makes sense. Also, indexing boolean fields rarely helps unless the distribution of yes/no is heavily skewed towards one choice or the other and you usually are trying to get the value that least often occurs. All the above is MY OPINION and observations through a wide variety of databases. Paul James wrote: In Access Help under Performance, it says that you can improve query performance indexing any field used to set criteria for the query. It also says to index fields on both sides of a join. However, elsewhere I have also read that indexed fields can also slow down certain operations like adding or deleting records, because Access has to update the index when records containing those fields are added or deleted. So evidently there is some performance benefit to be derived from indexing criteria fields, but you also have to suffer a disadvantage. If I only have a few such fields, I suppose I could answer this question with some experimentation. However, I have about 10 databases in production with about 900 query criteria and join fields. (I printed out a list of them in the Immediate Window with some VB code). Can anyone give me some guidelines for deciding when to index query criteria and join fields, and when it's best to leave them unindexed, if you want to improve the overall performance of your database? Thanks in advance, Paul |
#3
|
|||
|
|||
On Sun, 6 Feb 2005 12:48:18 -0800, "Paul James"
wrote: Can anyone give me some guidelines for deciding when to index query criteria and join fields, and when it's best to leave them unindexed, if you want to improve the overall performance of your database? I'll agree with John. The cost you pay in slower updates and increased database size is *usually* outweighed by the improved performance on searching. However, don't gild the lily! Access automatically creates a unique Index for each Primary Key field, and a nonunique Index for each field used as a foreign key in the Relationships window; if you're unwise enough to use the Lookup wizard, it creates ANOTHER index for the fields in both tables involved, even if indexes already exist; unless you've turned it off, certain fieldnames (those ending in ID or NUM for example, see Tools... Options... Tables) get automatically indexed. Check each table's Indexes collection to see what indexes you already have (or the Documentor), and delete any redundant ones. Back when I was good friends with an Oracle DBA, I learned that she spent a great deal of time and thought tuning tables by determining which fields should be indexed and which should not. At times it can be a bit of a black art. John W. Vinson[MVP] |
#4
|
|||
|
|||
Hi Paul,
It also says to index fields on both sides of a join. Yes, I've seen this too, however, you do not need to index any foreign key fields if you establish relationships first in the relationships window. The reason is that Access automatically indexes the foreign key field when you create a relationship. Indexing fields will slow down an add operation, since any indexes must be updated at the same time. I'd be really surprised to learn that indexes slow down delete operations. It seems to me that an index would allow the appropriate records to be located faster in a delete operation. So evidently there is some performance benefit to be derived from indexing criteria fields, but you also have to suffer a disadvantage. I guess you can think of it as a "pay me now" or "pay me later" type proposition. You can pay a slight time penalty at the time of adding a record, or pay it every time that a field is used as a critieria. Note: you should also index fields that are used to specify sort orders. If I only have a few such fields, I suppose I could answer this question with some experimentation. You might want to take a look at this article: Use Microsoft Jet's ShowPlan to write more efficient queries http://builder.com.com/5100-6388-5064388.html My advice is to index fields that you use to specify criteria and sort orders. This is especially important in a multi-user database, where the back-end is on a file server. Without proper indexing, all records will be transferred over the wire before criteria are applied to filter them out. With proper indexing, the index will be transferred over the wire plus just the appropriate records (assuming the query optimizer can use the index). Here are some additional articles you might be interested in looking at: Information about query performance in an Access database http://support.microsoft.com/?id=209126 How To Use QueryPerformanceCounter to Time Code http://support.microsoft.com/?id=172338 How to optimize Microsoft Access when using ODBC data sources http://support.microsoft.com/?id=286222 How to keep a Jet 4.0 database in top working condition http://support.microsoft.com/?id=303528 Optimizing for Client/Server Performance http://support.microsoft.com/?id=208858 How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients http://support.microsoft.com/?id=889588 Tom ________________________________ "Paul James" wrote in message ... In Access Help under Performance, it says that you can improve query performance indexing any field used to set criteria for the query. It also says to index fields on both sides of a join. However, elsewhere I have also read that indexed fields can also slow down certain operations like adding or deleting records, because Access has to update the index when records containing those fields are added or deleted. So evidently there is some performance benefit to be derived from indexing criteria fields, but you also have to suffer a disadvantage. If I only have a few such fields, I suppose I could answer this question with some experimentation. However, I have about 10 databases in production with about 900 query criteria and join fields. (I printed out a list of them in the Immediate Window with some VB code). Can anyone give me some guidelines for deciding when to index query criteria and join fields, and when it's best to leave them unindexed, if you want to improve the overall performance of your database? Thanks in advance, Paul |
#5
|
|||
|
|||
Thanks for the information, gentlemen.
I'm convinced -I'm going to index my criteria and sort fields. In that regard, I have two additional questions: John Vinson had said: Check each table's Indexes collection to see what indexes you already have (or the Documentor), and delete any redundant ones. 1. I know how to count the number of indices in a table using tdf.Indexes.Count but that just gives me an integer telling how many indices there are in the table. Is there another property or method I can use to reveal more information about the Indices in a table? (I usually establish joins in the relationships window wherever appropriate, and I never use the Lookup wizard. However, some of my design predecessors (who are long gone) may have done so, and I'd like to know how to check to see if I've got redundant indices, in order to remove them. How can I examine the indices for a table to tell whether I have this situation?) 2. How do you delete an index - is it simply by going into table design view and setting the Indexed property of a field to "No"? And if there are two indices for that field, will it remove both? Thanks again in advance, Paul |
#6
|
|||
|
|||
On Sun, 6 Feb 2005 20:47:06 -0800, "Paul James"
wrote: but that just gives me an integer telling how many indices there are in the table. Is there another property or method I can use to reveal more information about the Indices in a table? ... 2. How do you delete an index - is it simply by going into table design view and setting the Indexed property of a field to "No"? And if there are two indices for that field, will it remove both? A couple of ways. In table design view you can use a GUI - click the "indexes" icon (looks light lightning hitting a datasheet). This will show all of the indexes by name in the left column, and their corresponding fields (an index can have up to ten!) in the right. To delete an index simply select it and hit the delete key. In VBA you can loop through the Indexes collection: Dim idx As Index Dim tdf As Tabledef Dim fld As Field For Each tdf In CurrentDB.TableDefs Debug.Print "Table: " & tdf.Name For Each idx In tdf.Indexes Debug.Print " Index: " & idx.Name For Each fld In idx.Fields Debug.Print " " & fld.Name Next fld Next idx Next tdf An index has a Delete method as well. John W. Vinson[MVP] |
#7
|
|||
|
|||
Hi James,
Is there another property or method I can use to reveal more information about the Indices in a table? I think the easiest method is to simply use the built-in database documenter. Click on Tools Analyze Documenter. Select the tables tab. Select all tables. Click on the Option... button. Select "Names and Fields" (or "Names, Fields and Properties") under the heading "Include for Indexes". Don't check off any options under "Include for Table" and select Nothing under "Include for Fields". You should be able to go into table design view, and click on View Indexes to bring up the Indexes window. Delete any duplicate indexes here. Tom _____________________________________ "Paul James" wrote in message ... Thanks for the information, gentlemen. I'm convinced -I'm going to index my criteria and sort fields. In that regard, I have two additional questions: John Vinson had said: Check each table's Indexes collection to see what indexes you already have (or the Documentor), and delete any redundant ones. 1. I know how to count the number of indices in a table using tdf.Indexes.Count but that just gives me an integer telling how many indices there are in the table. Is there another property or method I can use to reveal more information about the Indices in a table? (I usually establish joins in the relationships window wherever appropriate, and I never use the Lookup wizard. However, some of my design predecessors (who are long gone) may have done so, and I'd like to know how to check to see if I've got redundant indices, in order to remove them. How can I examine the indices for a table to tell whether I have this situation?) 2. How do you delete an index - is it simply by going into table design view and setting the Indexed property of a field to "No"? And if there are two indices for that field, will it remove both? Thanks again in advance, Paul |
#8
|
|||
|
|||
That information is exactly what I needed.
Thanks for the information about using the Documenter to examine indices, the lightning tool and the code to loop through the indexes collection. Thanks also for your comments about the benefits of indexing fields used in query criteria, sorts and joins. Paul |
#9
|
|||
|
|||
Paul James wrote:
Thanks for the information about using the Documenter to examine indices, the lightning tool and the code to loop through the indexes collection. Be sure you understand that the Indexes window (in table design view) will **not** show you the foreign key indexes that are automatically created when you set up referential integrity in the Relationships window. As others pointed out, these are the ones most likely to be duplicated. AFAIK, the ony way to see all the indexes is to use a code loop like John posted. (The Documenter seems to do this, but I find the output too cluttered to be useful). -- Marsh MVP [MS Access] |
#10
|
|||
|
|||
Thanks for this additional information, Marsh.
AFAIK, the ony way to see all the indexes is to use a code loop like John posted. Does this also apply to redundant indexes automatically created by the Lookup Wizard? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
count number of records | Joe_Access | General Discussion | 1 | January 13th, 2005 06:27 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Query criteria conflict | dunnotar02 | Running & Setting Up Queries | 6 | November 9th, 2004 07:04 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |