Index on Multiple Fields

[ions]

I was reading Access 2003 Inside Out today and it mentions that you can place a single index spread over Multiple Fields.

For example you can place one index on fields.

Lastname
MiddleName
Firstname

This is to increase effeciency for searches for multi criteria where clauses.

Where [Lastname] = "Smith" AND [firstName] = "John"

Do you take advantage of this feature? Is it worth implementing. This is the first time I have heard of multiple Field index.

Thanks

 

[georgedwilkinson]

This feature of all DBMSs is critical. Especially if you get into reporting databases.

Just remember, the more indexes you have, the longer inserts will take. As long as you don't go crazy, it shouldn't be a problem.

In your example, many DBMSs would not use the firstName field of the index because you didn't compare the MiddleName. The keys are used in order that they're placed in the index. (I hope that makes sense)

 

[ions]

Thanks Georged,

Makes sense.

>>many DBMSs would not use the firstName field of the index because you didn't compare the MiddleName<<

Does this include Access?

Can you specify Duplicates or NO Duplicates allowed on Multiple field Indexes in Access?

 

[georgedwilkinson]

Yes, I'm pretty sure Access (aka Jet) works the same way but I don't have the patience to look it up.

In Access, you CAN specify duplicates or no duplicates.

 

[ions]

Thanks Georged.

 

[georgedwilkinson]

Thanks Georged.

Always glad to help when I can.

 

[LPurvis]

George has informed you accurately.
Indeed you'd have to use (apply criteria to) the first field in the index to be able to have the index used by the query engine in applying criteria to the subsequent field(s).
If this isn't likely then you might want separate indexes.
(You could always maintain both - but that's a big decision and the multi-field index would likely only then be to enforce uniqueness).

Cheers.

 

[gemma-the-husky]

if you create a (visual) stored query, (either to use as a recordsource for a form, or just for a combo box) access computes a query plan, to evaluate the best way it can retrieve the information.

if keys are avaialble, then access will most probably use those keys, but it doesnt have too. once it has ascertained the query plan, then subsequent use of the query is quicker. i am not sure whether typing the whole query into a forms record source does the same thing - i dont usually do it anyway (although combobox/listbox wizards do) - i use stored queries, as they become reusable, and i find them easierr to edit anyway.

you can see this in action with a complex query. the first time it may take a few seconds, but subsequent use is much quicker.

the number of keys you need depends to some extent on the complexity of the database. if its simple, with not many reocrds, then having indexes wont make much difference to the speed anyway.

 

[LPurvis]

Database engines can indeed choose a table scan over index use on tables with few records or with poor spread of values.
A saved execution plan is usually an advantage. However when table statistics become out of date then that plan make not make the best sense for the database as it stands (i.e. the plan formed during the early days of a database with few records may be less optimum when there are many rows - or the balance has shifted to certain other tables).
Equally, though saved plans are an advantage much more often than not, the time of execution of a query would very much more likely be weighted towards the data fetch (disk reads, network bandwidth etc) compared to the relatively fast plan execution. When you run a query a second time, bear in mind that, inevitably on both client and server some disk caching of that read data will occur just through normal OS processes. (Hence some speed increase).
There would be something seriously wrong if the engine was taking several seconds to form a query execution plan! ;-)
On-the-fly queries Vs saved defintions would be a subtle distinction most of the time.
(Hence the relative popularity of on-the-fly querying - and the flexibility it offers).

On the form and control front, yes when you enter a SQL string as a source a saved query definition is created. They're hidden but as viable as other deliberate querydefs.
They can be seen by enumerating the QueryDefs collection (named akin to "~sq_fFormName" etc.

Cheers.

 

[gemma-the-husky]

aahh - i never paid much attention, but i had thought the ~ prefixes related to deleted objects. got that wrong 118