Normalising data - bulk inserts using loop

Question

Hi guys

Does anyone have any tips that could help speed up a process of breaking down a table and inserting a large number of records into a new table.

I'm currently using Access and VBA to convert a table that contains records with a large string (700 + characters) into a new table where each character has its own record (row). I'm doing this by looping through the string 1 character at a time and inserting into the new table using simple DAO in VBA.

Currently I'm working with a small subset of data - 300 records each with a 700 character string. This process takes about 3 hours to run so isn't going to scale up to the full dataset of 50,000 records!

table 1 structure

id - string 001 - abcdefg

becomes

table 2 structure

id - string 001 - a 001 - b 001 - c . . . . . .

I'm open to any suggestions that could improve things.

Cheers

Phil

Answer 1

Consider this example using Northwind. Create a table called Sequence with an INTEGER (Access = Long Integer) and populate it with values 1 to 20 (i.e. 20 row table). Then use this ACE/Jet syntax SQL code to parse each letter of the employees' last names:

SELECT E1.EmployeeID, E1.LastName, S1.seq, MID(E1.LastName, S1.Seq, 1)
  FROM Employees AS E1, Sequence AS S1
 WHERE S1.seq BETWEEN 1 AND LEN(E1.LastName);

Answer 2

When doing bulk inserts, you can often get a substantial performance boost by dropping the table's indexes, doing the bulk insert, and then restoring the indexes. In one case, when inserting a couple million records into a MySQL table, I've seen this bring the run time down from 17 hours to about 20 minutes.

I can't advise specifically regarding Access (I haven't used it since Access 2, 15 or so years ago), but the general technique is applicable to pretty much any database engine.

Answer 3

We have a routine that transposes data. Not sure if the code is optimized, but it runs significantly faster after the file has been compacted.

Doing a lot of deleting and rebuilding of tables bloats an .mdb file significantly.