0

I am trying to optimize performance for a server and this particular query was causing huge reads from database, in turn causing timeout for queries. This query is generated from EF6 in Asp.Net MVC.

Here is the problematic query:

exec sp_executesql N'SELECT 
[Project1].[C1] AS [C1], 
[Project1].[Date] AS [Date], 
[Project1].[AssetID] AS [AssetID], 
[Project1].[EventData] AS [EventData]
FROM ( SELECT 
    [Extent1].[AssetID] AS [AssetID], 
    [Extent1].[Date] AS [Date], 
    [Extent1].[EventData] AS [EventData], 
    1 AS [C1]
    FROM [dbo].[Alarm] AS [Extent1]
    WHERE ([Extent1].[AssetID] IN (cast(''c6e3142e-5b1f-4a91-90d2-03a504e86ece'' as uniqueidentifier), cast(''4de25e8a-7401-49ae-bd6d-0861d67f0d2f'' as uniqueidentifier), cast(''455e3a5f-1091-4784-9964-0a1a54eaa644'' as uniqueidentifier), cast(''04b46c21-c44f-4b67-b64b-12f2764c0448'' as uniqueidentifier), cast(''a350992b-8548-4bf1-bd22-131c114a5343'' as uniqueidentifier), cast(''98ec1f36-cc54-45d2-a0e3-22aa1b669373'' as uniqueidentifier), cast(''27abcf37-2093-43d5-ae62-2e7b10fe4692'' as uniqueidentifier), cast(''c9f43598-2b9c-47b0-9230-37440e6aea54'' as uniqueidentifier), cast(''c5964caa-5c73-4c0e-bb80-4c1dc7e11039'' as uniqueidentifier), cast(''6ac30678-3876-43c9-b708-61ef19b5ea17'' as uniqueidentifier), cast(''e69d870a-87de-4e3d-b4fc-62c962489a7b'' as uniqueidentifier), cast(''a7c2f407-c605-4491-85fe-66c16fc15586'' as uniqueidentifier), cast(''a38f452e-ee3a-4be7-94ad-99c1474a417f'' as uniqueidentifier), cast(''b0f65616-d5d1-4af9-bffd-9c4b2b7f52e7'' as uniqueidentifier), cast(''3940fed6-9c40-4db6-bdc2-9dc5ef7b49ea'' as uniqueidentifier), cast(''e09f7618-c7d7-414d-b5d7-9ec22b3e9b64'' as uniqueidentifier), cast(''62c91349-d33f-42ed-b16d-a63424acca4a'' as uniqueidentifier), cast(''46812e72-45af-426e-9d72-aafdbcc9c4a7'' as uniqueidentifier), cast(''2a1d2b2a-0471-4f57-adc5-b42a03eb5e01'' as uniqueidentifier), cast(''2797d370-b237-4d2c-bede-b7af67f2b0f4'' as uniqueidentifier), cast(''0c50bb44-133e-4434-b403-c172873564e9'' as uniqueidentifier), cast(''dda75f7a-d366-472e-81b5-c4f3119dd715'' as uniqueidentifier), cast(''cc469264-a706-49c6-961b-d6520437e796'' as uniqueidentifier), cast(''ee2d7ea0-1f94-4cc3-9f64-d8d56374cae5'' as uniqueidentifier), cast(''f7dc6b77-3735-479d-b420-e145d4e3d66f'' as uniqueidentifier), cast(''8a7dbe93-ed1a-49f3-a3d0-e19c6dd4e4ef'' as uniqueidentifier), cast(''b0524a60-f980-4b82-a799-e788a9a4d04b'' as uniqueidentifier), cast(''ceaf8a0b-f410-4f88-9062-e804b76b6e78'' as uniqueidentifier))) AND ([Extent1].[EventCode] = @p__linq__0) AND ([Extent1].[Date] >= @p__linq__1) AND ([Extent1].[Date] <= @p__linq__2) AND (([Extent1].[SiteID] = @p__linq__3) OR (([Extent1].[SiteID] IS NULL) AND (@p__linq__3 IS NULL)))
)  AS [Project1]
ORDER BY [Project1].[Date] DESC',N'@p__linq__0 int,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 uniqueidentifier',@p__linq__0=1799,@p__linq__1='2018-04-22 10:00:00',@p__linq__2='2018-04-23 10:00:00',@p__linq__3='B02A51FE-2248-E611-A64E-782BCB72ACED' 
go

