can it be faster?

[Thai]

Is there a way to make this faster? If I comment out the two cnSQL.Execute statements, it goes lightning fast, but it is extremely slow with them there. Is there a way to tell ADO to update the Executes only once in a while instead of every time I run the execute statement? I am connecting to an SQL Server using ADO.

  For x = 1 To ItemCounter
    cnSQL.Execute "INSERT INTO [CompletedFile] (ID) values(" & x & ")"
    
    Do Until iCounter = UBound(NewRecord)
      If NewRecord(iCounter).ContentID = x Then
        iCounter = iCounter + 1
        strSQL = strSQL & "[" & NewRecord(iCounter).ContentName & "]" & "='" & NewRecord(iCounter).ContentValue & "', "
      Else
        lngnewrecord = iCounter + 1
        Exit Do
      End If
    Loop
    strSQL = "UPDATE [CompletedFile] SET " & Left(strSQL, Len(strSQL) - 2) & " WHERE [ID] = " & x
    cnSQL.Execute strSQL
    strSQL = ""
    ProgressBar1.Value = ProgressBar1.Value + 1
    DoEvents
  Next

Thanks,
Thai

[JHausmann]

Where are your cursors?

[Thai]

I didn't set it, so it would be the default. is ClientSide or ServerSide cursor faster? Also, what if the SQL Server is on the machine running the program, would it matter at that point?

Thanks,
Thai

[everjoe]

If you are going to add or update a lot of records at a time,you could use the RecordSet object of ADO.
set the cursorLocation to adUseClient,cursorType to adDynamic,LockType to adLockBatchOptimistic,then you can modify the object,and use the UpdateBatch method to update all the records in a single batch.It should be faster than your current code(not tested ).

the code goes like this:
dim rs as new ADODB.recordset
with rs
.cursorlocation = aduseclient
.cursortype = adCursorDynamic
.LockType = adLockBatchOptimistic
.ActiveConnection = cn
.Source = "select * from [Your Table]"
end with
rs.open

for...
rs.AddNew
rs!Number = ...
rs!Update

while...
rs!Text = ...
...
rs.Update
loop
next...

rs.UpdateBatch
rs.close

[Clunietp]

Hi Thai, this might help. This batches the operation, so you don't have to communicate with the server on each record, you can perform a batch operation. This should speed things up a bit

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim lngCounter As Long

    'prepare batch sql statement
    For lngCounter = 1 To 100
        strSQL = strSQL & " Insert Into TestTable ([FIELDNAME]) VALUES ('" & lngCounter & "');"
    Next lngCounter
    
    'ok, its prepared, lets connect & execute the statement 1 time

    cn.Open "Provider=SQLOLEDB;Data Source=W2KSRV;Integrated Security=SSPI;Initial Catalog=Northwind"

    cn.Execute strSQL

    '100 records have been inserted, only 1 cn.Execute was necessary

[Edited by Clunietp on 08-14-2000 at 12:07 PM]

[Thai]

Cool thanks guys, I tried your way EverJoe and it sped things up a lot. I am going to try your way now Clunietp and see which one is faster. I really appreciate the help!

Thai

[Clunietp]

Please let us know, I'd be interested to see the results

[Thai]

Well I tried your way also Cluntp, it works but the the slowdown is no longer the sql engine, its building the string. The first few times through the string building loop its very fast, but it slows down tremendously after a while because the string is so big. Any other suggestions?

Thanks for the help
Thai

[Clunietp]

Maybe its your object...what is: NewRecord(iCounter).ContentID ?

[Thai]

NewRecord() is a user defined Type that I made, it has ContentID, ContentName, ContentValue, the ID is the unique identifier to update the table, the Name is the field name and the value is the value which can be a number of things (part number, description, unit of measure, etc).

Thanks,
Thai

[Clunietp]

does it appear to be the string concatenation that is giving you the slowdown, or is it your UDT?

[Thai]

I believe it is the string, because it is fast at first, until the string gets exremely big then it slows down.. slower and slower..

Thanks,
Thai

[Clunietp]

keep a counter, and execute the SQL string after every 10 or so, then clear the string and start over