Performance..should one index

[alex]

Hello all,

Using Access ’03…

I have an mdb file that performs between four to six queries each
day. Each query only utilizes two tables:

One – an imported table (updated daily) that contains between 15 and
50 thousand records (it’s a bit fat too). This table contains names
and addresses (very little duplication; e.g., yes/no).

Two – a table contained in the mdb file that holds probably 200
records and remains mostly static.

The query works like this: both tables exist in the query’s design
grid (no relationship between the two). Table one is the main table
and a particular field from table two is listed in the criteria (e.g.,
like [tbl2].[Applicant_Name])

My question is about performance and indexing (I don’t have access to
SQL Server!). I use about 15 fields from table two in the criteria
section of my queries. Should those fields be indexed in table one?
If so, can I do it programmatically (possibly with code). Each query
takes about five minutes to run.

If not, table one comes from an ORACLE db; if I index the ORACLE table
before it gets to Access, will Access keep the indexes?

Thoughts? Thanks,
alex

 

[Tony Toews [MVP]]

My question is about performance and indexing (I don’t have access to
SQL Server!). I use about 15 fields from table two in the criteria
section of my queries. Should those fields be indexed in table one?

Yes, all fields used as criteria should be indexed. Otherwise Access
has to do a complete table scan to pull out the records.

If so, can I do it programmatically (possibly with code).

Yes, you can create indexes programmatically. But why wouldn't you
just create the indexes once and leave them be? Ah, you import the
complete table. Yes, in the case you are best to remove all the
indexes, import the table and recreate the indexes.

Set tdfUpdate = dbsupdate.TableDefs("Equipment")
Set idxUpdate = .CreateIndex("eUnitNBR")
idxUpdate.Fields.Append idxUpdate.CreateField("eUnitNBR")
idxUpdate.Unique = True
.Indexes.Append idxUpdate
and
.Indexes.Delete idxNew.Name

Although chances are in your case the .Unique = True isn't required or
necessary.

If not, table one comes from an ORACLE db; if I index the ORACLE table
before it gets to Access, will Access keep the indexes?

No.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[alex]

Yes, all fields used as criteria should be indexed.  Otherwise Access
has to do a complete table scan to pull out the records.


Yes, you can create indexes programmatically.  But why wouldn't you
just create the indexes once and leave them be?  Ah, you import the
complete table.  Yes, in the case you are best to remove all the
indexes, import the table and recreate the indexes.

    Set tdfUpdate = dbsupdate.TableDefs("Equipment")
        Set idxUpdate = .CreateIndex("eUnitNBR")
        idxUpdate.Fields.Append idxUpdate.CreateField("eUnitNBR")
        idxUpdate.Unique = True
        .Indexes.Append idxUpdate
and
        .Indexes.Delete idxNew.Name

Although chances are in your case the .Unique = True isn't required or
necessary.


No.

Tony
--
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Great advice! Thanks Tony.