Query performance question

[Seth Schrock]

I have a very complex query that I use as the record source for a report. I want it to run all its calculations as little as possible. My question is, is there a difference between putting a WHERE clause in the query or putting a WhereCondition in the DoCmd.OpenRepor t command? Right now I don't have a ton of data to test this on, so I was hoping someone knew how this worked. Currently I have the query reference values on a form to provide its WHERE clause and then the report just pulls all records from the query. Now I'm running across a situation where I want to be able to run this query from another form and I don't know if I should copy my query and make just a few changes or if it would not be a performance hit to use the filter on the report.

[Rabbit]

I don't know the answer to this personally. But if your backend is on a DBMS like SQL Server, you can try both and look at the query it generates for the server to see if they are different.

[NeoPa]

It won't give a performance hit per se. The SQL is worked out and sent through to the back end after all tweaks have been applied.

If your query is a saved QueryDef then it's unlikely that will make much of a difference anyway. Their execution plans are generally saved with them once they've been run, but when SQL is updated in any way then these are ignored anyway and a new one worked out for the updated SQL.

So, in theory, you should see no difference when applying extra filtering to a report.

[hvsummer]

@Seth Schrock:

As I know and in theory only,
JET engine query data only if "Where conditions" is true at first (this is Pre-Process)

And I assumpt that Extra filter (Where condition) in DoCmd.OpenRepor t equal to "Having" in group query, mean that process is post process, JET engine still have to query all data before apply that filter.

so If you use where condition in query first, JET will know where it have to query (reduce memory load and faster execute)--> better performance

[Seth Schrock]

As it happens, my database is based on a SQL server BE so I will follow Rabbit's suggest and look at the query as it hits the server and test the different theories provided. I'll post back what I find out.

[Rabbit]

This is a query I use on SQL Server to identify queries that are blocking other queries. I have commented out the WHERE clause which will make it return all running queries.

SELECT 
	u.login_name AS blocked_person, 
	r.start_time AS blocked_start_time,
	h.text AS blocked_text,
	u2.login_name AS blocking_person,
	r2.start_time AS blocking_start_time,
	h2.text AS blocking_text

FROM 
	sys.dm_exec_requests r
	
	INNER JOIN sys.dm_exec_sessions u
		ON r.session_id = u.session_id
		
	LEFT JOIN sys.dm_exec_sessions u2
		ON r.blocking_session_id = u2.session_id
		
	LEFT JOIN sys.dm_exec_requests r2
		ON r.blocking_session_id = r2.session_id
		
	CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
		
	OUTER APPLY sys.dm_exec_sql_text(r2.sql_handle) h2
	
--WHERE r.blocking_session_id <>0
	
ORDER BY
	ISNULL(r.blocking_session_id, 32767)

[NeoPa]

HVSummer:
so If you use where condition in query first, JET will know where it have to query (reduce memory load and faster execute)--> better performance

I don't believe that's correct. Read my earlier post for how this is processed.

[Rabbit]

You can also turn on SQL tracing for all odbc connections that access uses in the registry. This will log the exact SQL sent to the SQL server for everything that happens in the access front end.

https://msdn.microsoft.com/en-us/library/bb177636(v=office.12).aspx

[hvsummer]

@NeoPa: since Seth is using SQL server, My suggestion can't apply for, only right in Access which using JET engine.

about SQL code, I still think apply the filter first in "where" then the total rows SQL have to query and calculate will smaller than apply filter after SQL query and calculate all data first.

the smaller data calculate, the better perfomance. I believe in this theory.

[NeoPa]

@HVSummer.
The concept of filtering as much as possible is right my friend. I may not have understood the rest of it well as your English, while being much better than my use of your language, is still a little hard to follow sometimes.

With Jet/ACE, as well as SQL Server (except for when using pass-thru queries), Access will work out a resultant set of SQL from what it knows before applying it. Even if a QueryDef is used it will determine the SQL from there and mix it up with any Form or Report filtering before it is applied. It is clever enough not to have to use a multi-step process as we might imagine from our human perspective.

Even with non-Jet/ACE back ends much of the work can be done by Access if using ODBC or similar connections. All that it can't do really is fiddle around with Pass-Thru SQL, as that is native to the BE system.

[hvsummer]

@NeoPa:

in this link, those last sections, "query optimization" that I read

http://allenbrowne.com/QueryPerfIssue.html

[Rabbit]

@Seth, here is an article from Microsoft about optimizing performance between Access and SQL Server.

https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx

I have been reading it in an effort to resolve my issue. And while it has been fruitless so far, it still seems like a very good article if you are experiencing performance issues.

[NeoPa]

HVSummer:
in this link, those last sections, "query optimization" that I read

I disagree with nothing Allen says in his article. I would point out though, that where he says :

Allen Browne:
Totals queries (those with a GROUP BY clause) can have both a WHERE clause and a HAVING clause. The WHERE is executed first - before aggregation; the HAVING is executed afterwards - when the totals have been calculated. It makes sense, then, to put your criteria in the WHERE clause, and use the HAVING clause only when you must apply criteria on the aggregated totals.

The underlined part is very important. this is not talking about applying a Form or Report on top of the original query, but is referring specifically, and only, to aggregate queries (Those using GROUP BY).