В этой статье рассматриваются способы выполнения Axapta-запросов SQL сервером, их преимущества и недостатки, влияние на производительность, а также способы повышения производительности выполнения запросов.

Вадим Гончаренко, vgoncharenko@rabota-na-rezultat.ru

LiteralsVsPlaceholders.xpo (26 Кб, данные с минимальной настройкой, для загрузки требуется регистрация на форуме у Mazzy)

Literals vs placeholders

Наверное, все, кому приходилось программировать на X ++, так или иначе сталкивались с подобной ситуацией: есть потребность в увеличении производительности участка кода, представляющего из себя длинный цикл, внутри которого выполняются запросы с изменяющимися параметрами в условии WHERE. Примером может служить метод \Classes\RTax25RegCalc_InventReceipt\calc

 

while select inventTable
{
     itemId = inventTable.ItemId;..
     // Search used inventory dimensions
     while select forceSelectOrder InventDimId from inventSum
         join inventDim where
             inventSum.ItemId == itemId &&
             inventDim.InventDimId == inventSum.InventDimId
     {..
     }
 
     progress.incCount();
 }

В данном случае цикл организован по таблице InventTable, поле InventId из которой используется в объединении таблиц InventSum и InventDim.

Лирическое отступление: статья не посвящена оптимизации именно этого метода, он используется здесь лишь в качестве иллюстрации.

Итак, вполне возможно, Вы знаете о существовании нескольких "узких мест" в приложении с подобным кодом и хотели бы увеличить скорость его выполнения. Допустим, Вы уже провели анализ выполняемых при этом запросов и убедились в том, что все необходимые для оптимального выполнения запроса индексы построены и используются оптимизатором SQL сервера, фрагментация страниц БД – в пределах нормы, данные оптимально распределены по имеющимся физическим дискам. Получается, что иного выхода, кроме наращивания мощности сервера, нет? Это почти так. ПОЧТИ.

Рассмотрим, как SQL сервер выполняет запрос. Поступающий запрос при этом проходит несколько стадий:

Одной из самых ресурсоемких стадий (разумеется, кроме исполнения) является стадия построения плана исполнения. Чтобы понять, насколько она нетривиальна, рассмотрим, какие решения приходится принимать оптимизатору:

Таким образом, есть категория запросов, для которых время исполнения сравнимо (а иногда – значительно меньше) времени построения плана исполнения. Можем ли мы предпринять что-то в этой ситуации? Разумеется, без плана исполнения запрос не может быть выполнен. Однако вполне логично было бы попытаться не строить его заново для каждого нового запроса, отличающегося от предыдущего только аргументами поиска, а использовать его повторно. Сразу оговорюсь: Microsoft SQL Server может кэшировать планы исполнения запросов, но, к сожалению, эта функциональность далеко не всегда работает так, как хотелось бы.

В утилите Profiler отсылаемые Аксаптой на сервер запросы будут выглядеть так:

