CODEWITHSUNDEEP
c#performancelinq
ManInMoon
ManInMoon

Reputation: 7005

how do I make this LINQ query faster?

modelData has 100,000 items in the list.

I am doing 2 "Selects" within 2 loops.

Could it be structured differently - as it take a long time - 10 mins

public class ModelData
{
    public string name;
    public DateTime DT;
    public int real;
    public int trade;
    public int position;
    public int dayPnl;
}

List<ModelData> modelData;

var dates = modelData.Select(x => x.DT.Date).Distinct();
var names = modelData.Select(x => x.name).Distinct();

foreach (var aDate in dates)
{
    var dateRealTrades = modelData.Select(x => x)
                                  .Where(x => x.DT.Date.Equals(aDate) && x.real.Equals(1));

    foreach (var aName in names)
    {
        var namesRealTrades = dateRealTrades.Select(x => x)
                                            .Where(x => x.name.Equals(aName));

        // DO MY PROCESSING
    }
}

Upvotes: 3

Views: 1886

Answers (5)

Anton&#237;n Lejsek
Anton&#237;n Lejsek

Reputation: 6103

There are two ways the code is ineffective.

  • names has deffered evaluation. Every time You iterate over it, it has to go though the whole data to find all the distinct names again. You should save the result.
  • You find distinct values from collection and then You go through collection again for every distinct value and look fot its occurences. You should use grouping.

the rewritten code can look like this

    var dates = modelData.GroupBy(x => x.DT.Date);
    var names = modelData.Select(x => x.name).Distinct().ToArray();

    foreach (var date in dates)
    {
        var dateRealTrades = date.Where(x => x.real.Equals(1)).ToArray();
        var namesRealTradesLookup = dateRealTrades.ToLookup(x => x.name);

        foreach (var aName in names)
        {
            var namesRealTrades = namesRealTradesLookup[aName];

            // DO MY PROCESSING
            // var aDate = date.Key;
        }
    }

In case You are not interestested in date/name combination with no real trade, it can be done in much more straightforward way

    var realModelData = modelData.Where(x => x.real.Equals(1));

    foreach (var dateRealTrades in realModelData.ToLookup(x => x.DT.Date))
    {
        foreach (var namesRealTrades in dateRealTrades.ToLookup(x => x.name))
        {

            // DO MY PROCESSING
            //var aDate = dateRealTrades.Key;
            //var aName = namesRealTrades.Key;
            //foreach(var trade in namesRealTrades) { ...
            //foreach(var trade in dateRealTrades) { ...
        }
    }

Upvotes: 0

juharr
juharr

Reputation: 32266

I believe what you want can be achieved with two queries using group by. One to create a lookup by the date and the other to give you the name-date grouped items.

var data = modelData.Where(x => x.real.Equals(1))
                    .GroupBy(x => new { x.DT.Date, x.name });
var byDate = modelData.Where(x => x.real.Equals(1))
                      .ToLookup(x => x.DT.Date);

foreach(var item in data)
{
    var aDate = item.Key.Date; 
    var aName = item.Key.name;
    var namesRealTrades = item.ToList();
    var dateRealTrades = byDate[aDate].ToList();

    // DO MY PROCESSING
}

The first query will give you items grouped by the name and date to iterate over and the second will give you a lookup to get all the items associated with a given date. The second uses a lookup so that the list is iterated once and gives you fast access to the resulting list of items.

This should greatly reduce the number of times you iterate over modelData from what you currently have.

Upvotes: 4

user1023602
user1023602

Reputation:

A couple of things:

  • use .ToList() to calculate a sequence once, so you can keep it for later.

  • use .GroupBy() to avoid re-searching modelData for things you have already found.

    // Collections of models having the same Date or Name.
var dates = modelData.GroupBy(x => x.DT.Date);
var names = modelData.GroupBy(x => x.Name);

foreach (var modelsWithDate in dates)
{
   var aDate = modelsWithDate.Key;
   var dateRealTrades = modelsWithDate.Where(x => x.real == 1).ToList();

   foreach (var modelsWithName in names)
   {
       var aName = modelsWithName.Key;
       var namesRealTrades = modelsWithName.ToList();

       // DO MY PROCESSING
   }
}

Upvotes: 0

pix
pix

Reputation: 1290

Did you try to compile your query as suggested on MSDN WebSite?

When you have an application that executes structurally similar queries many times, you can often increase performance by compiling the query one time and executing it several times with different parameters. For example, an application might have to retrieve all the customers who are in a particular city, where the city is specified at runtime by the user in a form. LINQ to SQL supports the use of compiled queries for this purpose.

https://msdn.microsoft.com/en-us/library/bb399335(v=vs.110).aspx

Upvotes: 0

Alex
Alex

Reputation: 21766

You could rewrite your for loop like this:

foreach (var namesRealTrades in names.Select(aName => dateRealTrades.Where(x => x.name.Equals(aName))))
{
   //DO STUFF
}

Depending on your data this could reduce the number of queries you have to make

Upvotes: 0

Related Questions