Linq SQL Count Method performance problem

Question

I have a code first database with two tables: campaign and campaignItem.

Campaign can has many campaign items.

In one method I want to get number of all of failed campaign items from certain campaign. now my problem starts:

at first is use this code :

long id = 593;
var failedCount = db.CampaignItems.Count(x => x.CampaignId == id && x.Status == CampaignItemStatus.Failed);

generated SQL Command was :

exec sp_executesql N'SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[CampaignItems] AS [Extent1]
        WHERE ([Extent1].[CampaignId] = @p__linq__0) AND (0 = [Extent1].[Status])
    )  AS [GroupBy1]',N'@p__linq__0 bigint',@p__linq__0=593

this was too slow. almost 2 seconds. but when I execute it directly that was very fast. After that I try do something else to check why this query is so slow.

in my first try i use direct query

db.Database.SqlQuery("SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT  COUNT(1) AS [A1]         FROM [dbo].[CampaignItems] AS [Extent1]         WHERE (593 = [Extent1].[CampaignId]) AND (0 = [Extent1].[Status])     )  AS [GroupBy1] ").First();

and

db.Database.SqlQuery("exec sp_executesql N'SELECT      [GroupBy1].[A1] AS [C1]    FROM ( SELECT         COUNT(1) AS [A1]        FROM [dbo].[CampaignItems] AS [Extent1]        WHERE ([Extent1].[CampaignId] = @p__linq__0) AND (0 = [Extent1].[Status])    )  AS [GroupBy1]',N'@p__linq__0 bigint',@p__linq__0=593").First();

gets fast result (less than 150 ms)

after that i try some other LINQ samples.

I find out if I use constant values instead of variables the generated query is different and very very faster. so the following code make very fast result:

var failed = db.CampaignItems.Count(x => x.CampaignId == 593 && x.Status == CampaignItemStatus.Failed);

after that i try add another variable. I guess it should be very slower because now there are two variables. I use thsis code:

long id = 593;
var validState = CampaignItemStatus.Failed;
var failedCount = db.CampaignItems.Count(x => x.CampaignId == id && x.Status == validState);

SQL generated query was:

exec sp_executesql N'SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[CampaignItems] AS [Extent1]
        WHERE ([Extent1].[CampaignId] = @p__linq__0) AND ([Extent1].[Status] = @p__linq__1)
    )  AS [GroupBy1]',N'@p__linq__0 bigint,@p__linq__1 int',@p__linq__0=593,@p__linq__1=0

i get SURPRISED when the result comes too fast. that was amazing.almost 150 ms to get result.

After that i get another try with something else.

This time i add a number (0) in LINQ query like this:

long id = 593;
var validState = CampaignItemStatus.Failed;
failed = db.CampaignItems.Count(x => x.CampaignId == id + 0 && x.Status == validState);

generated query was:

exec sp_executesql N'SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[CampaignItems] AS [Extent1]
        WHERE ([Extent1].[CampaignId] = (@p__linq__0 + cast(0 as bigint))) AND ([Extent1].[Status] = @p__linq__1)
    )  AS [GroupBy1]',N'@p__linq__0 bigint,@p__linq__1 int',@p__linq__0=593,@p__linq__1=0

and i get surprise again. because this was very fast,too.

now here's are my questions:

1- why using more than a variable make it faster?

2- why adding a zero make it faster? both of these tries make query more complex

3- What should I do to make my normal code work fast without do weird stuff like add nonsense variables or add zero?

ps : campaign item table has almost 1,000,000 rows witch almost 400,000 rows of them are failed items

ps 2: @mjwills : yes I do. each time i run query 30 times and get average time. to clearing cache has no sence. @Thomas Weller : You are rigth. i test that case too. That was also fast. but the question was very long and I dont put all of my result here.

Linq SQL Count Method performance problem

0 Answers