exec sp_cursoropen @P1 output, N'SELECT A.ID,A.RECID,B.ID,B.RECID,C.ID,C.RECID,D.ID,D.RECID FROM MAINTABLE A,WSLINETABLE
B,WSHEADERTABLE C,TRANSACTIONTABLE D WHERE ((A.DATAAREAID=''dat'') AND ( A.ID=1 )) AND ((B.DATAAREAID=''dat'')
AND (B.ID=A.ID)) AND ((C.DATAAREAID=''dat'') AND (C.ID=B.ID)) AND ((D.DATAAREAID=''dat'') AND (D.ID=C.ID)) OPTION(FAST 100)',
@P2 output, @P3 output, @P4 output 
..
exec sp_cursoropen @P1 output, N'SELECT A.ID,A.RECID,B.ID,B.RECID,C.ID,C.RECID,D.ID,D.RECID FROM MAINTABLE A,WSLINETABLE
B,WSHEADERTABLE C,TRANSACTIONTABLE D WHERE ((A.DATAAREAID=''dat'') AND ( A.ID=2 )) AND ((B.DATAAREAID=''dat'')
AND (B.ID=A.ID)) AND ((C.DATAAREAID=''dat'') AND (C.ID=B.ID)) AND ((D.DATAAREAID=''dat'') AND (D.ID=C.ID)) OPTION(FAST 100)',
@P2 output, @P3 output, @P4 output 
select @P1, @P2, @P3, @P4 
..
exec sp_cursoropen @P1 output, N'SELECT A.ID,A.RECID,B.ID,B.RECID,C.ID,C.RECID,D.ID,D.RECID FROM MAINTABLE A,WSLINETABLE
B,WSHEADERTABLE C,TRANSACTIONTABLE D WHERE ((A.DATAAREAID=''dat'') AND ( A.ID=1000 )) AND ((B.DATAAREAID=''dat'')
AND (B.ID=A.ID)) AND ((C.DATAAREAID=''dat'') AND (C.ID=B.ID)) AND ((D.DATAAREAID=''dat'') AND (D.ID=C.ID)) OPTION(FAST 100)',
@P2 output, @P3 output, @P4 output 
select @P1, @P2, @P3, @P4 

Цветом и жирным шрифтом в приведенном листинге выше выделяется переменная часть запроса в условии WHERE

Описанный выше способ исполнения запросов называется Direct execution (прямое, или непосредственное исполнение). При нем поступающий запрос проходит все описанные выше стадии. Альтернативным способом является так называемое Prepared execution. При этом запросы будут выполняться следующим образом:

SELECT * FROM INVENTTABLE WHERE ITEMID = ?
exec sp_cursorprepexec @P1 output, @P2 output, N'@P1 varchar(1000),@P2 int,@P3 varchar(1000),@P4 varchar(1000),@P5 varchar(1000)',
N'SELECT A.ID,A.RECID,B.ID,B.RECID,C.ID,C.RECID,D.ID,D.RECID FROM MAINTABLE A,WSLINETABLE B,WSHEADERTABLE C,TRANSACTIONTABLE
D WHERE ((A.DATAAREAID=@P1) AND (A.ID=@P2)) AND ((B.DATAAREAID=@P3) AND (B.ID=A.ID)) AND ((C.DATAAREAID=@P4) AND (C.ID=B.ID))
AND ((D.DATAAREAID=@P5) AND (D.ID=C.ID)) OPTION(FAST 100)', @P3 output, @P4 output, @P5 output, 'dat', 1 ,
'dat', 'dat', 'dat'
exec sp_cursorexecute 4, @P1 output, @P2 output, @P3 output, @P4 output, 'dat', 2 , 'dat', 'dat', 'dat' 
..
exec sp_cursorexecute 4, @P1 output, @P2 output, @P3 output, @P4 output, 'dat', 1000 , 'dat', 'dat',
'dat' 

Как уже упоминалось выше, в отдельных случаях (часто выполняемые запросы с меняющимися аргументами поиска в условии WHERE , не требующими массы операций ввода-вывода) Prepared execution может давать значительный выигрыш в производительности. Axapta может использовать одновременно оба метода, при этом решение о том, какой именно из них будет использован, принимает ядро системы. Критерии, которые используется при принятии решения, просты: для запросов, в которых используется более одной «большой» таблицы, будет использоваться Direct execution , в остальных случаях будет использовано Prepared execution. «Большими» система считает таблицы со свойством TableGroup, равным Miscellaneous, Main, Transaction, Worksheet Header или Worksheet Line.

В терминах Axapta метод Direct execution называется методом с использованием literals, а Prepared execution – методом с использованием placeholders.

Стоит отметить, что описанное выше поведение системы – это поведение по умолчанию. Его можно переопределить следующим образом:

Тестирование производилось следующим образом: организовывался цикл, в котором 1000 раз выполнялся единственный запрос, представляющий собой объединение ( JOIN ) нескольких таблиц с использованием переменной цикла в условии WHERE. Код и определения таблиц, используемых в эксперименте можно найти в приложенном к статье проекте.

