Optimizing File I/O & Database Inserts 1

[sbushway]

Hi,
I have a flatfile that contains about 7500 records that are delimited by a newline character - Chr(10). I'm reading that file into a string, parsing that string, and inserting each record into a SQL Server 2000 table. Here's my code:

Dim myFile as String = Server.MapPath("file.txt")
Dim myStreamReader as StreamReader = File.OpenText(myFile)
Dim entireFile as String = myStreamReader.ReadToEnd
Dim delimeter as String = Chr(10)
Dim contentsArray = Split(entireFile, delimeter)
Dim i as Integer
Dim uic, diary, parent, inac, title, add1, add2, add3, add4, state, zip as String

For i = 0 to UBound(contentsArray)
  uic = Mid(contentsArray(i), 1, 5)
  diary = Mid(contentsArray(i), 6, 2)
  parent = Mid(contentsArray(i), 8, 1)
  inac = Mid(contentsArray(i), 9, 1)
  title = Mid(contentsArray(i), 10, 50)
  add1 = Mid(contentsArray(i), 60, 30)
  add2 = Mid(contentsArray(i), 90, 30)
  add3 = Mid(contentsArray(i), 120, 30)
  add4 = Mid(contentsArray(i), 150, 25)
  state = Mid(contentsArray(i), 175, 2)
  zip = Mid(contentsArray(i), 177, 10)

  HERE'S WHERE I INSERT EACH RECORD USING A STORED PROCEDURE
Next

lblStatus.Text = "Finished"
myStreamReader.Close()

This takes about 3 minutes first time I run it. Each subsequent time takes about 1 minute, 45 seconds.

In ColdFusion, this program will run in about 1 minute, 30 seconds.

Is there any way to optimize my VB code to make it run faster?

Thanks in advance,
Suzanne

 

[sunaj]

Consider using bulk insert.
I bulk insert 2,8 million rows into an MS SQL server 2000 (very old test server) in less than 10 minutes.
If you cannot read the file directly from a bulk insert statement with a format file (which looking at your code I think you can), it is still MUCH faster to parse the data into a new file that can be bulk loaded (I parse the 2,8 mill. rows from several hundres files, 4.2 Gb total and bulk insert them on the test server in 45 minutes)

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

 

[sbushway]

Can you be more specific about a bulk insert? I'm a little confused.

Thank you for your help,
Suzanne

 

[nomi2000]

HI
This example imports order detail information from the specified data file using a pipe (|) as the field terminator and |\n as the row terminator.

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

Regards
Nouman


Nouman Zaheer
Software Engineer
MSR

 

[nomi2000]

One more thing to add
for Permissions of BULK INSERT that Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
so if you want to do this you have to assign the server role for that user
Regards
Nouman



Nouman Zaheer
Software Engineer
MSR

 

[sbushway]

Can you do a Bulk Insert with a fixed-length flat file??

 

[nomi2000]

Yes you can
the thing which i mentioned above should take in account regarding security when doing bulk insert so for your case
the code will be
You don't have to loop through as bulk-insert will do it in one hit
so here is code
At above
Imports System.Data 'to use OLEDB libraary

'in your function
Dim myFile as String = Server.MapPath("file.txt&quot
Dim strExecQuery as string="BULK INSERT Northwind.dbo.[Order Details] FROM '" & myFile & "'" &
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)"
Dim objCmdToExecute As New OleDb.OleDbCommand()
Dim objMainConnection As New OleDb.OleDbConnection()
objMainConnection.ConnectionString = "Your Connection String" 'Here set your conneciton string
With objCmdToExecute
.CommandText = strExecQuery
.CommandType = CommandType.Text
.Connection = objMainConnection
End with
'// Open connection.
objMainConnection.Open()
' // Execute query.
objCmdToExecute.ExecuteNonQuery()

Regards
Nouman

Nouman Zaheer
Software Engineer
MSR

 

[sbushway]

Hi Nouman,
I don't have any FIELDTERMINATORs since I'm using a fixed-width flatfile.

What would I do in my case?

Thank you for your time - I really appreciate it!
Suzanne

 

[nomi2000]

Hi suzzanne
no i don't think so you will be able to do that task without having the delimeters so in ur case the solution might be is to make at run-time a comma delimated or any other delimated text-file and then doing the BULK INSERT
so here is the code for making COMMA DELIMATED TEXT file
'STEP # 2
Dim sr As StreamReader
Dim sw As StreamWriter
' Open the file to read.
sr = File.OpenText("c:\test.txt&quot
sw = File.CreateText("c:\Temp.txt&quot
' Read each line in the file.
' When the end of the file is reached, return the value "-1".
Dim x As String
Dim strtoWrite As String
While sr.Peek <> -1
x = sr.ReadLine()
x = x.PadRight(80)
strtoWrite = x.Substring(1, 5) & &quot;,&quot; & x.Substring(6, 2) & &quot;,&quot; & x.Substring(7, 7) & &quot;,&quot; & x.Substring(14, 2) & &quot;,&quot; & x.Substring(16, 3)
sw.WriteLine(strtoWrite)
Console.WriteLine(x)
End While
reached.&quot
sr.Close()
sw.Close()

'STEP # 2

Dim myFile as String = &quot;c:\Temp.txt&quot; 'It should be the file which u created in STEP # 1
Dim strExecQuery as string=&quot;BULK INSERT Northwind.dbo.[Order Details] FROM '&quot; & myFile & &quot;'&quot; &
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)&quot;
Dim objCmdToExecute As New OleDb.OleDbCommand()
Dim objMainConnection As New OleDb.OleDbConnection()
objMainConnection.ConnectionString = &quot;Your Connection String&quot; 'Here set your conneciton string
With objCmdToExecute
.CommandText = strExecQuery
.CommandType = CommandType.Text
.Connection = objMainConnection
End with
'// Open connection.
objMainConnection.Open()
' // Execute query.
objCmdToExecute.ExecuteNonQuery()

Regards


Nouman Zaheer
Software Engineer
MSR