使用递归循环通过ADO RS [英] Using Recursion looping through a ADO RS

问题描述

是否可以使用递归函数更快地遍历记录集？


我有一个表，我需要在进行一些计算后编辑其内容。该表有一个字段具有RawData字段和CalcData字段。我打开记录集，exctract RawData，并在做了一些计算后用计算的数据更新CalcData。在代码中我有如下内容。


dim rs as new ADODB.Recordset

dim cmdUpdate as new ADODB.Command

昏暗的CalcValue作为字符串


''设置命令

使用cmdUpdate

.ActiveConnection = ConnStr

.CommandType = adCmdText

结束


''设置记录集

用rs

。打开从myTable中选择itemId，ConnStr，1

Do While Not .EOF

calcValue = CalcFunction（.fields（" itemId"）。value）

cmdUpdate.CommandText =" update myTable set calcData =''" &安培; calcValue& ""其中itemId =& .fields（" itemId"）。value

cmdUpdate.update

.MoveNext

循环

结束

希望我想要做的事情并不太混乱。代码非常简单，但它并不像我想要的那么快，因为myTable有大约150万条记录！也许有更快的方法来做这个而不是使用ADO？ ADO.Net更快吗？


-

感谢您的帮助

解决方案

只是一些想法...


ADODB.Command对象是否有Prepare方法？我会检查一下，

它可能会有所帮助。也许使用参数可以加快速度。


这是SQL服务器中的数据吗？如果是这样，CalcFunction背后的逻辑是否可以转换为SQL内联UDF？
？这会将所有数据留在服务器上。


HTH，

Greg


Dacuna <哒**** @ houston.rr.SAYNOTOSPAM.com>在消息中写道

新闻：EE ********************************** @ microsof t.com ...

是否可以使用递归函数更快地遍历记录集
？
我有一个表，我需要在编写完成后再编辑其内容一些
计算。该表有一个字段有一个RawData字段和一个CalcData

字段。我打开记录集，exctract RawData，并在做了一些

计算后，用计算出的数据更新CalcData。在代码中我有以下内容：$ / b $ b dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''设置命令
使用cmdUpdate
.ActiveConnection = ConnStr
.CommandType = adCmdText
结束

''设置记录集
使用rs
。打开从myTable中选择itemId，ConnStr，1
Do While Not .EOF
calcValue = CalcFunction（.fields（" itemId"）。）value ）
cmdUpdate.CommandText =" update myTable set calcData =''" &安培;
calcValue& ""其中itemId =& .fields（" itemId"）。value cmdUpdate.update
.MoveNext
循环
结束

希望我不是太困惑我正在尝试做。这个代码非常简单，但它并不像我想要的那么快，因为

myTable有大约150万条记录！也许有更快的方法来做这个而不是使用ADO吗？ ADO.Net更快吗？
-
感谢您的帮助

SQL内联UDF是SQL Server 2000内联用户 - 定义的函数。


你可以在SQL上创建一个类似的商店程序：


创建程序dbo.usp_MyProc


AS


更新myTable

SET calcData = dbo.ufn_CalcFunction（itemId）


GO


创建功能dbo.ufn_CalcFuntion（

@itemID int

）

RETURNS int

BEGIN


DECLARE @calcData int


- 这里的东西......


RETURN @calcData


结束


GO


有一个这里有一个很大的假设，即在没有外界帮助的情况下，在服务器上处理CalcFuntion背后的逻辑可以是

。


在线搜索UDF的SQL Server书籍


准备通常是Command对象的方法。它预编译了你的SQL

命令，使其执行得更快（或类似的东西）。


格雷格

Dacuna ; <哒**** @ houston.rr.SAYNOTOSPAM.com>在消息中写道

新闻：3B ********************************** @ microsof t.com ...

请原谅我的无知......但是什么是SQL内联UDF？是的，数据
驻留在SQL服务器中。就准备方法而言，这是一个方法

，这是命令的一部分吗？ -
感谢您的帮助

" Greg Burns"写道：

只是一些想法......

ADODB.Command对象是否有Prepare方法？我会检查
，这可能会有所帮助。也许使用参数可以加快速度。

