title: Reading a CSV file with 50M lines, how to improve performance
body: <p>I have a data file in CSV (Comma-Separated-Value) format that has about 50 million lines in it.</p>
  <p>Each line is read into a string, parsed, and then used to fill in the fields of an object of type FOO.  The object then gets added to a List(of FOO) that ultimately has 50 million items.</p>
  <p>That all works, and fits in memory (at least on an x64 machine), but its SLOW.  It takes like 5 minutes every time load and parse the file into the list.  I would like to make it faster.  <strong>How can I make it faster?</strong></p>
  <p>The important parts of the code are shown below.</p>
  <pre><code>Public Sub LoadCsvFile(ByVal FilePath As String)
      Dim s As IO.StreamReader = My.Computer.FileSystem.OpenTextFileReader(FilePath)
  
      'Find header line
      Dim L As String
      While Not s.EndOfStream
          L = s.ReadLine()
          If L = &quot;&quot; Then Continue While 'discard blank line
          Exit While
      End While
      'Parse data lines
      While Not s.EndOfStream
          L = s.ReadLine()
          If L = &quot;&quot; Then Continue While 'discard blank line          
          Dim T As FOO = FOO.FromCSV(L)
         Add(T)
      End While
      s.Close()
  End Sub
  
  
  Public Class FOO
      Public time As Date
      Public ID As UInt64
      Public A As Double
      Public B As Double
      Public C As Double
  
      Public Shared Function FromCSV(ByVal X As String) As FOO
          Dim T As New FOO
          Dim tokens As String() = X.Split(&quot;,&quot;)
          If Not DateTime.TryParse(tokens(0), T.time) Then
              Throw New Exception(&quot;Could not convert CSV to FOO:  Invalid ISO 8601 timestamp&quot;)
          End If
          If Not UInt64.TryParse(tokens(1), T.ID) Then
              Throw New Exception(&quot;Could not convert CSV to FOO:  Invalid ID&quot;)
          End If
          If Not Double.TryParse(tokens(2), T.A) Then
              Throw New Exception(&quot;Could not convert CSV to FOO:  Invalid Format for A&quot;)
          End If
          If Not Double.TryParse(tokens(3), T.B) Then
              Throw New Exception(&quot;Could not convert CSV to FOO:  Invalid Format for B&quot;)
          End If
          If Not Double.TryParse(tokens(4), T.C) Then
              Throw New Exception(&quot;Could not convert CSV to FOO:  Invalid Format for C&quot;)
          End If
          Return T
      End Function
  End Class
  </code></pre>
  <p>I did some benchmarking and here are the results.</p>
  <ul>
  <li>The complete algorithm above took 314 seconds to load the whole file and put the objects into the list.</li>
  <li>With the body of FromCSV() reduced to just returning a new object of type FOO with default field values, the whole process took 84 seconds.  Therefore it appears that processing the line of text into the object fields is taking 230 seconds (73% of the total time).</li>
  <li>Doing everything but parsing the ISO 8601 date string takes 175 seconds.  Therefore it appears that processing the date string takes 139 seconds, which is 60% of the text processing time, just for that one field.</li>
  <li>Just reading the lines in the file without any processing or object creating takes 41 seconds.</li>
  <li>Using StreamReader.ReadBlock to read the whole file in chunks of about 1KB takes 24s, but its a minor improvement in the grand scheme of things and probably not worth the added complexity.  In order to use TryParse I would now need to manually create the temporary strings rather than using String.Split().</li>
  </ul>
  <p>At this point the only path I see is to just display status to the user every few seconds so they don't wonder if the program is frozen or something.</p>
  <p><strong>UPDATE</strong><BR>
  I created two new functions.  One can save the dataset from memory into a binary file using System.IO.BinaryWriter.  The other function can load that binary file back into memory using System.IO.BinaryReader.  The binary versions were considerably faster than CSV versions, and the binary files take up much less space.</p>
  <p>Here are the benchmark results (same dataset for all tests):</p>
  <ul>
  <li>LOAD CSV:  340s</li>
  <li>SAVE CSV:  312s</li>
  <li>SAVE BIN:  29s</li>
  <li>LOAD BIN:  41s</li>
  <li>CSV FILE SIZE:  3.86GB</li>
  <li>BIN FILE SIZE:  1.63GB</li>
  </ul>