Updateing NText causing long delay/timeouts

Question

I'm trying to update an NText field in SQL 2000 using Classic ASP. Here is the code I'm using to do it. Any one have any pointers on how to maybe speed it up? Or am I stuck with it.

set Cnn = server.CreateObject("ADODB.connection")
Cnn.ConnectionString = Application("Cnn_ConnectionString")
Cnn.open 
set rs = server.CreateObject("ADODB.Recordset")

rs.CursorType = adoOpenDynamic
rs.LockType = adLockOptimistic      
conChunkSize = 100   
rs.Open "MyTable",Cnn, , , adCmdTable 
rs.Find "MyDataId=" & request("DataId"),,adSearchForward,1     

lngOffset = 0   
lngLogoSize = len(request("txtMyEntry"))*2
Do while lngOffset < lngLogoSize
    varChunk = LeftB(RightB(request("txtMyEntry"), lngLogoSize - _
               lngOffset), conChunkSize)
        rs("MyDataField").AppendChunk varChunk
        lngOffset = lngOffset + conChunkSize
    Loop

rs.Update
rs.Close

Oh and this code is almost verbatim from the MSDN site.

Answer 1

First I would eliminate the chunking which is so 90's.

Then there is:-

rs.Open "MyTable",Cnn, , , adCmdTable 
rs.Find "MyDataId=" & request("DataId"),,adSearchForward,1

Yikes! You'd like to think that ADO intelligently asked SQL server to find that record based on the indexed MyDataId field but bet it doesn't. Its most likely pulling the entire contents of the table across until the record is arrived at.

This really should be done with an UPDATE T-SQL statement and an ADODB.Command object.

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandType = 1 '' // adCmdText
cmd.CommandText = "UPDATE MyTable SET MyDataField = ? WHERE MyDataId = ?"
cmd.Parameters.Append cmd.CreateParameter("dataField", 203, 1, Len(txtMyEntry), txtMyEntry)  '' // 203 = asLongVarWChar, 1 = adParamInput
cmd.Parameters.Append cmd.CreateParameter("id", 3, 1, , CInt(DataID)) '' // 3 = adInteger
cmd.Execute