这是SQL服务器中的数据吗？如果是这样，CalcFunction背后的逻辑可以转换为SQL内联UDF吗？这会将所有数据留在
服务器上。
HTH，
Greg

Dacuna <哒**** @ houston.rr.SAYNOTOSPAM.com>在消息中写道
新闻：EE ********************************** @ microsof t.com。 ..

是否可以使用递归函数更快地循环记录集

？blockquote class =post_quotes>
我有一个表格我需要在做一些

计算之后编辑它的内容。该表有一个字段有一个RawData字段和一个
CalcData字段。我打开记录集，提取RawData，并在做了一些
计算后用计算数据更新CalcData。在代码I
中有如下内容。

dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''设置命令
使用cmdUpdate
.ActiveConnection = ConnStr
.CommandType = adCmdText
结束

' '设置记录集
用rs
。打开从myTable中选择itemId，ConnStr，1
Do While Not .EOF
calcValue = CalcFunction（.fields（" itemId"） ;）。value）
cmdUpdate.CommandText =" update myTable set calcData =''" &

calcValue& ""其中itemId =& .fields（" itemId"）。value

cmdUpdate.update
.MoveNext
循环
结束

希望不是太多混淆了我想要做的事情。代码是

非常简单，但它并不像我想要的那么快，因为
myTable有大约150万条记录！也许有更快的方式来
这样做而不是使用ADO？ ADO.Net更快吗？

-
感谢您的帮助

Dacuna，

也许有更快的方法来做这个而不是使用ADO？ ADO.Net
更快吗？

ADO&如果使用参数化更新

语句，ADO.NET会更快。请注意以下更新

语句文本中的参数标记（@calcValue& @itemId）。

来自VB.NET ADO.NET通常会更快然后是ADODB，特别是用SQL

服务器。


类似的东西（仅限语法检查）：

const connectionString As String =" Data Source = localhost; Integrated

Security = SSPI; Initial Catalog = northwind"


Const selectText As String =" select itemId from myTable"

Const updateText As String =" update myTable set calcData =

@calcValue where itemId = @ itemId"


Dim connection As New SqlConnection（connectionString）


Dim selectCommand As New SqlCommand（selectText，connection）


''设置命令

Dim updateCommand As New SqlCommand（updateText，connection）

updateCommand.Parameters.Add（" @ calcValue"，SqlDbType.NVarChar，80）

updat eCommand.Parameters.Add（" @ itemId"，SqlDbType.Int）

updateCommand.Prepare（）


Dim reader As SqlDataReader = selectCommand.ExecuteReader（ ）


''设置记录集

Dim CalcValue as String


Do read.Read（）

calcValue = CalcFunction（reader.Item（" @ itemId"））

updateCommand.Parameters（" @ calcValue"）。Value = calcValue

updateCommand.Parameters（" @ itemId"）。Value =

reader.Item（" @ itemId"）

updateCommand.ExecuteNonQuery（）

循环


reader.Close（）

connection.Close（）

也许有更快的方法来实现这一点，而不是使用ADO？


正如Greg Burns建议的那样，在SQL Server上创建一个UDF&完全这样做

服务器端通常会更快...但是你需要SQL Server 2000

或更高版本的UDF。


希望这有帮助

Jay


Dacuna <哒**** @ houston.rr.SAYNOTOSPAM.com>在消息中写道

新闻：EE ********************************** @ microsof t.com ...是否可以使用递归函数更快地循环记录集
？
我有一个表，我需要在进行一些
计算后编辑其内容。该表有一个字段有一个RawData字段和一个CalcData

字段。我打开记录集，exctract RawData，并在做了一些

计算后，用计算出的数据更新CalcData。在代码中我有以下内容：$ / b $ b dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''设置命令
使用cmdUpdate
.ActiveConnection = ConnStr
.CommandType = adCmdText
结束

''设置记录集
使用rs
。打开从myTable中选择itemId，ConnStr，1
Do While Not .EOF
calcValue = CalcFunction（.fields（" itemId"）。）value ）
cmdUpdate.CommandText =" update myTable set calcData =''" &安培;
calcValue& ""其中itemId =& .fields（" itemId"）。value cmdUpdate.update
.MoveNext
循环
结束

