Load Text Files To A Database 10 Times Faster

By Robbe D. Morris

Printer Friendly Version


Robbe & Melisa Morris


While completing a contract project for an Outbound Telemarketing company recently, I ran into a situation where a creative solution to an age old problem was needed.  We desperately needed to improve the performance of our desktop application that read in fixed position, fixed length, ASCII text files and handled various inserts and updates to multiple tables for each record.  To complicate matters, the load process had to take the following requirements into account:
1. All access to the SQL Server database had to be done remotely.  Local access was not allowed.
2. With each record read in from the text file, conditional statements as well as table inserts with the newly generated foreign keys needed to be handled accordingly.
3. It had to be fast enough to be feasible over a very slow network.
4. Needed to be simple enough for our computer operators to run without support from the programming group.
5. Be able to insert characters that would typically foul up standard SQL insert/update statements.


These requirements really left me with the option of creating a desktop app that could read the text files line by line, pull out specific pieces of data by position on the record, and then perform the various inserts and updates required.  Typically, an approach using the ADO Command object and SQL Server stored procedures would be used on a record by record basis.  Of course, loading 50,000 to 500,000 records can be quite time consuming and I needed an approach that would be much faster.  So, I put on my thinking cap and began to look for alternatives that could at least double the speed of our current processes.  As it turned out, I was able to increase that speed by at least a factor of ten just working against a local instance of SQL Server.  Depending on the size of the text file (ie number of records to process), the speed improvements gradually improved above and beyond the factor of 10 when applied to our remote database servers over the network.
Here's how I did it.  I assumed that the largest time consumer in the process was the repeated trips to the database for each record.  A little test I put together confirmed that.  With this in mind, a test app was written to compare the current approach against one that would send a thousand records at a time to the database to be processed.  Rather than parsing the text file locally in the desktop app for each record, I created a process to package up a thousand records at a time and send the whole string to a similar stored procedure.  The stored procedure would iterate through the string and parse out the individual pieces of data and react accordingly.  A key point to mention is that I used a slight variation of the string builder class mentioned in another article here on EggHeadCafe:  A Fast String Class for ASP Pages.  Using standard string concatenation techniques would have dramatically impacted the performance gains in this endeavor.
In your implementations, you may have to adjust the number of records you can send to the stored procedure at any given time.  If you don't need unicode support, you can set the internal string to be varchar and support 8000 bytes.  Otherwise, if you use nvarchar, you may need to reduce that.  You may also opt to use a slightly different approach to parse the data directly from the varchar input parameter directly.  In either event, it is just something you need to consider.  The fewer trips you make to the database, the faster your process will be.
All of the test code has been included here for your review.  You'll notice that I used a column in the tables called RecordNum.  I've often found it useful to flag records as they are being loaded to make it much easier to break out large text files into smaller groups of records to be used on predictive dialers.  As always, if you would like to comment on this article, feel free to email me at the address at the bottom of this article.

Visual Basic basMain.bas Module (Primary Test Oriented Code)
 

   Option Explicit

Public Const mksTestFileName = "egghead.txt"
Public UI As cUI

Public Enum ekTestOptions
            EggHeadTest1 = 1
            EggHeadTest2 = 2
End Enum
            
 
Public Sub Main()

   Set UI = New cUI
   
   UI.AppPath = App.Path & "\"
    
    If InStr(1, UI.AppPath, "VB98", vbTextCompare) > 0 Then
       UI.AppPath = "C:\EggHeadTest\"
       ' This is to prevent VB from using the default Visual Studio as it's root
       ' when developing in the IDE.  Simply change the path to fit the
       ' folder where your source code resides.  This has no effect on
       ' production executables.
    End If
    
   DeleteExistingTestRecords
   CreateTestFile
   
   UI.TimerInit
   LoadTest ekTestOptions.EggHeadTest1
   Debug.Print "Single Records: " & UI.TimerGetElapsedTime
   
   UI.TimerInit
   LoadTest ekTestOptions.EggHeadTest2
   Debug.Print "Multiple Records: " & UI.TimerGetElapsedTime
   
   ExitApp
   
End Sub
 

Public Sub ExitApp()
    Set UI = Nothing
    Err.Clear
    End
End Sub

