Serious LINQ performance problems compared to running raw sql (with same query)

Hi,

In the attached code you will find the query that linq generates from a realtively complex expression.

I also attach the query converted to sql (its the same query with the parameters set so I can run it in management studio).

Now my problem is this: The exact same query is taking way more time to be ran from LINQ than sql studio. And by way more time I don't mean like a few seconds, it's more like a few minutes.

So here is what I did to test (in exact order):

-Ran the query in sql studio: 2-3sec (making sure the DB was hot, and the query time is more tahn acceptable.
-Ran the query again in sql studio: 2-3 sec again
-Ran the query with LINQ: 329 750ms
-Ran the query with sql studio: 2-3sec
-Ran the query with LINQ a second time: 265 703ms
-Ran the query with sql studio: 2-3 sec
-Ran the query with LINQ one last time: 173 578ms

Well I think you get the picture. No matter what I do, the exact same query takes 2-3 second in sql studio but I haven't yet been able to get it below 2 minutes with LINQ. There must be something really big I am missing, because this does not make sense.

I am not a DBA, so my database and queries might not be optimal (well since I use LINQ they sure are not optimal). But I can afford a little delay for the requests, but 2-3 minutes is simply unnacceptable.

I am open to any idea, I am sure some people have already experienced similar problems.

Please do not give me tips for optimizing my query, I do not want to do that for now and 2-3 seconds is more than acceptable (even if a query would take up to 10-15sec I woudln't really mind).
LINQ query reported by DataContext.Log:

SELECT [t0].[pkItem], [t0].[fkCharacter], [t0].[CharacterClass], [t0].[Name], [t0].[Quality], [t0].[SuperiorType], [t0].[ItemClass], [t0].[Type], [t0].[isEthereal], [t0].[LevelRequirement], [t5].[pkStat], [t5].[fkItem], [t5].[StatId], [t5].[statValue], [t5].[ExtraValue], (
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t10]
    WHERE [t10].[fkItem] = [t0].[pkItem]
    ) AS [value], [t4].[test], [t4].[pkCharacter], [t4].[fkAccount], [t4].[CharacterName], [t4].[Mode], [t4].[Inventory], [t4].[Stash], [t4].[test2], [t4].[pkAccount], [t4].[AccountName], [t4].[AccountPassword], [t4].[isAnniMule], [t4].[Realm]
FROM [dbo].[Items] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[pkCharacter], [t1].[fkAccount], [t1].[CharacterName], [t1].[Mode], [t1].[Inventory], [t1].[Stash], [t3].[test] AS [test2], [t3].[pkAccount], [t3].[AccountName], [t3].[AccountPassword], [t3].[isAnniMule], [t3].[Realm]
    FROM [dbo].[Characters] AS [t1]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t2].[pkAccount], [t2].[AccountName], [t2].[AccountPassword], [t2].[isAnniMule], [t2].[Realm]
        FROM [dbo].[Accounts] AS [t2]
        ) AS [t3] ON [t3].[pkAccount] = [t1].[fkAccount]
    ) AS [t4] ON [t4].[pkCharacter] = [t0].[fkCharacter]
LEFT OUTER JOIN [dbo].[Stats] AS [t5] ON [t5].[fkItem] = [t0].[pkItem]
WHERE (@p0 = [t4].[Realm]) AND (@p1 = [t4].[Mode]) AND (@p2 = [t0].[Quality]) AND (@p3 = [t0].[ItemClass]) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t6]
    WHERE ([t6].[StatId] = @p4) AND ([t6].[statValue] >= @p5) AND ([t6].[fkItem] = [t0].[pkItem])
    )) > @p6) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t7]
    WHERE ([t7].[StatId] = @p7) AND ([t7].[statValue] <= @p8) AND ([t7].[fkItem] = [t0].[pkItem])
    )) > @p9) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t8]
    WHERE ([t8].[StatId] = @p10) AND ([t8].[statValue] >= @p11) AND ([t8].[fkItem] = [t0].[pkItem])
    )) > @p12) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t9]
    WHERE ([t9].[StatId] = @p13) AND ([t9].[statValue] <= @p14) AND ([t9].[fkItem] = [t0].[pkItem])
    )) > @p15)
ORDER BY [t0].[pkItem], [t4].[pkCharacter], [t4].[pkAccount], [t5].[pkStat]

