Using Recursion looping through a ADO RS

[Guest]

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?

 

[Greg Burns]

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

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?

 

[Greg Burns]

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

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?

 

[Jay B. Harlow [MVP - Outlook]]

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

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?

 

[Cablewizard]

Is "itemId" a unique record identifier?
If so, is it indexed as a primary key on the server?
If not, then I can see a number of things that can happen that are undesirable
given this code.
At minimum, you should include a primary key in your returned recordset and use
that key to identify record to update.
This alone will make more of a difference than whether you use ADODB vs ADO.Net,
or parameterized updates, etc. combined.

Gerald

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?

 

[Greg Burns]

% is the modulus operator in SQL

From BOL:
Examples
This example returns the book title number and any modulo (remainder) of
dividing the price (converted to an integer value) of each book into the
total yearly sales (ytd_sales * price).

USE pubs
GO
SELECT title_id,
CAST((ytd_sales * price) AS int) % CAST(price AS int) AS Modulo
FROM titles
WHERE price IS NOT NULL and type = 'trad_cook'
ORDER BY title_id
GO

Greg

 

[Greg Burns]

Prepared will only make a difference if you are using parameters...

 

[Jay B. Harlow [MVP - Outlook]]

Dacuna,
As Greg states, % is the modulo division operator in SQL 2000.

Also as Cablewizard suggests, does your SQL Server myTable table have a
unique index or primary key constraint on itemId?

If you give the statement "update myTable set calcData = 'something' where
itemId = 20" to SQL Server Query Analyzer, what kind of executation plan
does it say its going to use.

What does Query Analyzer say about the statement "update myTable set
calcData = @calcValue where itemId = @itemId"?

Hope this helps
Jay

Jay,

Unfortunately my function uses modular division and I don't think SQL 2000

has that feature. One thing I will try, however, is the parametized update
statement because that is where the code slows down as it's parsing through
the recordset. As it stands, right now it takes me a little under a second
to update each record so I'm definately looking to improve performance.

 

[Cor Ligthert]

Hi Dacune

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?

A different answer than the others until now, which maybe is a question
however as well an answer, is this not typical a situation for the ADO.Net
SQLdatareader and the SQL update command?

http://msdn.microsoft.com/library/d...ystemDataSqlClientSqlDataReaderClassTopic.asp

http://msdn.microsoft.com/library/d...ientsqldataadapterclassupdatecommandtopic.asp

However maybe I am wrong?

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,

is this not typical a situation for the ADO.Net
SQLdatareader and the SQL update command?

That's the way I read it, so that is why my sample used a SqlDataReader & an
update SqlCommand.

NOTE: You don't need to get an SQLDataAdapter involved, as you are not going
to be using the SqlDataAdapter.Update method all you need is the SqlCommand
itself (that you would have assigned to the SqlDataAdapter.UpdateCommand
property).

http://msdn.microsoft.com/library/d...rfSystemDataSqlClientSqlCommandClassTopic.asp

Hope this helps
Jay

 

[Cor Ligthert]

Hi Jay,

After good rereading it, I see that we have exactly the same idea, I readed
your message and there was the part
'Setup the recordset
That has probably brought me on the wrong idea of your solution. When I saw
that I thought it was a loop to setup a recordset (dataset) and did not read
further. Now I see it is the same as I ment.

Than it is in my opinion only good that we have very independently from each
other the same best solution for this problem. I hope that you understand
that when I had readed it as it was I had not made this previous message.



Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,
The "'Setup the recordset" is from the original code, I left it there as a
bread crumb so the OP knew where my code matched his code...

Hope this helps
Jay