DTS Activex ADODB.Recordset Do...Loop and UPDATE via SP is very slow

You don't need to do any of this recordset stuff. A single SQL UPDATE
statement is required to perform the update you want, something like:

update IN_Global
SET LINKID = 'G' + SUBSTRING(b.RecDetail, 34, 5) + SUBSTRING(b.RecDetail,
55, 11)
FROM IN_Global as a INNER JOIN IN_Global as b ON a.RecNum = b.RecNum WHERE
a.RecType = '20'

This should take a few seconds to execute.

Hope this helps,

Charles Kangai, MCT, MCDBA
Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm
email: charles at kangai.demon.co.uk



"david.a.karpiak@kp.org" wrote:

> Hello...I'm trying to update a field in a SQL 2000 table with a value
> that is extracted from another field within the same table based on a
> specific field value ([rectype] value). This value is dependant on a
> [rectype] value = 20. All the records in the table that have a
> [rectype] of 20 will determine the field value that will be populated.
> The code opens a recordset uses ADODB.Connection and ADODB.recordset in
> a DTS package Activex Script and loops through the table and updates
> the LINKID field based upon what it finds.... This code runs very
> slow... is there any possible way to speed this up? I have used a SP
> to update the table to make things faster and I'm trying to figure out
> an even faster way to do this... Any help is much appreciated. I have
> pasted the code from the Activex Task and the SP below...
>
> Activex Task code:
> '--------------------------------------------------------------
> Function Main()
> dim countr
> dim mySourceConn
> dim mySourceRecordset
> dim mySQLCmdText
> dim RecNumber
> dim RecType
> dim TotalRecs
> dim TrmPreFx
> dim LINKIDstr
> Dim oCmd
>
>
> '------Connection to SQL that is used by all recordsets below
>
> set mySourceConn=CreateObject("ADODB.Connection")
> mySourceConn.Open="Provider=SQLOLEDB;Trusted_Connection=yes;SERVER=CSPARPSQL001;Initial
> Catalog=ADRS"
> 'mySourceConn.Open="Provider=SQLOLEDB;Description=CSPARPSQL001;DATABASE=ADRS;Trusted_Connection=Yes"
>
>
> '------Assign each group a unique identifier to associate the detail
> records with the header record.
>
> set mySourceRecordset=CreateObject("ADODB.Recordset")
> mySQLCmdText= "SELECT IN_Global.* FROM IN_Global ORDER BY RecNum"
> mySourceRecordset.open mySQLCmdText, mySourceConn
>
> Set oCmd = CreateObject("ADODB.Command")
> oCmd.ActiveConnection = mySourceConn
> oCmd.CommandText = "usp_UpdateLINKID"
> oCmd.CommandType = 4
>
> countr = 1
> mySourceRecordset.movefirst
>
> Do Until mySourceRecordset.Fields("RecType").value = "20"
> mySourceRecordset.MoveNext
> Loop
>
> Do While Not mySourceRecordset.EOF
>
> TrmPreFx = MID(mySourceRecordset.Fields("RecDetail").value, 34, 5)
> LINKIDstr = "G" & TrmPreFx &
> MID(mySourceRecordset.Fields("RecDetail").value, 55, 11)
>
> Do
> '-----Call the SP
> oCmd.Parameters(1) = cstr(LINKIDstr)
> oCmd.Parameters(2) = mySourceRecordset.Fields("RecNum").value
> oCmd.Execute
> mySourceRecordset.MoveNext
> If mysourceRecordset.EOF Then Exit Do
> Loop Until mySourceRecordset.Fields("RecType").value = "20"
>
> Loop
>
> mySourceRecordset.close
> mySourceConn.close
> Main = DTSTaskExecResult_Success
>
> End Function
>
>
> Stored Proc CODE
> '----------------------------------------------------------
> CREATE PROCEDURE usp_UpdateLINKID
> (
> @LINKID as varchar(17),
> @RecNum as varchar(15)
> )
> AS
> UPDATE IN_Global SET LINKID = @LINKID WHERE RecNum = @RecNum
> GO
>
>