General SQL question

Posted on 2011-05-12
Last Modified: 2012-06-27
Hi. This is just a conceptual  question.

In SQL, let's say you're checking multiple conditions in a WHERE statement.

Something like WHERE (Datefield > 12/1/10) AND (DollarAmount  > $100.00) AND (REGION = 'NY')

Are every one of those conditions checked for each record? Or is there some sort of 'efficiency' for lack of a better term built into the system that doesn't bother to check the remaining conditions if a given condition is not met.

For example, let's say for a given record, the Datefield is NOT greater than 12/1/10, are DollarAmount and REGION still checked?  Since all AND clauses are being used in my example, that means all conditions must be met. I'm trying to find out if all conditions for each record are checked regardless.

In old school programming (say COBOL), I would  set up something like this:

If Datefield > 12/1/10 then
   If DollarAmount > $100.00 then
       If REGION = 'NY'  then

In the IF THEN ELSE example, for efficiency, if the first condition is not met, I don't bother to check the others.   Just wondering how it actually works behind the scenes in SQL so that I can possibly set up some queries to run quicker.  Thanks.
Question by:dbfromnewjersey
    LVL 39

    Expert Comment

    I hope other conditions are not checked if first not met, but this question should be asked in Microsoft Access blog (or in MySQL, if it is stored query)
    LVL 3

    Expert Comment

    If Datefield > 12/1/10 then
       If DollarAmount > $100.00 then
           If REGION = 'NY'  then

    Open in new window

    would be something like
    WHERE (Datefield > 12/1/10 and (DollarAmount > $100.00 and REGION = 'NY' and whatever
     )) or (Datefield <= 12/1/10 and 1=1) ....

    Open in new window

    this can continue infinite
    LVL 3

    Expert Comment

    but the query depends much on what you want to obtain,
    if you have problems on getting exact data, post the conditions you have
    and we can convert it to sql condition
    LVL 26

    Accepted Solution

    Just so you don't actually attack soemthing like this in Access and wonder why it dosn't work
    Datefield > #12/1/10#
    Access needs date parameters in its SQL enclosed in hashes.
    REGION = "NY"
    Access needs string parameters in its SQL enclosed in quotes

    Now, the guts of your question:  It depends!
    Access and SQL Server do a lot of work behind the scenes to optimize queries.
    It used to be that only saved queries got optimized.
    Now, All SQL statements that get run multiple times get optimized.

    Much also depends on the indexes you define on the fields involved.
    Index searches are more efficient than table seeks, so the engines involved use indexes where they exist and it makes sense.
    = conditions are also easier to find than inequalities which are easiler than LIKE conditions
    Numeric data is easier to search than text.
    AND conditions have to exist and are likely to run first automatically over OR conditions.

    So, there are really to0 many variables for me to say how Access would optimize your query.
    But, run it more than two or three times and it will run as fast as it possibly can, and Access will look at each run thereafter to see whether it's optimization may need to be updated.

    About the only thing left that remains to the develper to optimize manually is putting indexes on fields used in WHERE clauses that don't participate in Primary key / foriegn key relationships
    (Access adds those indexes automatically)

    Hope this helps

    Author Comment

    I'm not asking how to write a specific query. I know how to do that. I just wanted to know if all conditions are checked for each record or if it skips to the next record once one condition is not met (presuming just this example that you're using all AND statements in a WHERE clause)
    LVL 26

    Expert Comment

    On something like SQL Server Express Edition, you can open the estimated query execution plan.
    That will give you an idea of what the engine is going to do.

    Let's say from your example that Datefield, DollarAmount and Region are all indexed.
    Early this year there were a dozen records after that date you mentioned.
    There were 10,000 records under $100
    There were 600 from NY

    Because everything is indexed, the engine would grab the indexes for records where the date > 12/1/10
    It then would grab PK's from that first batch those from NY, and then from the second batch it'd pull the full records that matched < $100.

    Later in the year there are 2000 records > 12/1/10
    There are 10500 records < $100 and 610 from NY.

    The engine would change the plan auto-magically, pull indexes for NY, and then narrow down the other two.

    Ultimately, where SQL used to be an instruction set that told an dumb engine how to grab a set of records, it is now a set of conditions that the data a smart engine retrieves had to meet.  Where before writing stupid SQL impacted performance, now unless you use stuff like 'use hints' idiotically, the engine will typically figure out the most efficient to deliver a dataset.

    The only major tuning left to do, like I had said earlier, is adding indexes to fields used in WHERE clauses that don't already participate in PK/FK relationships

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now