DAL Performance Improvement

Question

The way I populate business objects now uses something similar to snipet 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 on how to improve performance on something like this? Keep in mind that PopulateFromReader for some types contains more database queries to fill the object completely.

Answer 1

One obvious change is to replace these kinds of statements: ailias.AiliasId = reader.GetInt32 (reader.GetOrdinal ("AiliasId"));

from

ailias.AiliasId = reader.GetInt32(constAiliasId);

where constAiliasId is a constant containing the sequence number of the AiliasId field.

This avoids ordinal queries at each iteration of the loop.

Answer 2

, , ; ; .

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

( foreach ..), ( ). , ( List<SomeType>(sequence) .NET 3.5: sequence.ToList()).

( MoveNext()/Current , foreach), , , , .

Answer 3

-. DAL, .

• ? , ( .) , , , . SQL ( , ..) , DataReaders, , , DB.

• / , , . , , . , . / .

• , , . , SELECT * FROM ( 30 , ), SELECT Id, FROM ( , ) .

Answer 4

AFAIK, , . , SQL-/. - .

Answer 5

Probably the real problem is multiple requests for the objects you mention. You carefully looked, whether all of them can be placed in one stored procedure?