CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sql
user5032790
user5032790

Reputation:

Can I optimize this query any further?

I have two queries in my code:

var query1 = (
      from c in db.HugeTableData
      where c.price >= 1000
      group c by new { c.year, c.month, c.day } into cgroup
      select new { Count = cgroup.Count(), cgroup.Key })
     .OrderByDescending(x => x.Key.year).ThenByDescending(y => y.Key.month)
     .ThenByDescending(z => z.Key.day).Take(50);
//Some other code
var query2 = (
     from c in db.HugeTableData
     where c.price >= 1000 && c.check == true
     group c by new { c.year, c.month, c.day } into cgroup
     select new { Count = cgroup.Count(), cgroup.Key })
    .OrderByDescending(x => x.Key.year).ThenByDescending(y => y.Key.month)
    .ThenByDescending(z => z.Key.day).Take(50);

This two queries are retrieving the qualified records from a table that has a huge amounts of data and as you see the only difference between them is the && c.check == true part but I'm not looking for how can I refactor it. Unfortunately this code is running very slow, now my question is: is the low performance because two roundtrips to DataBase? And also if I have a huge data, then is Stored Procedure a good choice here?

EDIT: The code that uses these queries is as below:

foreach (var item in query1)
{
    DateTime dt = Convert.ToDateTime(item.Key.year + "-" + item.Key.month + "-" + item.Key.day);
    string temp = MyConvertToStringMethod(dt);
    if (firstDictionary.ContainsKey(temp))
        firstDictionary[temp] = item.Count;
}

And also another foreach for query2 similar to this to fill the secondDictionary.

Upvotes: 0

Views: 97

Answers (2)

sgmoore
sgmoore

Reputation: 16067

You are only retrieving the first 50 records. If the total numbers of records is small and it is possible to hold the complete results in memory, then you could avoid the two round trips to the database by grouping by check, ie

var list = (
  from c in db.HugeTableData
  where c.price >= 1000
  group c by new { c.year, c.month, c.day ,  c.check } into cgroup
  select new { 
    Count = cgroup.Count(), 
    cgroup.Key.year , 
    cgroup.Key.month, 
    cgroup.Key.day 
  }).ToList();

Then you can perform linq-to-objects queries

var query1 = (
  from c in list
  group c by new { c.year, c.month, c.day } into cgroup
  select new { Count = cgroup.Sum(), cgroup.Key })
 .OrderByDescending(x => x.Key.year).ThenByDescending(y => y.Key.month)
 .ThenByDescending(z => z.Key.day).Take(50);

var query2 = (
 from c in list where c.check == true
 group c by new { c.year, c.month, c.day } into cgroup
 select new { Count = cgroup.Sum(), cgroup.Key })
.OrderByDescending(x => x.Key.year).ThenByDescending(y => y.Key.month)
.ThenByDescending(z => z.Key.day).Take(50);

// Note that you need to use Count=cgroup.Sum()

Upvotes: 1

Gavin
Gavin

Reputation: 516

There are several approaches you could take here depending on your scenario. The most widely applicable would be creating a view that implements this logic and then you use it directly. Depending on your Dbms you may be able to speed up this approach further by using indexes. Alternatively if the processing of the results is slow you could look into using the c# yield keyword as you enumerate the results so that the data is processed as it is enumerated.

Upvotes: 0

Related Questions