title: SqlDataReader weird performance comparison results
body: <p>I'm currently trying to figure out the most efficient way to read data from SQL Server from C#.</p>
  
  <p>I'm quite surprised at one of the result I get.</p>
  
  <p>1)</p>
  
  <pre><code>watch.Restart();
  while (reader.Read())
  {
     lst.Add(new { n = reader.GetInt64(0), a = reader.GetString(1), b = reader.GetString(2) });
  }
  watch.Stop();
  </code></pre>
  
  <p>2)</p>
  
  <pre><code>watch.Restart();
  while (reader.Read())
  {
      lst.Add(new { n = reader[0], a = reader[1], b = reader[2] });
  }
  watch.Stop();
  </code></pre>
  
  <p>For some reason the first loop gets excecuted faster than the second one.</p>
  
  <p>I had read that casting was a time consuming operation so I wanted to see how much it costed but here, I don't get it.</p>
  
  <p>Do you know why that is?</p>
  
  <p>EDIT: Full code</p>
  
  <pre><code>class Program
  {
      private class Measure
      {
          public TimeSpan TimeCommand;
          public TimeSpan TimeExecute;
  
          public Measure()
          {
              this.TimeCommand = new TimeSpan(0);
              this.TimeExecute = new TimeSpan(0);
          }
      }
  
      private static void Write(string str)
      {
          Console.Write(".");
  
          // Console.Write(str);
      }
  
      private static void WriteLine(string str)
      {
          Console.Write(".");
  
          // Console.WriteLine(str);
      }
  
      static void Main(string[] args)
      {
          var measures = new Dictionary&lt;string, Measure&gt;();
          List&lt;Action&gt; actions = new List&lt;Action&gt;();
          Stopwatch watch;
          SqlCommand com;
          List&lt;object&gt; lst;
          DataTable table;
          SqlDataReader reader;
  
  
  
          actions.Add(() =&gt;
          {
              lst = new List&lt;object&gt;();
  
              #region DataTable cast
              var line = "DataTable cast";
              Measure measure;
  
              if (!measures.TryGetValue(line, out measure))
              {
                  measures.Add(line, (measure = new Measure()));
              }
  
              com = GetCommand();
              watch = Stopwatch.StartNew();
              Write(line + "... ");
  
              table = new DataTable();
              new SqlDataAdapter(com).Fill(table);
              watch.Stop();
  
              measure.TimeCommand += watch.Elapsed;
              Write(watch.Elapsed.ToString(@"mm\:ss\.ffff") + "...");
  
              lst = new List&lt;object&gt;();
  
              watch.Restart();
              foreach (DataRow dr in table.Rows)
              {
                  lst.Add(new { n = (long)dr["n"], a = (string)dr["a"], b = (string)dr["b"] });
              }
              watch.Stop();
  
              measure.TimeExecute += watch.Elapsed;
              WriteLine(watch.Elapsed.ToString(@"mm\:ss\.ffff") + " " + lst.Count.ToString());
              #endregion
          });
  
          actions.Add(() =&gt;
          {
              lst = new List&lt;object&gt;();
  
              #region DataTable cast
              var line = "DataTable field";
              Measure measure;
  
              if (!measures.TryGetValue(line, out measure))
              {
                  measures.Add(line, (measure = new Measure()));
              }
  
              com = GetCommand();
              watch = Stopwatch.StartNew();
              Write(line + "... ");
  
              table = new DataTable();
              new SqlDataAdapter(com).Fill(table);
              watch.Stop();
  
              measure.TimeCommand += watch.Elapsed;
              Write(watch.Elapsed.ToString(@"mm\:ss\.ffff") + "...");
  
              lst = new List&lt;object&gt;();
  
              watch.Restart();
              foreach (DataRow dr in table.Rows)
              {
                  lst.Add(new { n = dr.Field&lt;long&gt;("n"), a = dr.Field&lt;string&gt;("a"), b = dr.Field&lt;string&gt;("b") });
              }
              watch.Stop();
  
              measure.TimeExecute += watch.Elapsed;
              WriteLine(watch.Elapsed.ToString(@"mm\:ss\.ffff") + " " + lst.Count.ToString());
              #endregion
          });
  
          actions.Add(() =&gt;
          {
              lst = new List&lt;object&gt;();
  
              #region Reader ordinal
              var line = "Reader ordinal no cast";
              Measure measure;
  
              if (!measures.TryGetValue(line, out measure))
              {
                  measures.Add(line, (measure = new Measure()));
              }
  
              com = GetCommand();
              watch = Stopwatch.StartNew();
              Write(line + "... ");
  
              com.Connection.Open();
  
              using (reader = com.ExecuteReader())
              {
                  watch.Stop();
  
                  measure.TimeCommand += watch.Elapsed;
                  Write(watch.Elapsed.ToString(@"mm\:ss\.ffff") + "...");
  
                  lst = new List&lt;object&gt;();
  
                  watch.Restart();
                  while(reader.Read())
                  {
                      lst.Add(new { n = reader.GetInt64(0), a = reader.GetString(1), b = reader.GetString(2) });
                  }
                  watch.Stop();
  
                  measure.TimeExecute += watch.Elapsed;
                  WriteLine(watch.Elapsed.ToString(@"mm\:ss\.ffff") + " " + lst.Count.ToString());
              }
  
              com.Connection.Close();
              #endregion
          });
  
          actions.Add(() =&gt;
          {
              lst = new List&lt;object&gt;();
  
              #region Reader ordinal
              var line = "Reader ordinal no cast try";
              Measure measure;
  
              if (!measures.TryGetValue(line, out measure))
              {
                  measures.Add(line, (measure = new Measure()));
              }
  
              com = GetCommand();
              watch = Stopwatch.StartNew();
              Write(line + "... ");
  
              com.Connection.Open();
  
              try
              {
                  using (reader = com.ExecuteReader())
                  {
                      watch.Stop();
  
                      measure.TimeCommand += watch.Elapsed;
                      Write(watch.Elapsed.ToString(@"mm\:ss\.ffff") + "...");
  
                      lst = new List&lt;object&gt;();
  
                      watch.Restart();
                      while (reader.Read())
                      {
                          lst.Add(new { n = reader.GetInt64(0), a = reader.GetString(1), b = reader.GetString(2) });
                      }
                      watch.Stop();
  
                      measure.TimeExecute += watch.Elapsed;
                      WriteLine(watch.Elapsed.ToString(@"mm\:ss\.ffff") + " " + lst.Count.ToString());
                  }
              }
              finally
              {
                  com.Connection.Close();
              }
              #endregion
          });
  
          actions.Add(() =&gt;
          {
              lst = new List&lt;object&gt;();
  
              #region Reader ordinal
              var line = "Reader ordinal cast";
              Measure measure;
  
              if (!measures.TryGetValue(line, out measure))
              {
                  measures.Add(line, (measure = new Measure()));
              }
  
              com = GetCommand();
              watch = Stopwatch.StartNew();
              Write(line + "... ");
  
              com.Connection.Open();
  
              using (reader = com.ExecuteReader())
              {
                  watch.Stop();
  
                  measure.TimeCommand += watch.Elapsed;
                  Write(watch.Elapsed.ToString(@"mm\:ss\.ffff") + "...");
  
                  lst = new List&lt;object&gt;();
  
                  watch.Restart();
                  while (reader.Read())
                  {
                      lst.Add(new { n = (long)reader[0], a = (string)reader[1], b = (string)reader[2] });
                  }
                  watch.Stop();
  
                  measure.TimeExecute += watch.Elapsed;
                  WriteLine(watch.Elapsed.ToString(@"mm\:ss\.ffff") + " " + lst.Count.ToString());
              }
  
              com.Connection.Close();
              #endregion
          });
  
          actions.Add(() =&gt;
          {
              lst = new List&lt;object&gt;();
  
              #region Reader ordinal
              var line = "Reader ordinal no type";
              Measure measure;
  
              if (!measures.TryGetValue(line, out measure))
              {
                  measures.Add(line, (measure = new Measure()));
              }
  
              com = GetCommand();
              watch = Stopwatch.StartNew();
              Write(line + "... ");
  
              com.Connection.Open();
  
              using (reader = com.ExecuteReader())
              {
                  watch.Stop();
  
                  measure.TimeCommand += watch.Elapsed;
                  Write(watch.Elapsed.ToString(@"mm\:ss\.ffff") + "...");
  
                  lst = new List&lt;object&gt;();
  
                  watch.Restart();
                  while (reader.Read())
                  {
                      lst.Add(new { n = reader[0], a = reader[1], b = reader[2] });
                  }
                  watch.Stop();
  
                  measure.TimeExecute += watch.Elapsed;
                  WriteLine(watch.Elapsed.ToString(@"mm\:ss\.ffff") + " " + lst.Count.ToString());
              }
  
              com.Connection.Close();
              #endregion
          });
  
          for (int x = 0; x &lt; 10; x++)
          {
              var range = Enumerable.Range(0, actions.Count).OrderBy(elt =&gt; Guid.NewGuid());
  
              foreach(var i in range)
              {
                  var action = actions[i];
  
                  action();
              }
          }
  
          Console.WriteLine();
  
          var keys = measures.Keys.OrderBy(s =&gt; s);
          foreach (var k in keys)
          {
              Console.WriteLine(String.Format(
                  @"{0} - Command: {1:mm\:ss\.ffff} Execute: {2:mm\:ss\.ffff}"
                  , k
                  , measures[k].TimeCommand
                  , measures[k].TimeExecute
              ));
          }
  
          Console.ReadKey();
  
      }
  
      static SqlCommand GetCommand()
      {
          return new SqlCommand()
          {
              Connection = new SqlConnection("server=localhost;uid=xxx;pwd=xxx;database=xxx")
              , CommandType = System.Data.CommandType.Text
              , CommandText = @"
  SELECT
      n
      , 'a' AS a
      , 'b' AS b
  FROM dbo.v_VIRTUAL_NUMBERS AS N
  WHERE N &lt; 100000
  "
          };
      }
  }
  </code></pre>
  
  <p>If you want to run the code, dbo.v_VIRTUAL_NUMBERS can be any tally table.</p>