Тестирование осуществлялось в двухуровневой конфигурации, способ выполнения изменялся настройкой "Literals from complex joins" в конфигурационной утилите Axapta и использованием хинтов forceliterals и forceplaceholders в запросе. По результатам тестирования время выполнения с использованием Direct execution оказывалось в 5-10 раз больше, чем при использовании Prepared execution. Причем при увеличении сложности запросов (добавлении условий WHERE и увеличении числа связываемых таблиц) накладные расходы на их разбор и оптимизацию для Direct execution будут расти и разрыв между двумя методами исполнения будет увеличиваться.

В случае с Direct execution наблюдаются следующие эффекты: при выполнении теста резко возрастает количество кэшируемых сервером планов выполнения. Фактически наблюдалось кэширование 1000 одинаковых планов, отличающихся друг от друга лишь аргументом в условии WHERE. Учитывая, что на хранение каждого из них используется одна или более страниц, а размер одной страницы в Microsoft SQL Server составляет 8 килобайт, резко возрастает объем памяти, необходимой для их хранения. Причем память при этом используется крайне неэффективно: каждый план исполнения, соответствующий единственному значению параметра в условии WHERE, будет использован очень редко - только при повторном выполнении теста и в том случае, если он не будет вытеснен из кэша другими объектами. Естественно, на поддержание большого количества объектов в кэше, для добавления и удаления из него элементов тратятся дополнительные ресурсы процессора. Кроме того, так как объем оперативной памяти любого сервера ограничен, выделяемые под кэш планов исполнения страницы отбираются у других процессов SQL Server. В частности, если наблюдать в ходе теста за такими счетчиками производительности, как SQLServer::BufferManager - Database pages (количество страниц памяти, используемой для кэширования данных) и SQLServer::BufferManager - Procedure cache pages , видно, как резко падают показания первого счетчика (Database pages) при увеличении значения второго. Естественно, это затрудняет кэширование данных и сильно увеличивает нагрузку на систему ввода-вывода сервера.

Говоря о режиме Prepared execution, нельзя не упомянуть о потенциальных проблемах, которые могут быть связаны с его использованием. Дело в том, что план исполнения, построенный для одних условий поиска, может оказаться неоптимальным для других. Например, для таблицы с миллионом строк, в которой данные в индексированном поле, принимающем два возможных значения, 0 и 1, распределены неравномерно, например значение поля равно 1 в 95% строк и 0 в 5% строк, оптимальным спсобом может оказаться поиск по индексу для значения 0 и сканирование таблицы для значения 1. Использование неоптимального плана исполнения для запроса, использующего одну или несколько крупных таблиц, может быть чревато серьезным увеличением времени выполнения запроса.

Если обратиться к рекомендациям разработчиков Axapta , то они рекомендуют использовать Prepared execution (placeholders) для часто используемых запросов, а Direct execution (literals) – для относительно редко используемых запросов, связывающих несколько крупных таблиц или таблицы с неравномерным распределением данных.

P.S. В этой статье для тестирования использовался Microsoft SQL Server 2000 и использовалась относящаяся к нему терминология, но ее идеи в равной степени применимы и к серверу Oracle, что подтверждается проведенными тестами для Oracle 9i.

P.P.S. Практика показывает, что в стандартной функциональности хинты forceliterals и forceplaceholders, как правило, уже используются там, где это наиболее необходимо. Интересные (обычно – положительные) результаты дает выключение пунктов "Literals from join queries from forms and reports" и "Literas from complex joins from X++" в конфигурационной утилите. Как правило, при этом серьезно возрастает производительность тех участков кода в собственных модификациях, где должен был, но не использовался хинт forceplaceholders. После их выявления желательно, конечно же, закреплять их в тексте запросов, чтобы не зависеть от того, будут или нет указанные опции включены в Вашей конфигурации в будущем.

Вадим Гончаренко, vgoncharenko@rabota-na-rezultat.ru