100000 records in txt file, need fast way to upload to database

[jlkidd]

Code looks like this:

myReader = File.OpenText("\\Barracuda\MIS Documents\Autocar\imperialvdmstr.txt&quot


'read data in from imperialvdmstr and place in database
While myReader.Peek <> -1

'newRow = dtImperialVDMstr.NewRow()
newRow = DsImperialMstr1.Imperialvdmstr.NewRow

'read line of text
myStringImperialVDMstr = myReader.ReadLine
myStringImperialVDMstr = myStringImperialVDMstr & &quot; &quot;

'use myString to divide up the data
'add text info to appropriate field
newRow(&quot;vendor_id&quot = myStringImperialVDMstr.Substring(0, 6)
newRow(&quot;vendor_name&quot = myStringImperialVDMstr.Substring(6, 25)
newRow(&quot;Vendor_abbrev&quot = myStringImperialVDMstr.Substring(31, 10)
newRow(&quot;vendor_address_1&quot = myStringImperialVDMstr.Substring(41, 25)
newRow(&quot;vendor_address_2&quot = myStringImperialVDMstr.Substring(66, 25)
newRow(&quot;Vendor_city&quot = myStringImperialVDMstr.Substring(91, 25)
newRow(&quot;Vendor_state&quot = myStringImperialVDMstr.Substring(116, 2)
newRow(&quot;vendor_zip&quot = myStringImperialVDMstr.Substring(118, 10)
newRow(&quot;vendor_country&quot = myStringImperialVDMstr.Substring(128, 3)
newRow(&quot;vendor_phone&quot = myStringImperialVDMstr.Substring(131, 20)
newRow(&quot;vendor_fax&quot = myStringImperialVDMstr.Substring(151, 20)
newRow(&quot;vendor_terms_code&quot = myStringImperialVDMstr.Substring(171, 3)
newRow(&quot;vendor_terms_desc&quot = myStringImperialVDMstr.Substring(174, 25)
newRow(&quot;vendor_contact&quot = myStringImperialVDMstr.Substring(199, 25)
newRow(&quot;blanket_order_ok&quot = myStringImperialVDMstr.Substring(224, 1)
newRow(&quot;tax_suffix&quot = myStringImperialVDMstr.Substring(225, 5)
newRow(&quot;sales_tax_id_1&quot = myStringImperialVDMstr.Substring(230, 15)
newRow(&quot;sales_tax_id_2&quot = myStringImperialVDMstr.Substring(245, 15)
newRow(&quot;purchase_lead_time&quot = myStringImperialVDMstr.Substring(259, 3)

'add new row for the dataset.table
DsImperialMstr1.Tables(&quot;imperialvdmstr&quot.Rows.Add(newRow)
newRow = Nothing

End While


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
When I hit around the 50,000 mark the process becomes so slow that it does about 1 record per/second.

Is their any quicker way to add these to the database from a fixed length file.

 

[fellthor]

Your sample show that you are building this table in memory. Your problem could be creating a lot of garbage collections or swaping. If you can try writing batches of data to the database and clear out the data service table.

If you're using SQL Server 2000 I would have your data service call a stored procedure that uses 'Bulk Insert' to copy a data file into a database table. This is really fast. If you're writing to multiple table you would have to manage the load sequence to avoid constraint issue.

Dependent on your infrastructure you may need to move the file to a location where the SQL Server can pick up the data.

As an aside, consider using the FileSystemWatcher class to determine when the file watcher arrives. Watch out for the buffer overflow exception. FileSystemWatch use memory from the stack.

Best of luck,

fellthor

 

[digiduck]

If its getting slow around the 50,000 mark why not just stop it there and then pick up where you left off on the second run?

 

[jlkidd]

I am using a Access 2000 so I am unable to use &quot;Bulk Insert,&quot; as far as I know.

The speed begins slowing after just 1,000 records but at 50,000 it becomes so slow that it would only do around 1 record per second.

I would like to know more about what fellthor mentioned, &quot;writing batches of data to the database and clear out the data service table.&quot; I've never heard of that and I am unsure what I would need to do.

Thanks for your responses.

 

[KornGeek]

In Access 2000, you can link to the text file so that Access sees it as a table, and then use an append query to copy the records. This is a pretty efficient method.

 

[young]

In Access, I use:

DoCmd.TransferText acImportDelim, &quot;My Import Specification&quot;, &quot;my_destination_table&quot;, &quot;textfile.txt&quot;, False, &quot;&quot;

 

[jlkidd]

Is their someway that I could run the VBA Access 2000 DoCMD.TransferText from outside of Access. My goal is to eventually access this from within a service. I would like to be able to use the filesystemwatcher from within VB.Net to watch an FTP site so that when a new text file is sent, no matter what time I can upload the files to the table in the database. I could do that if either in VBA I can somehow watch the folder for new files or if somehow from from VB.net I could tell It to run the function or program.
Thanks

 

[Nollauno]

Why not use something like SQL-Loader. Write an interface to VB.Net - 100,000 records would go in, in about 2 seconds.

 

[jlkidd]

Nollauno,

I feel like I may be asking a ignorant question but what is SQL-Loader?

 

[sunaj]

Hi,

This load 100000 rows (20 character string and an integer in each row) into a .mdb in ~ 1 sec on my 2.4 Ghz Xeon.

---------------------------------------------------------
Dim Conn As New OleDb.OleDbConnection(&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & txtmdbFile.Text)
Conn.Open()
Dim Comm As New OleDb.OleDbCommand(&quot;INSERT INTO tblTest(MyString,MyNumeric) SELECT col1,col2 FROM [Text;Database=d:\tmp\;HDR=YES;FMT=Delimited].[ADOTest.csv]&quot;, Conn)
Comm.ExecuteNonQuery()
Conn.Close()
----------------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jlkidd]

sunaj

Is their a way to do that with a fixed lenth text file instead of a comma delimited one?

 

[sunaj]

Yes.

The command could look something like:

&quot;INSERT INTO tblTest(MyString,MyNumeric) SELECT col1,col2 FROM [Text;Database=d:\tmp\;HDR=YES;Format = FixedLength;Col1=MyString Char Width 25;Col2=MyNumeric Float Width 10].[ADOTest.txt]&quot;

For more information search the web for &quot;ISAM fixed width&quot; and you'll find lots of documentation. E.g.

http://msdn.microsoft.com/library/d.../en-us/office97/html/workingwithtextfiles.asp

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'