File Import Slows Down

[Guest]

I periodically import large text files into a database using vba code. The
importing starts out very fast (1+mb/sec), but then slows down considerably
(to about 33kb/sec) after a couple minutes. Consequently, each file is
taking about 30 minutes to import. What would cause the imports to start out
very fast, and then slow down so much? I'm importing from the harddrive, and
there's plenty of free space on the harddrive. I'm using a Dell Latitude
D600 X86 notebook with:

Total Physical Memory: 1,024MB
Avail Physical Memory: 342.71MB
Total Virtual Memory: 4.35GB
Avail Virtual Memory: 3.26GB
Page File Space: 3.35GB

Here's a sample of my vba code:
Private Sub cmdCO_Click()
' Delete Existing Records
CurrentDb.Execute "DELETE * FROM CO;"

Dim rs1 As DAO.Recordset
Dim FileName As String
Dim strFilter As String
Dim strFileDesc As String
Dim strFileExt As String
Dim FileDate As Date
Dim FileNum As Integer
strFileDesc = "Text Files (*.txt)"
strFileExt = "bacCO*.txt"
strFilter = ahtAddFilterItem(strFilter, strFileDesc, strFileExt)
DoCmd.Hourglass False
FileName = ahtCommonFileOpenSave( _
InitialDir:="D:\", _
Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:="Please Select File for Import ... bacCO.txt", _
Flags:=ahtOFN_HIDEREADONLY)
FileDate = FileDateTime(FileName)
FileNum = FreeFile()

Set rs1 = CurrentDb.OpenRecordset("CO")
DoCmd.Hourglass True

Dim InputString As String
Open FileName For Input As #FileNum
Do While Not (EOF(FileNum))
Line Input #FileNum, InputString
rs1.AddNew
rs1.Fields("Co") = Mid(InputString, 1, 3)
rs1.Fields("CoName") = Trim(Mid(InputString, 5, 50))
rs1.Fields("DB") = Trim(Mid(InputString, 56, 4))
rs1.Fields("Code") = Mid(InputString, 61, 3)
rs1.Fields("FileDate") = Mid(InputString, 65, 2) & "/" & _
Mid(InputString, 67, 2) & "/" & _
Mid(InputString, 69, 2)
rs1.Update
Loop
rs1.Close
Close #FileNum
DoCmd.Hourglass False
MsgBox "Import of bacCO.txt Is Complete", _
vbInformation, "File Import Complete"
End Sub

Thanks

 

[John Nurick]

This sort of behaviour is normal in my experience.VBA recordset operations
and string manipulation aren't exactly speedy at the best of times. It may
be worth trying one or other of these approaches:

1) Use your favourite scripting language to convert the fixed-width file
into a tab-delimited or CSV file with the right fields, including the
formatted date; then use any of the usual techniques to import that.

2) Link the text file using an import specification that gives you all the
fields (including separate text fields for day, month and year); then use an
append query to move the data into the "main" table. Use a calculated field
to get the date, e.g.
DateSerial([TheDay],[TheMonth],[TheYear]) AS FileDate

If the text file is really big (maybe 100MB or more) it may save time if
you drop all indexes on the table (or as many as possible) and re-create
them afterwards.

 

[Guest]

Thanks for responding John.
The files I'm importing are approximately 250MB.
I tried your suggestion to drop the Indexes, and that improved the
performance significantly.
Thanks again for your help.

This sort of behaviour is normal in my experience.VBA recordset operations
and string manipulation aren't exactly speedy at the best of times. It may
be worth trying one or other of these approaches:

1) Use your favourite scripting language to convert the fixed-width file
into a tab-delimited or CSV file with the right fields, including the
formatted date; then use any of the usual techniques to import that.

2) Link the text file using an import specification that gives you all the
fields (including separate text fields for day, month and year); then use an
append query to move the data into the "main" table. Use a calculated field
to get the date, e.g.
DateSerial([TheDay],[TheMonth],[TheYear]) AS FileDate

If the text file is really big (maybe 100MB or more) it may save time if
you drop all indexes on the table (or as many as possible) and re-create
them afterwards.

I periodically import large text files into a database using vba code. The
importing starts out very fast (1+mb/sec), but then slows down
considerably
(to about 33kb/sec) after a couple minutes. Consequently, each file is
taking about 30 minutes to import. What would cause the imports to start
out
very fast, and then slow down so much? I'm importing from the harddrive,
and
there's plenty of free space on the harddrive. I'm using a Dell Latitude
D600 X86 notebook with:

Here's a sample of my vba code:
Private Sub cmdCO_Click()
' Delete Existing Records
CurrentDb.Execute "DELETE * FROM CO;"

Dim rs1 As DAO.Recordset
Dim FileName As String
Dim strFilter As String
Dim strFileDesc As String
Dim strFileExt As String
Dim FileDate As Date
Dim FileNum As Integer
strFileDesc = "Text Files (*.txt)"
strFileExt = "bacCO*.txt"
strFilter = ahtAddFilterItem(strFilter, strFileDesc, strFileExt)
DoCmd.Hourglass False
FileName = ahtCommonFileOpenSave( _
InitialDir:="D:\", _
Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:="Please Select File for Import ... bacCO.txt", _
Flags:=ahtOFN_HIDEREADONLY)
FileDate = FileDateTime(FileName)
FileNum = FreeFile()

Set rs1 = CurrentDb.OpenRecordset("CO")
DoCmd.Hourglass True

Dim InputString As String
Open FileName For Input As #FileNum
Do While Not (EOF(FileNum))
Line Input #FileNum, InputString
rs1.AddNew
rs1.Fields("Co") = Mid(InputString, 1, 3)
rs1.Fields("CoName") = Trim(Mid(InputString, 5, 50))
rs1.Fields("DB") = Trim(Mid(InputString, 56, 4))
rs1.Fields("Code") = Mid(InputString, 61, 3)
rs1.Fields("FileDate") = Mid(InputString, 65, 2) & "/" & _
Mid(InputString, 67, 2) & "/" & _
Mid(InputString, 69, 2)
rs1.Update
Loop
rs1.Close
Close #FileNum
DoCmd.Hourglass False
MsgBox "Import of bacCO.txt Is Complete", _
vbInformation, "File Import Complete"
End Sub

Thanks