Bruce L-C
11/5/2002 7:24:00 PM
I can think of one thing right now that will make a big big difference. Wrap
them in a transaction. You can play with the number of records but I usually
do 100 records and then commit. Right now, every insert is a single
transaction.
Also, I disagree with the comment about ADO being slower than DAO. I
converted from DAO to ADO a long time ago and did not see this. True, at
first you had to use the OLEDB->ODBC route but with a native OLEDB provider
for ADO this issue went away (an extra level of indirection). Anyway, try
wrapping it in a transaction and see if it gets faster.
Bruce L-C
"Joe Tobey" <tobeyj@biotek.com> wrote in message
news:#wUm6oNhCHA.2636@tkmsftngp08...
> I'm considering migrating an application to .NET but I'm finding it takes
> about 10 times longer to write records to an Access db than my VS 6 app
> which used DAO. For my .NET test I'm using OleDbConnection and
> OleDbDataAdapter classes as follows:
>
> WriteRecords()
> {
> oleDbConnection1.Open()
>
> for {each record}
> oleDbDataAdapter1.InsertCommand.CommandText = "INSERT INTO ...."
> oleDBDataAdapter1.ExecuteNonQuery()
> }
>
> oleDbConnection1.Close()
> }
>
>
> My application doesn't use a DataView, and its not clear to me if I should
> be using a DataSet. I tend to have 10 or 15 different tables some of
which
> get a single record added and others which get multiple records added
> (approx 100-400 records) per transaction.
>
> Is there a more efficient way for writing multiple records to a database?
>
> In general, I'm having a difficult time understanding what my various
> options for database interaction are.
>
> Thanks,
> Joe
>
>