Data Normalization - Bulk Inserts Using a Loop
Does anyone have any tips that can help speed up the process of splitting a table and inserting a large number of records into a new table.
I am currently using Access and VBA to convert a table that contains large row (700+ characters) records to a new table where each character has its own record (row). I do this by iterating over the line character 1 at a time and inserting into a new table using a simple DAO in VBA.
I am currently working with a small subset of data - 300 records each with a 700 character string. This process takes about 3 hours, so it's not going to scale to a full dataset of 50,000 records!
Table 1 structure
id - line 001 - abcdefg
becomes
Table 2 structure
id - line 001 - a 001 - b 001 - c,,,.
I am open to any suggestions that can improve the situation.
Greetings
Phil
Let's look at this example using Northwind. Create a table called "Sequence" with INTEGER
(Access = Long Integer) and fill it with values from 1 to 20 (ie 20 table rows). Then use this ACE / Jet syntax SQL to parse each letter of the employee surnames:
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);
a source to share
When performing bulk inserts, you can often get significant performance gains by dropping the indexes on the table, doing the bulk insert, and then rebuilding the indexes. On one occasion, when you are inserting a couple of million records into a MySQL table, I have seen it reduce the execution time from 17 hours to 20 minutes.
I can't specifically flesh out access to Access (I didn't use it with Access 2, about 15 years ago), but the general technique applies to pretty much all database engines.
a source to share