Обнаружение параметров SQL возможно, что перекомпиляция не помогает, но локальные переменные делают

Я использую sp_executesql пройти сложный выбор с несколькими параметрами. Делать это намного медленнее, чем извлекать из хранимой процедуры и объявлять переменные.

Я видел много вопросов о сниффинге параметров SQL, и мой сценарий звучит так. Однако даже после звонка DBCC FREEPROCCACHE или внесение поправок в внешний Select с помощью Option (Recompile) он по-прежнему использует другой и неэффективный план выполнения по сравнению с написанием того же запроса вне хранимой процедуры.

Однако все еще используется хранимая процедура, но при настройке копий параметров в качестве локальных переменных будет использоваться эффективный план выполнения.

Этот сценарий исключает использование параметра SQL в качестве причины? Потому что я перекомпилирую запрос, конечно, нет никакого ранее существующего плана выполнения, который он использует. Если да, то какие могут быть другие причины такого поведения?

Просто чтобы дать вам представление о SQL-запросе, вы можете увидеть его ниже (Messy, сгенерированный через Entity Framework). Это быстрый запрос, но при вводе в sp_executesql proc с переменной, извлеченной и вставленной в параметры, генерирует неэффективный план выполнения

 DECLARE    @p__linq__0 INT = 2032
,@p__linq__1 UNIQUEIDENTIFIER = '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE'
,@p__linq__2 uniqueidentifier= '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE',
@p__linq__3 uniqueidentifier= '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE',
@p__linq__4 uniqueidentifier= '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE',
@p__linq__5 INT = 6771

SELECT 
[Limit1].[UserIdValue] AS [UserIdValue]
FROM   (SELECT [Extent1].[Id] AS [Id]
    FROM [dbo].[Request] AS [Extent1]
    WHERE ([Extent1].[InstanceId] = @p__linq__0) AND ([Extent1].[DeletedDate] IS NULL) AND (( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent2].[TeamId] AS [TeamId], 
            [Extent2].[HasUpdateAccess] AS [HasUpdateAccess]
            FROM [dbo].[RequestTypeTeam] AS [Extent2]
            WHERE [Extent1].[RequestTypeId] = [Extent2].[RequestTypeId]
        )  AS [Project1]
        WHERE ([Project1].[HasUpdateAccess] = 1) AND ( EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[UserTeam] AS [Extent3]
            WHERE ([Project1].[TeamId] = [Extent3].[TeamId]) AND ([Extent3].[UserId] = @p__linq__1)
        ))
    )) OR (([Extent1].[InsertUserId] = @p__linq__2) AND ( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent4].[TeamId] AS [TeamId], 
            [Extent4].[HasCreatorAccess] AS [HasCreatorAccess]
            FROM [dbo].[RequestTypeTeam] AS [Extent4]
            WHERE [Extent1].[RequestTypeId] = [Extent4].[RequestTypeId]
        )  AS [Project4]
        WHERE ([Project4].[HasCreatorAccess] = 1) AND ( EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[UserTeam] AS [Extent5]
            WHERE ([Project4].[TeamId] = [Extent5].[TeamId]) AND ([Extent5].[UserId] = @p__linq__3)
        ))
    ))) OR ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[RequestTeam] AS [Extent6]
        WHERE ([Extent1].[Id] = [Extent6].[RequestId]) AND ([Extent6].[TeamId] IN (3147, 3165))
    )) OR ( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[RequestControlData] AS [Extent8]
                WHERE ([Project8].[Id] = [Extent8].[ControlId]) AND ([Extent8].[RequestId] = [Extent1].[Id]) AND ([Extent8].[UserIdValue] = @p__linq__4)) AS [C1]
            FROM ( SELECT 
                [Extent7].[Id] AS [Id]
                FROM [dbo].[Control] AS [Extent7]
                WHERE ([Extent7].[RequestTypeId] IS NOT NULL) AND ([Extent1].[RequestTypeId] = [Extent7].[RequestTypeId]) AND ([Extent7].[DeletedDate] IS NULL) AND ([Extent7].[IsAuthorisation] = 1)
            )  AS [Project8]
        )  AS [Project9]
        WHERE [Project9].[C1] > 0
    ))) AND ( NOT ([Extent1].[StatusId] IN (1071))) AND ( NOT ([Extent1].[RequestTypeId] IN (1215)))) AS [Filter11] 
OUTER APPLY  (SELECT TOP (1) 
    [Extent9].[ControlId] AS [ControlId], 
    [Extent9].[UserIdValue] AS [UserIdValue], 
    [Extent10].[Id] AS [Id], 
    [Extent10].[SharedControlId] AS [SharedControlId]
    FROM  [dbo].[RequestControlData] AS [Extent9]
    INNER JOIN [dbo].[Control] AS [Extent10] ON [Extent9].[ControlId] = [Extent10].[Id]
    WHERE ([Filter11].[Id] = [Extent9].[RequestId]) AND (([Extent10].[SharedControlId] = @p__linq__5) OR (([Extent10].[SharedControlId] IS NULL) AND (@p__linq__5 IS NULL
    ))) 
    )
     AS [Limit1]
Источник

2 ответа

Решение

Снова столкнулся с той же проблемой и хотел опубликовать ответ, который работал для меня.

Я побежал UPDATE STATISTICS [TableName] WITH FULLSCAN в различных таблицах, относящихся к моему запросу, после этого он работал так же быстро, как и при использовании локальных переменных.

Интересно отметить, что без WITH FULLSCAN это все равно будет медленно. Кроме того, после обновления статистики был быстрый первый запуск, а затем медленный следующий запуск, поэтому мне пришлось постоянно обновлять статистику, чтобы заставить ее работать быстро, затем я попытался обновить статистику, выполнить запрос (был быстрый запуск), а затем призвание DBCC FREEPROCCACHEи это, казалось, это исправить, так что это было быстро каждый раз, когда я запускал его

Я предполагаю, что это означает, что проблема была не в прослушивании параметров.

Источник

Я думаю, что ваша проблема с дизайном, а не с данными, несколько подзапросов дороже, особенно с оператором ИЛИ

Я бы лучше использовал несколько табличных переменных и оставил внешние объединения

Источник
Другие вопросы по тегам