CODEWITHSUNDEEP
c#.netsql-serverlinq
WPalombini
WPalombini

Reputation: 981

Slow performance in LINQ query

I have this t-sql query that is from an old system that run on Cold Fusion. This query takes less than one second to return the records.

select  dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'}) p, count(*) c 
from    account 
where   createdAt <= {ts '2015-02-28 23:59:59'} 
and accountType = 'business' 
and dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'}) <12 
group by    dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'}) 
order by    dateDiff(month, dateAdd(hour, 11, createdAt), {ts '2015-02-28 23:59:59'})

I am now converting this to the new system using .NET and LINQ. I managed to write this LINQ query which gives me the same results.

from a in db.Accounts
where SqlFunctions.DateDiff("Month", SqlFunctions.DateAdd("Hour", 11, a.createdAt), "2015-02-28 23:59:59") < 12
&& a.accountType == "business"
group a by SqlFunctions.DateDiff("Month", a.createdAt, "2015-02-28 23:59:59") into grp
orderby SqlFunctions.DateDiff("Month", grp.FirstOrDefault().createdAt, "2015-02-28 23:59:59")
select new ProgressViewModel.Data
{
     date = SqlFunctions.DateDiff("Month", grp.FirstOrDefault().createdAt, "2015-02-28 23:59:59"),
     amount = grp.Count()
});

However, this query takes no less than 5 seconds to run, while with the first one (t-sql) it takes less than 1 second.

By using Glimpse, we could see the t-sql that that LINQ query generates. It has multiple sub selects and it is 5 times longer than the fast query.

How could I improve the LINQ query?

Upvotes: 4

Views: 2031

Answers (4)

Aron
Aron

Reputation: 15772

I really doubt you actually want to use FirstOrDefault() at any point in your code.

BTW It looks like you are using LinqToSQL as your Linq provider. That thing is nasty, inefficient and downright buggy. You should switch to EntityFramework if its at all possible

Given that...perhaps you should try this...

var date = new Date(2015, 2, 28).AddDays(1);
var query = from account in context.Accounts
            where account.CreatedAt < date
            where account.accountType == "business"
            group account by 
                   SqlFunctions.DateDiff(
                            "Month", 
                             SqlFunctions.DateAdd(
                                   "Hour", 11, a.createdAt), 
                             date)
            into g
            where g.Key < 12
            order by g.Key ascending
            select new 
            {
                MonthsAgo = g.Key,
                Count = g.Count(),
            };

Upvotes: 1

Rob
Rob

Reputation: 11798

I would definitely go for a parametrized Stored Procedure in this case. You should also consider creating a covering index on the tables you need. These steps usually boost performance by a very noticable amount.

Upvotes: -1

Russ
Russ

Reputation: 549

Taking a quick look I would investigate your section grp.FirstOrDefault - is this really what you want to do?

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117174

Try something like this to bring it in to memory before the grouping:

from ca in (
    from a in db.Accounts
    where SqlFunctions.DateDiff("Month", SqlFunctions.DateAdd("Hour", 11, a.createdAt), "2015-02-28 23:59:59") < 12 && a.accountType == "business"
    select a.createdAt).ToArray()
group a by new /* month diff */ into grp
orderby grp.Key
select new ProgressViewModel.Data
{
    date = grp.key,
    amount = grp.Count()
});

Upvotes: 2

Related Questions