A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Proper Indexing of Table Fields



 
 
Thread Tools Display Modes
  #1  
Old March 22nd, 2010, 09:38 PM posted to microsoft.public.access.tablesdbdesign
QB
external usenet poster
 
Posts: 150
Default Proper Indexing of Table Fields

Correct me if I misunderstood, but I read that one should index fields that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB
  #2  
Old March 22nd, 2010, 10:03 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Proper Indexing of Table Fields

.... it depends ... G

If your table has a few thousand rows, if your database isn't split, if you
only have one user at a time, ...
you might not need indexing.

If you have 100's of thousands of rows, if the back-end/data is on a slow
LAN, in a db server, if ...
you might need indexing.

What performance differences have you seen when you tried it?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
Correct me if I misunderstood, but I read that one should index fields
that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using
multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB



  #3  
Old March 22nd, 2010, 11:46 PM posted to microsoft.public.access.tablesdbdesign
QB
external usenet poster
 
Posts: 150
Default Proper Indexing of Table Fields

So based on your post, it is a trial and error thing?!

Here is the scenario:
Access 2003
Split Db
No server, simply shared off of a NAS Drive -- Each user has the FE
10 or less users
biggest table currently hold 130K records but the db is growing steadily
(currently BE at 54MB) probably 75K-100K records a year to the biggest table.

I haven't had the chance to do testing on indexing, that is why I wanted to
get some advice on the matter before I try and delve into it. I don't want
to make useless mistake and am more than willing to learn form people such as
yourself (people with far more knowledge and experience on the matter).

Is there a drawback to indexing fields? What about indexing fields that
aren't used in queries as criteria (over-indexing)? Does indexing add load
to database processing (I'm assuming so or else you would have told me to
index to my heart's content :-) )?

Thank you,

QB





"Jeff Boyce" wrote:

.... it depends ... G

If your table has a few thousand rows, if your database isn't split, if you
only have one user at a time, ...
you might not need indexing.

If you have 100's of thousands of rows, if the back-end/data is on a slow
LAN, in a db server, if ...
you might need indexing.

What performance differences have you seen when you tried it?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
Correct me if I misunderstood, but I read that one should index fields
that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using
multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB



.

  #4  
Old March 22nd, 2010, 11:51 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Proper Indexing of Table Fields

On Mon, 22 Mar 2010 13:38:05 -0700, QB wrote:

Correct me if I misunderstood, but I read that one should index fields that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB


Indexing the underlying fields in a calculated expression will not help, if
you're applying criteria to PercentDiff: it will still have to retrieve all
the rows, do the calculation on all of them, and only then apply the
criterion.

If you find performance is unacceptable (try it first, you might be pleasantly
surprised), post back with the actual criterion you're using; it may be that
some alternative way of framing the question will allow you to search for all
NewValues based on some expression on OldValue. Failing that, this might be
one of the rare instances where it's appropriate to store a derived value.
--

John W. Vinson [MVP]
  #5  
Old March 23rd, 2010, 12:51 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Proper Indexing of Table Fields

On Mon, 22 Mar 2010 15:46:01 -0700, QB wrote:

Is there a drawback to indexing fields? What about indexing fields that
aren't used in queries as criteria (over-indexing)? Does indexing add load
to database processing (I'm assuming so or else you would have told me to
index to my heart's content :-) )?


Indexes speed data retrieval but they *slow* data insertion or updating (the
program must update not only the table but all the indexes that are touched by
a change of the data); there is also a limit of 32 indexes on any one table.
In addition indexes do take up room in the database, counting toward the
2GByte limit on the size of the database.

--

John W. Vinson [MVP]
  #6  
Old March 23rd, 2010, 12:58 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Proper Indexing of Table Fields

As John points out, each situation is different.

If I were faced with deciding which fields to index, yes, I'd probably "try"
some different approaches to see which worked best.

But "trial and error" implies not using any intelligence...

If you have a field that has only 2 or 3 different values across 100,000
records, indexing is not going to help much!

If you have only 1,000 records, indexing might help, but most humans would
never be able to tell the difference!

If you are selecting on it, sorting by it, or joining on it, it's a
potential index.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
So based on your post, it is a trial and error thing?!

Here is the scenario:
Access 2003
Split Db
No server, simply shared off of a NAS Drive -- Each user has the FE
10 or less users
biggest table currently hold 130K records but the db is growing steadily
(currently BE at 54MB) probably 75K-100K records a year to the biggest
table.

I haven't had the chance to do testing on indexing, that is why I wanted
to
get some advice on the matter before I try and delve into it. I don't
want
to make useless mistake and am more than willing to learn form people such
as
yourself (people with far more knowledge and experience on the matter).

Is there a drawback to indexing fields? What about indexing fields that
aren't used in queries as criteria (over-indexing)? Does indexing add
load
to database processing (I'm assuming so or else you would have told me to
index to my heart's content :-) )?

Thank you,

QB





"Jeff Boyce" wrote:

.... it depends ... G

If your table has a few thousand rows, if your database isn't split, if
you
only have one user at a time, ...
you might not need indexing.

If you have 100's of thousands of rows, if the back-end/data is on a slow
LAN, in a db server, if ...
you might need indexing.

What performance differences have you seen when you tried it?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"QB" wrote in message
...
Correct me if I misunderstood, but I read that one should index fields
that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using
multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB



.



  #7  
Old March 23rd, 2010, 07:26 AM posted to microsoft.public.access.tablesdbdesign
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default Proper Indexing of Table Fields

On Mon, 22 Mar 2010 15:46:01 -0700, QB
wrote:

No server, simply shared off of a NAS Drive -- Each user has the FE


On a side note, I'm concerned about the BE on a NAS. Be aware that if
the back-end is on an OS without the right file locking mechanisms,
very bad corruption can result when multiple users make changes.

Look at the BE folder. If the LDB locking file disappears when
subsequent users start using the database, that's one sign that you're
headed for trouble. Move the BE to a Windows machine.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #8  
Old March 23rd, 2010, 07:04 PM posted to microsoft.public.access.tablesdbdesign
QB
external usenet poster
 
Posts: 150
Default Proper Indexing of Table Fields

The WD NAS is running NTFS, so from what I know this should be fine (unless
you tell me otherwise) and the ldb remains intact with multiple user
connection.

QB





"Armen Stein" wrote:

On Mon, 22 Mar 2010 15:46:01 -0700, QB
wrote:

No server, simply shared off of a NAS Drive -- Each user has the FE


On a side note, I'm concerned about the BE on a NAS. Be aware that if
the back-end is on an OS without the right file locking mechanisms,
very bad corruption can result when multiple users make changes.

Look at the BE folder. If the LDB locking file disappears when
subsequent users start using the database, that's one sign that you're
headed for trouble. Move the BE to a Windows machine.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

.

  #9  
Old March 23rd, 2010, 08:31 PM posted to microsoft.public.access.tablesdbdesign
J_Goddard via AccessMonster.com
external usenet poster
 
Posts: 221
Default Proper Indexing of Table Fields

Hi -

As usual - "it depends".

In this case, it depends on whether or not oldValue and/or newValue are used
to determine which records to select. If they are not used in the selection
criteria, there would be no point in indexing them.

John



QB wrote:
Correct me if I misunderstood, but I read that one should index fields that
are used as criteria in queries.

With this in mind, when one does a caculated field in a query using multiple
table fields, this means I should index each of these as well?

Ie:
(oldValue - newValue)/oldValue As PercentDiff

I should index oldValue and newValue?

Thank you,

QB


--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201003/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.