-- @p0: Input VarChar (Size = 6; Prec = 0; Scale = 0) [europe]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [96]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [7]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [135]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [335]
-- @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p7: Input Int (Size = 0; Prec = 0; Scale = 0) [331]
-- @p8: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p9: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p10: Input Int (Size = 0; Prec = 0; Scale = 0) [331]
-- @p11: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p12: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p13: Input Int (Size = 0; Prec = 0; Scale = 0) [335]
-- @p14: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p15: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

Same query used to test with sql studio: 
DECLARE @p0 VarChar(6)
DECLARE @p1 int
DECLARE @p2 int
DECLARE @p3 int
DECLARE @p4 int
DECLARE @p5 int
DECLARE @p6 int
DECLARE @p7 int
DECLARE @p8 int
DECLARE @p9 int
DECLARE @p10 int
DECLARE @p11 int
DECLARE @p12 int
DECLARE @p13 int
DECLARE @p14 int
DECLARE @p15 int

SET @p0 = 'europe'
SET @p1 = 96
SET @p2 = 7
SET @p3 = 135
SET @p4 = 335
SET @p5 = 3
SET @p6 = 0
SET @p7 = 331
SET @p8 = 4
SET @p9 = 0
SET @p10 = 331
SET @p11 = 3
SET @p12 = 0
SET @p13 = 335
SET @p14 = 4
SET @p15 = 0

SELECT [t0].[pkItem], [t0].[fkCharacter], [t0].[CharacterClass], [t0].[Name], [t0].[Quality], [t0].[SuperiorType], [t0].[ItemClass], [t0].[Type], [t0].[isEthereal], [t0].[LevelRequirement], [t5].[pkStat], [t5].[fkItem], [t5].[StatId], [t5].[statValue], [t5].[ExtraValue], (
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t10]
    WHERE [t10].[fkItem] = [t0].[pkItem]
    ) AS [value], [t4].[test], [t4].[pkCharacter], [t4].[fkAccount], [t4].[CharacterName], [t4].[Mode], [t4].[Inventory], [t4].[Stash], [t4].[test2], [t4].[pkAccount], [t4].[AccountName], [t4].[AccountPassword], [t4].[isAnniMule], [t4].[Realm]
FROM [dbo].[Items] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[pkCharacter], [t1].[fkAccount], [t1].[CharacterName], [t1].[Mode], [t1].[Inventory], [t1].[Stash], [t3].[test] AS [test2], [t3].[pkAccount], [t3].[AccountName], [t3].[AccountPassword], [t3].[isAnniMule], [t3].[Realm]
    FROM [dbo].[Characters] AS [t1]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t2].[pkAccount], [t2].[AccountName], [t2].[AccountPassword], [t2].[isAnniMule], [t2].[Realm]
        FROM [dbo].[Accounts] AS [t2]
        ) AS [t3] ON [t3].[pkAccount] = [t1].[fkAccount]
    ) AS [t4] ON [t4].[pkCharacter] = [t0].[fkCharacter]
LEFT OUTER JOIN [dbo].[Stats] AS [t5] ON [t5].[fkItem] = [t0].[pkItem]
WHERE (@p0 = [t4].[Realm]) AND (@p1 = [t4].[Mode]) AND (@p2 = [t0].[Quality]) AND (@p3 = [t0].[ItemClass]) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t6]
    WHERE ([t6].[StatId] = @p4) AND ([t6].[statValue] >= @p5) AND ([t6].[fkItem] = [t0].[pkItem])
    )) > @p6) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t7]
    WHERE ([t7].[StatId] = @p7) AND ([t7].[statValue] <= @p8) AND ([t7].[fkItem] = [t0].[pkItem])
    )) > @p9) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t8]
    WHERE ([t8].[StatId] = @p10) AND ([t8].[statValue] >= @p11) AND ([t8].[fkItem] = [t0].[pkItem])
    )) > @p12) AND (((
    SELECT COUNT(*)
    FROM [dbo].[Stats] AS [t9]
    WHERE ([t9].[StatId] = @p13) AND ([t9].[statValue] <= @p14) AND ([t9].[fkItem] = [t0].[pkItem])
    )) > @p15)
ORDER BY [t0].[pkItem], [t4].[pkCharacter], [t4].[pkAccount], [t5].[pkStat]

Open in new window

