查询性能问题 [英] Query performance question

问题描述

我有一个非常复杂的查询，我用它作为报告的记录源。我希望它尽可能少地运行所有计算。我的问题是，在查询中放置WHERE子句或在DoCmd.OpenReport命令中放置WhereCondition之间有区别吗？现在我没有大量的数据来测试这个，所以我希望有人知道这是如何工作的。目前我在表单上有查询引用值来提供其WHERE子句，然后报表只从查询中提取所有记录。现在我正在遇到一种情况，我希望能够从另一个表单运行此查询，我不知道是否应该复制我的查询并进行一些更改，或者它是否会影响性能在报告上使用过滤器。

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.

推荐答案

我个人并不知道答案。但是如果你的后端是在像SQL Server这样的DBMS上，你可以尝试两者并查看它为服务器生成的查询，看它们是否不同。

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.

它不会给性能打击本身。在完成所有调整后，SQL会被计算出来并发送到后端。


如果你的查询是一个保存的QueryDef那么它不太可能会产生很大的影响。不管怎么说。一旦它们运行，它们的执行计划通常会随之保存，但是当SQL以任何方式更新时，无论如何都会忽略这些计划，并为更新的SQL创建一个新的计划。


所以，理论上，你应该看到在对报告中应用额外过滤时没有区别。

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.

@Seth Schrock：


据我所知并且仅在理论上，

JET引擎查询数据仅在Where conditions的情况下。一开始是真的（这是预处理）


我认为DoCmd.OpenReport中的Extra filter（Where condition）等于Having在组查询中，意味着进程是后进程，JET引擎仍然必须在应用该过滤器之前查询所有数据。


so如果你在查询中使用where条件，JET将知道在哪里它必须查询（减少内存负载和更快执行） - >更好的表现

@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

这篇关于查询性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！