OLEDB - Slow Inserts

I have noticed that when using OLEDB (.Net 1.1) with an .MDB file the insert times are very slow. I did some testing using the same table layout and insert data with SQL2005 express edition and was blown away by how much faster it was. This presents a problem as the application that I wrote gathers lots of data via a TCP/IP socket and then stores it for later processing, etc. The release date for SQL2005 is ~ Nov so in the meantime is there anything I can do to try to improve the insert speed using an OLEDB (.Net 1.1) / .MDB solution I have removed the indexes, etc... Hopefully there is some undocumented parameter or something that can provide some relief until November. Any suggestions or comments on this would be greatly appreciated.

Thanks

Answer this question

OLEDB - Slow Inserts

Tyler Whitney - MSFT
Unfortunately that's not an option as getting MSDE laid down on a machine is a royal pain. This is a client application so ease of deployment is critical. I am looking for some performance suggestions related to using OLEDB (.Net 1.1) and large numbers of inserts against an .MDB file.

Thanks
Amarelo_Mrt
oh yah. . . when is the last time the mdb file was compacted
mtsman
oh yeah. . . the most elusive setting was 'DISABLENETWORKPROTOCOLS'

Unless its an upgrade, the default is '1' (disabled), else what ever the system had before.

So if you want network clients to connect via mdac you have to set:

DISABLENETWORKPROTOCOLS = 0

kkam

Chris Condrey wrote:
Unfortunately that's not an option as getting MSDE laid down on a machine is a royal pain. This is a client application so ease of deployment is critical. I am looking for some performance suggestions related to using OLEDB (.Net 1.1) and large numbers of inserts against an .MDB file.

Thanks

problems with laying down msde

are you using the latest msde release from MS
are you familiar with the setup.ini file
the command line for installing

I have absolutely no problems.
Though I had many before using the latest release and understand the ini and the command line.

Event wrote a delphi wrapper for automating user coustomized installs in about 5 minutes. We use the utility all the time with no problems at all.

that being said. . .

How are you trying to push the data into the database ADO.NET commands
Maybe interoping the adodb libraries might be a little faster.
Connect to the database and open a client side recordset on the table inquestion that has no rows, so you get the schema for the table and set its active connection to nothing/null.

Repeatedly use the recordset method addnew and populate with the data. . .
when you have finished populating, reconnect the recordset and call the UpdateBatch method.

That might be faster.

Moien
http://download.microsoft.com/download/d/5/4/d5402c33-65de-4464-9d82-d1de2971d9db/ReadmeMSDE2000A.htm
dlmac
Could you post more information regarding the use of the .ini file It's been a while since I have had to install MSDE via an .msi package. I am using the provided OLEDB.NET (1.1) / ADO.NET (1.1) to talk to the .mde file. I ran some comparitive tests against SQL2005 and found it to be roughly 4.5 times faster at just about everything compared to using an .mde file.
Daniel Kolman
if you anticipate using SQL2005, you might as well use MSDE right now.
OLEDB - Slow Inserts

Answer this question