LVL 3
MikeDotNet555Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PockyMasterCommented:
Could you post your Linq statement? That would help. Have you also watched the profiler while executing? Could be that Linq is executing SQL statements to load child entities.

Alexandre SimõesManager / Technology SpecialistCommented:
Mate... write a stored procedure.
Problem solved :)

On my projects, when Linq queries tend to get big I don't even bother write them, I go straight to an SP and is plain simple/fast.
Richard LeeSoftware EnthusiastCommented:
Stored procedure solves the problem but when I have this problem with LINQ I use the SQL Profiler to see what is being sent. Quite often LINQ splits the query into several queries when executing. This may be the issue.

DaTribe
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

MikeDotNet555Author Commented:
Hi, thanks for your replies.

@PockyMaster: I have attached the Linq statement associated with the creation of that SQL statement. I am not sure how to watch with the profiler (I use sql server express, I am not even sure that tool is available with it). About your statement "Could be that Linq is executing SQL statements to load child entities.", do you mean that linq would do some deferred loading? If so then I am sure it does not (having setup the right loadwith options) and I think that I would see those statement within the DataContext.Log.

@AlexCode: That could be an idea, but I don't have the required SQL knowledge to write such complex SQL. I could be interested in doing so with some help tho, as long as it dosen't take 2 days for me to do it (I don't have that much time to allocate for rewriting data-access logic).


DataLoadOptions op = new DataLoadOptions();
            op.LoadWith<DBEngine.Item>(n => n.Stats);
            op.LoadWith<DBEngine.Item>(n => n.Character);
            op.LoadWith<DBEngine.Character>(n => n.Account);
            op.LoadWith<DBEngine.Stat>(n => n.ExtraValue);
            op.LoadWith<DBEngine.Stat>(n => n.fkItem);
            op.LoadWith<DBEngine.Stat>(n => n.pkStat);
            op.LoadWith<DBEngine.Stat>(n => n.StatId);
            op.LoadWith<DBEngine.Stat>(n => n.statValue);
            dataContext.LoadOptions = op;

var result = from k in dataContext.Items
                         where (realm == k.Character.Account.Realm)
                         && (mode == k.Character.Mode)
                         && (CharacterClass == null || CharacterClass == (D2DataNew.CharacterClass?)k.CharacterClass)
                         && (ItemQuality == null || ItemQuality == (ItemQuality?)k.Quality)
                         && (SuperiorType == null || SuperiorType == (SuperiorItemType?)k.SuperiorType)
                         && (ItemClass == null || ItemClass == (ItemClass?)k.ItemClass)
                         && (ItemType == null || ItemType == (ItemType?)k.Type)
                         && (isEthereal == null || isEthereal == (bool?)k.isEthereal)

                         && (equalStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[0].StatType) && (n.statValue == (int?)equalStats[0].Value) && (equalStats[0].Extra == null ? true : n.ExtraValue == equalStats[0].Extra)) > 0)
                         && (greaterStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[0].StatType) && (n.statValue > (int?)greaterStats[0].Value) && (greaterStats[0].Extra == null ? true : n.ExtraValue == greaterStats[0].Extra)) > 0)
                         && (smallerStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[0].StatType) && (n.statValue < (int?)smallerStats[0].Value) && (smallerStats[0].Extra == null ? true : n.ExtraValue == smallerStats[0].Extra)) > 0)
                         && (greaterOrEqualsStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[0].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[0].Value) && (greaterOrEqualsStats[0].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[0].Extra)) > 0)
                         && (smallerOrEqualsStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[0].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[0].Value) && (smallerOrEqualsStats[0].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[0].Extra)) > 0)
                         && (notStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[0].StatType) == 0)

                         && (equalStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[1].StatType) && (n.statValue == (int?)equalStats[1].Value) && (equalStats[1].Extra == null ? true : n.ExtraValue == equalStats[1].Extra)) > 0)
                         && (greaterStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[1].StatType) && (n.statValue > (int?)greaterStats[1].Value) && (greaterStats[1].Extra == null ? true : n.ExtraValue == greaterStats[1].Extra)) > 0)
                         && (smallerStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[1].StatType) && (n.statValue < (int?)smallerStats[1].Value) && (smallerStats[1].Extra == null ? true : n.ExtraValue == smallerStats[1].Extra)) > 0)
                         && (greaterOrEqualsStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[1].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[1].Value) && (greaterOrEqualsStats[1].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[1].Extra)) > 0)
                         && (smallerOrEqualsStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[1].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[1].Value) && (smallerOrEqualsStats[1].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[1].Extra)) > 0)
                         && (notStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[1].StatType) == 0)

                         && (equalStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[2].StatType) && (n.statValue == (int?)equalStats[2].Value) && (equalStats[2].Extra == null ? true : n.ExtraValue == equalStats[2].Extra)) > 0)
                         && (greaterStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[2].StatType) && (n.statValue > (int?)greaterStats[2].Value) && (greaterStats[2].Extra == null ? true : n.ExtraValue == greaterStats[2].Extra)) > 0)
                         && (smallerStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[2].StatType) && (n.statValue < (int?)smallerStats[2].Value) && (smallerStats[2].Extra == null ? true : n.ExtraValue == smallerStats[2].Extra)) > 0)
                         && (greaterOrEqualsStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[2].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[2].Value) && (greaterOrEqualsStats[2].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[2].Extra)) > 0)
                         && (smallerOrEqualsStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[2].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[2].Value) && (smallerOrEqualsStats[2].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[2].Extra)) > 0)
                         && (notStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[2].StatType) == 0)

                         && (equalStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[3].StatType) && (n.statValue == (int?)equalStats[0].Value) && (equalStats[3].Extra == null ? true : n.ExtraValue == equalStats[3].Extra)) > 0)
                         && (greaterStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[3].StatType) && (n.statValue > (int?)greaterStats[3].Value) && (greaterStats[3].Extra == null ? true : n.ExtraValue == greaterStats[3].Extra)) > 0)
                         && (smallerStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[3].StatType) && (n.statValue < (int?)smallerStats[3].Value) && (smallerStats[3].Extra == null ? true : n.ExtraValue == smallerStats[3].Extra)) > 0)
                         && (greaterOrEqualsStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[3].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[3].Value) && (greaterOrEqualsStats[3].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[3].Extra)) > 0)
                         && (smallerOrEqualsStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[3].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[3].Value) && (smallerOrEqualsStats[3].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[3].Extra)) > 0)
                         && (notStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[3].StatType) == 0)

                         && (equalStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[4].StatType) && (n.statValue == (int?)equalStats[4].Value) && (equalStats[4].Extra == null ? true : n.ExtraValue == equalStats[4].Extra)) > 0)
                         && (greaterStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[4].StatType) && (n.statValue > (int?)greaterStats[4].Value) && (greaterStats[4].Extra == null ? true : n.ExtraValue == greaterStats[4].Extra)) > 0)
                         && (smallerStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[4].StatType) && (n.statValue < (int?)smallerStats[4].Value) && (smallerStats[4].Extra == null ? true : n.ExtraValue == smallerStats[4].Extra)) > 0)
                         && (greaterOrEqualsStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[4].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[4].Value) && (greaterOrEqualsStats[4].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[4].Extra)) > 0)
                         && (smallerOrEqualsStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[4].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[4].Value) && (smallerOrEqualsStats[4].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[4].Extra)) > 0)
                         && (notStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[4].StatType) == 0)

                         && (notStats[5].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[5].StatType) == 0)
                         && (notStats[6].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[6].StatType) == 0)
                         && (notStats[7].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[7].StatType) == 0)
                         && (notStats[8].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[8].StatType) == 0)
                         && (notStats[9].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[9].StatType) == 0)
                         && (notStats[10].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[10].StatType) == 0)
                         && (notStats[11].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[11].StatType) == 0)
                         && (notStats[12].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[12].StatType) == 0)
                         && (notStats[13].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[13].StatType) == 0)
                         && (notStats[14].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[14].StatType) == 0)
                         select  k;

