Entity Framework Core : query improvement

Question

I'm new to EF, I have a database in which a certain table contains keys of a number of other tables, it is the central table from which I go to the other tables (ProductIdentifiers).

The input to the query is not id, but the Name which is not defined as any key.

Here's my Entity Framework query :

public ProductIdentifier? GetFullCedMed(string v)
       => db.ProductIdentifiers.Where(a => a.Name == v)
            .Include(ced => ced.Project)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.Parameter)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.Bin)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.Stage)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.TestTypeNavigation)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.Parameter)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.Bin)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.Stage)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.TestTypeNavigation)
            .Include(ced => ced.FatherCedmed)
            .ToList().FirstOrDefault();

The code is converted to a SQL query that looks like this:

exec sp_executesql N'SELECT [p].[id], [p].[FatherCedmedId], [p].[Name], [p].[ProjectId], [m].[id], [m].[IsActive], [m].[Name], [m].[ProductLineName], [p0].[id], [t].[id], [t].[BinId], [t].[CED_MED], [t].[LSL], [t].[ParameterID], [t].[StageID], [t].[TestType], [t].[USL], [t].[id0], [t].[Enabled], [t].[FORMAT], [t].[IsLimit], [t].[ParamID], [t].[Parameter_Name], [t].[Print], [t].[Unit], [t].[id1], [t].[BinDescription], [t].[BinDescriptionOverride], [t].[BinNumber], [t].[BinNumberOverride], [t].[GroupID], [t].[ParamID0], [t].[StageID0], [t].[id2], [t].[Name], [t].[StageNumber], [t].[id3], [t].[LevelId], [t].[Name0], [t].[OrderingId], [t0].[id], [t0].[BinId], [t0].[CED_MED], [t0].[ParameterID], [t0].[StageID], [t0].[TestType], [t0].[Value], [t0].[id0], [t0].[Enabled], [t0].[FORMAT], [t0].[IsLimit], [t0].[ParamID], [t0].[Parameter_Name], [t0].[Print], [t0].[Unit], [t0].[id1], [t0].[BinDescription], [t0].[BinDescriptionOverride], [t0].[BinNumber], [t0].[BinNumberOverride], [t0].[GroupID], [t0].[ParamID0], [t0].[StageID0], [t0].[id2], [t0].[Name], [t0].[StageNumber], [t0].[id3], [t0].[LevelId], [t0].[Name0], [t0].[OrderingId], [p0].[FatherCedmedId], [p0].[Name], [p0].[ProjectId]
FROM [ProductIdentifiers] AS [p]
LEFT JOIN [main_Projects] AS [m] ON [p].[ProjectId] = [m].[id]
LEFT JOIN [ProductIdentifiers] AS [p0] ON [p].[FatherCedmedId] = [p0].[id]
LEFT JOIN (
    SELECT [l].[id], [l].[BinId], [l].[CED_MED], [l].[LSL], [l].[ParameterID], [l].[StageID], [l].[TestType], [l].[USL], [p1].[id] AS [id0], [p1].[Enabled], [p1].[FORMAT], [p1].[IsLimit], [p1].[ParamID], [p1].[Parameter_Name], [p1].[Print], [p1].[Unit], [b].[id] AS [id1], [b].[BinDescription], [b].[BinDescriptionOverride], [b].[BinNumber], [b].[BinNumberOverride], [b].[GroupID], [b].[ParamID] AS [ParamID0], [b].[StageID] AS [StageID0], [s].[id] AS [id2], [s].[Name], [s].[StageNumber], [m0].[id] AS [id3], [m0].[LevelId], [m0].[Name] AS [Name0], [m0].[OrderingId]
    FROM [LimitValues] AS [l]
    INNER JOIN [Parameters] AS [p1] ON [l].[ParameterID] = [p1].[id]
    INNER JOIN [Bins] AS [b] ON [l].[BinId] = [b].[id]
    LEFT JOIN [Stages] AS [s] ON [l].[StageID] = [s].[id]
    LEFT JOIN [main_TestTypes] AS [m0] ON [l].[TestType] = [m0].[id]
) AS [t] ON [p].[id] = [t].[CED_MED]
LEFT JOIN (
    SELECT [c].[id], [c].[BinId], [c].[CED_MED], [c].[ParameterID], [c].[StageID], [c].[TestType], [c].[Value], [p2].[id] AS [id0], [p2].[Enabled], [p2].[FORMAT], [p2].[IsLimit], [p2].[ParamID], [p2].[Parameter_Name], [p2].[Print], [p2].[Unit], [b0].[id] AS [id1], [b0].[BinDescription], [b0].[BinDescriptionOverride], [b0].[BinNumber], [b0].[BinNumberOverride], [b0].[GroupID], [b0].[ParamID] AS [ParamID0], [b0].[StageID] AS [StageID0], [s0].[id] AS [id2], [s0].[Name], [s0].[StageNumber], [m1].[id] AS [id3], [m1].[LevelId], [m1].[Name] AS [Name0], [m1].[OrderingId]
    FROM [ConfigValues] AS [c]
    INNER JOIN [Parameters] AS [p2] ON [c].[ParameterID] = [p2].[id]
    INNER JOIN [Bins] AS [b0] ON [c].[BinId] = [b0].[id]
    LEFT JOIN [Stages] AS [s0] ON [c].[StageID] = [s0].[id]
    LEFT JOIN [main_TestTypes] AS [m1] ON [c].[TestType] = [m1].[id]
) AS [t0] ON [p].[id] = [t0].[CED_MED]
WHERE [p].[Name] = @__v_0
ORDER BY [p].[id], [m].[id], [p0].[id], [t].[id], [t].[id0], [t].[id1], [t].[id2], [t].[id3], [t0].[id], [t0].[id0], [t0].[id1], [t0].[id2]',N'@__v_0 varchar(255)',@__v_0='NAME_OF_RECORD_FROM_ProductIdentifier_TABLE'

Pay attention to the input in the ORDER BY line.

My question is: how can the query be improved?

The tables contain a lot of data and the query takes a lot of time.

Will retrieval by ID make it faster? Indicates that all the data is relevant for me, which means that it is necessary to join all the tables.

Thanks for any other advice.

Are all the foreign key columns in your database properly indexed? That can definitely help with query speed — marc_s
– marc_s, Commented Dec 26, 2022 at 11:06
Remove ToList() from query! It materializes whole table. FirstOrDefault is enough. Also use AsSplitQuery — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Dec 26, 2022 at 11:16
+1000 for @Svyatoslav comment. This ToList() will fetch all your data in memory to finally keep only one value. To avoid. — paradise
– paradise, Commented Dec 27, 2022 at 10:26
"how can the query be improved?" => First step you should analyse it using SQLS Profiler*, visualizing query plan which will indicate you if your index is used and relevant, the time spent for each operation within your query, etc. (*if your RDBMS is SQLServer) — paradise
– paradise, Commented Dec 27, 2022 at 10:37

Hossein Giyah · Accepted Answer · 2022-12-26 11:03:34Z

0

for creating separated query and getting better performance use .AsSplitQuery()

answered Dec 26, 2022 at 11:03

Hossein Giyah

564 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework Core : query improvement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related