Query efficiency

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

44797-Sample-Query-Execution-Plan

Reply | Quote

Thanks for your replies. I see how this reflects the ability of Access to display the code in different views.

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.

Reply | Quote