Open in new window

PockyMasterCommented:
Wow.. that's quite a statement. I would assume the query itself would normally take as long to execute as it would when executed on SQL, but query preparation will take a bit longer, due to your way of structuring. I'm not 100% convinced that you're hitting the database only once.

What you can so, is taking out the major part of your construction to outside of your query.

e.g.
var result = from k in dataContext.Items
                         where (realm == k.Character.Account.Realm)
                         && (mode == k.Character.Mode)
                         && (CharacterClass == null || CharacterClass == (D2DataNew.CharacterClass?)k.CharacterClass)
                         && (ItemQuality == null || ItemQuality == (ItemQuality?)k.Quality)
                         && (SuperiorType == null || SuperiorType == (SuperiorItemType?)k.SuperiorType)
                         && (ItemClass == null || ItemClass == (ItemClass?)k.ItemClass)
                         && (ItemType == null || ItemType == (ItemType?)k.Type)
                         && (isEthereal == null || isEthereal == (bool?)k.isEthereal)
                select k;

that will give you a shorter query.

now append whatever is required:
for (int i = 1; i < 15; i++)
{
  if (notStats[i].StatType != (int)StatType.None)
         result = result.Where(r => r.Stats.Count(n => n.StatId == (int?)notStats[i].StatType) == 0);
}

