慢查询 [英] slow query
问题描述
我的问题：
我遇到问题需要花费太长时间才能运行;查询
没有任何标准只评估650条记录需要300秒才能运行（通过网络运行
。本地驱动器需要120秒）。
设置：
我正在运行Access 97非开发人员版。我没有其他工具可以使用，也没办法改变它=（。我的数据库已经编译好了，而且b / b
驻留在网络驱动器上。数据库尚未拆分因为我没有工具来有效地将b / b $ b重新分配到超过350个（偶尔）用户的前端。
前后端
每次我做出更改。尽管数据库目前仅保存了
650条记录，但预计它将包含大约10,000条记录后
已经上涨运行了几个月。（IE，我的问题会变得更糟糕，而不是更好）。这个查询的结果以只读
连续格式显示。
我已经将98％的问题分解为一个计数器例程我在查询中调用了
。这个例程记下并显示数字在
日期范围减去周末和公司假期的日子。目的是
显示'a'的总年龄'事件''（一个记录），它在当前部门的时间长度
，以及当前所有者已经拥有的时间长度'一个事件。禁用这些计数器，
查询在5秒内运行（这是完全可以接受的）。我需要
获取此查询以显示我需要的所有数字，不超过
10秒。
当我发现与A97一起提供的DateDiff功能时，我无可奈何地从另一篇文章中借用了另一篇文章作为一个
解决方法（遗憾的是，我不记得他的名字）了
无法正常计算
平日。 （显然，在
A97的DateDiff函数中的w参数不会，也没有，也不会正常运行）
有问题的代码是贴在下面。我一直在寻找方法来稍微调整一下，并设法通过将SET db和RST语句移动到一个单独的公共场所来将性能提高40％运行
的函数只运行一次，而不是每次循环运行。但是它仍然是太慢了。
此代码在两个日期跨度之间每天迭代一次，并且
递增a如果当前评估的日期不是周末或公司假期，则计数器。
公共函数srcWorkingDays2（StartDate As Date，EndDate As Date）As
Double
Dim intCount As Double
Dim RST As Recordset
Dim db As Database
设置db = CurrentDb
设置RST = db.OpenRecordset（" SELECT [HolidayDate] FROM tblHolidays"，
dbOpenSnapshot）
intCount = 0
做StartDate< EndDate
RST.FindFirst" [HolidayDate] =＃" &安培; StartDate& "＃"
如果是WeekDay（StartDate）<> vbSunday和WeekDay（StartDate）<> vbSaturday
然后
如果是RST.NoMatch那么
intCount = intCount + 1
结束如果
结束如果
StartDate = StartDate + 1
循环
srcWorkingDays2 = intCount
结束函数
这是查询中的一个表达式的例子，它调用
这个函数：
TOTDaysOpen：
srcworkingdays2（格式$（[currentTOTdate]，" mm / dd / yyyy"），IIf（IsNull（[dateclosed]），Date（），Format $ （[dateclosed]，mm / dd / yyyy）））
代码VB部分的一个大问题区域似乎是FindFirst
动作。评论计算的假期部分
将查询时间减少60％。我还注意到，每次用户点击我的搜索输入屏幕上的搜索按钮时，Access似乎都会运行查询四次完成。
按钮。这似乎是一些内部Jet
工作，我不知道如何制止它。如果这可能会停止，那将会使查询时间减少75％。
但是，即使进行了这两项改进，查询时间也会
仍然远远超过10秒大关。我想我需要的是在显示结果的连续表单上的一些分页
。如果我
可以限制这个，这样它一次只计算12条记录，
查询时间会好得多。
我怎么可能不知道如何设置它。
任何你可能有的任何提示或建议都会非常感激。
（[currentTOTdate]，" mm / dd / yyyy"），IIf（IsNull（[dateclosed]），Date（），Format
（[dateclosed]，" mm / dd / yyyy"）））
代码的VB部分中的一个大问题区域似乎成为FindFirst
行动。评论计算的假期部分
将查询时间减少60％。我还注意到，每次用户点击我的搜索输入屏幕上的搜索按钮时，Access似乎都会运行查询四次完成。
按钮。这似乎是一些内部Jet
工作，我不知道如何制止它。如果这可能会停止，那将会使查询时间减少75％。
但是，即使进行了这两项改进，查询时间也会
仍然远远超过10秒大关。我想我需要的是在显示结果的连续表单上的一些分页
。如果我
可以限制这个，这样它一次只计算12条记录，
查询时间会好得多。
我怎么可能不知道如何设置它。
任何你可能有的任何提示或建议都会非常感激。
2005年12月28日15:04:11 -0800，一个*********** @ bcbsmn.com 写道：
然而，即使有这两项改进，查询时间也是如此将继续超过10秒大关。我想我需要的是在连续表单上显示结果的一些分页。如果我可以限制这个，那么它一次只计算12条记录，
查询时间会好得多。
我不是最模糊的想法我怎么样可能会对此进行设置。
你们中的任何提示或建议都会非常感激。
带你的srcWorkingDays2功能一起查询并从连续形式的文本框中调用它。
假设你有连续形式的文本框，名为StartDate和EndDate，添加一个文本框，因为它''控制
源码放置 -
= srcWorkingDays2（[StartDate]，[EndDate]）
只调用该函数对于当前以连续形式显示的记录。然而，你可以在评估每条记录时获得轻微的b $ b b延迟，但这通常是微不足道的。
Wayne Gillespie
澳大利亚戈斯福德澳大利亚
My problem:
I''m having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive takes 120 seconds).
The Setup:
I''m running Access 97 non-developer edition. I have exactly zero other
tools to use and no way to change that =(. My database is compiled and
resides on a network drive. The database has not been split into a
front and backend, as I don''t have the tools to effectively
redistribute the front end to the more than 350 (occasional) users
every time I make a change. Although the database currently holds only
650 records, it is expected to contain around 10,000 records after it
has been up and running for a few months. (I.E., my problem will get
worse, not better). The results of this query display in a read only
continuous forms format.
I''ve isolated 98% of the problem down to a counter routine I''ve called
in the query. This routine tallies and displays the number of days in a
date span minus weekends and company holidays. The purpose is to
display the total age of an ''incident'' (one record), the length of time
it has been in its current department, and the length of time its
current owner has ''owned'' an incident. With these counters disabled,
the query runs in 5 seconds, (which is perfectly acceptable). I need to
get this query to display all the numbers I need it to in no more than
10 seconds.
The routine itself I shamelessly borrowed from another article as a
workaround (regrettably, I don''t remember his name) when I discovered
that the DateDiff function provided with A97 couldn''t correctly compute
weekdays only. (Apparently the "w" argument in the DateDiff function of
A97 does not, has not, nor ever will function correctly)
The problematic code is pasted below. I''ve been looking for ways to
tweak it a bit, and managed to increase performance by 40% by moving
the SET db and RST statements to a separate public function that runs
only once rather than each and every time the loop runs. It is still
too slow though.
This code iterates one time for every day between two date spans and
increments a counter if the date it is currently evaluating is not a
weekend or company holiday.
Public Function srcWorkingDays2(StartDate As Date, EndDate As Date) As
Double
Dim intCount As Double
Dim RST As Recordset
Dim db As Database
Set db = CurrentDb
Set RST = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
intCount = 0
Do While StartDate < EndDate
RST.FindFirst "[HolidayDate] = #" & StartDate & "#"
If WeekDay(StartDate) <> vbSunday And WeekDay(StartDate) <> vbSaturday
Then
If RST.NoMatch Then
intCount = intCount + 1
End If
End If
StartDate = StartDate + 1
Loop
srcWorkingDays2 = intCount
End Function
This is an example of one of the expressions in the query that calls
this function:
TOTDaysOpen:
srcworkingdays2(Format$([currentTOTdate],"mm/dd/yyyy"),IIf(IsNull([dateclosed]),Date(),Format$([dateclosed],"mm/dd/yyyy")))
A big problem area in the VB part of the code seems to be the FindFirst
action. Commenting out just the Holidays part of the calculation
slashes query times by 60%. I''ve also noticed that Access seems to be
running the query four complete times every time a user hits the search
button on my search-input screen. This appears to be some internal Jet
workings, and I have no idea how to put a stop to it. If that could be
stopped that would slash query times by another 75%.
However, even with both of those improvements the query time would
still be well over the 10-second mark. What I''m thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.
I haven''t the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated.
([currentTOTdate],"mm/dd/yyyy"),IIf(IsNull([dateclosed]),Date(),Format
([dateclosed],"mm/dd/yyyy")))
A big problem area in the VB part of the code seems to be the FindFirst
action. Commenting out just the Holidays part of the calculation
slashes query times by 60%. I''ve also noticed that Access seems to be
running the query four complete times every time a user hits the search
button on my search-input screen. This appears to be some internal Jet
workings, and I have no idea how to put a stop to it. If that could be
stopped that would slash query times by another 75%.
However, even with both of those improvements the query time would
still be well over the 10-second mark. What I''m thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.
I haven''t the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated.
On 28 Dec 2005 15:04:11 -0800, An***********@bcbsmn.com wrote:
However, even with both of those improvements the query time would
still be well over the 10-second mark. What I''m thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.
I haven''t the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated.
Take your srcWorkingDays2 function out of the query all together and call it from a text box in the continuous form.
Assuming you have textboxes in the continuous form called StartDate and EndDate, add a textbox and as it''s control
source put -
=srcWorkingDays2([StartDate],[EndDate])
The function will only be called for records currently displayed in the continuous form. You may however get a slight
delay as each record is evaluated but this usually insignificant.
Wayne Gillespie
Gosford NSW Australia
这篇关于慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