So what i did i remove the parameters from query and run it as a single unit like this.

 select * from (  SELECT  1 as c1,  [Extent1].[Date] AS [Date], 
    [Extent1].[AssetID] AS [AssetID],         
    [Extent1].[EventData] AS [EventData]  
    FROM [dbo].[Alarm] AS [Extent1]
    WHERE ([Extent1].[AssetID] IN (cast('c6e3142e-5b1f-4a91-90d2-03a504e86ece' as uniqueidentifier), cast('4de25e8a-7401-49ae-bd6d-0861d67f0d2f' as uniqueidentifier), cast('455e3a5f-1091-4784-9964-0a1a54eaa644' as uniqueidentifier), cast('04b46c21-c44f-4b67-b64b-12f2764c0448' as uniqueidentifier), cast('a350992b-8548-4bf1-bd22-131c114a5343' as uniqueidentifier), cast('98ec1f36-cc54-45d2-a0e3-22aa1b669373' as uniqueidentifier), cast('27abcf37-2093-43d5-ae62-2e7b10fe4692' as uniqueidentifier), cast('c9f43598-2b9c-47b0-9230-37440e6aea54' as uniqueidentifier), cast('c5964caa-5c73-4c0e-bb80-4c1dc7e11039' as uniqueidentifier), cast('6ac30678-3876-43c9-b708-61ef19b5ea17' as uniqueidentifier), cast('e69d870a-87de-4e3d-b4fc-62c962489a7b' as uniqueidentifier), cast('a7c2f407-c605-4491-85fe-66c16fc15586' as uniqueidentifier), cast('a38f452e-ee3a-4be7-94ad-99c1474a417f' as uniqueidentifier), cast('b0f65616-d5d1-4af9-bffd-9c4b2b7f52e7' as uniqueidentifier), cast('3940fed6-9c40-4db6-bdc2-9dc5ef7b49ea' as uniqueidentifier), cast('e09f7618-c7d7-414d-b5d7-9ec22b3e9b64' as uniqueidentifier), cast('62c91349-d33f-42ed-b16d-a63424acca4a' as uniqueidentifier), cast('46812e72-45af-426e-9d72-aafdbcc9c4a7' as uniqueidentifier), cast('2a1d2b2a-0471-4f57-adc5-b42a03eb5e01' as uniqueidentifier), cast('2797d370-b237-4d2c-bede-b7af67f2b0f4' as uniqueidentifier), cast('0c50bb44-133e-4434-b403-c172873564e9' as uniqueidentifier), cast('dda75f7a-d366-472e-81b5-c4f3119dd715' as uniqueidentifier), cast('cc469264-a706-49c6-961b-d6520437e796' as uniqueidentifier), cast('ee2d7ea0-1f94-4cc3-9f64-d8d56374cae5' as uniqueidentifier), cast('f7dc6b77-3735-479d-b420-e145d4e3d66f' as uniqueidentifier), cast('8a7dbe93-ed1a-49f3-a3d0-e19c6dd4e4ef' as uniqueidentifier), cast('b0524a60-f980-4b82-a799-e788a9a4d04b' as uniqueidentifier), cast('ceaf8a0b-f410-4f88-9062-e804b76b6e78' as uniqueidentifier))) AND ([Extent1].[EventCode] = 1799) AND ([Extent1].[Date] >= '2018-04-22 10:00:00') AND ([Extent1].[Date] <= '2018-04-23 10:00:00') AND (([Extent1].[SiteID] = 'B02A51FE-2248-E611-A64E-782BCB72ACED') OR (([Extent1].[SiteID] IS NULL) AND ('B02A51FE-2248-E611-A64E-782BCB72ACED' IS NULL))) 
) as proeject1  order by proeject1.Date desc

These are the respective IO costs for queries.

(3721 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Alarm'. Scan count 5, logical reads 69032, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Bad Plan

Better plan

(3721 row(s) affected)
Table 'Alarm'. Scan count 28, logical reads 564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Good Plan

Actual execution plan with good performance

Actual execution plan with bad performance

I have tried:

  1. Option recompile
  2. DBCC FREEPROCCACHE
  3. sp_updatestats
  4. Rebuilding corresponding index

But query always picks this plan causing a huge load on server disk.

Thanks

migrated from stackoverflow.com May 20 '18 at 8:42

This question came from our site for professional and enthusiast programmers.

  • Why do you need to use sub-queries? – paparazzo Apr 23 '18 at 12:23
  • @paparazzo it was generated by entity framework – Rohit Apr 23 '18 at 12:36
  • and have you tried adding the proposed index? – vikjon0 Apr 23 '18 at 12:37
  • 1
    See stackoverflow.com/questions/31615760/…, looks similar – Ivan Stoev Apr 23 '18 at 13:04
  • 1
    @IvanStoev i tried the query with parameter by changing datetime2 to datetime. and hopefully it has worked in ssms. On the way to implement QueryInterceptor :) – Rohit Apr 23 '18 at 13:25
0

