asked on

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

PockyMaster

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ões

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.

Avodah

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