1

So I am writing software that has to insert thousands of records into a Microsoft Access. Currently I am using an RBAR(row by agonising row) approach with JET. I wrote a question on SO before asking how to speed up the process and was told to use DAO instead of JET as it would be much faster, and to avoid RBAR.

I am now in the situation where I am ready to implement a faster solution however, I have some further questions. Firstly something simple, does anyone know of a good tutorial for DAO syntax in vb.net as I have tried to look for one online but have struggled. Also does anyone know of a library to use instead?

Secondly what is the best way to avoid an RBAR approach to inserting data into a database. I assume that this is using a record set, but I am unsure how to go about implementing one of these, or the SQL query required to insert several rows at once.

Finally I was told before that ms-access was not the best thing to use a it is slow. Does anyone have any suggestions of a different alternative to access? I would need it to be a free commercial license which I know is a stretch. I only use the database as store for the data I don't need any of the user interfacing that access provides. I found a question on SO about this which suggests SQLite, MySQL or FireBird. Does anyone know of the speed of these solutions compared to Access and whether they are still free for commercial use.

So I am basically looking for advice for how to read/write large amounts of data to a database as quickly as possible. Any help or suggestions would be greatly appreciated.

Community
  • 1
  • 1
Pezzzz
  • 738
  • 4
  • 14
  • 33
  • 2
    MySQL is an excellent well-rounded database server for most applications. It supports bulk INSERTs without problem. SQLite is good for when you need a file-based database. It is similar to Access where there is no real server handling writes to disk. – Brad Oct 16 '12 at 14:23
  • If you are used to using MS access, and your needs are minimal, you can use the free MS SQL Server Express (note that there are limitations as to size etc., but for small databases, this is probably not an issue). – Paddy Oct 16 '12 at 14:24
  • What is the source of the data you wish to insert? Does it come from a file? A different database? You can get data into an Access db file without RBAR by importing the data as a single unit operation. The specific method you would use depends on the nature of the data source. – HansUp Oct 16 '12 at 15:10
  • @Hans, data is generally taken from the database and has operations performed on it in a visual basic front end, before being put back into the database from arrays or lists. When the data is first put into the database it comes from excel, that could be converted into a .csv format. – Pezzzz Oct 17 '12 at 07:18

2 Answers2

1

Bulk inserts using MS SQL Express (free edition):

http://www.sswug.org/articles/viewarticle.aspx?id=35680

Basically involves using the bulk insert syntax and passing in a file:

BULK INSERT Test..Clients FROM
'c:\TestData.csv'
WITH (
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
Paddy
  • 33,309
  • 15
  • 79
  • 114
1

Instead of access, take a look at firebird Its free and very reliable.

Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms. Firebird offers excellent concurrency, high performance, and powerful language support for stored procedures and triggers. It has been used in production systems, under a variety of names, since 1981.

The Firebird Project is a commercially independent project of C and C++ programmers, technical advisors and supporters developing and enhancing a multi-platform relational database management system based on the source code released by Inprise Corp (now known as Borland Software Corp) on 25 July, 2000.

Diego
  • 34,802
  • 21
  • 91
  • 134
  • I had a quick look through that website on Firebird and it appears to me that I would have to run Firebird as a server, is this true? I would like the database to be stored on a network drive and to not have a machine tasked to look after it. – Pezzzz Oct 17 '12 at 10:52
  • so you dont want a RDMS, you just want a file :) Do you know the difference? If you only want a file as a DB you are stuck with access – Diego Oct 17 '12 at 10:55
  • I do not know the difference :P, or what they entail. I want something that can sit on the network and hold the data. I don't need any user interfacing and I want to be able to leave it as alone as possible. Would running it as a server require a PC to hold the server and deal with requests for data? – Pezzzz Oct 17 '12 at 14:40
  • RDMS are Relational Database Management Systems like SQl Server, Oracle or Firebird. They are services that manage data files. Access is only a File sitting on a disk – Diego Oct 17 '12 at 14:46
  • So with Firebird etc, something has to run Firebird like a server. It can't just be a file sat on a disk? – Pezzzz Oct 17 '12 at 16:02
  • no it cant, the file does exist but you need a service to manipulate the file and you communicate with the service – Diego Oct 17 '12 at 18:44
  • @Diego, there is FB Embedded edition for local file access without services – Fr0sT Mar 28 '13 at 10:35