Оптимизация генерации плана запроса SQL Server

У меня довольно сложное представление, в определении которого содержится около 100 подзапросов.

Простое заявление вроде:

SELECT * FROM MyView

на создание плана и выполнение запроса потребуется 2 секунды. Последующие выборки при кэшировании плана занимают менее 1 мс.

Это было бы нормально, если бы у меня было всего несколько запросов - приемлемо только однократное снижение производительности. Проблема в том, что наша ORM генерирует запросы на подкачку с параметрами с использованием CTE. Изменение значений параметров (страниц) вызывает перерасчет плана запроса - в этом случае, к сожалению, это занимает около 4 секунд!

Давайте добавим фильтрацию, сортировку и вы поймете, что происходит ..

Что я могу сделать, чтобы сократить время создания плана запроса или сократить его или оптимизировать каким-либо другим способом?

@MartinSmith "SQL Server не создает план для каждого значения параметра, кроме случаев, когда текст каким-то образом изменился"

У меня есть такой запрос (я поставил здесь звездочки вместо списка из более чем 120 полей):

DECLARE @low int = 20;
DECLARE @high int = 300;

WITH __actualSet
AS (
SELECT *
    ,ROW_NUMBER() OVER (
        ORDER BY CURRENT_TIMESTAMP
        ) AS __rowcnt
FROM (
    SELECT TOP 5000 *
    FROM [dbo].[Project] [LPA_L1]
    ORDER BY [LPA_L1].[CreatedOn] ASC
    ) AS _tmpSet
)
SELECT *
FROM __actualSet
WHERE [__rowcnt] > @low
AND [__rowcnt] <= @high
ORDER BY [__rowcnt] ASC

Первый раз запускаю этот запрос ~ 4с. Второй раз ~ 1мс. Когда меняю значения параметров - снова 4с. Может я тут что-то неверно истолковываю?


sql sql-server sql-server-2008-r2 query-performance
person kubal5003    schedule 10.06.2013    source источник
comment
Переписать представление, чтобы не использовать 100 подзапросов? Хотя, если изменяется только @parameter значения, это в любом случае не должно вызывать перекомпиляцию.   -  person Martin Smith    schedule 10.06.2013
comment
Я не могу переписать представление, чтобы не использовать 100 подзапросов, однако я попробую использовать проекцию, чтобы сократить количество столбцов, запрашиваемых каждый раз. Это работает в основном так же, но все же генерировать план выполнения для каждого значения параметра - это безумие.   -  person kubal5003    schedule 10.06.2013
comment
SQL Server не создает план для каждого значения параметра, кроме случаев, когда текст каким-то образом изменился.   -  person Martin Smith    schedule 10.06.2013
comment
Когда стоимость создания плана становится проблемой (вместо стоимости выполнения), у вас ДЕЙСТВИТЕЛЬНО большая проблема.   -  person    schedule 10.06.2013
comment
Я обновил вопрос с более подробной информацией.   -  person kubal5003    schedule 10.06.2013
comment
Действительно ли ваша ORM генерирует такие запросы, как опубликованный вами код? При этом используются переменные, а не параметры. Если фактический текст, отправленный на сервер, включает текст DECLARE @low int = 20;DECLARE @high int = 300;, за которым следует непосредственно SQL, тогда да, это приведет к компиляции нового плана. ORDER BY CURRENT_TIMESTAMP не гарантируется, что сделает что-либо детерминированное BTW.   -  person Martin Smith    schedule 10.06.2013
comment
Спасибо за ответ! Я считаю, что моя ORM генерирует параметры, но я тестировал это на переменных в SSMS. Я не знал, что это важно. Более того, я не заметил этого ORDER BY CURRENT_TIMESTAMP фрагмента - LOL!   -  person kubal5003    schedule 10.06.2013

Ответы (1)


arrow_upward
1
arrow_downward

Чтобы протестировать в SSMS и повторно использовать один и тот же план для разных значений, вам необходимо параметризовать запрос и выполнить его с помощью sp_executesql

DECLARE @low int = 20;
DECLARE @high int = 300;

EXEC sp_executesql N'
WITH __actualSet
AS (
SELECT *
    ,ROW_NUMBER() OVER (
        ORDER BY CURRENT_TIMESTAMP
        ) AS __rowcnt
FROM (
    SELECT TOP 5000 *
    FROM [dbo].[Project] [LPA_L1]
    ORDER BY [LPA_L1].[CreatedOn] ASC
    ) AS _tmpSet
)
SELECT *
FROM __actualSet
WHERE [__rowcnt] > @low
AND [__rowcnt] <= @high
ORDER BY [__rowcnt] ASC

', N'@low INT, @high INT', @low = @low, @high = @high
person Martin Smith    schedule 10.06.2013
comment
Спасибо за это. Это работает как шарм! Менее 1 мсек для любых значений параметров! - person kubal5003; 10.06.2013