希望我不是太困惑我正在尝试做。这个代码非常简单，但它并不像我想要的那么快，因为

myTable有大约150万条记录！也许有更快的方法来做这个而不是使用ADO吗？ ADO.Net更快吗？
-
感谢您的帮助

Is it possible to use a recursive function to loop through a recordset faster?

I have a table that I need to edit its contents after doing some calculation. The table has one field has an RawData field and a CalcData field. I open the recordset, exctract the RawData and after doing some calculations update the CalcData with the calculated data. In code I have something as follows.

dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''Setup the Command
With cmdUpdate
.ActiveConnection = ConnStr
.CommandType=adCmdText
End With

''Setup the Recordset
With rs
.Open "select itemId from myTable", ConnStr, 1
Do While Not .EOF
calcValue = CalcFunction (.fields("itemId").value)
cmdUpdate.CommandText = "update myTable set calcData = ''" & calcValue & "" where itemId = & .fields("itemId").value
cmdUpdate.update
.MoveNext
Loop
End With
Hopefully is not too confusing what I''m trying to do. The code is pretty simple but it''s not as fast as I would like it to be specially because the myTable has about 1.5 million records! Maybe there is a faster way to do this instead of using ADO? Is ADO.Net faster?

--
Thank you for your help

解决方案

just some ideas...

Does a ADODB.Command object have a Prepare method? I would check into that,
it may help. Maybe using parameters would speed things up to.

Is this data in SQL server? If so can the logic behind CalcFunction be
converted to a SQL Inline UDF? This would leave all the data on the server.

HTH,
Greg

"Dacuna" <da****@houston.rr.SAYNOTOSPAM.com> wrote in message
news:EE**********************************@microsof t.com...

Is it possible to use a recursive function to loop through a recordset faster?
I have a table that I need to edit its contents after doing some calculation. The table has one field has an RawData field and a CalcData
field. I open the recordset, exctract the RawData and after doing some
calculations update the CalcData with the calculated data. In code I have
something as follows.
dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''Setup the Command
With cmdUpdate
.ActiveConnection = ConnStr
.CommandType=adCmdText
End With

''Setup the Recordset
With rs
.Open "select itemId from myTable", ConnStr, 1
Do While Not .EOF
calcValue = CalcFunction (.fields("itemId").value)
cmdUpdate.CommandText = "update myTable set calcData = ''" & calcValue & "" where itemId = & .fields("itemId").value cmdUpdate.update
.MoveNext
Loop
End With
Hopefully is not too confusing what I''m trying to do. The code is pretty simple but it''s not as fast as I would like it to be specially because the
myTable has about 1.5 million records! Maybe there is a faster way to do
this instead of using ADO? Is ADO.Net faster?
--
Thank you for your help

SQL Inline UDF is SQL Server 2000 Inline User-Defined Function.

You could create a Store Procedure on SQL something like so:

CREATE PROCEDURE dbo.usp_MyProc

AS

UPDATE myTable
SET calcData =dbo.ufn_CalcFunction(itemId)

GO

CREATE FUNCTION dbo.ufn_CalcFuntion (
@itemID int
)
RETURNS int
BEGIN

DECLARE @calcData int

--do something here...

RETURN @calcData

END

GO

There is a big assumption here, that the logic behind CalcFuntion could be
processed on the server without outside assistance.

Search SQL Server Books Online for UDF

"Prepare" is generally a method on Command objects. It precompiles your SQL
command to make it execute faster (or something akin to that).

Greg
"Dacuna" <da****@houston.rr.SAYNOTOSPAM.com> wrote in message
news:3B**********************************@microsof t.com...

Pardon my ignorance...but what is a "SQL Inline UDF"? Yes, the data resides in a SQL server. As far as the prepare method, is this a method
that is part of the command? --
Thank you for your help
"Greg Burns" wrote:

just some ideas...

Does a ADODB.Command object have a Prepare method? I would check into that, it may help. Maybe using parameters would speed things up to.

Is this data in SQL server? If so can the logic behind CalcFunction be
converted to a SQL Inline UDF? This would leave all the data on the server.
HTH,
Greg