Public Sub DeleteExistingTestRecords()
   On Error GoTo ErrHandler
   
    If UI.DBOpen = True Then
       UI.ADOCon.Execute "delete  from eggheadtest1"
       UI.ADOCon.Execute "delete  from eggheadtest1trans"
       UI.ADOCon.Execute "delete  from eggheadtest2"
       UI.ADOCon.Execute "delete  from eggheadtest2trans"
    End If
 
ErrHandler:
     If Err.Number <> 0 Then MsgBox "Failure to delete test records. " & vbCrLf & Err.Description
          On Error Resume Next
          UI.DBClose
          Err.Clear
End Sub


Public Sub CreateTestFile()

            Dim lTot As Long
            Dim lCnt As Long
            Dim oFS As Scripting.FileSystemObject
            Dim oFSFile As Scripting.TextStream
 
            On Error GoTo ErrHandler
   
            Const ForWriting = 2

            Set oFS = New Scripting.FileSystemObject
 
             If oFS.FileExists(UI.AppPath & mksTestFileName) = True Then 
			                 oFS.DeleteFile UI.AppPath & mksTestFileName, True
   
                 Set oFSFile = oFS.OpenTextFile(UI.AppPath & mksTestFileName, ForWriting, True)
 
            lTot = 20005
         
           ' Record length = 100
         
            For lCnt = 1 To lTot
                    oFSFile.Write "4075551212" & "Rush" & Space(6) & "Limbaugh" & Space(2)
					& "12 Main St" & Space(2) & Space(44) & "Test" & vbCrLf
                   DoEvents
            Next
            
            oFSFile.Close
                   
          
ErrHandler:
          If Err.Number <> 0 Then MsgBox "Failure to open file for writing. " &
		                vbCrLf & Err.Description
          On Error Resume Next
          Set oFSFile = Nothing
          Set oFS = Nothing
          Err.Clear
End Sub


Public Sub LoadTest(ByRef ekTestOption As ekTestOptions)

      Dim sRecord As String
      Dim lTotRecCnt As Long
      Dim lRecLength As Long
      Dim sMassRec As String
      Dim lMassRecCnt As Long
      Dim lStartRecord As Long
      Dim lRecordCnt As Long
      Dim lRecordCntMax As Long
      Dim lRecs As String
     
      
      On Error GoTo ErrHandler
      
      ' If we use the Open/For Input method, it allows us to reference one record at
	  ' a time .v loading up a 40-50MB file into
      ' a string variable with the Scripting.FileSystemObject.  But, remember that it
	  ' removes the vbcrlf that was at the end of each
      ' record.  Thus, we adjusted the previous record length of 100 to 98.
      
      UI.HourGlass True
      
      lRecLength = 98
      lRecordCntMax = 1000
      
      UI.DBOpen
      UI.DBClearParameters
      
      Open UI.AppPath & mksTestFileName For Input As #1
      
       Do Until EOF(1)
       
              lTotRecCnt = lTotRecCnt + 1
              Line Input #1, sRecord
              DoEvents
              Select Case ekTestOption
              
                          Case ekTestOptions.EggHeadTest1
                          
                               With UI.ADOCom
                                 .CommandText = "spEggHeadTest" & ekTestOption
                                 .CommandType = adCmdStoredProc
                                 .Parameters.Append 
                                 .CreateParameter("@RETURN_VALUE", 
                                        adInteger, adParamReturnValue, 0)
                                        .Parameters.Append
                                 .CreateParameter("@Phone",
                                adVarWChar, adParamInput, 10, Mid(sRecord, 1, 10))
                                 .Parameters.Append .CreateParameter("@FName",
                                  adVarWChar, adParamInput, 50, Mid(sRecord, 11, 10))
                                 .Parameters.Append .CreateParameter("@LName",
                                  adVarWChar, adParamInput, 50, Mid(sRecord, 21, 10))
                                 .Parameters.Append .CreateParameter("@RecordData",
                                   adVarWChar, adParamInput, 500, Mid(sRecord, 31, 64))
                                 .Parameters.Append .CreateParameter("@LastPieceOfRecord", 
                                  adVarWChar, adParamInput, 50, Mid(sRecord, 95, 4))
                                 .Parameters.Append .CreateParameter("@RecordNum",
				         adInteger, adParamInput, , lTotRecCnt)
                                        .Execute lRecs, , adExecuteNoRecords
                                    End With
 
                                    UI.DBClearParameters
                          
                          
                          Case ekTestOptions.EggHeadTest2
                          
                                  
                                     lMassRecCnt = lMassRecCnt + 1
                                     
                                    If lMassRecCnt = 1 Then
                                       UI.StringBuilderInit
                                       lStartRecord = lTotRecCnt
                                    End If
                                    
                                      UI.StringBuilderAppend sRecord
                                      
                                      If lTotRecCnt Mod lRecordCntMax = 0 Then
                                          
                                         sMassRec = UI.StringBuilderConcat
                                         UI.StringBuilderInit
                                         
                                            With UI.ADOCom
                                                  .CommandText = "spEggHeadTest" & ekTestOption
                                                  .CommandType = adCmdStoredProc
                                                  .Parameters.Append
                                             .CreateParameter("@RETURN_VALUE", adInteger,
                                                   adParamReturnValue, 0)
                                                  .Parameters.Append 
                                              .CreateParameter("@RecLength", adInteger,													
                                          adParamInput, , lRecLength)
                                              .Parameters.Append
                                           .CreateParameter("@StartRecord",
                                              adInteger, adParamInput, , lStartRecord)
                                           .Parameters.Append 
                                           .CreateParameter("@CurRecordCnt",
                                             adInteger, adParamInput, , lMassRecCnt)
                                           .Parameters.Append 
                                           .CreateParameter("@RecordData", adLongVarWChar,
                                              adParamInput, 1073741823, sMassRec)
                                                  .Execute lRecs, , adExecuteNoRecords
                                              End With
                                             
                                              UI.DBClearParameters
                                               lMassRecCnt = 0
                                   
                                    End If
                                     
              End Select
              
       Loop
       
         Close #1
       
       If ekTestOption = ekTestOptions.EggHeadTest2 Then
         If ((lMassRecCnt < lRecordCntMax) And (lMassRecCnt > 0)) Then
         
             sMassRec = UI.StringBuilderConcat
              
              With UI.ADOCom
                       .CommandText = "spEggHeadTest" & ekTestOption
                       .CommandType = adCmdStoredProc
                       .Parameters.Append .CreateParameter("@RETURN_VALUE",
			                      adInteger, adParamReturnValue, 0)
                       .Parameters.Append .CreateParameter("@RecLength", 
                       adInteger, adParamInput, , lRecLength)
                       .Parameters.Append .CreateParameter("@StartRecord",
			                  adInteger, adParamInput, , lStartRecord)
                       .Parameters.Append .CreateParameter("@CurRecordCnt",
			                adInteger, adParamInput, , lMassRecCnt)
                       .Parameters.Append .CreateParameter("@RecordData",
			        adLongVarWChar, adParamInput, 1073741823, sMassRec)
                       .Execute lRecs, , adExecuteNoRecords
              End With
                            
             UI.DBClearParameters
            UI.StringBuilderInit
            
        End If
      End If
      
  

ErrHandler:
          UI.HourGlass False
          If Err.Number <> 0 Then MsgBox "Error loading test file. "
		                & vbCrLf & Err.Description
          On Error Resume Next
          Close #1
          UI.DBClose
          Err.Clear
End Sub

Visual Basic cUI.cls (Support Class Module With Common Methods)
   
  Option Explicit

Private msStringBuilderArray() As String
Private mlStringBuilderGrowthRate As Long
Private mlStringBuilderNumItems As Long
Private mlStartTimer As Long
Public AppPath As String
Public ADOCon As ADODB.Connection
Public ADOCom As ADODB.Command
 
Private Sub Class_Initialize()
   Set ADOCon = New ADODB.Connection
   Set ADOCom = New ADODB.Command
End Sub

Private Sub Class_Terminate()
   On Error Resume Next
   ADOCon.Close
   Set ADOCom = Nothing
   Set ADOCon = Nothing
   Err.Clear
End Sub

Public Function DBOpen() As Boolean
   On Error Resume Next
     Me.ADOCon.Close
     Err.Clear
   On Error GoTo ErrHandler
       DBOpen = False
                 
      Me.ADOCon.Open "Provider=SQLOLEDB;Data Source=(local);
	                 User ID=yourusr;Password=yourpwd;Initial Catalog=YOURDB"
      Set ADOCom.ActiveConnection = Me.ADOCon
       DBOpen = True

ErrHandler:
         If Err.Number <> 0 Then MsgBox "Unable to connect to " &
		          vbCrLf & Err.Description
         Err.Clear
End Function

