Question about indexing fields (Access)

I have a very large table I want to speed up by using an index.

As my application shows the user the appointments of the day, the query gathering my information mainly uses the DATE in the WHERE/HAVING clause.

I've then created an index on the 'date' field.

My question is: my query is also having 3 or 4 more fields in the WHERE/HAVING clause. Are these fields slowing down the whole query because those aren't ALSO indexed? Even tho one of those is a boolean field and wouldn't deserve indexing.

Is one index sufficient?

Any important details to know like: is the field order in the WHERE clause important?... I made it so the first field is my 'date' (indexed) field. But I guess that was useless? Access would've automatically managed to use the indexed field?

I would tend to believe that. My table is like 150,000 records-long and the query will return a only 45-50 records-long recordset.

Just thought I'd ask you guys for your 2 cents... any comments greatly appreciated.

The field order in the Where clause might be important, but something that definitely is important is the query you are using (including how you use fields in the Where clause, any table joins, etc).

If you show us the query, we can give apt advice.

An important thing to note is that an Index is not a magic bullet - while it makes some things faster, it will make others slower (so it might speed up a Select, but then slow down all Insert's on that table). If you have multiple "slow" queries that run against the table, it is probably a good idea to show us the others too.

Quote:

---

I made it so the first field is my 'date' (indexed) field. But I guess that was useless? Access would've automatically managed to use the indexed field?

---

Correct.

I had only one slow query. Well, I had a couple of recordsets dealing with this, but they all grab data using the date field, and everything seems to be blazing fast now. I'm pretty happy with the result of indexing that field.

I've also read everywhere that it might slow down when adding new records to the table - but this seems to be unnoticable.

Here's what my recordset looks like:

Code:

---

    Set rstRemplirGrille = CurrentDb.OpenRecordset("SELECT tabRendezVous.NoRendezVous, tabRendezVous.HeureRendezVous, tabRendezVous.ConfirmationFaite, tabDossierPatient.NoDossier, tabDossierPatient.DossierMedical, tabDossierPatient.DossierCapilaire, tabDossierPatient.DossierGranby, tabDossierPatient.DossierCISEP, tabDossierPatient.TelResidence, tabDossierPatient.TelBureau, tabRendezVous.ConsultationDescription, tabRendezVous.ClientArrive, tabRendezVous.TypeDossier, tabDossierPatient.NoPatient, [Prenom] & ' ' & [Nom] AS NomPatient " & _
                                                "FROM tabDossierPatient INNER JOIN tabRendezVous ON tabDossierPatient.NoPatient = tabRendezVous.NoPatient " & _
                                                "WHERE TypeRendezVous = " & TypeRV & _
                                                " AND Annule = FALSE " & _
                                                " AND NoDocteur = " & NoDocteur & _
                                                " AND DateRendezVous = #" & DateRV & "#" & _
                                                " ORDER BY HeureRendezVous", dbOpenSnapshot)

---

...might be a bit confusing for you since it's in french. What I just noticed is that I have an 'ORDER BY' clause on a field that is not indexed. Anyhow that query is now very quick because the 'DateRendezVous' field has been indexed. Also note that, in THAT particular query, the 'DateRendezVous' is NOT the first in the 'WHERE' clause, so that kinda confirms it can be anywhere in the WHERE clause for it to be effective.

Note concerning my INNER JOIN:
tabDossierPatient.NoPatient is a primary key.
tabRendezVous.NoPatient is NOT a primary key, but is indexed.

I appreciate your comments. Thank you.

One question, if this is a large database table why not up-size to true Relational database such as SQL Server Express 2005/2008? Your performance will be increased ten fold over MS Access.

I was waiting for someone to mention it.

I've not yet figured out the real reason why I didn't migrate to a proper SQL server. Is it lazyness, the fear of change - because I never played with that. But I'm pretty comfortable with the structured query langage, I guess I should give it a try soon.

On the other hand, that particular client wants things done without paying too much and, after indexing some important fields, I achieved a very VERY acceptable speed.

Thank you for mentionning it tho.

That all seems fine - you are using the entire field each time, rather than using functions on the field (which not only adds extra work, but also eliminates the use of Indexes on those fields).

The only concern I have is your DateRV variable, which to avoid bugs (when Regional Settings change/differ) should be a String containing the date formatted as "mm/dd/yyyy" or "yyyy-mm-dd". You may well have that already, but I thought I should mention it.



In terms of switching to SQL Server, it could be a good idea, but would take some effort (modifying SQL statements, etc), and a fair bit more to get best performance (such as switching from DAO to ADO, etc). For existing projects it is debatable if it would be worth it, but I would recommend it for new projects.

Date in a string? That'd be better?

Right now, I got 'DateRV As Date' is this procedure's declaration. It receives the date from my calendar control:

ctlCalendrier.Selection.Blocks(0).DateBegin

Should that be good? Or should I bother passing the parameter using DateSerial, to ensure months and days doesn't mix up?

I don't quite understand why "mm/dd/yyyy" or "yyyy-mm-dd" would help. In french, I'd say most computers will have "dd-mm-yyyy" as the default display. I don't understand why this formatting wouldn't confuse the query even more... If I use a string and it is formatted "dd-mm-yyyy", what would avoid the query to go wrong by thinking my string is containing "mm-dd-yyyy" ?

In ANSI SQL when you pass a date thru the SQL query it (ANSI SQL) assumes that the date is formated in US standard (mm/dd/yyyy). It could turn and bite you if not careful.

Indeed - so when the value is #2/1/2008#, it will be interpreted as Feb 1st, no matter what you intended it to be. When that interpretation is not valid (such as today, #27/1/2008#), Access will try to interpret it using ISO format (yyyy-mm-dd), and if that fails then it will try your Regional Settings - at which point it will get the value that you intended.

Other database systems will not try your Regional Settings at all (which is a good thing, as you know about the problem immediately, rather than storing invalid data/searches not returning correct data/etc).


In VB, when you simply append a Date to a String, it is formatted using Regional Settings - thus creating the possibility of bugs. By using the Format command to specify the format, you eliminate those bugs.