title: SQL Server BULK INSERT of Over 1 Million rows - Need Performance Improvement
body: <p>I have been tasked with developing a method for importing text files with 1 million or more rows into a table. Each row within the table is 856 Bytes, split into approximately 150 "columns" of data. I am using SQL Server 2012 Standard Edition. The activity related to the INSERTs is the only activity on the database. I have built a stored procedure that does the following:</p>
  
  <pre><code>GET LIST OF UNIMPORTED DATA FILES IN A SPECIFIC DIRECTORY
  LOOP THROUGH LIST OF FILES
      BULK INSERT INDIVIDUAL FILE DATA INTO (DISK-BASED) TEMP TABLE
      LOOP THROUGH RECORD-LEVEL DATA IN TEMP TABLE
      PARSE/WRITE RECORD-LEVEL DATA TO DETAILRECORD TABLE
      IF ALL RECORDS FOR A GIVEN FILE ARE SUCCESSFULLY WRITTEN TO THE DETAIL RECORD TABLE
          THEN 
              MOVE FILE TO IMPORTED_FILES DIRECTORY
              WRITE SUCESS RECORD TO IMPORTLOG TABLE
          ELSE
              WRITE FAIL RECORD TO IMPORTLOG TABLE
              MOVE ON TO NEXT FILE
  </code></pre>
  
  <p>The BULK INSERT of the raw text file into the TEMP TABLE is fast enough, at less than 1 minute. However, I need to parse that raw data and put the data into its destination table. To be clear, that means, for example, that data from row location 1-10 should be written to  in the destination table, 11-13 should be written to  in the destination table, etc. I am doing this by using a WHILE loop instead of a CURSOR and looping through each of the 1 million rows, then passing the raw data as a parameter to another stored procedure that then parses the raw data and inserts into the destination table. I am parsing the raw data simply by using the SUBSTRING function, i.e. SUBSTRING(@rawdata,1,10).
  And as I mentioned, there are approximately 150 fields within each row. Performance of the parsing/writing step is terrible, however. It's has taken 10 hours to import 170K rows. </p>
  
  <p>There are no clustered indexes on the destination table. The Recovery Model is set to SIMPLE. I have "Instant File Initialization" enabled.</p>
  
  <p>I am looking at possibly using SSIS, but 1) I suspect performance might not be much faster, and 2) troubleshooting seems more cumbersome in SSIS.</p>
  
  <p>Looking for thoughts, brilliant ideas, and obvious suggestions I've overlooked.</p>
  
  <p>EDIT (Added code inside WHILE loop):</p>
  
  <pre><code>WHILE @RecordCounter &lt;= @FileRecordCount
  BEGIN
      SELECT @Record = record FROM CDR.tempFile WHERE id = @RecordCounter                                 
  
      EXEC [CDR].[usp_ImportCDRData_Record] @pRecord = @Record, @pCarrier = @carrier, @pLogid = @ImportLogID
  
      SET @RecordCounter = @RecordCounter + 1
  END
  
  --Inside [CDR].[usp_ImportCDRData_Record] 
  
  INSERT INTO dbo.DetailRecord
      ( 
      LOGID ,
      ACCOUNTNUMBER ,
      CORPID ,
      SERVICELOCATION ,
      NETWORKINDICATOR ,
      ...
      )
  VALUES
      ( 
      @pLogID,
      SUBSTRING(@pRecord, 1,10), -- ACCOUNTNUMBER
      SUBSTRING(@pRecord, 11,8), -- CORPID
      SUBSTRING(@pRecord, 19,8), -- SERVICELOCATION
      SUBSTRING(@pRecord, 27,1), -- NETWORKINDICATOR
      ...
      )
  </code></pre>