October 2, 2018 at 5:51 pm
I've got a query that's costing me a lot during index seek.
I ran the query on Database Tuning Advisor but it didn't recommend any suggestions.
This query is ran multiple times on a page.
Appreciate any help on this.
Query below generated by EntityFramework. I've attached the execution plan as well.SELECT
[Project4].[Id1] AS [Id],
[Project4].[Code] AS [Code],
[Project4].[Name] AS [Name],
[Project4].[IsCore] AS [IsCore],
[Project4].[ColorSquaresRgb] AS [ColorSquaresRgb],
[Project4].[LimitedToStores] AS [LimitedToStores]
FROM ( SELECT
[Project2].[Id] AS [Id],
[Limit1].[Id] AS [Id1],
[Limit1].[Code] AS [Code],
[Limit1].[Name] AS [Name],
[Limit1].[IsCore] AS [IsCore],
[Limit1].[ColorSquaresRgb] AS [ColorSquaresRgb],
[Limit1].[LimitedToStores] AS [LimitedToStores]
FROM (SELECT
[Distinct1].[Id] AS [Id]
FROM ( SELECT DISTINCT
[Extent1].[Id] AS [Id]
FROM [dbo].[Color] AS [Extent1]
LEFT OUTER JOIN [dbo].[StoreMapping] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[EntityId]) AND (N'Color' = [Extent2].[EntityName])
WHERE ([Extent1].[LimitedToStores] <> 1) OR (17 = [Extent2].[StoreId])
) AS [Distinct1] ) AS [Project2]
OUTER APPLY (SELECT TOP (1)
[Extent3].[Id] AS [Id],
[Extent3].[Code] AS [Code],
[Extent3].[Name] AS [Name],
[Extent3].[IsCore] AS [IsCore],
[Extent3].[ColorSquaresRgb] AS [ColorSquaresRgb],
[Extent3].[LimitedToStores] AS [LimitedToStores]
FROM [dbo].[Color] AS [Extent3]
LEFT OUTER JOIN [dbo].[StoreMapping] AS [Extent4] ON ([Extent3].[Id] = [Extent4].[EntityId]) AND (N'Color' = [Extent4].[EntityName])
WHERE (([Extent3].[LimitedToStores] <> 1) OR (17 = [Extent4].[StoreId])) AND ([Project2].[Id] = [Extent3].[Id]) ) AS [Limit1]
) AS [Project4]
ORDER BY row_number() OVER (ORDER BY [Project4].[Id] ASC)
OFFSET 0 ROWS FETCH NEXT 2147483647 ROWS ONLY
October 2, 2018 at 7:21 pm
The outer apply with the TOP 1 is killing you. It's doing 8443 seeks on the color table. There's only 8443 rows in the whole table.
Of course, the triple nested SELECTs aren't helping at all here either.
When we find EF code at work that looks like this, we build the code we need as a stored procedure and have EF call the stored procedure. Of course, the stored procedure is more well written.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2018 at 12:31 am
Thanks Jeff for taking a look at this.
You are right we will have to revisit the actual implementation of this now.
October 3, 2018 at 6:16 am
Seems to me there might be a simpler query lurking inside that one - and I reckon it might look something like this:
SELECT
d.[Id],
d.[Code],
d.[Name],
d.[IsCore],
d.[ColorSquaresRgb],
d.[LimitedToStores]
FROM (
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY c2.[Id] ORDER BY (SELECT NULL)),
c2.[Id],
c2.[Code],
c2.[Name],
c2.[IsCore],
c2.[ColorSquaresRgb],
c2.[LimitedToStores]
FROM [dbo].[Color] AS c2
LEFT OUTER JOIN [dbo].[StoreMapping] AS s2
ON (c2.[Id] = s2.[EntityId])
AND (N'Color' = s2.[EntityName])
WHERE (c2.[LimitedToStores] <> 1) OR (17 = s2.[StoreId])
) d
WHERE rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2018 at 8:31 am
vicesjr - Wednesday, October 3, 2018 12:31 AMThanks Jeff for taking a look at this.
You are right we will have to revisit the actual implementation of this now.
I've not checked his code to see if it's going to produce identical results but Chris Morris is on the right track. I know it causes a distraction for some front-enders but having a stored procedure do things like this eliminates a whole lot of fluff that EF and other ORMs add to the code. The other cool part about it is that it's a very handy level of abstraction where you don't need to do a software release to make simple changes either for an increase in functionality or an increase in performance.
As for the argument of portability (which usually comes up as some justification to not use stored procedures)... true portability is pretty much a myth. Take advantage of the incredible functionality and performance attributes of each RDBMS you might want to play the front-end code against.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2018 at 5:18 pm
Cheers for your insights Chris and Jeff. We'll consider transforming some of these LINQ queries into SP. You have a strong argument of having that capability to optimize queries without touching and deploying front end code base.
October 4, 2018 at 6:51 am
If you're using Entity Framework (not EF Core) I'd consider using a view, not a stored procedure, for the select. If you shape the view the way the entity expects then you don't have to go through the rigmarole of trying to get Entity Framework to use a stored procedure for your select (different code)...you can simply use the view as you would normally reference any other table entity. Couple that with stored procedures for the INSERT/UPDATE/DELETE (and map your entity to stored procedures) and you're good to go. If you're translating an existing model to this pattern it'd be a lot of rework though, but maybe not bad for a single entity.
October 4, 2018 at 7:14 am
;WITH Distinct1 AS
(
SELECT DISTINCT
Extent1.Id AS Id
FROM dbo.Color AS Extent1
LEFT JOIN dbo.StoreMapping AS Extent2
ON Extent1.Id = Extent2.EntityId
AND N'Color' = Extent2.EntityName
WHERE Extent1.LimitedToStores <> 1
OR 17 = Extent2.StoreId
)
SELECT Limit1.Id AS Id,
Limit1.Code AS Code,
Limit1.Name AS Name,
Limit1.IsCore AS IsCore,
Limit1.ColorSquaresRgb AS ColorSquaresRgb,
Limit1.LimitedToStores AS LimitedToStores
FROM Distinct1
OUTER APPLY(SELECT TOP(1)
Extent3.Id AS Id,
Extent3.Code AS Code,
Extent3.Name AS Name,
Extent3.IsCore AS IsCore,
Extent3.ColorSquaresRgb AS ColorSquaresRgb,
Extent3.LimitedToStores AS LimitedToStores
FROM dbo.Color AS Extent3
LEFT JOIN dbo.StoreMapping AS Extent4
ON Extent3.Id = Extent4.EntityId
AND (N'Color' = Extent4.EntityName)
WHERE (Extent3.LimitedToStores <> 1
OR 17 = Extent4.StoreId)
AND Distinct1.Id = Extent3.Id) AS Limit1
ORDER BY Project4.Id SELECT DISTINCT
[Extent1].[Id] AS [Id]
FROM [dbo].[Color] AS [Extent1]
LEFT JOIN [dbo].[StoreMapping] AS [Extent2]
ON [Extent1].[Id] = [Extent2].[EntityId]
AND N'Color' = [Extent2].[EntityName]
WHERE [Extent1].[LimitedToStores] <> 1
OR 17 = [Extent2].[StoreId]SELECT TOP(1)
Extent3.Id AS Id,
Extent3.Code AS Code,
Extent3.Name AS Name,
Extent3.IsCore AS IsCore,
Extent3.ColorSquaresRgb AS ColorSquaresRgb,
Extent3.LimitedToStores AS LimitedToStores
FROM dbo.Color AS Extent3
LEFT JOIN dbo.StoreMapping AS Extent4
ON Extent3.Id = Extent4.EntityId
AND (N'Color' = Extent4.EntityName)
WHERE (Extent3.LimitedToStores <> 1
OR 17 = Extent4.StoreId)
AND Distinct1.Id = Extent3.IdCREATE INDEX IX_Color_1 ON [dbo].[Color] (Id)
CREATE INDEX IX_StoreMapping_1 ON [dbo].[StoreMapping] ([EntityName],EntityId)
CREATE INDEX IX_StoreMapping_2 ON [dbo].[StoreMapping] (StoreId)October 4, 2018 at 7:21 am
dmbaker - Thursday, October 4, 2018 6:51 AMIf you're using Entity Framework (not EF Core) I'd consider using a view, not a stored procedure, for the select. If you shape the view the way the entity expects then you don't have to go through the rigmarole of trying to get Entity Framework to use a stored procedure for your select (different code)...you can simply use the view as you would normally reference any other table entity. Couple that with stored procedures for the INSERT/UPDATE/DELETE (and map your entity to stored procedures) and you're good to go. If you're translating an existing model to this pattern it'd be a lot of rework though, but maybe not bad for a single entity.
The problem with views is that they can only be externally filtered. It would be better to use a parameterized stored procedure that can take advantage of pre-filtering, especially if any aggregation is involved. A stored procedure will also allow for the use of very high performance "Divide'n'Conquer" techniques where a view can only be a single, sometimes massive and massively complex query.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2018 at 7:31 am
And the problem with a stored procedure is that you have to work around Entity Framework (with different code) to get it to work, whereas a view appears to EF as, for the most part, a normal table/entity object.
I've done it both ways so yeah, I know how it works and a stored proc can definitely be better for some situations. If, however, OP already has model coded and wants to minimize code changes to the client app, then a view might fit that better because the view can be shaped to fit the existing entity exactly. This, however, looks like it's a projection/join situation though, so if OP doesn't already have an existing entity to fit the data then maybe the extra code isn't a big deal (and it's not that big of a deal regardless).
October 4, 2018 at 1:36 pm
dmbaker - Thursday, October 4, 2018 7:31 AMAnd the problem with a stored procedure is that you have to work around Entity Framework (with different code) to get it to work, whereas a view appears to EF as, for the most part, a normal table/entity object.I've done it both ways so yeah, I know how it works and a stored proc can definitely be better for some situations. If, however, OP already has model coded and wants to minimize code changes to the client app, then a view might fit that better because the view can be shaped to fit the existing entity exactly. This, however, looks like it's a projection/join situation though, so if OP doesn't already have an existing entity to fit the data then maybe the extra code isn't a big deal (and it's not that big of a deal regardless).
Consider the original problem on this thread where the ORM is addressing multiple "entities" to begin with. There WILL be a managed code change required whether it's to use a view or use a stored procedure. It'll also be worth it either way, as you say. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2018 at 1:53 pm
Jeff Moden - Thursday, October 4, 2018 1:36 PMConsider the original problem on this thread where the ORM is addressing multiple "entities" to begin with.
I did, that's why I said "This, however, looks like it's a projection/join situation though". That doesn't mean there isn't an existing POCO to handle the projection (can't tell, we don't have the source code), but even if there is it'd have to be tied into the EF context if it's not already (and I'm guessing it's not), so yeah, some code change will be required. Sort-of depends on the pattern OP is using I guess, seems a shame to have a one-off that may be coded differently than everything else but you gotta do that sometimes.
October 4, 2018 at 9:18 pm
dmbaker - Thursday, October 4, 2018 1:53 PMJeff Moden - Thursday, October 4, 2018 1:36 PMConsider the original problem on this thread where the ORM is addressing multiple "entities" to begin with.I did, that's why I said "This, however, looks like it's a projection/join situation though". That doesn't mean there isn't an existing POCO to handle the projection (can't tell, we don't have the source code), but even if there is it'd have to be tied into the EF context if it's not already (and I'm guessing it's not), so yeah, some code change will be required. Sort-of depends on the pattern OP is using I guess, seems a shame to have a one-off that may be coded differently than everything else but you gotta do that sometimes.
Agreed, I guess it was my backwards way of saying that, from what's available on this thread, it doesn't look like the OP has an entity already in existence to make use of either a view or a procedure without a code change.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply