ADO参数查询和存储过程 - 以及Connections [英] ADO parameters queries and stored procedures - and Connections
问题描述
我使用以下代码将记录集检索到两个本地表中，
来自WAN上的mdb文件。工作正常，但我想调整速度，
我有一些问题：
1.我尝试了两个ADOX创建的存储程序和常规访问
参数查询。经过几次测试，我得出的结论是，两个人之间没有统计差异。这是否有意义，或者我可以做一些不同的事情来加快速度（两个查询只是
标准三表连接选择带一个参数的查询）？
2.当我在第三个记录集中打开下面的两个调用，在用户
表上打开时，这会加快速度 - 换句话说，当记录集rs是
已关闭并重新打开，与WAN mdb的连接丢失，必须重新建立
- 这是显而易见的广域网。有没有其他方式
来保持连接打开...为什么关闭记录集关闭它？
3.还有其他建议吗？
-------------------------------------------- ---------------
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2作为ADODB.Recordset
Dim prm1作为ADODB.Parameter
Dim cmd1作为ADODB.Command
设置rs =新ADODB.Recordset
设置rs2 =新ADODB.Recordset
设置cnxn =新ADODB.Connection
设置cmd1 =新ADODB.Command
cnxn.Provider =" Microsoft.Jet.OLEDB.4.0"
cnxn.Open" Data Source =" &安培; GetServer（）
设置prm1 = cmd1.CreateParameter（" PO"，adVarChar，adParamInput，
Len（GetSelectedPO（）））
prm1.Value = GetSelectedPO（）
使用cmd1
.ActiveConnection = cnxn
.CommandText =" HomeInvestigationPQuery"
.CommandType = adCmdStoredProc
.Parameters.Append prm1
结束
rs2.Open" HomeInvestigationTable"，CurrentProject.Connection，adOpenKeyset，
adLockOptimistic，adCmdTable
with rs
。打开cmd1 ,, adOpenForwardOnly，adLockReadOnly，adCmdStoredProc
请勿.EOF
rs2.AddNew
对于f = 0到5
rs2.Fields（f） = .Fields（f）
下一页f
rs2.Update
.MoveNext
循环
。关闭
rs2.Close
rs2.Open" HomeCaseloadTable"，CurrentProject.Connection，
adOpenKeyset ，adLockOptimistic，adCmdTable
cmd1.CommandText =" HomeCaseloadPQuery"
。打开cmd1 ,, adOpenForwardOnly，adLockReadOnly，adCmdStoredProc
Do While Not .EOF
rs2.AddNew
对于f = 0到2
rs2.Fields（f）= .Fields（f）
下一个f
rs2.Update
.MoveNext
循环
。关闭
结束用
rs2.Close
cnxn.Close
设置cmd1 =无什么
设置prm1 = Nothing
-
Darryl Kerkeslager
电力腐败。
绝对电力完全腐败。
知识就是力量。
见 www.adcritic.com/interactive/view.php?id=5927
I use the following code to retrieve to recordsets into two local tables,
from an mdb file over a WAN. Works fine, but I''m trying to tweak the speed,
and I have some questions:
1. I tried both an ADOX-created stored procedure and a regular Access
parameter query. After several tests, I came to the conclusion that there
was no statistcal difference between the two. Does this make sense, or
could I do something different to speed it up (the two queries are just
standard three-table joined Select queries with one parameter)?
2. When I wrap the two calls below in a third recordset, opened on the user
table, this speeds things up - in other words, when the recordset ''rs'' is
closed and re-opened, the connection to the WAN mdb is lost, and has to be
re-established - which is noticeable over the WAN. Is there any other way
to keep the connection open ... and why does closing the recordset close it?
3. Any other suggestions?
-----------------------------------------------------------
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim cmd1 As ADODB.Command
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set cnxn = New ADODB.Connection
Set cmd1 = New ADODB.Command
cnxn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxn.Open "Data Source=" & GetServer()
Set prm1 = cmd1.CreateParameter("PO", adVarChar, adParamInput,
Len(GetSelectedPO()))
prm1.Value = GetSelectedPO()
With cmd1
.ActiveConnection = cnxn
.CommandText = "HomeInvestigationPQuery"
.CommandType = adCmdStoredProc
.Parameters.Append prm1
End With
rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
rs2.Close
rs2.Open "HomeCaseloadTable", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic, adCmdTable
cmd1.CommandText = "HomeCaseloadPQuery"
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 2
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
End With
rs2.Close
cnxn.Close
Set cmd1 = Nothing
Set prm1 = Nothing
--
Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
推荐答案
你真的必须使用记录集？你能用两个更新
查询吗？
如果记录集那么......
处理ADODB记录集时经常这样打开它们更快，打开它们更快，打开它们，断开它们（Set .ActiveConnection = Nothing），
做我们的工作，重新连接它们（Set .ActiveConnection =
YourConnectionObject）并进行批量更新。 TTBOMK批量更新是异步的
，所以我们可以在它工作的时候去做其他工作。
它没有必要关闭和释放ADO对象指针。
Do you really have to use recordsets? Can you just use two Update
Queries?
If recordsets then ...
Quite often when dealing with ADODB recordsets it''s more efficient and
faster to open them, disconnect them (Set .ActiveConnection = Nothing),
do our work, reconnect them (Set .ActiveConnection =
YourConnectionObject) and do a batch update. TTBOMK the batch update is
asynchronous, so we can go about doing something else while it does its
work.
It''s unnecessary to close and release ADO object pointers.
2005年7月25日19:09:51 -0700，lylefair < LY ****** @ yahoo.ca>写道：
On 25 Jul 2005 19:09:51 -0700, "lylefair" <ly******@yahoo.ca> wrote:
你真的必须使用记录集吗？你能不能只使用两个更新
查询？
如果是记录集那么......
在处理ADODB记录集时，它更有效率，而且更快打开它们，断开它们（设置.ActiveConnection = Nothing），
完成我们的工作，重新连接它们（Set .ActiveConnection =
YourConnectionObject）并进行批量更新。 TTBOMK批处理更新是异步的，因此我们可以在其工作时继续做其他事情。
关闭和释放ADO对象指针是不必要的。
Do you really have to use recordsets? Can you just use two Update
Queries?
If recordsets then ...
Quite often when dealing with ADODB recordsets it''s more efficient and
faster to open them, disconnect them (Set .ActiveConnection = Nothing),
do our work, reconnect them (Set .ActiveConnection =
YourConnectionObject) and do a batch update. TTBOMK the batch update is
asynchronous, so we can go about doing something else while it does its
work.
It''s unnecessary to close and release ADO object pointers.
实际上，如果你将记录集打开为静态，批量乐观，那么断开连接就没有好处。在您进行批量更新或断开连接之前，它不会再与数据库通信
。
断开连接的唯一原因是你真的想要关闭连接到conserver
资源。
Actually, if you open the recordset as static, batch optimistic, there''s no
benefit to disconnecting it. It''s not going to talk to the database again
until you do the batch update, disconnected or not. The only reason to
disconnect is if you really do want to close the connection to conserver
resources.
Lyle Fairfield写道：
Lyle Fairfield wrote:
你真的必须使用记录集吗？你能用两个Update
查询吗？
好问题。我将从后端mdb更新前端mdb，
我以前从未做过。我该怎么做？不知道BE有没有？b $ b知道FE在哪里？
如果记录集那么......
很多时候处理ADODB记录集时它会'更高效，打开它们更快，断开它们（Set .ActiveConnection = Nothing），
做我们的工作，重新连接它们（Set .ActiveConnection =
YourConnectionObject）并进行批量更新。
所以改变这个...
rs2.Open" HomeInvestigationTable"，CurrentProject.Connection，adOpenKeyset，
adLockOptimistic，adCmdTable
带rs
。打开cmd1 ,, adOpenForwardOnly，adLockReadOnly，adCmdStoredProc
虽然不是.EOF
rs2.AddNew
对于f = 0到5
rs2.Fields（f）= .Fields（f）
下一页f
rs2.Update
.MoveNext
循环
....这个？ ..
rs2.Open" HomeInvestigationTable"，CurrentProject.Connection，adOpenKeyset，
adLockBatchOptimistic，adCmdTable
使用rs
。打开cmd1，adOpenForwardOnly，adLockReadOnly，adCmdStoredProc
设置.ActiveConnection = Nothing
请勿。 EOF
rs2.AddNew
对于f = 0到5
rs2.Fields（f）= .Fields（f）
下一个f
.MoveNext
循环
设置.ActiveConnection = cnxn
rs2.UpdateBatch
没有必要关闭和释放ADO对象指针。
Do you really have to use recordsets? Can you just use two Update
Queries?
Good question. I would be updating the Front End mdb from the Back End mdb,
which I have never done before. How would I do this? Wouldn''t the BE have
to know where the FE is?
If recordsets then ...
Quite often when dealing with ADODB recordsets it''s more efficient and
faster to open them, disconnect them (Set .ActiveConnection = Nothing),
do our work, reconnect them (Set .ActiveConnection =
YourConnectionObject) and do a batch update.
So change this ...
rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.... to this? ..
rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockBatchOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Set .ActiveConnection = Nothing
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
.MoveNext
Loop
Set .ActiveConnection = cnxn
rs2.UpdateBatch
It''s unnecessary to close and release ADO object pointers.
我已停止为记录集执行此操作，但是我不确定是否同样适用于
命令和参数对象。
Darryl Kerkeslager
电力腐败。
绝对电力绝对腐败。
知识就是力量。
见 www.adcritic.com/interactive/view.php?id=5927
这篇关于ADO参数查询和存储过程 - 以及Connections的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
