0

I'm having a problem with one of my methods taking 4-5 minutes to initialize ~17,000 poco objects and filling a list with them. I'm trying to get away from paging the data in the query, so initializing the ~17,000 is needed. We are using an Oracle DB and the Microsoft.Practices.EnterpriseLibrary to call the DB. Not sure if that info is relevant but just in case. I'm using ExecuteReader method's reader object to set the Poco class's 60 properties. I'm not really sure why it is taking so long. The query take about 3-5 seconds to execute. So any advice on how I can change my code to speed things up would be greatly appreciated. Also, someone told me that the query is executed on the reader.Read() call and not the ExecuteReader() call. I don't think that's correct, but if someone know's if it is could you clue me in? Thanks again.

POCO Class

public class BaseWorkItem : ITransactionWorkItem  
{  
    public int Prop1 { get; set; }  
    public string Prop2 { get; set; }
    ....  

    public ITransactionWorkItem SetValues(IDataReader reader)
    {
        Prop1 = reader["Prop1"] != DBNull.Value ?
            Convert.ToInt32(reader["Prop1"]) : -1;
        Prop2 = reader["Prop2"] != DBNull.Value ?
            reader["Prop2"].ToString() : "";  
        ....  
        return this;
    }
}

DataAccess & Initialization Methods

static IEnumerable<ITransactionWorkItem> GetQueue_WorkItems<T>(IDataReader reader)
    where T : ITransactionWorkItem, new()
{
    if (reader.IsNotNull())
    {
        while (reader.Read())
        {
            yield return (T)(new T().SetValues(reader));
        }
    }
}

public static List<ITransactionWorkItem> GetItems(int param1, int param2,
    int param3, int param4)
{
    Database oracle = null;
    IDataReader reader = null;
    var items = new List<ITransactionWorkItem>();

    oracle = Oracle.GetDatabase();

    using (reader = oracle.ExecuteReader("SomeProcedureName", param1, param2,
        param3, param4, null))
    {
        items.AddRange(GetQueue_WorkItems<BaseWorkItem>(reader));
    }

    return items;
}
JG in SD
  • 5,427
  • 3
  • 34
  • 46
jhorton
  • 1,019
  • 3
  • 17
  • 36
  • You could try simply 1) reading through the reader without setting values, and 2) creating objects without adding them to the list, to see if you can determine more precisely what the bottleneck is. – C.Evenhuis Sep 20 '13 at 19:07
  • I'm not really sure what you mean by reading through the reader without setting values. But I'm needing to create objects to be serialized and returned as JSON. But I'm setting a breakpoint in the while reader.read loop and checking a counter variable. So I'm thinking the bottle neck is in the SetValues method. – jhorton Sep 20 '13 at 19:16
  • Ok, I think I got what you were saying there. I commented out the line in the read loop that initialized the object and set the values. And it appears the bottleneck is in the Read() because it's still taking 5 minutes to finish the loop. Any ideas on what could be the deal with the IDataReader Read method? Also, when I only returned like 5 fields in initialized 17 K objects in less than a second. I wouldn't have thought returning 59 fields would be that costly. – jhorton Sep 20 '13 at 21:04
  • I don't know the details, just thought splitting up the problem might help. I do know when you call `Read()` for the first time you may not have received all data from the database yet. – C.Evenhuis Sep 20 '13 at 21:18
  • Ok, thanks for the input C. I think I'll try to optimize the query some more and see if that helps. – jhorton Sep 21 '13 at 01:07

0 Answers0