etc.. for your other statements. In this way it's not the Linq query builder doing the work, but the normal runtime. Could be faster.
If you want to do a re-run compare, you also might want to experiment with compiled linq queries, since that's more fair to compare to re-running SQL queries right on your database.
MikeDotNet555Author Commented:
Hi, I was doing something similar to this befor but I completly remade the query so it generate 1 query. The problem with a query having multiple where clauses is that it will do alot of DB roundtrip (performances was 2-5 time worst befor this, and the db was twice smaller).

So except if I missed something from your post, doing database roundtrips will not help but only decrease performances.

I also tough about compiled query but I think that with that level of complexity it could not be possible.

Also something interested I noted from your post is "I'm not 100% convinced that you're hitting the database only once.". Would this mean that there is a bug in the DataContext.Log output and that it hides some queries? That could explain alot of things. Do you know if there is a way to verify wich queries are run on the SQL Server 2005 express side? That way atleast I could be sure that I am really only hitting SQL server once.
Alexandre SimõesManager / Technology SpecialistCommented:
Now I understand why it takes that much time on C# than directly running the query... :)
Besides the normal performance differences between the two, I cannot imagine the amount of work needed to evaluate all those ternary where clauses... :)

Do you really need all those "expensive" where clauses evaluated at once?
You have conditions based on external arrays items... this all looks too complex, can't we help to simplify the way you're implementing this?

Try to explain your needs, I'll try to help you figuring out a better solution.

MikeDotNet555Author Commented:
So the processing time to generate the query would be the problem? That would make sense except there is a little thing that bug me. If I run taht query in a database with nothing in it, it returns 0 items in less than 1 second, If I rebuild my indexes and restart sql server on my big database it runs a little bit faster.

So I will try to explain waht I need now.

This is used for a search in one of my database. There are 4 tables linked togerther each with a 1:n relationship as follow:

Account <1:N> Character <1:N> Item <1:N> Stat

The search need to retrieve the item with information from all 3 tables. There are possible search criteria in account, character and item table. It get a little more complicated with stat table.

For the stats, There can be a multitude of stats with >, <, = operators. So and item can have search criteria with 5 different stats. It could look like this:

Item need to have:
-Stat #43 > 34
-Stat #66 < 104
-Stat #901 = 22
-Stat #545 >= 1
-Stat #34 <= 22

I don't know if I explained thing clearly enough (I am not really good at explaining that kind of stuff). If you need more precisions let me know.

Thank you alot.
Alexandre SimõesManager / Technology SpecialistCommented:
I understood the relations: 1 Account can have N Characters and 1 character can have N Stats correct?

I also understood that the result includes columns from the 3 tables. Here you must elaborate your simple result query without any filers, something like:

var q = from acc in Accounts
            join chars in Character on acc.AccountID equals chars.AccountID
            join stats in Stats on chars.CharacterID equals stats.CharacterID
            select acc.Name, chars.Name, stats.Name, stats.Value;

