How to insert 800000 records into MS Access table?

Question

I need to insert 800,000 records into an MS Access table. I am using Delphi 2007 and TAdoXxxx components. The table contains some integer fields, one floating point field and one text field with one character. There is a primary key in one of the integer fields (which is not auto-synchronizing) and two indices for the other integer and the float field.

Inserting data using AdoTable.AppendRecord(...) takes> 10 minutes, which is unacceptable, since this is done every time the user starts using a new database with the program. I can’t pre-populate the table because the data comes from another database (which is not available through ADO ).

I managed to get down for about 1 minute by writing to a tab-separated text file and using the tAdoCommand object to execute

 insert into table (...) select * from [filename.txt] in "c:\somedir" "Text;HDR=Yes" 

But I don't like the overhead.

There must be a better way, I think.

EDIT:

Additional Information:

• MS Access was chosen because it does not need additional installation on the target machine (s), and the entire database is contained in a single file that can be easily copied.
• This is a single user application.
• Data will be inserted only once and will not change throughout the life of the database. Although the table contains one additional field, which is used as a flag to indicate that the corresponding record in another database has been processed by the user.
• One minute is acceptable (it will also be up to 3 minutes), and my solution works, but it seems to me too complicated, so I thought that there should be an easier way to do this.
• After the data has been inserted, the performance of the table is pretty good.
• When I started planning / implementing the function of the program working with the Access database, the table was not required. This became necessary later when the client requested another function. (Isn't that always the case?)

EDIT:

Of all the answers I have received so far, it seems that I already have the fastest way to insert so much data into the Access table. Thanks to everyone, I appreciate your help.

Answer 1

Your text solution seems to be the fastest, but you can get it faster if you can get the pre-allocated MS Access in a size close to the end. You can do this by filling out a typical user database, closing the application (so that the buffers are flushed) and manually deleting all the records of this large table, but not shortening / compacting it.

So use this file to start the actual filling. Access will not request any (or very little) additional disk space. Do not remember if MS Access has a way to automate this, but it can help a lot ...

Answer 2

Since you said that the data of 800K records will not change during the life of the database, I would suggest a link to a text file in the form of a table and generally skip the insert.

If you insist on pulling it into the database, then 800,000 records in 1 minute exceed 13,000 / second. I don’t think you will beat this in MS Access.

If you want it to be more receptive to the user, you might want to download some minimal data set and set up a background thread to load the rest while you work.

Answer 3

It will be faster without indexes. Can you add them after import?

There are a number of suggestions that may be of interest in this thread. Recording a slow MSAccess disc

Answer 4

How to skip a text file and use ODBC or OLEDB to import directly from the source table? This would mean changing the FROM clause to use the name of the source table and the corresponding connection string as the IN '' part of the FROM clause.

EDIT: Actually, I see you are saying that the source format is xBase, so you should use ISA xBase, which is part of Jet, instead of having to use ODBC or OLEDB. It will look something like this:

 INSERT INTO table (...) SELECT * FROM tablename IN 'c:\somedir\'[dBase 5.0;HDR=NO;IMEX=2;]; 

You may need to tweak this - I just grabbed a connection string for a linked table pointing to a DBF file, so the options may vary slightly.

Answer 5

How about an alternate location ...

It would be possible to make a copy of an existing Access database file in which you need this table, and then simply delete all the other data except for this large table (I don’t know if Access has an equivalent of something like “trim table” on SQL server )?

Answer 6

I would replace MS Access with another database, and for your situation I see Sqlite is the best choice, it does not require any installation on the client machine, as well as a very fast database and one of the best built-in database solutions.

You can use it in Delphi in two ways:

• You can download the Dll Database engine from Sqlite and use the Free Delphi component to access it, for example Delphi SQLite or SQLite4Delphi components

• Use DISQLite3 , in which the engine is built in, and you do not need to distribute DLLs with your application, they have a free version; -)

if you still need to use MS Access, try using TAdoCommand with the status of SQL Insert directly instead of using TADOTable, which should be faster than using TADOTable.Append;

Answer 7

You will not import 800,000 records in less than a minute, as someone said; it is really fast.

You can skip the annoying step of translating to text, however, if you use the correct method (DAO recordsets) to perform inserts. See the previous question I asked and answered StackOverflow: MS Access: why does ADODB.Recordset.BatchUpdate work much slower than Application.ImportXML?

Do not use INSERT INTO even with a DAO; he is slow. Do not use ADO; he is slow. But DAO + Delphi + Recordsets +, by instantiating the DbEngine COM object directly (instead of the Access.Application object), will give you a lot of speed.

Answer 8

You are looking in the right direction in one direction. Using one statement for bulk insertion will be faster than trying to iterate over data and insert it row by row. Access, which is a file database, will be extremely slow in iterative writing.

The problem is that Access handles the way it optimizes records internally, and there really is no way to control this. You have probably reached the maximum efficiency of the INSERT statement. For added speed, you probably should evaluate if there is a way to write 800,000 records to the database each time the application starts.

Answer 9

Get SQL Server Express (free) and connect to it from access to an external table. SQL Express is much faster than MS Access.

Answer 10

I would pre-populate the database and transfer the file to them, rather than populate the existing (but empty) database.

If the data needs to be filled in, save the ODBC access database (MDB file) synchronized on the server using a bit of code to see the changes in the main database and copy them to the access database.

When the user requests a new database, archive the MDB, transfer it to them and open it.

In addition, you can find code that opens and inserts data into databases directly.

Alternatively, you can find a different format (except csv), access to which is imported faster.

-Adam

Answer 11

Perhaps you can open the ADO recordset for the table with the adLockBatchOptimistic and CursorLocation adUseClient lock adUseClient , write all the data to the recordset, then perform a batch update ( rs.UpdateBatch ).

Answer 12

Also check how long it takes to copy the file. This will be the bottom line of how fast you can record data. In db, like SQL, a bulk-loading utility is usually required to get close to that speed. As far as I know, MS has never created a tool for direct access to MS Access tables, as bcp does. Specialized ETL tools also optimize some insert-related steps, for example, how SSIS does in-memory transforms, DTS also has some optimizations.

Answer 13

If it comes from dbase, can you just copy the data and index files and connect directly without downloading? It should be pretty effective (from the people who bring you FoxPro.) I believe that it would also use existing indexes.

At the very least, this should be a fairly efficient import of one team.

Answer 14

How many of the 800,000 entries change from one creation to another? Is it possible to pre-populate the records and then just update the ones that were changed in the external database when creating the new database?

This may allow you to quickly create a new database file.

Answer 15

How fast does your drive turn? If it is 7200RPM, then 800,000 lines in 3 minutes still make up 37 lines per disk revolution. I don’t think you will do much better.

Meanwhile, if the goal is to optimize the process, what about the links in the table?

You say you cannot access the source database through ADO. Can you customize a table link in MS Access to a table or view in the source database? Then a simple append request from the table link would copy the data from the source database to the target database for you. I'm not sure, but I think it will be pretty fast.

If you cannot configure the link to the table before execution, perhaps you could create a link to the table programmatically through ADO, then program the add request, and then call the append request.

Answer 16

HI The best way is to bulk insert from a txt file, as they said, you should insert your record into a txt file, and then insert the txt file into the table this time should be less than 3 seconds.