CODEWITHSUNDEEP
c#.netperformancelistado.net
Müsli
Müsli

Reputation: 1774

Huge list performance and memory consumption

I'm starting a new application. I run querys to the databases then I return a list of customs objects. Here is an example:

With this method i get all the records of a table. Example: SELECT ID,NAME, LASTNAME FROM PKR_PLAYER

public List<TEntity> GetAll()
{
    List<TEntity> all = new List<TEntity>();
    String query = String.Format("SELECT {0} FROM {1}",AllFieldsSelection,TableName);
    var data = SqlExecutionData.Create().WithConn(ConnectionString).WithQuery(query);
    foreach (IDataRecord record in SqlServerUtils.GetRecords(data))
    {
        all.Add(CreateOneFromRecord(record));
    }
    return all;
}

With this i create a TEntityObject with the data stored in record

private TEntity CreateOneFromRecord(IDataRecord record)
{
    var result = new TEntity();
    for (int i = 0; i < record.FieldCount; i++)
    {
        ColumnMap colMap = maps.FirstOrDefault(x => x.Column.Equals(record.GetName(i)));
        if (colMap == null || record.IsDBNull(i)) continue;
        object value = record.GetValue(i);
        colMap.PropertyInfo.SetValue(result, value, null);
    }
    return result;
}

How i execute the query

public static IEnumerable<IDataRecord> GetRecords(SqlExecutionData data)
{
    using (SqlConnection sqlConnection = new SqlConnection(data.ConnectionString))
    using (SqlCommand command = new SqlCommand(data.Query, sqlConnection))
    {
        if (data.Parameters!=null && data.Parameters.Count > 0)
        {
            foreach (String key in data.Parameters.Keys)
                command.Parameters.AddWithValue(key, data.Parameters[key]);
        }
        sqlConnection.Open();

        using (IDataReader rdr = command.ExecuteReader())
        {
            while (rdr.Read())
            {
                yield return (IDataRecord)rdr;
            }
        }

        sqlConnection.Close();
    }
}

In Brief, the idea is to iterate over a SqlDataReader (with yield return) and create an entity and finally stored it on a List.

I think this consumes a lot of resources. The query returns aprox 22k records and the method GetAll will be called very often. There will be times where GetAll Method will be called every 1 or 2 minutes.

I just execute a couple of times (10 times aprox) the method GetAll and the information on the Task Manager of windows shows me that the memory grows from 17MB to 45 MB in a few seconds.

I believe this code is not performance.

The question is: How can i make the code consumes less memory? May be i have to change the List, but what is the alternative?

Upvotes: 1

Views: 2297

Answers (2)

mo.
mo.

Reputation: 3534

Another option could be to delegate each item. This is more explicit and you have more control over each item. But you need to be careful, the delegate-call will block the iteration. I prefer to use IEnumerable because it has so many features in .NET (PLinq, foreach, Collection-ctors).

public void IterateItems(Action<int, TEntity> handler)
{
    String query = String.Format("SELECT {0} FROM {1}",AllFieldsSelection,TableName);
    var data = SqlExecutionData.Create().WithConn(ConnectionString).WithQuery(query);

    var index = 0;   
    foreach (IDataRecord record in SqlServerUtils.GetRecords(data))
    {
        handler(CreateOneFromRecord(index, record));
        index++;
    }
}

a memory efficient call:

public void MemoryEfficient()
{
    ProcessItems((index, item) => Console.WriteLine("{0}: {1}", index, item));
}

collect items in a list:

public void FillAList()
{
    var list = new List<TEntity>();
    ProcessItems((index, item) => list.Add(item));
}

Upvotes: 1

mo.
mo.

Reputation: 3534

you could use yield return but then you need to change your method a bit.
this will minimize your memory consumption.

public IEnumerable<TEntity> GetAll()
{
    String query = String.Format("SELECT {0} FROM {1}",AllFieldsSelection,TableName);
    var data = SqlExecutionData.Create().WithConn(ConnectionString).WithQuery(query);
    foreach (IDataRecord record in SqlServerUtils.GetRecords(data))
    {
        yield return CreateOneFromRecord(record);
    }
}

this will consume much less memory, but it will also keep your connection to SQL Server open. please read more on this MSDN article.

Upvotes: 2

Related Questions