Improving DAL performance

Question

The way i currently populate business objects is using something similar to the snippet below.

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.CDRDatabase))
{
    using (SqlCommand comm = new SqlCommand(SELECT, conn))
    {
        conn.Open();

        using (SqlDataReader r = comm.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (r.Read())
            {
                Ailias ailias = PopulateFromReader(r);
                tmpList.Add(ailias);
            }
        }
    }
}

private static Ailias PopulateFromReader(IDataReader reader)
{
    Ailias ailias = new Ailias();

    if (!reader.IsDBNull(reader.GetOrdinal("AiliasId")))
    {
        ailias.AiliasId = reader.GetInt32(reader.GetOrdinal("AiliasId"));
    }

    if (!reader.IsDBNull(reader.GetOrdinal("TenantId")))
    {
        ailias.TenantId = reader.GetInt32(reader.GetOrdinal("TenantId"));
    }

    if (!reader.IsDBNull(reader.GetOrdinal("Name")))
    {
        ailias.Name = reader.GetString(reader.GetOrdinal("Name"));
    }

    if (!reader.IsDBNull(reader.GetOrdinal("Extention")))
    {
        ailias.Extention = reader.GetString(reader.GetOrdinal("Extention"));
    }

    return ailias;
}

Does anyone have any suggestions of how to improve performance on something like this? Bear in mind that PopulateFromReader, for some types, contains more database look-ups in order to populate the object fully.

Answer 1

One obvious change would be to replace this kind of statement: ailias.AiliasId = reader.GetInt32(reader.GetOrdinal("AiliasId"));

with

ailias.AiliasId = reader.GetInt32(constAiliasId);

where constAiliasId is a constant holding the ordinal of the field AiliasId.

This avoids the ordinal lookups in each iteration of the loop.

Answer 2

If the data volume is high, then it can happen that the overhead of building a huge list can be a bottleneck; in which case, it can be more efficient to use a streaming object model; i.e.

public IEnumerable<YourType> SomeMethod(...args...) {
    using(connection+reader) {
        while(reader.Read()) {
            YourType item = BuildObj(reader);
            yield return item;
        }
    }
}

The consuming code (via foreach etc) then only has a single object to deal with (at a time). If they want to get a list, they can (with new List<SomeType>(sequence), or in .NET 3.5: sequence.ToList()).

This involves a few more method calls (an additional MoveNext()/Current per sequence item, hidden behind the foreach), but you will never notice this when you have out-of-process data such as from a database.