How does access read Where clauses?

[MadProfessor]

Hi,

Simple question: when you have multiple conditions in the Where clause, does Access 2000 read them from left to right or from right to left? I am trying to optimize the SQL statements hence wanting to know this.

 

[Aivars]

I think on the whole. Use parenthesis in the Where clauses.

Aivars

 

[MadProfessor]

point taken, but it doesn't answer my original question: which way are they read - left to right or right to left?

On the Where clause, I want to put the join conditions then most restrictive conditions first, to optimise performance, so want to know which way it is read so that I can place them properly.

 

[Aivars]

In Access join conditions are include in From clause. Open query Design window and create simple query with two or three tables. Then open query SQL window and investigate the join conditions sequence.

I almost never write SQL manually. I create query on query Design window, then open query SQL window[/b], then delete needless parenthesis from SQL text for simplify its (make it more readable), then put all rows into first one, then copy and paste SQL string into my VBA codes and change criterias to needed. It require less that one-two minutes for designing of complicate SQL string (more then 3 tables with different join conditions).

Aivars

 

[MichaelRed]

AFAIK, MS. Acccess's little boy "Jet" reads the entire SQL statement from left to right and top to bottom. Unfortunatly, there is a built in optimizer which 'plays with' the whole process to do it's own optimization, so your order doesn't really make any (much?) difference. Again, AFAAIK, ALL 'real' relational database engines do their own internal optimization, thus negating your attempts to influence any single query. You mad be the "MAD" professor, if you persist in attempting to 'fool mother misrosoft', I think you will soon also be the "FRUSTRATED" one as well.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over