Question Custom Import Tool performance

[toddbailey]

Hi All I wrote a simple import tool that imports 60K records into an Access data base. It basically reads a line from a text file, creates an insert statement, executes the insert and repeats.

If I open a connection 1 time and try to populate all 60K records using 1 connection, the process takes much longer than if I close and reopen the connection after every 1000 records. Any one have ideas why?

thanks

here is a small portion of the import process

System.Console.WriteLine(" Loading table");
long rowcnt = 0;
while ((text_line = freader.ReadLine()) != null)
{
rowcnt++
if (text_line.Trim().Length < 10) break; // look for a blank line then exit
string sqlstring = buildsqlstring(text_line,UseAccess);
ExecSqlString(sqlstring, conn, UseAccess);
if (rowcnt % 100 == 0)
{
System.Console.Write(rowcnt);
System.Console.Write(" rows processed. \r");
}
// for each 1,000 rows, close and reopen the connection to the database
if (rowcnt % 1000 == 0)
{
CloseDataBase(conn);
conn = OpenDataBase(UseAccess);
}

}
System.Console.WriteLine(" ");
System.Console.Write(rowcnt);
System.Console.WriteLine(" Data Rows Processed");

 

[DJkarl]

This isn't running in Access is it, this looks like C# code.

 

[toddbailey]

Correct,

C#, VB, C, Java or any other programming language was needed to implement the import. A lot of conditional tasks were needed to clean up the data and automate for weekly update.

the bottom line is importing a data file into an access database.

 

[DJkarl]

Well without seeing all of the code its difficult to say exactly why this is happening, but I would suspect it's related to a memory issue. While your connection is opened and being used the Garbage Collector won't try to free up the memory consumed by it, when you close, dispose, and reopen it the GC can run and free up memory that is no longer being used.