this would return 3 columns (I'm assuming that the 3 tables have a column 'Name' and Stat have a Value column).

From this base query we can build our conditions as we need, for example:

q += q.Where(c => stats.Value > 34);

this will add a condintion filtering all stats > 34. If you have to add another condition you can repeat the above line with new conditions as many times as you need.
I use this technique for search methods, where I receive nullable arguments and validate them the following way:

consider: int? age
if(age.HasValue) { q += q.Where(c => c.Age > age.Value); }

this way I only apply the age filter if any age was passed.


You can make these where clauses as nasty as you would make them directly on the select, the important here is that you only actually apply them to the query if you really need to.


Hope this helps,
Alex
Alexandre SimõesManager / Technology SpecialistCommented:
Forgot to say that the query will only hit the database when actually request the data... this is how Linq works.
PockyMasterCommented:
It also might help in many cases that you only select a certain amount of columns you need, instead of the object.

So e.g.
var qry = from k in ctx.MyTable
              select new { Column1 = k.Column1,
                                   Column2 = k.Column2
                      };

In that case you will never have the need of creating other objects than the ones you are querying. I hope that is clear. (That's what I do most of the time when using Linq to fill grids).

@ where clauses:
It should not do extra roundtrips for the where clauses, that's because you're extending your query and not performing the Where on your result (nothing's enumerated at that time).
MikeDotNet555Author Commented:
Thanks Alex I will try to apply your solution and come back with results.

@PockyMaster: Yes that could be interesting eventually but that query above was only returning 8 items (or 32 row in sql) so that should not be the case there.
PockyMasterCommented:
@29730267 AlexCode:

that was exactly what I was trying to say in 29704879
MikeDotNet555Author Commented:
Unfortunately, Changing the way query is built did not solve anything, in fact, the query building dosen't take pretty much more than 300ms (I tested that by building my query and using DataContext.ExecuteQuery() instead of using only LINQ syntax). I have a 300ms query build time almost every time but my query ran over 180 000ms, still being a few seconds in sql server.

I am really out of ideas for now.
MikeDotNet555Author Commented:
I also make sure that there was no locks prior of the execution of the query. Also I noticed that things are much worst when I have operations on "stats" (see above) table. Queries without stats take anywhere from 5-30sec usually wich is alot less (still not acceptable, but for now if I could have queries that run in 30sec  or less I will be happy).

and again, the exact same query in sql server run in 3-7 seconds.
Alexandre SimõesManager / Technology SpecialistCommented:
For me, any query that runs above 5 seconds is a bad query or is running on a bad server :)
So when I don't have budget to spend on new hardware and a query takes longer than I accept I need so work on "workarounds" :)

One of the most effective is adding pre-calculated columns.
Time consuming tasks on queries are usually SUMs over log tables, so calculating them to date and/or by month helps me retrieving financial reports much faster that they would be.

Your problem here is different tho... you have a nasty where clause. :)

My last option is to bypass the Linq Reflection tasks when retrieving the data.
If the slow part isn't on the query construction then lets execute the query Linq generates in the ADO old fashion way :)

Perform the query as shown on the code snippet bellow, lets see how much time will it take.
Don't forget to comment the line on your code that executes the query using the Linq engine ok?

Mate, after this one, if it doesn't help, I only see a StoredProcedure shining :)

Cheers!
Alex

 
DataSet dsInc = new DataSet();
DataClassesDataContext db = new DataClassesDataContext();
var result = !!! YOUR NASTY LINQ QUERY HERE !!!
SqlCommand cmd = db.GetCommand(result) as SqlCommand;
DataTable dataTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dsInc);       

// At this point dsInc DataSet holds the query result... 

Open in new window

MikeDotNet555Author Commented:
wow. adapter.Fill(dsInc) = 2-3seconds exactly the time it takes in sql server management studio.

I don't know what to think, would this be a bug in linq-to-sql? Also this query that I am testing with is only returning 48 rows so I do not think that it would be the time that it takes to generate the objects that is taking over 180 000ms.

so basically the only thing I changed is
tested with: var result = myQuery.ToList(); (180 000-  350 000ms execution)
tested with: var result = dataContext.ExecuteQuery<Item>(dbCommand.CommandText, dbParams.ToArray()); (180 000 - 250 000ms execution)
tested with: var result = adapter.Fill(dsInc); (2-3 seconds)

Other than that... there is nothing changed. Do you think I ran in a bug from linq? What should I do now? Chaging everything to use datasets would take so much time.. I would need a quicker solution are there any propositions?
MikeDotNet555Author Commented:
Actually I was happy too fast.. for some reason today that code under linq is running at 3-5 seconds.. I really wonder what is hapenning. I will need to wait a little when the query is taking forever again to re-test your solution.
MikeDotNet555Author Commented:
I think I might be experiencing long locking issues.. Is there any way to monitor that except sp_lock?
Alexandre SimõesManager / Technology SpecialistCommented:
These are SELECT statements so no locks are used... don't bother about that.
Now  for your issue... You can use this method I gave you only when extremely necessary, tho I don't know why your Linq execution ever took forever to finish but with ADO there's no underlying magic.

