多个记录集继续 [英] Multiple recordsets continued
问题描述
最近有关将多个记录集从存储过程返回到
ADO的讨论的启发。 （惊讶可能更准确）。我询问了如何与甲骨文完成同样的工作，并从克雷夫特先生那里轻松获得了正确的b
方向。我承诺一旦工作就会提供细节，所以
就在这里。
代码如下所示。我的下一步是将此技术构建到我的
应用程序中。我希望获得可观的性能提升。
这对我来说是全新的，我会欢迎任何建设性的批评。通过
相同的标记，我得到了它的工作，所以我很乐意尝试回答任何人可能有的任何问题。
Randy Harris
在Oracle中我创建了这个包和存储过程：
/ *一个PL / SQL书说光标对象
必须在包装中声明* /
创建或更换包装OraOLEDB AS
TYPE m_refcur是REF CURSOR;
结束OraOLEDB;
/
/ * ======================= ============ * /
/ *这个程序将返回两个REF游标* /
/ *我添加了几个IN参数证明我可以* /
创建或替换程序getrecs（
每周最后一次，
上周数字，
firstrs OUT OraOLEDB.m_refcur，
secondrs OUT OraOLEDB.m_refcur）
IS
BEGIN
/ *定义将成为记录集的第一个光标* /
OPEN firstrs FOR
SELE CT weekid，weekstart
来自weeks_tbl
WHERE weekid BETWEEN第一周和上周;
/ *现在是第二个* /
OPEN Tweers FOR
SELECT tablename，updateseq
FROM concurrence;
END getrecs;
/
这是调用SP的VBA程序：
Private Sub MultiRecs（）
Dim conn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
>
Dim cmd As New ADODB.Command
with conn
.Provider =" OraOLEDB.Oracle"
。打开数据源= &安培; OracleDB& " ;;"，dbUser，dbPass
结束
cmd.ActiveConnection = conn
cmd.Properties（" PLSQLRSet"）= True
cmd.CommandType = adCmdText
cmd.CommandText =" {Call getrecs（420,430）}"
>
''调用程序并检索游标
''创建第一个记录集
设置rst1 = cmd.Execute
虽然不是rst1.EOF
Debug.Print rst1（0），rst1（1）
rst1.MoveNext
Wend
''现在调用NextRecordset方法
设置rst2 = rst1.NextRecordset
虽然不是rst2。 EOF
Debug.Print rst2（0），rst2（1）
rst2.MoveNext
Wend
rst1.Close
rst2.Close
结束子
< blockquote> Randy Harris写道：
代码如下所示。我的下一步是将此技术构建到我的应用程序中。我希望获得可观的性能提升。
为什么获得一个由两个记录集组成的记录集会导致
大幅增加性能只需得到两个
记录集？
1.确实你会发送一个命令而不是两个，但是会是
你没有以相同的形式获得相同的数据（形成名词不是形式
访问对象）并且这些数据的传输不太可能是
整个过程的主要耗时活动;
2.在MS-SQL中我们可以在存储过程中创建和使用游标
并获得第二个记录集（假设第一个记录集仅用作第二个记录集）;这可以在Oracle中完成;
3.我们多久会编写一个会返回多个
记录集的程序，这些记录集可能会被重写为Joins以返回需要
数据;
4.如果我们连续使用
它们在内存中保存两个记录集的效率如何，也就是说，如果我们使用第一个，那么使用第二个
并且永远不会返回第一个;
4.在场结构相同的情况下工会或多个
记录集会更有效吗？
这些是我自己的闲聊，我在思考;我现在正处于这个阶段的
：
1.我没有任何运行速度如此缓慢或低效，以至于我不能/>
将使用多个记录集重写它;
2.当我在
的未来编写ADO记录集时，我会记住倍数;
3.我已经使用了Unions，当我有两个记录集具有相同的
字段时我想使用，使用绝对字段定义的东西
like " A"作为第一个和B的哪个组。 as WhichGroup为
秒;这让我很烦恼，也许我会用倍数代替它
记录集;
4.使用SQL 2000和2005 VBA几乎是多余的;我在VRO模块中可以做的很少，我在SPROC中无法做到这一点;但是我真的好吗？b $ b想让服务器完成所有的工作，用一堆客户分享它的CPU时间，而不是让客户端机器完成所有工作
工作，没有什么共享CPU时间？
除了T-SQL之外，它甚至可以充当VBA的附件。不希望获得祖鲁时间的API函数麻烦吗？有权访问MS-SQL
服务器吗？然后使用它的函数：
CurrentProject.Connection.Execute（" SELECT GETUTCDATE（）"）。收集（0）
2005-12-10 03:24： 26
如果将其扩展到UDF，那么可以在服务器中执行所有操作
和UDF将成为代码的存储库。
当然，这并不需要与
您的数据或数据类型有任何关系您的数据可能是mdb或oracle
或者其他什么，你可以从一些中央企业访问函数
规则/函数ms-sql代码库在地球的另一边，
或你当地的硬盘驾驶。那就是我们都可以共享相同的
功能，并且可以访问即时更新和添加内容。
我很离题，抱歉。
Lyle
" Lyle Fairfield" < LY *********** @ aim.com>在消息中写道
news：11 ********************** @ g43g2000cwa.googlegr oups.com ...Randy Harris写道：
代码如下所示。我的下一步是将此技术构建到我的应用程序中。我希望获得可观的性能提升。
为什么获得一个由两个记录集组成的记录集会带来显着的性能提升，而不仅仅是获得两个
记录集？
1.你确实会发送一个命令而不是两个命令，但是你不会以相同的形式获得相同的数据（形成名词而不是形式
Access Object）并且这些数据的传输不太可能是整个过程的主要耗时活动;
2.在MS-SQL中我们可以在存储过程中创建和使用光标
并仅获取第二个记录集（假设第一个记录集仅用作第二个记录集）;这可以在Oracle中完成;
3.我们多久会编写一个返回多个
记录集的程序，这些记录集可能会被重写为联接以返回所需的数据; <如果我们连续使用它们，也就是说，如果我们使用第一个，那么使用第二个
并且永不返回，将两个记录集保存在内存中的效率如何到第一个;
4.在现场结构相同的情况下，工会或多个记录集会更有效吗？
这些都是我自己的想法我正在思考;我现在正处于这个阶段：
1.我没有任何运行如此缓慢或低效，以至于我要用多个重写它记录集;
2.当我在
未来编写ADO记录集时，我会记住倍数;
3.当我有两个相同的记录集时我已经使用了Unions
我想要使用的字段，使用绝对字段定义的东西
像A一样。作为第一个和B的哪个组。作为WhichGroup的第二个;这让我很烦恼，也许我会用倍数替换它们记录集;
4.使用SQL 2000和2005 VBA几乎是多余的;我无法在VBA模块中做到我在SPROC中无法做到的事情;但是我真的想让服务器完成所有的工作，与一群客户分享它的CPU时间，而不是让客户机完成所有的工作，分享它CPU时间什么都没有？
另外T-SQL甚至可以作为VBA的附件。不希望API函数的麻烦让Zulu有时间吗？有权访问MS-SQL
服务器吗？然后使用它的函数：
CurrentProject.Connection.Execute（" SELECT GETUTCDATE（）"）。收集（0）
2005-12-10 03:24:26
如果将其扩展到UDF，则可以在服务器中执行所有操作
并且UDF将成为代码的存储库。
当然，这并不需要做任何事情来做使用
您的数据或您的数据库类型您的数据可能是mdb或oracle
或其他什么，您可以访问某些中央业务的功能
规则/功能ms-sql代码库另一方面地球的一侧，
或在您的本地硬盘驱动器上。那就是我们都可以共享相同的功能，并可以访问即时更新和添加内容。
我很离题，抱歉。
Lyle
好问题，值得深思。也许我的判断可能因为我对这种方法的新颖性的热情而蒙上阴影。
关于问题＃1：我希望的特定领域这可能是有益的，包括连续将多个SQL语句传递给Oracle
然后检索或更新记录集数据，然后关闭。在几个地方
有4到6个操作。我的想法是有握手。时间
参与了这些操作。也许是叠加。那个时候
可以通过使用单个SP来减少。传递的数据量应该与目前使用的技术保持不变。
＃3：我认为有些地方正确定义的连接可以减少
检索记录集的数量。
＃5：所涉及的表具有不同的结构，但如果工会的效率更高，那么工会将更有效可用。
也许是一厢情愿的想法。我不知道。我可以想到只有一种方法可以确定找到
。
兰迪
Lyle，
一些答案。
1）但是当你向服务器发送一个命令时，它还有更多的东西
数据库（无论如何，SQL）有时间编译该命令并执行
之类的事情，比如解决执行计划。发送一个命令来检索
多个记录集会对这些产生影响，并且在某些情况下会显示性能增加
。
Of当然（和任何数据库一样）这不是绝对的，需要在特定情况下进行测试，以确定是否会出现性能提升。
2）在我的专业领域之外所以我会把它留给兰迪。
3）我会在我想要的情况下使用这种技术
记录集或我需要在记录集中导航的记录集
。在以前我会使用连接的情况下我会使用形状记录集，因为它们可以显示出比标准连接记录集更高的性能提升
。
4）这是你必须尝试看看的情况之一。
顺便说一句，我知道Randy的例子显示他正在使用记录集在
序列中，你不必这样做。你可以这样做
函数xxxx（）
Dim loCon As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 as ADODB.Recordset
Dim rs3 as ADODB.Recordset
设置rs1 =新ADODB.Recordset
Const CONN_STRING ="文件名= c：\ a.udl"
设置loCon =新ADODB.Connection
使用loCon
.CursorLocation = adUseClient
.ConnectionString = CONN_STRING
。打开
设置rs1 = .Execute（ SELECT * FROM scheme.plsuppm SELECT * FROM
scheme.slcustm"）
结束
''注意。克隆
设置rs2 = rs1.Clone
''现在.NextRecordset
设置rs3 = rs1.NextRecordset
''此时
''rs2包含第一个记录集
''rs3包含第二个记录集
结束函数
-
Terry Kreft
" Lyle Fairfield" < LY *********** @ aim.com>在消息中写道
news：11 ********************** @ g43g2000cwa.googlegr oups.com ...Randy Harris写道：
代码如下所示。我的下一步是将此技术构建到我的应用程序中。我希望获得可观的性能提升。
为什么获得一个由两个记录集组成的记录集会带来显着的性能提升，而不仅仅是获得两个
记录集？
1.你确实会发送一个命令而不是两个命令，但是你不会以相同的形式获得相同的数据（形成名词而不是形式
Access Object）并且这些数据的传输不太可能是整个过程的主要耗时活动;
2.在MS-SQL中我们可以在存储过程中创建和使用光标
并仅获取第二个记录集（假设第一个记录集仅用作第二个记录集）;这可以在Oracle中完成;
3.我们多久会编写一个返回多个
记录集的程序，这些记录集可能会被重写为联接以返回所需的数据; <如果我们连续使用它们，也就是说，如果我们使用第一个，那么使用第二个
并且永不返回，将两个记录集保存在内存中的效率如何到第一个;
4.在现场结构相同的情况下，工会或多个记录集会更有效吗？
这些都是我自己的想法我正在思考;我现在正处于这个阶段：
1.我没有任何运行如此缓慢或低效，以至于我要用多个重写它记录集;
2.当我在
未来编写ADO记录集时，我会记住倍数;
3.当我有两个相同的记录集时我已经使用了Unions
我想要使用的字段，使用绝对字段定义的东西
像A一样。作为第一个和B的哪个组。作为WhichGroup的第二个;这让我很烦恼，也许我会用倍数替换它们记录集;
4.使用SQL 2000和2005 VBA几乎是多余的;我无法在VBA模块中做到我在SPROC中无法做到的事情;但是我真的想让服务器完成所有的工作，与一群客户分享它的CPU时间，而不是让客户机完成所有的工作，分享它CPU时间什么都没有？
另外T-SQL甚至可以作为VBA的附件。不希望API函数的麻烦让Zulu有时间吗？有权访问MS-SQL
服务器吗？然后使用它的函数：
CurrentProject.Connection.Execute（" SELECT GETUTCDATE（）"）。收集（0）
2005-12-10 03:24:26
如果将其扩展到UDF，则可以在服务器中执行所有操作
并且UDF将成为代码的存储库。
当然，这并不需要做任何事情来做使用
您的数据或您的数据库类型您的数据可能是mdb或oracle
或其他什么，您可以访问某些中央业务的功能
规则/功能ms-sql代码库另一方面地球的一侧，
或在您的本地硬盘驱动器上。那就是我们都可以共享相同的功能，并可以访问即时更新和添加内容。
我很离题，抱歉。
Lyle
I was inspired by the recent discussion of returning multiple recordsets to
ADO from a stored procedure. (Amazed is probably more accurate). I asked
about how to accomplish same with Oracle and got a nudge in the right
direction from Mr. Kreft. I promised to provide details once working, so
here it is.
The code is shown below. My next step is to build this technique into my
application. I''m hoping for substantial performance gain.
This is totally new to me, I''d welcome any constructive criticism. By the
same token, I got it working, so I''ll be happy to attempt to answer any
questions anyone might have.
Randy Harris
In Oracle I created this package and stored procedure:
/* A PL/SQL book said that the cursor object
must be declard in a package */
CREATE OR REPLACE PACKAGE OraOLEDB AS
TYPE m_refcur IS REF CURSOR;
END OraOLEDB;
/
/* =================================== */
/* This procedure will return two REF cursors */
/* I added a couple of IN parameters to prove I could */
CREATE OR REPLACE PROCEDURE getrecs(
firstweek IN NUMBER,
lastweek IN NUMBER,
firstrs OUT OraOLEDB.m_refcur,
secondrs OUT OraOLEDB.m_refcur)
IS
BEGIN
/* Define the first cursor that will become a recordset */
OPEN firstrs FOR
SELECT weekid, weekstart
FROM weeks_tbl
WHERE weekid BETWEEN firstweek AND lastweek ;
/* And now a second */
OPEN secondrs FOR
SELECT tablename, updateseq
FROM concurrence ;
END getrecs;
/
Here''s the VBA procedure that calls the SP:
Private Sub MultiRecs()
Dim conn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim cmd As New ADODB.Command
With conn
.Provider = "OraOLEDB.Oracle"
.Open "data source=" & OracleDB & ";", dbUser, dbPass
End With
cmd.ActiveConnection = conn
cmd.Properties("PLSQLRSet") = True
cmd.CommandType = adCmdText
cmd.CommandText = "{Call getrecs (420, 430) }"
'' this calls the procedure and retrieves the cursors
'' the first recordset is created
Set rst1 = cmd.Execute
While Not rst1.EOF
Debug.Print rst1(0), rst1(1)
rst1.MoveNext
Wend
'' Now call the NextRecordset Method
Set rst2 = rst1.NextRecordset
While Not rst2.EOF
Debug.Print rst2(0), rst2(1)
rst2.MoveNext
Wend
rst1.Close
rst2.Close
End Sub
Randy Harris wrote:
The code is shown below. My next step is to build this technique into my
application. I''m hoping for substantial performance gain.
Why would getting one recordset composed of two recordsets result in a
substantial performance gain as compared with just getting two
recordsets?
1. It''s true that you will send one command instead of two, but will
you not get back the same data in the same form (form the noun not form
the Access Object) and is the transport of this data not likely to be
the chief time-consuming activity of the whole procedure;
2. In MS-SQL we can create and use the cursor in the stored procedure
and get only the second recordset (assuming the first is used only as
fodder for the second); can this be done in Oracle;
3. How often will we write Procedures that will return multiple
recordsets, which might be rewritten as Joins to return the required
data;
4. How efficient is it to hold two recordsets in memory if we are using
them consecutively, that is, if we use the first, then use the second
and never return to the first;
4. In cases where field structure is the same will Unions or multiple
recordsets be more efficient?
These are idle thoughts of my own that I am pondering; I''m currently at
this stage:
1. I don''t have anything running so slowly or inefficiently that I''m
going to rewrite it using multiple recordsets;
2. I''ll keep multiples in mind when I program ADO recordsets in the
future;
3. I already use Unions when I have two recordsets with identical
fields that I want to use, using an absolute field defined something
like "A" as WhichGroup for the first and "B" as WhichGroup for the
second; this troubles me and maybe I''ll replace it with multiples
recordsets;
4. With SQL 2000 and 2005 VBA is almost redundant; there is little I
can do in a VBA module that I can''t do in a SPROC; but do I really
wanna have the server do all the work sharing it''s CPU time with a
bunch of clients, rather than having the client machine do all the
work, sharing its CPU time with nothing?
As an aside T-SQL can even act as an adjunct to VBA. Don''t want the
hassle of API functions to get Zulu time? Have access to an MS-SQL
sever? Use its functions then:
CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0)
2005-12-10 03:24:26
If one extends this to UDFs then one could do everything in the server
and UDFs would be the repository for ones code.
And of course, this doesn''t have to have anything at all to do with
your data or your type of database Your data could be an mdb or oracle
or whatever and you could access functions from some central business
rules/ function ms-sql code repository on the other side of the earth,
or on your local hard drive. That is we could all share the same
functions and have access to instant updates and additions.
I''m digressing, sorry.
Lyle
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...Randy Harris wrote:The code is shown below. My next step is to build this technique into my
application. I''m hoping for substantial performance gain.
Why would getting one recordset composed of two recordsets result in a
substantial performance gain as compared with just getting two
recordsets?
1. It''s true that you will send one command instead of two, but will
you not get back the same data in the same form (form the noun not form
the Access Object) and is the transport of this data not likely to be
the chief time-consuming activity of the whole procedure;
2. In MS-SQL we can create and use the cursor in the stored procedure
and get only the second recordset (assuming the first is used only as
fodder for the second); can this be done in Oracle;
3. How often will we write Procedures that will return multiple
recordsets, which might be rewritten as Joins to return the required
data;
4. How efficient is it to hold two recordsets in memory if we are using
them consecutively, that is, if we use the first, then use the second
and never return to the first;
4. In cases where field structure is the same will Unions or multiple
recordsets be more efficient?
These are idle thoughts of my own that I am pondering; I''m currently at
this stage:
1. I don''t have anything running so slowly or inefficiently that I''m
going to rewrite it using multiple recordsets;
2. I''ll keep multiples in mind when I program ADO recordsets in the
future;
3. I already use Unions when I have two recordsets with identical
fields that I want to use, using an absolute field defined something
like "A" as WhichGroup for the first and "B" as WhichGroup for the
second; this troubles me and maybe I''ll replace it with multiples
recordsets;
4. With SQL 2000 and 2005 VBA is almost redundant; there is little I
can do in a VBA module that I can''t do in a SPROC; but do I really
wanna have the server do all the work sharing it''s CPU time with a
bunch of clients, rather than having the client machine do all the
work, sharing its CPU time with nothing?
As an aside T-SQL can even act as an adjunct to VBA. Don''t want the
hassle of API functions to get Zulu time? Have access to an MS-SQL
sever? Use its functions then:
CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0)
2005-12-10 03:24:26
If one extends this to UDFs then one could do everything in the server
and UDFs would be the repository for ones code.
And of course, this doesn''t have to have anything at all to do with
your data or your type of database Your data could be an mdb or oracle
or whatever and you could access functions from some central business
rules/ function ms-sql code repository on the other side of the earth,
or on your local hard drive. That is we could all share the same
functions and have access to instant updates and additions.
I''m digressing, sorry.
Lyle
Good questions, and food for thought. Perhaps my judgment might be clouded
by my enthusiasm for the novelty of this approach.
Regarding question #1: The particular areas where I''m hoping this might be
beneficial involve consecutively passing multiple SQL statements to Oracle
then retrieving or updating recordset data, then closing. In several places
there are 4 to 6 operations. My thinking is that there is "handshake" time
involved in each of these operations. Perhaps the "stack up" of that time
can be reduced by use of the single SP. The quantity of data passed should
be unchanged from the technique used currently.
#3: I think there are some places where properly defined joins could reduce
the number of retrieval recordsets.
#5: The tables involved have dissimilar structure, but unions would be more
efficient if usable.
Perhaps wishful thinking. I don''t know. I can think of only one way to find
out for certain.
Randy
Lyle,
Some answers.
1) But there is more to it than ths, when you send a command to a server
database (SQL anyway) there is time spent compiling that command and doing
things like resolving execution plans. Sending one command to retrieve
multiple recordsets will impact on these and should show a performance
increase in certain cases.
Of course (as with anything db) this is not an absolute and would need to be
tested for a particular situation to see if a performance increase occurs.
2) outside my area of expertise so I''ll leave this to Randy.
3) I would use this technique for situations where I wanted either disparate
recordsets or ones where I need to navigate through the recordsets
independently. In situations where previously I would have used joins I
tend to use shaped recordsets as these can show a performance improvement
over standard joined recordsets.
4) This is one of theose situation where you would have to try it and see.
BTW, I know that Randy''s example shows him consuming the recordsets in
sequence you don''t have to do this though. You can do this
Function xxxx()
Dim loCon As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Const CONN_STRING = "File Name=c:\a.udl"
Set loCon = New ADODB.Connection
With loCon
.CursorLocation = adUseClient
.ConnectionString = CONN_STRING
.Open
Set rs1 = .Execute("SELECT * FROM scheme.plsuppm SELECT * FROM
scheme.slcustm")
End With
'' NOTE .clone
Set rs2 = rs1.Clone
'' Now .NextRecordset
Set rs3 = rs1.NextRecordset
'' At this point
'' rs2 contains the first recordset
'' rs3 contains the second recordset
End Function
--
Terry Kreft
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...Randy Harris wrote:The code is shown below. My next step is to build this technique into my
application. I''m hoping for substantial performance gain.
Why would getting one recordset composed of two recordsets result in a
substantial performance gain as compared with just getting two
recordsets?
1. It''s true that you will send one command instead of two, but will
you not get back the same data in the same form (form the noun not form
the Access Object) and is the transport of this data not likely to be
the chief time-consuming activity of the whole procedure;
2. In MS-SQL we can create and use the cursor in the stored procedure
and get only the second recordset (assuming the first is used only as
fodder for the second); can this be done in Oracle;
3. How often will we write Procedures that will return multiple
recordsets, which might be rewritten as Joins to return the required
data;
4. How efficient is it to hold two recordsets in memory if we are using
them consecutively, that is, if we use the first, then use the second
and never return to the first;
4. In cases where field structure is the same will Unions or multiple
recordsets be more efficient?
These are idle thoughts of my own that I am pondering; I''m currently at
this stage:
1. I don''t have anything running so slowly or inefficiently that I''m
going to rewrite it using multiple recordsets;
2. I''ll keep multiples in mind when I program ADO recordsets in the
future;
3. I already use Unions when I have two recordsets with identical
fields that I want to use, using an absolute field defined something
like "A" as WhichGroup for the first and "B" as WhichGroup for the
second; this troubles me and maybe I''ll replace it with multiples
recordsets;
4. With SQL 2000 and 2005 VBA is almost redundant; there is little I
can do in a VBA module that I can''t do in a SPROC; but do I really
wanna have the server do all the work sharing it''s CPU time with a
bunch of clients, rather than having the client machine do all the
work, sharing its CPU time with nothing?
As an aside T-SQL can even act as an adjunct to VBA. Don''t want the
hassle of API functions to get Zulu time? Have access to an MS-SQL
sever? Use its functions then:
CurrentProject.Connection.Execute("SELECT GETUTCDATE()").Collect(0)
2005-12-10 03:24:26
If one extends this to UDFs then one could do everything in the server
and UDFs would be the repository for ones code.
And of course, this doesn''t have to have anything at all to do with
your data or your type of database Your data could be an mdb or oracle
or whatever and you could access functions from some central business
rules/ function ms-sql code repository on the other side of the earth,
or on your local hard drive. That is we could all share the same
functions and have access to instant updates and additions.
I''m digressing, sorry.
Lyle
这篇关于多个记录集继续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
