Alternative to Recordset Looping

Question

Back in the day using ADO, we used GetRows() to pull back an array and loop through it, because it was faster than using rs.MoveNext to walk through records. I'm writing an application that pulls back half a million rows and writes them out into a file. Pulling the data from SQL takes about 3 minutes, but writing it to a CSV is taking another 12 minutes. From the looks of it, it's because I'm looping through a SqlDataReader. What is a faster alternative?

Keep in mind, I do not know what the SQL Structure will look like as this is calling a reporting table that tells my application what query should be called. I looked at using linq and return an array, but that will require knowing the structure, so that will not work.

Note the below code, case statement has many cases, but to cut down on space, I removed them all, except one.

StringBuilder rowValue = new StringBuilder();
SqlDataReader reader = queryData.Execute(System.Data.CommandType.Text, sql, null);

//this is to handle multiple record sets
while (reader.HasRows)
{
  for (int i = 0; i < reader.FieldCount; i++)
  {
    if (rowValue.Length > 0)
        rowValue.Append("\",\"");
    else
        rowValue.Append("\"");
    rowValue.Append(reader.GetName(i).Replace("\"", "'").Trim());
  }
  rowValue.Append("\"" + Environment.NewLine);

  File.AppendAllText(soureFile, rowValue.ToString());

  while (reader.Read())
  {
    rowValue = new StringBuilder();

    for (int i = 0; i < reader.FieldCount; i++)
    {
      String value = "";
      switch (reader.GetFieldType(i).Name.ToLower())
      {
          case "int16":
              value = reader.IsDBNull(i) ? "NULL" : reader.GetInt16(i).ToString();
              break;
      }

      if (rowValue.Length > 0)
          rowValue.Append("\",=\"");               //seperate items
      else
          rowValue.Append("\"");                  //first item of the row.

      rowValue.Append(value.Replace("\"", "'").Trim());
    }
    rowValue.Append("\"" + Environment.NewLine);    //last item of the row.

    File.AppendAllText(soureFile, rowValue.ToString());
    }  

  //next record set
  reader.NextResult();

  if (reader.HasRows)
    File.AppendAllText(soureFile, Environment.NewLine);
}

reader.Close();

How do you know it takes 3 minutes to pull the data back if you are assuming that the other 12 minutes are from SqlDataReader? How are you measuring those two things separately? — RBarryYoung, Mar 04 '13 at 21:59
I cannot tell if it is too slow or as fast as can be. It depends on the amount of data that is written to the file and what kind of harddrive (ordinary or SSD) that you are using and whether the database is local or on a remote server etc. But anyway a code sample might spread some light and maybe some improvements will be shown. — Casperah, Mar 04 '13 at 21:59
You can also export it to csv directly with [bcp utility](http://msdn.microsoft.com/en-us/library/ms162802.aspx): http://dba.stackexchange.com/questions/23566/writing-select-result-to-a-csv-file — Tim Schmelter, Mar 04 '13 at 22:03
@RBarryYoung this information is all logged in a local text file. — Chizl, Mar 04 '13 at 22:08
@TimSchmelter Problem with that is, it converts leading zeros off columns with all zeros, which is required to be in the file. My application actually writes it out as ="0006516565",="00519565" to help prevent that. — Chizl, Mar 04 '13 at 22:09
@Chizl That's not really an answer to my question. You cannot readily separate "pulling the data back" from "looping through SqlDataReader" because they are the same thing. At this point, I think that we need to see some code. — RBarryYoung, Mar 04 '13 at 22:16
@RBarryYoung At a high level, I tried coping what I'm doing. Keep in mind my Case statement has many cases there based on different types. — Chizl, Mar 04 '13 at 22:19

score 2 · Accepted Answer · answered Mar 04 '13 at 22:46

2

The problem here is almost certainly that you are calling File.AppendAllText() for every row. Since AppendAllText opens, writes, then closes the file every time it is called, it can get quite slow.

A better way would be either to use the AppendText() method or else an explicit StreamWriter.

answered Mar 04 '13 at 22:46

RBarryYoung

55,398
14
96
137

I'll change it to see if that helps. – Chizl Mar 05 '13 at 12:56
wow, newbie mistake. Never thought about that for some odd reason. I use FileStream/StreamWriter all the time, but never got around to changing this as this tool is still being built. Quick and dirty, slapped me again. Thanks. – Chizl Mar 05 '13 at 13:15

Alternative to Recordset Looping

1 Answers1