• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

General SQL question

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.
1 Solution
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)
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
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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.
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
dbfromnewjerseyAuthor Commented:
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)
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now