SQL瓶颈，如何修复
这与我之前的帖子相关：SQL 查询大约需要 10 - 20 分钟
不过，我有点想通了这个问题。问题（如上一个线程中所述）不是插入（虽然它仍然很慢），问题是循环数据本身 考虑以下代码：
Dim rs As DAO.Recordset
Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single
Set rs = CurrentDb().QueryDefs("select-all").OpenRecordset
MsgBox "All records retreived"
sngStart = Timer
Do While Not rs.EOF
rs.MoveNext
Loop
sngEnd = Timer
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.
MsgBox ("The query took " & sngElapsed _
& " seconds to run.")
正如您所看到的，这个循环什么也不做。您预计它会在几秒钟内完成，但运行大约需要 857 秒（或 15 分钟）。我不知道为什么这么慢。也许是lotusnotes sql 驱动程序？
还有其他想法吗？ （基于java的解决方案，任何其他解决方案）
我的目标是：从远程服务器获取所有数据并插入到本地访问表中
This is related to my previous thread: SQL Query takes about 10 - 20 minutes
However, I kinda figured out the problem. The problem (as described in the previous thread) is not the insert (while its still slow), the problem is looping through the data itself
Consider the following code:
Dim rs As DAO.Recordset
Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single
Set rs = CurrentDb().QueryDefs("select-all").OpenRecordset
MsgBox "All records retreived"
sngStart = Timer
Do While Not rs.EOF
rs.MoveNext
Loop
sngEnd = Timer
sngElapsed = Format(sngEnd - sngStart, "Fixed") ' Elapsed time.
MsgBox ("The query took " & sngElapsed _
& " seconds to run.")
As you can see, this loop does NOTHING. You'd expect it to finish in seconds, however it takes about 857 seconds to run (or 15 minutes). I dont know why it is so slow. Maybe the lotusnotes sql driver?
any other ideas? (java based solution, any other solution)
What my goal is: To get all the data from remote server and insert into local access table
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
此文档包含一些有关性能的信息调音在 NotesSQL 中。如果您还没有这样做，请从 Notes 视图而不是 Notes 表单中选择数据。然后，NotesSQL 将利用视图中的索引来加快查询速度。您可能需要在 Notes 数据库中创建视图，但性能优势将使其值得。
This document has some information about performance tuning in NotesSQL. If you aren't already, select your data from Notes Views instead of Notes Forms. NotesSQL will then leverage the indexes within the views for faster queries. You may need to create the view in the Notes database, but the performance benefit will make it worthwhile.
我的建议是您创建一个传递查询来从远程服务器获取数据。然后创建一个使用上述查询作为其源的 Make Table 查询。然后，您的函数将被简化为对第二个查询的调用。
My recommendation is that you create a Pass-Through query that will get the data from the remote server. Then create a Make Table query that uses the aforementioned query as its source. Your function then would be simplified to a call to this second query.
该循环并没有“什么都不做”，它正在调用 MoveNext，这可能会做很多事情。
The loop isn't doing "nothing" it's calling MoveNext, which is potentially doing A LOT.