ADO Performance question

Hi,
I am writing a code which is actually performing multiple inserts on a table
.
Currently I am combining all my insert queries and passing as command text t
o ADO command object.
There is one more way by opening a recordset and call AddNew on recordset mu
ltiple times and then batch update.
But I am not sure which method is better than other and which approach shoul
d I use.
Thanks in advance.
PushkarHi
Why not just calling a stored procedures that does the job?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:uA7uMRk2FHA.472@.TK
2MSFTNGP15.phx.gbl...
Hi,
I am writing a code which is actually performing multiple inserts on a table
.
Currently I am combining all my insert queries and passing as command text t
o ADO command object.
There is one more way by opening a recordset and call AddNew on recordset mu
ltiple times and then batch update.
But I am not sure which method is better than other and which approach shoul
d I use.
Thanks in advance.
Pushkar|||If you are inserting 100,000+ records, then calling a Recordset.AddNew for e
ach record will be very slow.
I once wrote an ETL application in VB6.0/ADO2.5 that performed some rather c
omplex data transformations on millions of records per day that were derived
from the mainframe and MS Excel. I would run the app on the local workstati
on, appending each record to a tab delimited text file. When this step compl
eted, I would then bulk copy (BCP.EXE) the resulting file into SQL Server. T
he difference in runtime per batch was a couple of hours vs. 20 minutes. Ano
ther advantage to this is you can run the application on multiple PCs with n
o load on the database server except for the bulk copy operations.
You may also want to try posting to microsoft.public.data.ado
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:uA7uMRk2FHA.472@.TK
2MSFTNGP15.phx.gbl...
Hi,
I am writing a code which is actually performing multiple inserts on a table
.
Currently I am combining all my insert queries and passing as command text t
o ADO command object.
There is one more way by opening a recordset and call AddNew on recordset mu
ltiple times and then batch update.
But I am not sure which method is better than other and which approach shoul
d I use.
Thanks in advance.
Pushkar|||Batching is generally faster. But you are moving a lot of data across
the network. As Uri mentions, why not use a stored procedure instead
(even if this implies a few calls to the procedure). If you are dealing
with thousands of rows, this might perform better using a bulk insert or
running locally on the server.
David Gugick
Quest Software
www.quest.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:uA7uMRk2FHA.472@.TK2MSFTNGP15.phx.gbl...
Hi,
I am writing a code which is actually performing multiple inserts on a
table.
Currently I am combining all my insert queries and passing as command
text to ADO command object.
There is one more way by opening a recordset and call AddNew on
recordset multiple times and then batch update.
But I am not sure which method is better than other and which approach
should I use.
Thanks in advance.
Pushkar