Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
multiple indices over 1 field
18 views
Skip to first unread message
cubbybear3
Feb 3, 2012, 2:55:37 PM2/3/12
to
I have inherited the responsibility for a server that has a front-end
database (does the AM/PM processing) and most of the tables are in
separate back-end DB's.
In trying to clean up the system (and speed up the processing) I have
found that many of the B/E DB's have multiple indices over the same
field.
Example:
Field IndexName
OrdNo OrdNo
OrdNo PrimaryKey
OrdNo Index_OrdNo
OrdNo OrderheaderOrdNo
Now I am an Access newbie (but have many years of DB experience on
other systems), I have a few questions.
1. Is there any reason why we would need the extra indices?
2. I can not seem to find them referenced in any queries/forms/reports/
macros or modules. Is there any way to identify if they are being
used?
3. There are multiple users that have their own DB's that have links
to the backend DB's. Short of checking each of the users DB's how can
I find out if they are being referenced by another DB?
4. What kind of problems will I be facing if I just go ahead and
delete them?
The machine is running Win2K using A2003.
Thanks in advance for any help/suggestions.
-pb
database (does the AM/PM processing) and most of the tables are in
separate back-end DB's.
In trying to clean up the system (and speed up the processing) I have
found that many of the B/E DB's have multiple indices over the same
field.
Example:
Field IndexName
OrdNo OrdNo
OrdNo PrimaryKey
OrdNo Index_OrdNo
OrdNo OrderheaderOrdNo
Now I am an Access newbie (but have many years of DB experience on
other systems), I have a few questions.
1. Is there any reason why we would need the extra indices?
2. I can not seem to find them referenced in any queries/forms/reports/
macros or modules. Is there any way to identify if they are being
used?
3. There are multiple users that have their own DB's that have links
to the backend DB's. Short of checking each of the users DB's how can
I find out if they are being referenced by another DB?
4. What kind of problems will I be facing if I just go ahead and
delete them?
The machine is running Win2K using A2003.
Thanks in advance for any help/suggestions.
-pb
Bob Barrows
Feb 7, 2012, 7:00:33 AM2/7/12
to
cubbybear3 wrote:
> I have inherited the responsibility for a server that has a front-end
> database (does the AM/PM processing) and most of the tables are in
> separate back-end DB's.
What types and version are the backend DBs? This is relevant.
> I have inherited the responsibility for a server that has a front-end
> database (does the AM/PM processing) and most of the tables are in
> separate back-end DB's.
>
> In trying to clean up the system (and speed up the processing) I have
> found that many of the B/E DB's have multiple indices over the same
> field.
> Example:
> Field IndexName
> OrdNo OrdNo
> OrdNo PrimaryKey
> OrdNo Index_OrdNo
> OrdNo OrderheaderOrdNo
>
> Now I am an Access newbie (but have many years of DB experience on
> other systems), I have a few questions.
> 1. Is there any reason why we would need the extra indices?
There might be, expecially if the primary key is clustered and the others
> In trying to clean up the system (and speed up the processing) I have
> found that many of the B/E DB's have multiple indices over the same
> field.
> Example:
> Field IndexName
> OrdNo OrdNo
> OrdNo PrimaryKey
> OrdNo Index_OrdNo
> OrdNo OrderheaderOrdNo
>
> Now I am an Access newbie (but have many years of DB experience on
> other systems), I have a few questions.
> 1. Is there any reason why we would need the extra indices?
are not. Some DBs allow indexes to include columns that are not part of the
key and so each index might have been created to optimize a particular set
of queries.
OTOH, the DBA/developer might have been using an automatic database tuning
tool that recommended these indexes, and he blindly accepted its
recommendations without doing the due diligence that you are now doing.
> 2. I can not seem to find them referenced in any
> queries/forms/reports/ macros or modules. Is there any way to
> identify if they are being used?
allow query hints that specify indexes in the sql statements, and others do
not. Depending on the type of database, you might or might not see any
reference to the indexes.
> 3. There are multiple users that have their own DB's that have links
> to the backend DB's. Short of checking each of the users DB's how can
> I find out if they are being referenced by another DB?
> 4. What kind of problems will I be facing if I just go ahead and
> delete them?
1. No problems - the indexes were not needed in the first place (don't
delete the primary key)
2. Queries will start running slowly, and, in the worst case, users will
start experiencing timeouts
cubbybear3
Feb 8, 2012, 3:50:17 PM2/8/12
to
Bob, Thanks for the reply.
Everything is Access 2003. The DB's were created ages ago by someone
in the Marketing Dept (who was very well versed in Access), and then
modified by various employees over the years.
There are no compound (multi-field) keys on any of the tables. But
the tables have/had anywhere from 20 to 50 percent of the fields
indexed. I have since listed the indexed fields on each table and
removed all except those that the users asked to keep. As for speed
issues, the AM process re-builds most of the tables from our main
system (AS/400), so getting the AM processing *done* is more critical
than a users query running a bit slow during the day.
I have also removed the duplicate indexes, because as you said "they
are not needed". Things are running much better/faster.
-pb
Everything is Access 2003. The DB's were created ages ago by someone
in the Marketing Dept (who was very well versed in Access), and then
modified by various employees over the years.
There are no compound (multi-field) keys on any of the tables. But
the tables have/had anywhere from 20 to 50 percent of the fields
indexed. I have since listed the indexed fields on each table and
removed all except those that the users asked to keep. As for speed
issues, the AM process re-builds most of the tables from our main
system (AS/400), so getting the AM processing *done* is more critical
than a users query running a bit slow during the day.
I have also removed the duplicate indexes, because as you said "they
are not needed". Things are running much better/faster.
-pb
Tony Toews
Feb 8, 2012, 4:33:59 PM2/8/12
to
On Wed, 8 Feb 2012 12:50:17 -0800 (PST), cubbybear3 <n42...@gmail.com>
wrote:
>I have also removed the duplicate indexes, because as you said "they
>are not needed".
They aren't needed but then they don't cause problems either.
Duplicate indexes do not take extra room in the Access database file.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
wrote:
>I have also removed the duplicate indexes, because as you said "they
>are not needed".
Duplicate indexes do not take extra room in the Access database file.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Bob Barrows
Feb 8, 2012, 5:43:46 PM2/8/12
to
Tony Toews wrote:
> On Wed, 8 Feb 2012 12:50:17 -0800 (PST), cubbybear3 <n42...@gmail.com>
> wrote:
>
>> I have also removed the duplicate indexes, because as you said "they
>> are not needed".
>
> They aren't needed but then they don't cause problems either.
> Duplicate indexes do not take extra room in the Access database file.
>
> Tony
Could you expand on that Tony? Are you saying that Jet does not actually
> On Wed, 8 Feb 2012 12:50:17 -0800 (PST), cubbybear3 <n42...@gmail.com>
> wrote:
>
>> I have also removed the duplicate indexes, because as you said "they
>> are not needed".
>
> They aren't needed but then they don't cause problems either.
> Duplicate indexes do not take extra room in the Access database file.
>
> Tony
create an index if it would duplicate an existing index?
If so, I never knew that.In SQL Server, indexes are created and take up
space whether duplicated or not.
cubbybear3
Feb 9, 2012, 1:50:03 PM2/9/12
to
On Feb 8, 2:43 pm, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
The duplicate indexes may not take extra room in Access, but they *do*
cause a problem, at least from the standpoint of what was wrong with
this system before I got here.
Due to lack of system management, there were old/duplicate/backup
copies of DB's and tables within DB's. So much so that I have already
gotten rid of over 101gb of crap on the system. And that is just
databases. There is another 117gb of other *stuff* (PDF's, XLS's,
JPG's, DOC's etc.) that can be removed.
Because there was no documentation of what was done (and why) people
kept creating copies of things. Then years later no one wanted to
remove it out of fear (of the unknown/undocumented).
Needless to say, the system is now running much faster with lots of
available disk space.
-Paul B.
> Tony Toews wrote:
>
> They aren't needed but then they don't cause problems either.
> Duplicate indexes do not take extra room in the Access database file.
>
> > Tony
Tony,
>
> They aren't needed but then they don't cause problems either.
> Duplicate indexes do not take extra room in the Access database file.
>
> > Tony
The duplicate indexes may not take extra room in Access, but they *do*
cause a problem, at least from the standpoint of what was wrong with
this system before I got here.
Due to lack of system management, there were old/duplicate/backup
copies of DB's and tables within DB's. So much so that I have already
gotten rid of over 101gb of crap on the system. And that is just
databases. There is another 117gb of other *stuff* (PDF's, XLS's,
JPG's, DOC's etc.) that can be removed.
Because there was no documentation of what was done (and why) people
kept creating copies of things. Then years later no one wanted to
remove it out of fear (of the unknown/undocumented).
Needless to say, the system is now running much faster with lots of
available disk space.
-Paul B.
0 new messages