Please have a look at you availabe RAM on the computer and evaluate if SQL can place the whole database into memory. If SQL doesn't have the memory it needs available then your queries will be slooooow! :)
MikeDotNet555Author Commented:
Are you sure that locking can not be an issue even if alot of updates are happening at a moment?

I read this article
http://articles.techrepublic.com.com/5100-10878_11-6185492.html

and a few other googled stuff and to me it looks that while and update/insert is happening, the table is locked, wich could prevent reads from hapenning to prevent reading dirty data. Am I wrong in my assumption?
MikeDotNet555Author Commented:
Also if I execute sp_lock right after I start performing a search, I see a bunch of locked stuff that is not there usually.
PockyMasterCommented:
of course selects can be blocked by locks.
PockyMasterCommented:
As far as I know LinqToSql uses the read committed transaction mode, which is the heaviest. That means if rows that are part of your query happen to be updated, you might hit a lock.
Though that must be the same when you're running directly from SQL.
MikeDotNet555Author Commented:
so are there any way to monitor if the database is using too much locks with sql express?
MikeDotNet555Author Commented:
Hi everyone, I just started to see the slowdowns again today.

Well I can pretty much say that locking is not an issue.

Also using a SqlDataAdapter show 75000ms while the query ran under visual studio show 2seconds.

I am really clueless as to what to do now I have no clue why this is happening and it is a really big problem for my customer.

I also made sure to record the time of the Fill() function only with nothing else to be sure I do not have erronous data.

Any ideas?
Alexandre SimõesManager / Technology SpecialistCommented:
Let me see if I get this straight...
Using an SqlDataAdapter the query took 75 seconds to finish while pasting the same query on Management Studio and executing it directly took 2 seconds?

Are we talking about the same machine exactly under the same circunstances?
Is the SQL Instance local or remote?
Executing each kind of query twice in a row produces the same results each time?

How are you connecting to SQL Server?
Paste here your connection string please.

Cheers!
Alex
MikeDotNet555Author Commented:
Hi, you have some interesting questions here.

I am using the exact same machine, and the server is local. The cirsumstances should be the same since I run the query one right after the other on the same machine. So yes, executing each kind of query twice in a row produces the same result each time.

There is an exception tho, sometime my queries are fast with linq, that is why it took me that much time to answer, because I started to see some slowdowns again today. The other days were fine and the results were pretty similar (except maby 1-2sec overhead with .net for the time ti takes to process my data, but that is not a problem at all).

this is my connection string:
static string connString = @"Data Source=" + Environment.MachineName + @"\SQLEXPRESS;Initial Catalog=MuleBase;Integrated Security=True;MultipleActiveResultSets=True";    

the sql is per-instance I am pretty sure.

Here are a few things from the configuration manager:
-The only protocol enabled is shared memory
-enabled client protocols (in order): shared memory, tcp/ip, named pipes
-other than that, everything seem to be disabled.
Alexandre SimõesManager / Technology SpecialistCommented:
What about you machine memory? How much of it is free for SQL to use?

What is the size of your database mdf file?

What's the largest table involved on the query?


The big thing here is discover what happens on your machine when your queries are slow.
If the can run fast is because everything involved in the process can handle it, but somethimes becomes a zombie.

Do you have any scheduled maintenance tasks on your computer?
Something like Defrag, Antivirus Scan,... something heavy on IO...

When the queries are slow, have you run the TaskManager to see the CPU workload and the IO usage?



Right now I'm on a trial/Error stage with you :)

Cheers mate,
Alex
Alexandre SimõesManager / Technology SpecialistCommented:
I forgot the best of all tests... the reboot! :)

What happens after machine reboot? Is it fast or slow?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PockyMasterCommented:
Seems we've eliminated LinQ from the equasion right?
MikeDotNet555Author Commented:
Thank AlexCode and PockyMaster. I think you two have tryied to help me enough I will close this thread and award points now for a few reasons.

First of all I did not find the solution to my problem, but you two posted alot and tryied to help as much as you can this is why I think you deserve the points even if I do not find a solution yet.

I have a new hint tho, now I am pretty sure that this is due to disk I/O. I will post a new thread about it and give more ample details.
Alexandre SimõesManager / Technology SpecialistCommented:
Post the link to the new question so we can follow it...
In deed, this couldn't be a Linq problem.

Cheers,
Alex
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.