Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> .NET Development >> .NET Framework Data Access and Storage

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

[759 byte] By [ChrisCondrey] at [2007-12-16]
«« CF2.0 'Click-Once' Support
»» Another ComboBox Quandry!
# 1
if you anticipate using SQL2005, you might as well use MSDE right now.
BlairStark at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2
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

ChrisCondrey at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 3
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.

BlairStark at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 4
oh yah. . . when is the last time the mdb file was compacted?
BlairStark at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 5
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.

ChrisCondrey at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 6
http://download.microsoft.com/download/d/5/4/d5402c33-65de-4464-9d82-d1de2971d9db/ReadmeMSDE2000A.htm
BlairStark at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 7
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

BlairStark at 2007-9-9 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
.NET Development
.NET Framework Data Access and Storage

.NET Development Hot Topic

.NET Development New Topic

.NET Development

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified