LOOPING INSERTION OF 5000 entries

[vtNordia]

Hi everybody!

I have a question about tuning my VB6 "INSERT" procedure.
I have 5000 entries in a vb6 array that I need to insert in a SQLserver2K table.

My INSERT procedure takes about 11-12 secondes to process all the insertion. Can I speed up my procedure?
Here is my code:

Public Sub loopInsertInDB(cnx As adodb.Connection)

Dim statement As String
Dim commande As New adodb.Command

Dim i As Integer
i = 0

idxArray = ubound(TABDATA)
While i < idxArray

statement = &quot;INSERT INTO R27 &quot; & _
&quot;( v, w, x, y, z) &quot; & _
&quot; VALUES (&quot; & _
&quot;'&quot; & TABDATA(i).strV & &quot;', &quot; & _
&quot;'&quot; & TABDATA(i).strW & &quot;', &quot; & _
&quot;'&quot; & TABDATA(i).strX & &quot;', &quot; & _
&quot;'&quot; & TABDATA(i).strY & &quot;', &quot; & _
&quot;'&quot; & TABDATA(i).strZ & &quot;'&quot; & &quot&quot;

With commande
'.Prepared = True 'where should i use it?
.ActiveConnection = cn
.CommandText = statement
.CommandType = adCmdText
.Execute
End With

i = i + 1

Wend

Set commande = Nothing

End Sub

By the way, how and where do i use the prepared property

Thank you!
Vt

 

[jackfl]

Consider using a Stored Procedure; it should give a little more speed. 'Prepared' tells the Command object to compile the object as it will probably be called multiple times. Also, make sure you don't have any excess indexes on the R27 table, and that the 'fill factor' on the existing indexes allows for your bulk loading.