DataReader hangs while mapping a record to an object

Question

Hi all,

I'm in the middle of a project where we are querying a database with more than 20 million records, applying several set of filters our query returns about 200 records (after waiting for about 1.30 minutes). After querying the database I try to create objects from this particular datareader but once every 15 or 16 records the asp.net process hangs (it hangs in the debugger) so I think it is a DataReader issue.

This is driving me crazy.

Here's the code I'm using

using (IDataReader reader = cmd.ExecuteReader())
       {
List<Project> projects = new List<Project>();
        while(reader.Read()) projects.Add(GetMappedRecord(reader)); 
       }

private Project GetMappedRecord(DataRow reader)
     {
      Project project = new Project();

      project.PropertyA = reader["FieldA"] as string;

// and so on and so forth...
      return project;
     }

Answer 1

"A DataReader issue"?

If you wanted to write a piece of code that behaved the way you believe that DataReader is behaving, could you do that? Sometimes it's good to think, "If I were a bug, where would I be hiding", or "where could I not be hiding?"

Chances are there's more to it.

Experiment 1:

using (IDataReader reader = cmd.ExecuteReader())
{
    while(reader.Read())
        ; 
}

See if that hangs. See when it hangs (what set of filters).

When you get a hang (and a set of filters), go try out the same SQL (with the same parameters) in Management Studio. See if that combination is taking a long time (if you're using SQL Server 2008, look in Activity Monitor to see if your query is in the "slow" list).

---

Since experiment 1 succeeds, but it's an issue with the indexer, let's try the following:

try {
    using (IDataReader reader = cmd.ExecuteReader())
    {
        while(reader.Read()) {
            for (int i=0; i<reader.FieldCount; i++) {
                object v = reader.GetValue(i);
            }
        } 
    }
} catch (Exception ex) {
    Console.WriteLine(ex.ToString()); // Or MessageBox.Show or whatever
}

Let's see if that hangs (and for how long!)

Answer 2

Maybe it has something to do with the casting? The way I'm used to using DataReaders is more like this:

while(reader.Read())
{
    var Project = new Project();
    Project.StringProperty = reader.IsDBNull(reader.GetOrdinal("FieldA"))
                           ? ""
                           : reader.GetString(reader.GetOrdinal("FieldA"));
    //etc....
}

It's possible the implicit cast to the DataRow and then again the cast from reader["FieldA"] to string with the "as" could slow it down.