Public Sub DBClose()
     On Error Resume Next
     Me.ADOCon.Close
     Err.Clear
End Sub
 
Public Sub HourGlass(ByVal fOn As Boolean)
     If fOn = True Then
        Screen.MousePointer = vbHourglass
     Else
        Screen.MousePointer = vbDefault
     End If
End Sub

   
Public Function WriteFile(ByVal sFilePathAndName As String,
                          ByVal sFileContents As String) As Boolean

  Dim oFS As Scripting.FileSystemObject
  Dim oFSFile
 
  On Error GoTo ErrHandler
  
  WriteFile = False
  
  Const ForWriting = 2

  Set oFS = New Scripting.FileSystemObject
 
  If oFS.FileExists(sFilePathAndName) = True Then oFS.DeleteFile sFilePathAndName, True
   
  Set oFSFile = oFS.OpenTextFile(sFilePathAndName, ForWriting, True)

  oFSFile.Write (sFileContents)
  
  oFSFile.Close
  
  WriteFile = True
  
  
ErrHandler:
          If Err.Number <> 0 Then 
            MsgBox "Failure to open file for writing. " &
			        vbCrLf & sFilePathAndName & vbCrLf & Err.Description
           end if
          On Error Resume Next
          Set oFSFile = Nothing
          Set oFS = Nothing
          Err.Clear
End Function
 
Public Sub TimerInit()
           mlStartTimer = Timer
End Sub
Public Function TimerGetElapsedTime() As Long
            TimerGetElapsedTime = Timer - mlStartTimer
End Function
       
Public Sub CatStr(ByRef sOrgVal As String, ByRef sVal As String)
      ' Look at the egghead article for using arrays as a string builder.
      ' Since this sub is not part of the article's speed evaluation, I didn't bother.
           sOrgVal = sOrgVal & sVal
End Sub

Public Sub DBClearParameters()
   
   Dim nCnt
   On Error Resume Next
  
   If Me.ADOCom.Parameters.Count < 1 Then Exit Sub
   For nCnt = 0 To Me.ADOCom.Parameters.Count
       Me.ADOCom.Parameters.Delete 0
   Next
  Err.Clear
End Sub

Public Sub StringBuilderInit()
     On Error Resume Next
     Erase msStringBuilderArray
     Me.StringBuilderInit
     Err.Clear
     mlStringBuilderGrowthRate = 50: mlStringBuilderNumItems = 0
     ReDim msStringBuilderArray(mlStringBuilderGrowthRate)
End Sub

Public Sub StringBuilderAppend(ByVal strValue As String)
       If mlStringBuilderNumItems > UBound(msStringBuilderArray) Then 
        ReDim Preserve msStringBuilderArray(UBound(msStringBuilderArray)
		          + mlStringBuilderGrowthRate)
       end if
       msStringBuilderArray(mlStringBuilderNumItems) = 
	            strValue: mlStringBuilderNumItems = mlStringBuilderNumItems + 1
End Sub

 

Public Function StringBuilderConcat()
           ReDim Preserve msStringBuilderArray(mlStringBuilderNumItems)
           StringBuilderConcat = Join(msStringBuilderArray, "")
End Function

SQL Server Table Schemas And Stored Procedures
 
   

