Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

a general question about WHERE 2

Status
Not open for further replies.

VickyC

Technical User
Sep 25, 2010
206
CA
hi - Lets say I am processing a vast number of groups of 10 records to identify which of them 'survive' a series of restrictions. My WHERE statement might look something like...

WHERE (T.A + T.B/2 > T.C) AND
(T.A / T.B <> T.D) AND
(T.X < 1000) AND
... many more restrictions ...

Now, the 1st restriction rejects ~99% of the groups that will eventually be rejected, and the other 35+ restrictions reject only a few more.

I want to know how SQL deals with this WHERE statement. If the group fails the 1st constraint, does SQL still grind through all of the other constraints? If so, I'd probably want to use 2 queries - one to handle only the 1st constraint, and another to subject the 'survivors' to all of the other constraints. Any thoughts?

Thanks - Vicky
 
Since you are using "AND" and each condition is "equal" regarding selection importance, if any of your three conditions fail, then you will not receive the records. Just for grins, use the Query builder and make all kinds of weird conditions. Take a look at what happens -- and what the SQL clause looks like.

The speculative judgment of the quality of an answer is based directly on … what was the question again?
 
You don't know for sure in which sequence the constraints are evaluated, but as soon as one fail the row is rejected (provided you only have AND logical operators)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV - I guess that's what I really wanted to know - that no further tests are done on records once they have failed a restriction. Since you say we have no idea about the order in which constraints are handled, I think it would make sense to force the matter by using ONLY my first constraint in a query, then testing the relatively few survivors against all of the other 'AND' constraints in a 2nd query.
Thanks
Vicky
 
Vicky, if you are really curious about the performance of queries and the order in which the evaluations take place, and you don't mind putting in a little work, Google 'how to use access showplan' (omit the quotes). Depending on Access version/Windows version, it is rather simple to do. The showplan will information on every query you run while you have the option enabled. The results will show you the order in which each step is executed and statistics for your keys. Sometimes the results leave you scratching your head, but other times you slap yourself in the head and mutter "how could I have missed this?!?!". Here is an old sample in case you are curious:
Code:
- Inputs to Query -
Table 'tblObjCls'
Table 'tblLnItm'
    Using index 'PrimaryKey'
    Having Indexes:
    PrimaryKey 69 entries, 1 page, 69 values
      which has 1 column, fixed, unique, primary-key, no-nulls
- End inputs to Query -

01) Outer Join table 'tblObjCls' to table 'tblLnItm'
      using index 'tblLnItm!PrimaryKey'
      join expression "tblObjCls.[LineItem]=tblLnItm.[LineItem]"
02) Restrict rows of result of 01)
      by scanning
      testing expression "tblLnItm.[LineItem] Is Null"
03) Group result of '02)'

The speculative judgment of the quality of an answer is based directly on … what was the question again?
 
thanks trevil620 - that's really useful!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top