Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    654
    Thanks
    24
    Thanked 2 Times in 2 Posts

    Query efficiency

    I have an Access 2010 query with a set of criteria like this.

    Code A
    ----------
    WHERE Field1 = null AND Field2 = 1 AND Field3 > 0 AND Field4 Between 1 And 2 AND Field5 <> 1234 AND Field 5 <> 5678
    AND
    (
    (Field6 = 1 AND Field7 Not In SELECT[Test]FROM[Query2])
    OR
    (Field6 = 2 AND Field8 Not In SELECT[Test]FROM[Query2])
    )
    ----------

    I normally draft queries using Design view, so because of the OR between the last two criteria, I had to use two rows, with the first six criteria duplicated in both rows, like this.

    Code B
    ----------
    Row 1: WHERE Field1 = null AND Field2 = 1 AND Field3 > 0 AND Field4 Between 1 And 2 AND Field5 <> 1234 AND Field 5 <> 5678
    AND Field6 = 1 AND Field7 Not In SELECT[Test]FROM[Query2]

    Row 2: WHERE Field1 = null AND Field2 = 1 AND Field3 > 0 AND Field4 Between 1 And 2 AND Field5 <> 1234 AND Field 5 <> 5678
    AND Field6 = 2 AND Field8 Not In SELECT[Test]FROM[Query2]
    ----------

    The query worked OK but took several minutes to run, so in SQL view, I tried replacing the WHERE statement that Access had generated (i.e., as in Code B, with the first six criteria duplicated for Fields 1-5) with the condensed version (i.e., as in Code A, without those criteria duplicated), and it only took a few seconds to run.

    However, if I enter and save the condensed version in SQL view, then the entered version without the duplication is saved, but if I save after changing to Design view, then Access regenerates its own version with the duplication, and it runs slow again. Is this normal, and why the big difference in execution time?
    Last edited by Murgatroyd; 2016-06-17 at 21:54.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,421
    Thanks
    1
    Thanked 31 Times in 31 Posts
    The first problem you have is with "WHERE Field1 = null"; this needs to be "WHERE Field1 Is Null. Any expression such as Field1 = Null equates to Null and not True/False; it doesn't matter what Field1 is.

    The only explanation is that to present the the query in the query design view, Access must create the Rows 1 and 2 you showed; but apparently it isn't good at putting them back into the original SQL Where clause.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2010
    Location
    Fort McMurray, Alberta, Canada
    Posts
    501
    Thanks
    37
    Thanked 53 Times in 51 Posts
    The general explanation is queries go through a query engine, which decides how to execute the statement. Every year the query engines get upgraded to be smarter and more sophisticated. Every year it's a bad idea to expect too much of them.

    It's the same deal with query wizards. If you manually edit an SQL statement, sometimes the wizards will understand what you've done, but often they won't. The wizards are great at creating SQL if you only use the wizard, but will frequently get confused and upset by changes that didn't originate with the wizard itself. I'd suggest that Access is trying to resolve this confusion by regenerating your SQL.

    It's been a long time since I've done much with Access. In SQL Server you can ask the database engine for it's Query Plan. This is a graphical chart of how the database intends to execute a specific query. All query performance issues tend to go through this tool. Whenever there is a clear A-B comparison (one query form is fast, another query form is slow), the answers can always be found in the query plan.

    Sample Query Execution Plan.PNG

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    654
    Thanks
    24
    Thanked 2 Times in 2 Posts
    Thanks for your replies. I see how this reflects the ability of Access to display the code in different views.

    Quote Originally Posted by MarkLiquorman View Post
    The first problem you have is with "WHERE Field1 = null"; this needs to be "WHERE Field1 Is Null.
    Yes the actual code has "Is Null", I just used "=" and "<>" as shorthand throughout my example, sorry for any confusion there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •