Блог

Выполнение одного из запросов (приведенного ниже) занимает более 90 секунд. Возвращает ~ 500 строк из довольно большой таблицы LogMessage. Если ESCAPE N'~' удалить из запроса, он выполняется в течение нескольких секунд. Аналогично, если TOP (1000) удален, он выполняется в течение нескольких секунд. План запроса показан Key Lookup (Clustered) PK_LogMessage, Index Scan (NonClustered) IX_LogMessage and Nested Loops (Inner Join) в первом случае. Когда предложения ESCAPE N'~' or TOP (1000) удаляются, план запроса изменяется и отображается Clustered Index Scan (Clustered) PK_LogMessage . Пока мы рассматриваем возможность добавления дополнительных индексов (возможно, для ApplicationName), мы хотели бы понять, что происходит в данный момент.

Запрос генерируется из Entity Framework на случай, если вам интересно, почему он записывается таким образом. Также фактический запрос более сложный, но это самая короткая из возможных версий, которая демонстрирует то же поведение.

Запрос:

 SELECT TOP (1000) 
    [Project1].[MessageID] AS [MessageID], 
    [Project1].[TimeGenerated] AS [TimeGenerated], 
    [Project1].[SystemName] AS [SystemName], 
    [Project1].[ApplicationName] AS [ApplicationName]
FROM
    (
        SELECT
            [Project1].[MessageID] AS [MessageID],
            [Project1].[TimeGenerated] AS [TimeGenerated],
            [Project1].[SystemName] AS [SystemName],
            [Project1].[ApplicationName] AS [ApplicationName]
        FROM
        (
            SELECT 
                [Extent1].[MessageID] AS [MessageID], 
                [Extent1].[TimeGenerated] AS [TimeGenerated], 
                [Extent1].[SystemName] AS [SystemName], 
                [Extent1].[ApplicationName] AS [ApplicationName]
            FROM
                [dbo].[LogMessage] AS [Extent1]
            INNER JOIN
                [dbo].[LogMessageCategory] AS [Extent2]
            ON
                [Extent1].[CategoryID] = [Extent2].[CategoryID]
            WHERE
                ([Extent1].[ApplicationName] LIKE N'%tier%' ESCAPE N'~')
        )  AS [Project1]
    )  AS [Project1]
ORDER BY
    [Project1].[TimeGenerated] DESC
  

Table LogMessage:

 CREATE TABLE [dbo].[LogMessage](
    [MessageID] [int] IDENTITY(1000001,1) NOT NULL,
    [TimeGenerated] [datetime] NOT NULL,
    [SystemName] [nvarchar](256) NOT NULL,
    [ApplicationName] [nvarchar](512) NOT NULL,
        [CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_LogMessage] PRIMARY KEY CLUSTERED 
(
    [MessageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[LogMessage]  WITH CHECK ADD CONSTRAINT [FK_LogMessage_LogMessageCategory] FOREIGN KEY([CategoryID])
    REFERENCES [dbo].[LogMessageCategory] ([CategoryID])

ALTER TABLE [dbo].[LogMessage] CHECK CONSTRAINT [FK_LogMessage_LogMessageCategory]

ALTER TABLE [dbo].[LogMessage] ADD  DEFAULT ((100)) FOR [CategoryID]

CREATE NONCLUSTERED INDEX [IX_LogMessage] ON [dbo].[LogMessage] 
(
    [TimeGenerated] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
  

Таблица LogMessageCategory:

 CREATE TABLE [dbo].[LogMessageCategory](
    [CategoryID] [int] NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Description] [nvarchar](256) NULL,
 CONSTRAINT [PK_LogMessageCategory] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
  

План запроса 1 (занимает более 90 секунд)

План запроса 2 (занимает ~ 3 секунды)

Для меня это выглядит как простая проблема с отслеживанием параметров.

По вашему желанию TOP 1000 ordered by TimeGenerated SQL Server может либо отсканировать индекс на TimeGenerated и выполнить поиск в базовой таблице, чтобы вычислить предикат на ApplicationName и остановиться, когда найдена строка 1000, либо выполнить сканирование кластеризованного индекса, найти все строки, соответствующие ApplicationName предикату, а затем выполнить TOP N что-то вроде этого.

SQL Server ведет статистику по строковым столбцам. Первый план, скорее всего, будет выбран, если он считает, что многие строки в конечном итоге будут соответствовать ApplicationName предикату, однако этот план на самом деле не подходит для повторного использования в качестве параметризованного запроса, поскольку он может быть катастрофически неэффективным в случае совпадения нескольких строк. Если совпадений меньше 1000, определенно потребуется выполнить столько ключевых запросов, сколько строк в таблице.

В результате тестирования этого конца я не смог найти ни одной ситуации, когда добавление или удаление избыточного ESCAPE изменяло оценки мощности SQL Server. Конечно, изменение текста параметризованного запроса означает, что исходный план не может быть использован, однако, и ему необходимо скомпилировать другой, который, вероятно, будет более подходящим для конкретного значения, рассматриваемого в данный момент.

Зачем все эти вложенные запросы?? Приведенный ниже код выполняет ту же работу

         SELECT TOP(1000)
            [Extent1].[MessageID] AS [MessageID], 
            [Extent1].[TimeGenerated] AS [TimeGenerated], 
            [Extent1].[SystemName] AS [SystemName], 
            [Extent1].[ApplicationName] AS [ApplicationName]
        FROM
            [dbo].[LogMessage] AS [Extent1]
        INNER JOIN
            [dbo].[LogMessageCategory] AS [Extent2]
        ON
            [Extent1].[CategoryID] = [Extent2].[CategoryID]
        WHERE
            ([Extent1].[ApplicationName] LIKE N'%tier%' ESCAPE N'~')
        ORDER BY [Extent1].[TimeGenerated] DESC
  

Также я согласен, что ESCAPE N ‘~’ может быть исключен, поскольку я не могу найти причин для его использования.

Для начала я бы упростил запрос, как указано @niktrs. Несмотря на то, что план выполнения, похоже, игнорирует подзапросы, это делает его более удобным для пользователя и, следовательно, облегчает манипулирование и понимание.

Затем у вас есть ВНУТРЕННЕЕ соединение, которое, как мне кажется, может исчезнуть. Существует ли «реальная» необходимость во внутреннем соединении LogMessage с LogMessageCategory? Вы можете выполнить быструю проверку, используя следующее..

 SELECT LM.CategoryID AS FromLogMessage, LMC.CategoryID AS FromLogMessageCategory
FROM dbo.LogMessage AS LM
     FULL OUTER JOIN dbo.LogMessageCategory AS LMC ON LMC.CategoryID = LM.CategoryID
WHERE LM.CategoryID IS NULL OR LMC.CategoryID IS NULL
  

Как это выполняется, если вы это сделаете?

 Select * 
FROM
 (your whole scary framework query with the escape N) a
LIMIT 1000 
(or the mssql alternative if mssql does not support the correct syntax -- )
  

Потому что, если это сработает .. есть шанс, что вы могли бы продолжать использовать эту платформу и получить приличную производительность из действительно плохого sql (например … это означало бы, что вы создаете полный rs, а затем выбираете из него только 1k … ).