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