Maybe I am missing something but why do you need the sub-query?
OP said from entity framework and even the better query had a sub query.
I wanted to break down the query and it was more than I bargained for. Worth a try:

exec sp_executesql N'
SELECT [Extent1].[AssetID] AS [AssetID], 
       [Extent1].[Date] AS [Date], 
       [Extent1].[EventData] AS [EventData], 
       1 AS [C1]
  FROM [dbo].[Alarm] AS [Extent1]
 WHERE [Extent1].[AssetID] IN (  cast(''c6e3142e-5b1f-4a91-90d2-03a504e86ece'' as uniqueidentifier)
                               , cast(''4de25e8a-7401-49ae-bd6d-0861d67f0d2f'' as uniqueidentifier)
                               , cast(''455e3a5f-1091-4784-9964-0a1a54eaa644'' as uniqueidentifier)
                               , cast(''04b46c21-c44f-4b67-b64b-12f2764c0448'' as uniqueidentifier)
                               , cast(''a350992b-8548-4bf1-bd22-131c114a5343'' as uniqueidentifier)
                               , cast(''98ec1f36-cc54-45d2-a0e3-22aa1b669373'' as uniqueidentifier)
                               , cast(''27abcf37-2093-43d5-ae62-2e7b10fe4692'' as uniqueidentifier)
                               , cast(''c9f43598-2b9c-47b0-9230-37440e6aea54'' as uniqueidentifier)
                               , cast(''c5964caa-5c73-4c0e-bb80-4c1dc7e11039'' as uniqueidentifier)
                               , cast(''6ac30678-3876-43c9-b708-61ef19b5ea17'' as uniqueidentifier)
                               , cast(''e69d870a-87de-4e3d-b4fc-62c962489a7b'' as uniqueidentifier)
                               , cast(''a7c2f407-c605-4491-85fe-66c16fc15586'' as uniqueidentifier)
                               , cast(''a38f452e-ee3a-4be7-94ad-99c1474a417f'' as uniqueidentifier)
                               , cast(''b0f65616-d5d1-4af9-bffd-9c4b2b7f52e7'' as uniqueidentifier)
                               , cast(''3940fed6-9c40-4db6-bdc2-9dc5ef7b49ea'' as uniqueidentifier)
                               , cast(''e09f7618-c7d7-414d-b5d7-9ec22b3e9b64'' as uniqueidentifier)
                               , cast(''62c91349-d33f-42ed-b16d-a63424acca4a'' as uniqueidentifier)
                               , cast(''46812e72-45af-426e-9d72-aafdbcc9c4a7'' as uniqueidentifier)
                               , cast(''2a1d2b2a-0471-4f57-adc5-b42a03eb5e01'' as uniqueidentifier)
                               , cast(''2797d370-b237-4d2c-bede-b7af67f2b0f4'' as uniqueidentifier)
                               , cast(''0c50bb44-133e-4434-b403-c172873564e9'' as uniqueidentifier)
                               , cast(''dda75f7a-d366-472e-81b5-c4f3119dd715'' as uniqueidentifier)
                               , cast(''cc469264-a706-49c6-961b-d6520437e796'' as uniqueidentifier)
                               , cast(''ee2d7ea0-1f94-4cc3-9f64-d8d56374cae5'' as uniqueidentifier)
                               , cast(''f7dc6b77-3735-479d-b420-e145d4e3d66f'' as uniqueidentifier)
                               , cast(''8a7dbe93-ed1a-49f3-a3d0-e19c6dd4e4ef'' as uniqueidentifier)
                               , cast(''b0524a60-f980-4b82-a799-e788a9a4d04b'' as uniqueidentifier)
                               , cast(''ceaf8a0b-f410-4f88-9062-e804b76b6e78'' as uniqueidentifier)
                              )
   AND [Extent1].[EventCode] = @p__linq__0 
   AND [Extent1].[Date] >= @p__linq__1 
   AND [Extent1].[Date] <= @p__linq__2 
   AND (       [Extent1].[SiteID] = @p__linq__3 
         OR  ( [Extent1].[SiteID] IS NULL AND @p__linq__3 IS NULL )
       )
 ORDER BY [Date] DESC', N'@p__linq__0 int, @p__linq__1 datetime2(7), @p__linq__2 datetime2(7), 
          @p__linq__3 uniqueidentifier', @p__linq__0=1799, @p__linq__1='2018-04-22 10:00:00', 
          @p__linq__2='2018-04-23 10:00:00',@p__linq__3='B02A51FE-2248-E611-A64E-782BCB72ACED' 
go

try adding the recommended index
create index on EventCode, AssetID, SiteID, [Date]

  • this query was generated by entity framework – Rohit Apr 23 '18 at 12:51
  • please check query with better performance, it too has sub-query, but it work very well – Rohit Apr 23 '18 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.