473,322 Members | 1,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Query performance question

Seth Schrock
2,965 Expert 2GB
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.OpenReport 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.
Oct 19 '15 #1
12 1123
Rabbit
12,516 Expert Mod 8TB
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.
Oct 19 '15 #2
NeoPa
32,556 Expert Mod 16PB
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.
Oct 20 '15 #3
hvsummer
215 128KB
@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.OpenReport 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
Oct 20 '15 #4
Seth Schrock
2,965 Expert 2GB
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.
Oct 20 '15 #5
Rabbit
12,516 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     u.login_name AS blocked_person, 
  3.     r.start_time AS blocked_start_time,
  4.     h.text AS blocked_text,
  5.     u2.login_name AS blocking_person,
  6.     r2.start_time AS blocking_start_time,
  7.     h2.text AS blocking_text
  8.  
  9. FROM 
  10.     sys.dm_exec_requests r
  11.  
  12.     INNER JOIN sys.dm_exec_sessions u
  13.         ON r.session_id = u.session_id
  14.  
  15.     LEFT JOIN sys.dm_exec_sessions u2
  16.         ON r.blocking_session_id = u2.session_id
  17.  
  18.     LEFT JOIN sys.dm_exec_requests r2
  19.         ON r.blocking_session_id = r2.session_id
  20.  
  21.     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
  22.  
  23.     OUTER APPLY sys.dm_exec_sql_text(r2.sql_handle) h2
  24.  
  25. --WHERE r.blocking_session_id <>0
  26.  
  27. ORDER BY
  28.     ISNULL(r.blocking_session_id, 32767)
Oct 20 '15 #6
NeoPa
32,556 Expert Mod 16PB
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.
Oct 21 '15 #7
Rabbit
12,516 Expert Mod 8TB
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/lib...ffice.12).aspx
Oct 21 '15 #8
hvsummer
215 128KB
@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.
Oct 21 '15 #9
NeoPa
32,556 Expert Mod 16PB
@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.
Oct 21 '15 #10
hvsummer
215 128KB
@NeoPa:

in this link, those last sections, "query optimization" that I read
http://allenbrowne.com/QueryPerfIssue.html
Oct 22 '15 #11
Rabbit
12,516 Expert Mod 8TB
@Seth, here is an article from Microsoft about optimizing performance between Access and SQL Server.

https://technet.microsoft.com/en-us/...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.
Oct 22 '15 #12
NeoPa
32,556 Expert Mod 16PB
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).
Oct 24 '15 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Adi Schwarz | last post by:
I was asking myself what solution is better considering performance. I want to create some classes depending on a varable (for different languages). Of course, if the classes are huge I write...
2
by: inna | last post by:
Hello. I have query performance question. I need to optimize procedure CREATE PROCEDURE dbo.SECUQUSRCOMPACCES @P1 VARCHAR(50), @P2 INTEGER AS DECLARE @IORGANIZATIONID INTEGER EXECUTE...
7
by: Randell D. | last post by:
Folks, I have a Javascript performance question that I might have problems explaining... In PHP, better performance can be obtained dealing directly with a variable, as opposed to an element...
8
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run...
9
by: db2udbgirl | last post by:
Is this possible to tune this query further : Env : DB2 UDB 8.2 on AIX 5.3, Non partitioned tables Query: SELECT ETL.T00601.* FROM ETL.T00601, ETL.STG_LAAM_CARD20_BUS_ASCT_BUS_FCN where
2
by: Brian Tabios | last post by:
Hello Everyone, I have a very complex performance issue with our production database. Here's the scenario. We have a production webserver server and a development web server. Both are running...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
30
by: galiorenye | last post by:
Hi, Given this code: A** ppA = new A*; A *pA = NULL; for(int i = 0; i < 10; ++i) { pA = ppA; //do something with pA
2
by: Veeru71 | last post by:
We have the following query and is taking considerable longer time to run. select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid FROM A, B, C WHERE (B.user_type <2 and B.user_type is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.