如何激活一个极慢的查询 [英] How to Energize a Dreadfully Slow Query

问题描述

必须有一种方法可以增强查询的性能，或找到一个似乎合理的解决方法，但我似乎正在碰壁。我已经尝试了一些

的调整，但是，没有任何改进。


简单地说，我从一个单独的表/查询中包含一个计算。它

总计按产品ID编号销售的总单位数，并用于

其他选择查询以执行各种计算。也许

在使用数据库中使用的最大数量的记录以便充分评估实际情况时具有优势。

在这种情况下，有近10,000条记录。


我尝试过DSum和一个函数SumUnits（ProductID），但是

的表现要么几乎都一样。在完成之前点击排序或过滤

表格上的任何计算列后，大约需要25秒

- yuk。尝试了一个Totals查询以检查

的速度和瞧，只需几秒钟，但当然，它并不是
关于Totals查询的方式和位置因为它渲染任何其他

选择查询不可更新。


考虑过使用更新查询存储''计算值''

作为替代方案，但该方法通常是boohooed。但即使是这样的路线，每次打开

表格以刷新数字时仍然需要至少25秒，但当然，一旦打开任何

求助或重新过滤将是即时的。


查询的所有其他计算都是非常基本的，没有DSum'，

没有DLookup等等。通过暂时删除上面的UnitsSold

字段，性能不到一秒。任何建议都将是

欢迎。


谢谢，Rolan

There must be a way to enhance the performance of a query, or find a
plausible workaround, but I seem to be hitting a wall. I have tried a
few tweaks, however, there has been no improvement.

Simply, I''m including one calcualtion from a separate table/query. It
sums the total units sold to date by ProductID number and is used in
other select queries to perform various calculations. Perhaps there is
an advantage in working with a maximum number of records that would be
used in the database in order to fully evaluate real-world conditions.
In this case, there are nearly 10,000 records.

I have tried DSum and a Function SumUnits(ProductID), but the
performance of either is nearly the same. It takes about 25 seconds
after clicking sort or filter on any of the calculated columns on the
form before it is completed - yuk. Tried a Totals query to check the
speed and voila, just a couple of seconds, but of course, it doesn''t
matter how or where a Totals query is used as it renders any other
select query non-updatable.

Have thought about using an Update query to store a ''calculated value''
as an alternative, but that method is generally boohooed. But even
going that route, it would still take at least 25 seconds everytime the
form is opened to refresh the numbers, but of course, once opened any
resorting or refiltering would be instant.

All of the other calculations on the query are very basic, no DSum''s,
no DLookup''s, etc. And by temporarily removing the UnitsSold to date
field above, performance is under a second. Any suggestions will be
welcomed.

Thanks, Rolan

推荐答案

您可以通过w / sum从代码中打开记录集，并创建一个

函数，该函数使用FindFirst从打开的记录集中获取正确的总和。


2005年1月28日07:03:37 -0800，Rolan <共****** @ safe-mail.net>写道：

You could open a recordset from code that is the group by w/ sum, and create a
function that uses FindFirst to get the correct sum from the open recordset.

On 28 Jan 2005 07:03:37 -0800, "Rolan" <co******@safe-mail.net> wrote:

必须有一种方法来增强查询的性能，或找到一个似乎合理的解决方法，但我似乎正在碰壁。我尝试过几次调整，然而，没有任何改进。

简单地说，我要从一个单独的表/查询中包含一个计算。它按产品ID编号总计销售的总单位数，并用于其他选择查询以执行各种计算。也许在处理数据库中使用的最大数量的记录方面有一个优势，以便全面评估现实条件。
在这种情况下，有将近10,000个记录。

我尝试过DSum和一个函数SumUnits（ProductID），但两者的性能几乎相同。在完成之前单击排序或过滤
表单上的任何计算列后，大约需要25秒
- yuk。尝试了一个Totals查询以检查
速度和瞧，只需几秒钟，但当然，它并不重要使用Totals查询的方式或位置，因为它会渲染任何其他的
选择查询不可更新。

考虑使用更新查询存储计算值作为替代，但该方法通常是boohooed。但是即使走这条路，每次打开
表单至少需要25秒来刷新数字，但当然，一旦打开任何手段或重新过滤将是即时的。

查询的所有其他计算都是非常基本的，没有DSum'，没有DLookup'等等。暂时删除UnitsSold
以上领域，表现不到一秒。欢迎任何建议。

谢谢，Rolan

There must be a way to enhance the performance of a query, or find a
plausible workaround, but I seem to be hitting a wall. I have tried a
few tweaks, however, there has been no improvement.

Simply, I''m including one calcualtion from a separate table/query. It
sums the total units sold to date by ProductID number and is used in
other select queries to perform various calculations. Perhaps there is
an advantage in working with a maximum number of records that would be
used in the database in order to fully evaluate real-world conditions.
In this case, there are nearly 10,000 records.

