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
|
|||
|
|||
Why Index?
I am using a table with almost a million records and redesigning a database
to run a series of queries which currently are very lengthily. Could someone recommend where I could read up on indexing tables etc to help things run more quickly? Thanks |
#2
|
|||
|
|||
Why Index?
I generally index fields that a
- used in joins - used in criteria - used in Order By clauses A quick Google found Garry Robinson's page "Indexing Tables Tips For Microsoft Access" http://www.vb123.com/toolshed/99/indexfields.htm. -- Duane Hookom Microsoft Access MVP "Andy" wrote: I am using a table with almost a million records and redesigning a database to run a series of queries which currently are very lengthily. Could someone recommend where I could read up on indexing tables etc to help things run more quickly? Thanks |
#3
|
|||
|
|||
Why Index?
On Wed, 1 Apr 2009 06:21:03 -0700, Andy
wrote: I am using a table with almost a million records and redesigning a database to run a series of queries which currently are very lengthily. Could someone recommend where I could read up on indexing tables etc to help things run more quickly? Thanks Good indexing is critical and can make very dramatic improvements in performance... but bad indexing can make things WORSE, especially for update and insert queries. It's both a skill and an art, and can require some thorough testing. In addition to Duane's recommendation I'd take a look at Tony Toews' site http://www.granite.ab.ca/access/performancefaq.htm. It's not specifically about indexing but contains a lot of more general performance tips. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Why Index?
Just because you create an index, it doesn't mean that Access will use it.
There's a Jet tool called ShowPlan which can show you how a query is run including what indexes are used. Showplan http://articles.techrepublic.com.com...1-5064388.html Besides indexing, sometimes it's just the way the query is written. For example if I write an In/Not In clause and it's slow, I'll modify it into an Exists/Not Exists or visa versa. Sometimes the difference is dramatic. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Andy" wrote: I am using a table with almost a million records and redesigning a database to run a series of queries which currently are very lengthily. Could someone recommend where I could read up on indexing tables etc to help things run more quickly? Thanks |
Thread Tools | |
Display Modes | |
|
|