Prev: Importing Tab Delimited Text File to an Existing Table in Access
Next: Table structure for separated families
From: Jeff Boyce on 22 Mar 2010 19:58
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" <QB(a)discussions.microsoft.com> wrote in message
news:173087AF-6F7A-4150-8A7D-DDEFA25C58B9(a)microsoft.com...
> 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" <QB(a)discussions.microsoft.com> wrote in message
>> news:CB37E4D2-1CA8-467E-8DB8-F5D219748D50(a)microsoft.com...
>> > 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
>>
>>
>> .
>>


From: Armen Stein on 23 Mar 2010 02:26
On Mon, 22 Mar 2010 15:46:01 -0700, QB <QB(a)discussions.microsoft.com>
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

From: QB on 23 Mar 2010 14:04
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 <QB(a)discussions.microsoft.com>
> 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
>
> .
>
From: J_Goddard via AccessMonster.com on 23 Mar 2010 15:31
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/Forums.aspx/access-tablesdbdesign/201003/1

First  |  Prev  | 
Pages: 1 2
Prev: Importing Tab Delimited Text File to an Existing Table in Access
Next: Table structure for separated families