CREATE TABLE [dbo].[EggHeadTest1] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[Phone] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RecordData] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastPieceOfRecord] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RecordNum] [int] NOT NULL ,
	[LastUpdateTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EggHeadTest1Trans] (
	[TransID] [int] IDENTITY (1, 1) NOT NULL ,
	[RecordID] [int] NOT NULL ,
	[DispositionID] [int] NOT NULL ,
	[CallDateTime] [datetime] NULL ,
	[LastUpdateTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EggHeadTest2] (
	[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
	[Phone] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RecordData] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastPieceOfRecord] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RecordNum] [int] NOT NULL ,
	[LastUpdateTime] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EggHeadTest2Trans] (
	[TransID] [int] IDENTITY (1, 1) NOT NULL ,
	[RecordID] [int] NOT NULL ,
	[DispositionID] [int] NOT NULL ,
	[CallDateTime] [datetime] NULL ,
	[LastUpdateTime] [datetime] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EggHeadTest1] WITH NOCHECK ADD 
	CONSTRAINT [PK_EggHeadTest1] PRIMARY KEY  CLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[EggHeadTest1Trans] WITH NOCHECK ADD 
	CONSTRAINT [DF_EggHeadTest1Trans_DispositionID] DEFAULT (0) FOR [DispositionID],
	CONSTRAINT [PK_EggHeadTest1Trans] PRIMARY KEY  CLUSTERED 
	(
		[TransID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[EggHeadTest2] WITH NOCHECK ADD 
	CONSTRAINT [PK_EggHeadTest2] PRIMARY KEY  CLUSTERED 
	(
		[RecordID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[EggHeadTest2Trans] WITH NOCHECK ADD 
	CONSTRAINT [DF_EggHeadTest2Trans_DispositionID] DEFAULT (0) FOR [DispositionID],
	CONSTRAINT [PK_EggHeadTest2Trans] PRIMARY KEY  CLUSTERED 
	(
		[TransID]
	)  ON [PRIMARY] 
GO




CREATE PROCEDURE dbo.spEggHeadTest1
 (
  @Phone nvarchar(10), 
  @FName nvarchar(50), 
  @LName nvarchar(50), 
  @RecordData nvarchar(500), 
  @LastPieceOfRecord nvarchar(50),
  @RecordNum int
) AS

DECLARE @rc int
DECLARE @NewID int
 
BEGIN TRANSACTION EggHeadTest

    insert EggHeadTest1 (Phone,FName,LName,RecordData,LastPieceOfRecord,
               RecordNum,LastUpdateTime)
      values  (@Phone,@FName,@LName,@RecordData,@LastPieceOfRecord,
               @RecordNum,GetDate())
  
       if (@@ERROR <> 0)                
           BEGIN
              ROLLBACK TRANSACTION EggHeadTest
                 select @rc = @@Error
                 Goto OnExit   
          END

    SELECT @NewID = @@IDENTITY
  
    insert EggHeadTest1Trans (RecordID,DispositionID,
                             CallDateTime,LastUpdateTime)
        values (@NewID,0,null,GetDate())

   if (@@ERROR <> 0)                
         BEGIN
              ROLLBACK TRANSACTION EggHeadTest
                 select @rc = @@Error
                 Goto OnExit   
          END

   COMMIT TRANSACTION EggHeadTest
          
                         

OnExit:
  RETURN @rc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.spEggHeadTest2
 (
  @RecLength int,
  @StartRecord int,
  @CurRecordCnt int,
  @RecordData ntext
) AS

   
DECLARE @rc int
DECLARE @TotRecCnt int
DECLARE @NewID int
DECLARE @nCnt int
DECLARE @RecStart int
DECLARE @TmpData varchar(8000)
 
Select @TotRecCnt = @StartRecord - 1
Select @RecStart = 1
Select @nCnt = 1

 BEGIN TRANSACTION EggHeadTest 
 
WHILE (@nCnt <= @CurRecordCnt)
        BEGIN  
          
           Select @TotRecCnt = @TotRecCnt + 1      
		      /*  Increment our record count for the whole file */
           Select @TmpData = SUBSTRING(@RecordData,@RecStart,@RecLength)

   insert EggHeadTest2 (Phone,FName,LName,RecordData,
                 LastPieceOfRecord,RecordNum,LastUpdateTime)
   values  (SUBSTRING(@TmpData,1,10),SUBSTRING(@TmpData,11,10),
              SUBSTRING(@TmpData,21,10),
                SUBSTRING(@TmpData,31,64),
            SUBSTRING(@TmpData,95,4),@TotRecCnt, GetDate())
    
       if (@@ERROR <> 0)                
           BEGIN
               ROLLBACK TRANSACTION EggHeadTest 
                select @rc = @@Error 
                 Goto OnExit   
          END

    SELECT @NewID = @@IDENTITY
   
    insert EggHeadTest2Trans (RecordID,DispositionID,
                 CallDateTime,LastUpdateTime)
        values (@NewID,0,null,GetDate())

   if (@@ERROR <> 0)                
         BEGIN
          ROLLBACK TRANSACTION EggHeadTest 
          select @rc = @@Error  
           Goto OnExit   
          END





           Select @nCnt = @nCnt + 1 
           Select @RecStart  = @RecStart + @RecLength

        END   /* End of loop  */
 

     COMMIT TRANSACTION EggHeadTest  
          
                         

OnExit:
  RETURN @rc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



Robbe is a 2004-2008 Microsoft MVP for C# and the .NET Evangelist for Alinean Inc..  He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.