Performance - Importing text files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use ADO.NET to import records from a comma delimited text file into an Access data table. The table has 1 int field (prim key), 5 text fields and 4 date fields. The text file has 50000 records. There are no records in the table to start with

I parse the text file and for each record, I do

DataRow row = dt.NewRow()
row[field1] = ..
row[field2] = ..
et

dt.Rows.Add(row)

after I have added the 50 K rows (under 20 seconds), I do

daUserData.Update(dsUserData, "UserData")

to update the physical database. The update takes about 12 minutes. Conversely, if I import the whole file in Access, the total time is under 20 seconds. One other thing, after the import, the database has grown about 25 mb. However, most of this can be compacted.

I am hoping there is a better way to do this. Any help greatly appreciated

Regards

Bob Costello
 
If you were to create a business object with properties for all the
fields in your text file, a persistence framework like Gentle (see
below) could generate the sql and perform the inserts for you.

You simply loop over all lines, and inside the loop construct the
business object and call Persist() on it.

Gentle caches the generated SQL and type/table mapping metadata, and is
thus fairly fast even though it works with objects.

I have a test case where objects are inserted, selected, updated,
selected, deleted and selected (thus, 4 unique and 6 total sql
commands). A loop of 10000 runs completes in roughly 2 minutes, and
includes lots of overhead you wouldn't have (object construction
from results, for instance).

Gentle is open source and freely useable. You can find the source at
http://workspaces.gotdotnet.com/gentle if interested.

Yours,
Morten
 
Morten. Thanks. I will check it out. I was thinking of several possibilities myself. Buffering the updates and doing intermediate compacts of the database. I will probably also try SQL Insert statements.
 
Back
Top