I have tried DSum and a Function SumUnits(ProductID), but the
performance of either is nearly the same. It takes about 25 seconds
after clicking sort or filter on any of the calculated columns on the
form before it is completed - yuk. Tried a Totals query to check the
speed and voila, just a couple of seconds, but of course, it doesn''t
matter how or where a Totals query is used as it renders any other
select query non-updatable.

Have thought about using an Update query to store a ''calculated value''
as an alternative, but that method is generally boohooed. But even
going that route, it would still take at least 25 seconds everytime the
form is opened to refresh the numbers, but of course, once opened any
resorting or refiltering would be instant.

All of the other calculations on the query are very basic, no DSum''s,
no DLookup''s, etc. And by temporarily removing the UnitsSold to date
field above, performance is under a second. Any suggestions will be
welcomed.

Thanks, Rolan

2005年1月28日07： 03:37 - 0800，Rolan <共****** @ safe-mail.net>写道：


嗨

不确定我是不是这样，但如果总计查询如此之快，那么你不能给你带来
从中创建一个临时表并使用它来使主要的

查询/查询可更新？

David

On 28 Jan 2005 07:03:37 -0800, "Rolan" <co******@safe-mail.net> wrote:

Hi
Not sure I unerstand this but if the totals query is so fast, can''t
you make a temporary table from it and use this to make the main
query/queries updateable?
David

好吧，让我们考虑一下你的表单设计。如果你有一个连续的样式

形式，其中一个控件是一个域函数（DSum，DCount等），

然后运行一个计算该值的查询每个记录一次。当你发现了
时，加载许多记录时可能需要一些时间。除了

（可能不是那样）显而易见的帮助，你搜索的任何/大多数字段索引或

排序，我想你可能会考虑从
连续详细信息部分，并将其移动到弹出窗体，用户可以按需查看
。在主窗体的页眉/页脚中放置一个命令按钮，

将打开该窗体，并带有指向当前详细记录的条件链接。

-Ed


" Rolan" <共****** @ safe-mail.net>在消息中写道

news：11 ********************** @ c13g2000cwb.googlegr oups.com ...

Well, let''s think about your form design. If you have a continuous style
form where one of the controls is a Domain function (DSum, DCount, etc.),
then a query to calculate that value is run once for each record. As you''ve
discovered, this can take some time, when loading many records. Besides the
(perhaps not so) obvious help that indexing on any/most fields you search or
sort, I think you might consider removing the calculated value from the
continuous detail section, and moving it to a pop-up form that users could
view on demand. Put a command button in the main form''s header/footer that
would open that form, with a criteria link to the current detail record.
-Ed

"Rolan" <co******@safe-mail.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

必须有一种方法来增强查询的性能，或找到一个似乎合理的解决方法，但我似乎正在碰壁。我尝试过几次调整，然而，没有任何改进。

简单地说，我要从一个单独的表/查询中包含一个计算。它按产品ID编号总计销售的总单位数，并用于其他选择查询以执行各种计算。也许在处理数据库中使用的最大数量的记录方面有一个优势，以便全面评估现实条件。
在这种情况下，有将近10,000个记录。

我尝试过DSum和一个函数SumUnits（ProductID），但两者的性能几乎相同。在完成之前单击排序或过滤
表单上的任何计算列后，大约需要25秒
- yuk。尝试了一个Totals查询以检查
速度和瞧，只需几秒钟，但当然，它并不重要使用Totals查询的方式或位置，因为它会渲染任何其他的
选择查询不可更新。

考虑使用更新查询存储计算值作为替代，但该方法通常是boohooed。但是即使走这条路，每次打开
表单至少需要25秒来刷新数字，但当然，一旦打开任何手段或重新过滤将是即时的。

查询的所有其他计算都是非常基本的，没有DSum'，没有DLookup'等等。暂时删除UnitsSold
以上领域，表现不到一秒。欢迎任何建议。

谢谢，Rolan

There must be a way to enhance the performance of a query, or find a
plausible workaround, but I seem to be hitting a wall. I have tried a
few tweaks, however, there has been no improvement.

Simply, I''m including one calcualtion from a separate table/query. It
sums the total units sold to date by ProductID number and is used in
other select queries to perform various calculations. Perhaps there is
an advantage in working with a maximum number of records that would be
used in the database in order to fully evaluate real-world conditions.
In this case, there are nearly 10,000 records.

I have tried DSum and a Function SumUnits(ProductID), but the
performance of either is nearly the same. It takes about 25 seconds
after clicking sort or filter on any of the calculated columns on the
form before it is completed - yuk. Tried a Totals query to check the
speed and voila, just a couple of seconds, but of course, it doesn''t
matter how or where a Totals query is used as it renders any other
select query non-updatable.

Have thought about using an Update query to store a ''calculated value''
as an alternative, but that method is generally boohooed. But even
going that route, it would still take at least 25 seconds everytime the
form is opened to refresh the numbers, but of course, once opened any
resorting or refiltering would be instant.

All of the other calculations on the query are very basic, no DSum''s,
no DLookup''s, etc. And by temporarily removing the UnitsSold to date
field above, performance is under a second. Any suggestions will be
welcomed.

Thanks, Rolan

这篇关于如何激活一个极慢的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！