ACCESS FEED: [MS_AccessPros] RE: RE: Too many indexes.

Rabu, 04 September 2013

[MS_AccessPros] RE: RE: Too many indexes.

Andrew

It seems John isn't around right now so I'll try answering your questions.

Compound indices can benefit searches in Access just like in SQL Server. I understood John to be saying that Access frequently over-indexes tables especially if you leave the auto-indexing on in the options backstage. Over-indexing can kill performance.

SQL Server does not hide any indices. What you see in the index dialog box is what exists. And yes, creating foreign keys automatically create indices. Those indices are used not only for searches, but more importantly, referential integrity. Why Access hides foreign key indices is a mystery to me. Maybe it's to prevent a rookie from accidentally removing them.

Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com

--- In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

Hi John,

interesting that you say that compound indexes can/should be removed in Access. In SQL Server compound indexes can bring a significant benefit.

Does/Can Access use the hidden "Indexes" to speed the data access/searching? I'm also not sure whether a "Constraint" In SQL Server creates an index usable for searching (logically it would make sense to implement such things using the "index" infrastructure but, that might only be "logically" from my viewpoint and someone with closer knowledge might see something else as "logical".)

Yours, (a different to the original poster) Andrew

--- In ms_access_professionals@yahoogroups.com, <JohnV@...> wrote:

Andrew-

"Hidden" indexes are a result of defining Referential Integrity and should not be removed. Let's say you have an Orders and a Customers table with a Referential Integrity rule defined between CustomerID in Customers and CustomerID in Orders. If you've also defined a separate index on CustomerID in the Orders table via the Indexes window, that's a duplicate and can be removed. Also take a look at compound indexes - indexes on multiple fields. If you have separate indexes on each of the fields, you don't need the compound index.

Hope that helps...

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto: MS_Access_Professionals@yahoogroups.com ] On Behalf Of adwsys
Sent: Monday, September 02, 2013 1:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Too many indexes.

Hi All, I've run into the "Too many indexes" problem on a couple of tables in one of my Access 2003 applications. I know that there are several duplicate indexes in the tables, but I'm not sure which ones I can safely delete. The hidden ones don't show up in the Indexes dialog anyway & have to be deleted programmatically, so does anyone have a copy of Allen Brownes CleanUpIndexes utility handy, or anything similar.

Thanks, Andrew

------------------------------------

Yahoo! Groups Links