0

We have recently started experiencing a slowdown in our asp.net/EF5 application connecting to SQL server 2012. The sql being generated by SQL server is below, and takes 58 seconds to return a single row:

exec sp_executesql N'SELECT TOP (50) 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[Id] AS [Id]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[FullName] AS [FullName], 
    1 AS [C1], 
    [Extent2].[FullNameWithAddr] + CASE WHEN ([Extent1].[NPI] IS NOT NULL) THEN N''; NPI#: '' + [Extent1].[NPI] ELSE N'''' END + CASE WHEN ([Extent1].[StateLIC] IS NOT NULL) THEN N''; License#: '' + [Extent1].[StateLIC] ELSE N'''' END AS [C2]
    FROM  [dbo].[Provider] AS [Extent1]
    INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
    WHERE (1 = [Extent2].[Active]) AND (([Extent1].[NPI] LIKE @p__linq__0 ESCAPE ''~'') OR ([Extent1].[StateLIC] LIKE @p__linq__1 ESCAPE ''~''))
)  AS [Project1]
ORDER BY [Project1].[FullName] ASC',N'@p__linq__0 varchar(8000),@p__linq__1 varchar(8000)',@p__linq__0='1043203862%',@p__linq__1='%1043203862%'

If however, I remove the outer projection query, the new query (below) takes less than 1 second. Please advise any actions I might be able to take to diagnose and fix the issue on the database; I cannot easily control the query being emitted by EF.

exec sp_executesql N' SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[FullName] AS [FullName], 
    1 AS [C1], 
    [Extent2].[FullNameWithAddr] + CASE WHEN ([Extent1].[NPI] IS NOT NULL) THEN N''; NPI#: '' + [Extent1].[NPI] ELSE N'''' END + CASE WHEN ([Extent1].[StateLIC] IS NOT NULL) THEN N''; License#: '' + [Extent1].[StateLIC] ELSE N'''' END AS [C2]
    FROM  [dbo].[Provider] AS [Extent1]
    INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
    WHERE (1 = [Extent2].[Active]) AND (([Extent1].[NPI] LIKE @p__linq__0 ESCAPE ''~'') OR ([Extent1].[StateLIC] LIKE @p__linq__1 ESCAPE ''~''))
',N'@p__linq__0 varchar(8000),@p__linq__1 varchar(8000)',@p__linq__0='1043203862%',@p__linq__1='%1043203862%'

EDIT: I have determined that it is the 'TOP' operator which is causing the slowdown. If I execute the query below, it is still fast, even with the outer projection query, minus the TOP

exec sp_executesql N'SELECT 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[Id] AS [Id]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[FullName] AS [FullName], 
    1 AS [C1], 
    [Extent2].[FullNameWithAddr] + CASE WHEN ([Extent1].[NPI] IS NOT NULL) THEN N''; NPI#: '' + [Extent1].[NPI] ELSE N'''' END + CASE WHEN ([Extent1].[StateLIC] IS NOT NULL) THEN N''; License#: '' + [Extent1].[StateLIC] ELSE N'''' END AS [C2]
    FROM  [dbo].[Provider] AS [Extent1]
    INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
    WHERE (1 = [Extent2].[Active]) AND (([Extent1].[NPI] LIKE @p__linq__0 ESCAPE ''~'') OR ([Extent1].[StateLIC] LIKE @p__linq__1 ESCAPE ''~''))
)  AS [Project1]
ORDER BY [Project1].[FullName] ASC',N'@p__linq__0 varchar(8000),@p__linq__1 varchar(8000)',@p__linq__0='1043203862%',@p__linq__1='%1043203862%'

EDIT 2. I have also determined that if I create a variable for the top count, the query (below) is fast:

declare @p1 int = 50
Select top (@p1) * from 
(
    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent2].[FullName] AS [FullName], 
    1 AS [C1], 
    [Extent2].[FullNameWithAddr] + CASE WHEN ([Extent1].[NPI] IS NOT NULL) THEN N'; NPI#: ' + [Extent1].[NPI] ELSE N'' END + CASE WHEN ([Extent1].[StateLIC] IS NOT NULL) THEN N'; License#: ' + [Extent1].[StateLIC] ELSE N'' END AS [C2]
    FROM  [dbo].[Provider] AS [Extent1]
    INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
    WHERE (1 = [Extent2].[Active]) AND (([Extent1].[NPI] LIKE '1043203862%' ESCAPE '~') OR ([Extent1].[StateLIC] LIKE '%1043203862%' ESCAPE '~'))
) a order by a.fullname
user2202866
  • 91
  • 6
  • 14
  • Try to add index on `[Person].[FullName]` – Hamlet Hakobyan Jul 20 '18 at 22:56
  • Index on Person.FullName already exists: CREATE NONCLUSTERED INDEX [ix_Person_Active_include_ID_FNwAdd_FN] ON [dbo].[Person] ( [Active] ASC ) INCLUDE ( [Id], [FullNameWithAddr], [FullName]) GO – user2202866 Jul 20 '18 at 23:02
  • 1
    It is not index on `FullName` you just included it in index on`Active` – Hamlet Hakobyan Jul 20 '18 at 23:04
  • Sorry, I pasted the wrong index. The index on fullname does exist: CREATE NONCLUSTERED INDEX [IX_Person_FullName_3E7F7] ON [dbo].[Person] ( [FullName] ASC ) INCLUDE ( [Id]) WITH (PAD_INDEX = OFF.... – user2202866 Jul 20 '18 at 23:13
  • 1
    Look at the execution plan to find clues.. And try to create Indexes on all columns using in SQL and Index on only Fullname.. CREATE NONCLUSTERED INDEX [IX_Person_FullName] ON [dbo].[Person] ([FullName]) – CR241 Jul 20 '18 at 23:35
  • How long did it used to take before? It might be parameter sniffing. At the very least, rebuild statistics – Nick.Mc Jul 21 '18 at 05:22
  • 1
    Unrelated tip: If the table has a clustered index, then you don't need the `INCLUDE ([Id])` on the other indexes (I'm assuming that `Id` is the Primary Key). Reference https://stackoverflow.com/a/3842407/ – Richardissimo Jul 21 '18 at 05:45

0 Answers0