"Dacuna" <da****@houston.rr.SAYNOTOSPAM.com> wrote in message
news:EE**********************************@microsof t.com...

Is it possible to use a recursive function to loop through a recordset

faster?

I have a table that I need to edit its contents after doing some

calculation. The table has one field has an RawData field and a CalcData field. I open the recordset, exctract the RawData and after doing some
calculations update the CalcData with the calculated data. In code I have something as follows.

dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''Setup the Command
With cmdUpdate
.ActiveConnection = ConnStr
.CommandType=adCmdText
End With

''Setup the Recordset
With rs
.Open "select itemId from myTable", ConnStr, 1
Do While Not .EOF
calcValue = CalcFunction (.fields("itemId").value)
cmdUpdate.CommandText = "update myTable set calcData = ''" &

calcValue & "" where itemId = & .fields("itemId").value

cmdUpdate.update
.MoveNext
Loop
End With
Hopefully is not too confusing what I''m trying to do. The code is

pretty simple but it''s not as fast as I would like it to be specially because the myTable has about 1.5 million records! Maybe there is a faster way to do this instead of using ADO? Is ADO.Net faster?

--
Thank you for your help

Dacuna,

Maybe there is a faster way to do this instead of using ADO? Is ADO.Net faster?
Both ADO & ADO.NET will be faster if you use a parameterized update
statement. Note the parameter markers (@calcValue & @itemId) in the update
statement text below.

From VB.NET ADO.NET will generally be faster then ADODB, especially with SQL
Server.

Something like (syntax checked only):

Const connectionString As String = "Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=northwind"

Const selectText As String = "select itemId from myTable"
Const updateText As String = "update myTable set calcData =
@calcValue where itemId = @itemId"

Dim connection As New SqlConnection(connectionString)

Dim selectCommand As New SqlCommand(selectText, connection)

''Setup the Command
Dim updateCommand As New SqlCommand(updateText, connection)
updateCommand.Parameters.Add("@calcValue", SqlDbType.NVarChar, 80)
updateCommand.Parameters.Add("@itemId", SqlDbType.Int)
updateCommand.Prepare()

Dim reader As SqlDataReader = selectCommand.ExecuteReader()

''Setup the Recordset
Dim CalcValue as String

Do While reader.Read()
calcValue = CalcFunction(reader.Item("@itemId"))

updateCommand.Parameters("@calcValue").Value = calcValue
updateCommand.Parameters("@itemId").Value =
reader.Item("@itemId")
updateCommand.ExecuteNonQuery()
Loop

reader.Close()
connection.Close()
Maybe there is a faster way to do this instead of using ADO?
As Greg Burns suggested, creating a UDF on SQL Server & doing this entirely
server side will be generally be faster... However you need SQL Server 2000
or later for UDFs.

Hope this helps
Jay

"Dacuna" <da****@houston.rr.SAYNOTOSPAM.com> wrote in message
news:EE**********************************@microsof t.com... Is it possible to use a recursive function to loop through a recordset faster?
I have a table that I need to edit its contents after doing some calculation. The table has one field has an RawData field and a CalcData
field. I open the recordset, exctract the RawData and after doing some
calculations update the CalcData with the calculated data. In code I have
something as follows.
dim rs as new ADODB.Recordset
dim cmdUpdate as new ADODB.Command
dim CalcValue as String

''Setup the Command
With cmdUpdate
.ActiveConnection = ConnStr
.CommandType=adCmdText
End With

''Setup the Recordset
With rs
.Open "select itemId from myTable", ConnStr, 1
Do While Not .EOF
calcValue = CalcFunction (.fields("itemId").value)
cmdUpdate.CommandText = "update myTable set calcData = ''" & calcValue & "" where itemId = & .fields("itemId").value cmdUpdate.update
.MoveNext
Loop
End With
Hopefully is not too confusing what I''m trying to do. The code is pretty simple but it''s not as fast as I would like it to be specially because the
myTable has about 1.5 million records! Maybe there is a faster way to do
this instead of using ADO? Is ADO.Net faster?
--
Thank you for your help

这篇关于使用递归循环通过ADO RS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！