jeudi 7 janvier 2016

C#/SQLite performance vs FoxPro for importing files

I "inherited" some legacy software for manipulation of large text files which is currently written in Visual Foxpro (version 7 specifically).

I'm exploring options for replacing these programs with something new that will be easier to modify and maintain. However, I'm unable to get close to the performance of Foxpro for importing large text files.

For example I have a ~1gb text file with ~110,000 records each having 213 fields (each row is 9247 characters long) that I'm testing with.

Using Foxpro:

? SECONDS()
USE new
APPEND FROM test.dat TYPE sdf
USE
? SECONDS()

This import to a database is completed in just under 10 seconds on my computer.

Using C#/SQLite (with Filehelpers and System.Data.SQLite), the quickest I have been able to get this import to complete is over a minute. I've tried optimizing this as best possible using suggestions from this question (such as transactions etc). Realistically a minute for importing a 1gb file doesn't seem bad if I wasn't comparing it to 10 seconds for Foxpro.

The approximate breakdown of time spent during the ~1 minute is:

Filehelpers reading file and parsing: 12 seconds.
Building SQL commands from Filehelpers object: 15 seconds.
Running individual ExecuteNonQuery()'s: 24 seconds.
Committing transactions (every 5000 records): 12 seconds.

When compared to the thread linked my inserts per second are much slower but my records have 213 fields vs 7 so this is expected. If I break it down by fields/second I'm at approximately 360,000 vs the 630,000 of the thread. Insert rate by megabyte is ~2.24 megabyte/s for the other poster and 15.4 megatbyte/s for me. Therefore I think my performance is comparable to the other poster and there likely isn't a ton more optimization I can do.

Why is this so much slower than the Foxpro import (5-6x slower)? Is this just apples to oranges and I should just accept the slower speed in trade-off for the other benefits I get by using newer tech?

Aucun commentaire:

Enregistrer un commentaire