По мотивам Rewriting Queries to Improve Performance
Цель: изучить влияние синтаксиса запроса на генерируемые SQL планы запросов
Тестовая среда:
- Windows Server 2008 R2 Enterprise
- MS SQL server 2008 R2
- 1С:Предприятие 8.2.19.90
SQL properties:
Инструменты:
- SQL Server Profiler
- Консоль запросов 1С
Подготовка:
Запускаем SQL Server Profiler, устанавливаем соединение, отмечаем события и колонки как показано на рисунке:
Устанавливаем отбор для нашей базы:
Примеры
Выполняем запрос:
Данный вид запроса сгенерировал следующий план исполнения:
Теперь попробуем упорядочить по полю «Код». В результате получаем следующий план:
Попробуем добавить еще упорядочивание и по полю «Код Улицы В Коде»:
Теперь появились элементы параллелизма.
Далее, если добавить «Различные» и сортировку по убыванию мы получим схожий план выполнения.
Переписываем запрос
Как видно, вариант запроса с отбором "ИЛИ" вместо "В" вызывает тот же план выполнения, разве что работает дольше.
Если добавим упорядочивание по полю «КОД», то получим схожий результат с запросом с "В"
Добавление упорядочивания и по полю «Код Улицы В Коде» вызывает генерацию плана, схожего с запросом “В” при тех же условиях упорядочивания.
Теперь попробуем другой синтаксис:
Теперь мы наблюдаем в корне другой план исполнения, который, правда имеет худшие показатели, по сравнению с самым первым запросом. Если добавим упорядочивание по полю «Код»:
При добавлении упорядочивания по полю «Код Улицы В Коде», получим тот же план выполнения запроса.
Пример запроса с большим количеством таблиц
ВЫБРАТЬ ПЕРВЫЕ 1000 УчетЗатратРегл.АналитикаВидаУчета, УчетЗатратРегл.АналитикаУчетаЗатрат, УчетЗатратРегл.АналитикаУчетаПартий, УчетЗатратРегл.АналитикаРаспределенияЗатрат, УчетЗатратРегл.Количество, УчетЗатратРегл.КоличествоНУ, УчетЗатратРегл.Количество КАК КоличествоПолучатель, УчетЗатратРегл.КоличествоНУ КАК КоличествоНУПолучатель, УчетЗатратРегл.Стоимость, УчетЗатратРегл.Стоимость КАК СтоимостьПолучатель, УчетЗатратРегл.СтоимостьНУ, УчетЗатратРегл.СтоимостьНУ КАК СтоимостьНУПолучатель, УчетЗатратРегл.ПостояннаяРазница, УчетЗатратРегл.ПостояннаяРазница КАК ПостояннаяРазницаПолучатель, УчетЗатратРегл.КодОперации, УчетЗатратРегл.КорАналитикаВидаУчета, УчетЗатратРегл.КорАналитикаУчетаЗатрат, УчетЗатратРегл.КорАналитикаУчетаПартий, УчетЗатратРегл.КорАналитикаРаспределенияЗатрат, УчетЗатратРегл.НомерСтроки КАК НомерСтрокиВРегистре, РегАналитикаВидаУчета.РазделУчета, РегАналитикаВидаУчета.Организация, РегАналитикаВидаУчета.ПодразделениеОрганизации, РегАналитикаВидаУчета.Подразделение, РегАналитикаВидаУчета.СчетУчета, РегАналитикаВидаУчета.СчетУчетаНУ, РегАналитикаВидаУчета.Проект, РегАналитикаВидаУчета.Склад, РегАналитикаРаспределенияЗатрат.НоменклатурнаяГруппа, РегАналитикаРаспределенияЗатрат.Продукция, РегАналитикаРаспределенияЗатрат.ХарактеристикаПродукции, РегАналитикаРаспределенияЗатрат.СерияПродукции, РегАналитикаРаспределенияЗатрат.Спецификация, РегАналитикаУчетаЗатрат.СтатьяЗатрат, РегАналитикаУчетаЗатрат.Затрата, РегАналитикаУчетаЗатрат.ХарактеристикаЗатраты, РегАналитикаУчетаЗатрат.СерияЗатраты, РегАналитикаУчетаЗатрат.СпособРаспределенияЗатрат, РегАналитикаУчетаЗатрат.ХарактерЗатрат, РегАналитикаУчетаЗатрат.Качество, РегАналитикаУчетаПартий.СтатусПартии, РегАналитикаУчетаПартий.Заказ, РегАналитикаУчетаПартий.ДоговорКомитента, РегАналитикаУчетаПартий.ДоговорКомиссионера, РегАналитикаУчетаПартий.Комиссионер, КорРегАналитикаВидаУчета.РазделУчета КАК КорРазделУчета, КорРегАналитикаВидаУчета.Организация КАК КорОрганизация, КорРегАналитикаВидаУчета.ПодразделениеОрганизации КАК КорПодразделениеОрганизации, КорРегАналитикаВидаУчета.Подразделение КАК КорПодразделение, ЕСТЬNULL(КорРегАналитикаВидаУчета.СчетУчета, КорРегАналитикаУчетаПрочихЗатрат.СчетУчета) КАК КорСчетУчета, ЕСТЬNULL(КорРегАналитикаВидаУчета.СчетУчетаНУ, КорРегАналитикаУчетаПрочихЗатрат.СчетУчетаНУ) КАК КорСчетУчетаНУ, КорРегАналитикаВидаУчета.Проект КАК КорПроект, КорРегАналитикаВидаУчета.Склад КАК КорСклад, КорРегАналитикаВидаУчета.Склад КАК СкладПолучатель, КорРегАналитикаУчетаПрочихЗатрат.ОбъектСтроительства КАК КорОбъектСтроительства, КорРегАналитикаУчетаПрочихЗатрат.СпособСтроительства КАК КорСпособСтроительства, КорРегАналитикаУчетаПрочихЗатрат.Субконто1 КАК КорСубконто1, КорРегАналитикаУчетаПрочихЗатрат.Субконто2 КАК КорСубконто2, КорРегАналитикаУчетаПрочихЗатрат.Субконто3 КАК КорСубконто3, КорРегАналитикаУчетаПрочихЗатрат.СубконтоНУ1 КАК КорСубконтоНУ1, КорРегАналитикаУчетаПрочихЗатрат.СубконтоНУ2 КАК КорСубконтоНУ2, КорРегАналитикаУчетаПрочихЗатрат.СубконтоНУ3 КАК КорСубконтоНУ3, КорРегАналитикаУчетаПрочихЗатрат.ДатаИсправительнойЗаписи КАК ДатаИсправительнойЗаписи, КорРегАналитикаУчетаПрочихЗатрат.СчитатьРазницыПостоянными КАК СчитатьРазницыПостоянными, КорРегАналитикаРаспределенияЗатрат.НоменклатурнаяГруппа КАК КорНоменклатурнаяГруппа, КорРегАналитикаРаспределенияЗатрат.Продукция КАК КорПродукция, КорРегАналитикаРаспределенияЗатрат.ХарактеристикаПродукции КАК КорХарактеристикаПродукции, КорРегАналитикаРаспределенияЗатрат.СерияПродукции КАК КорСерияПродукции, КорРегАналитикаРаспределенияЗатрат.Спецификация КАК КорСпецификация, КорРегАналитикаУчетаЗатрат.СтатьяЗатрат КАК КорСтатьяЗатрат, КорРегАналитикаУчетаЗатрат.Затрата КАК КорЗатрата, КорРегАналитикаУчетаЗатрат.ХарактеристикаЗатраты КАК КорХарактеристикаЗатраты, КорРегАналитикаУчетаЗатрат.СерияЗатраты КАК КорСерияЗатраты, КорРегАналитикаУчетаЗатрат.СпособРаспределенияЗатрат КАК КорСпособРаспределенияЗатрат, КорРегАналитикаУчетаЗатрат.ХарактерЗатрат КАК КорХарактерЗатрат, КорРегАналитикаУчетаЗатрат.Качество КАК КорКачество, КорРегАналитикаУчетаПартий.СтатусПартии КАК КорСтатусПартии, КорРегАналитикаУчетаПартий.Заказ КАК КорЗаказ, КорРегАналитикаУчетаПартий.ДоговорКомитента КАК КорДоговорКомитента, КорРегАналитикаУчетаПартий.ДоговорКомиссионера КАК КорДоговорКомиссионера, КорРегАналитикаУчетаПартий.Комиссионер КАК КорКомиссионер, УчетЗатратРегл.Регистратор ИЗ РегистрНакопления.УчетЗатратРегл КАК УчетЗатратРегл ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаВидаУчета КАК РегАналитикаВидаУчета ПО УчетЗатратРегл.АналитикаВидаУчета = РегАналитикаВидаУчета.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаЗатрат КАК РегАналитикаУчетаЗатрат ПО УчетЗатратРегл.АналитикаУчетаЗатрат = РегАналитикаУчетаЗатрат.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПартий КАК РегАналитикаУчетаПартий ПО УчетЗатратРегл.АналитикаУчетаПартий = РегАналитикаУчетаПартий.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаРаспределенияЗатрат КАК РегАналитикаРаспределенияЗатрат ПО УчетЗатратРегл.АналитикаРаспределенияЗатрат = РегАналитикаРаспределенияЗатрат.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаВидаУчета КАК КорРегАналитикаВидаУчета ПО УчетЗатратРегл.КорАналитикаВидаУчета = КорРегАналитикаВидаУчета.Ссылка И (УчетЗатратРегл.КорАналитикаВидаУчета ССЫЛКА Справочник.КлючиАналитикиВидаУчета) ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПрочихЗатрат КАК КорРегАналитикаУчетаПрочихЗатрат ПО УчетЗатратРегл.КорАналитикаВидаУчета = КорРегАналитикаУчетаПрочихЗатрат.Ссылка И (УчетЗатратРегл.КорАналитикаВидаУчета ССЫЛКА Справочник.КлючиАналитикиУчетаПрочихЗатрат) ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаЗатрат КАК КорРегАналитикаУчетаЗатрат ПО УчетЗатратРегл.КорАналитикаУчетаЗатрат = КорРегАналитикаУчетаЗатрат.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПартий КАК КорРегАналитикаУчетаПартий ПО УчетЗатратРегл.КорАналитикаУчетаПартий = КорРегАналитикаУчетаПартий.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаРаспределенияЗатрат КАК КорРегАналитикаРаспределенияЗатрат ПО УчетЗатратРегл.КорАналитикаРаспределенияЗатрат = КорРегАналитикаРаспределенияЗатрат.Ссылка ГДЕ УчетЗатратРегл.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Расход) И УчетЗатратРегл.НомерСтроки >= -1 И УчетЗатратРегл.Регистратор = &Регистратор УПОРЯДОЧИТЬ ПО НомерСтрокиВРегистре
Показана только часть плана. Видно, что оператор Key Lookup имеет стоимость 52% и весь запрос выполняется 0,415 сек.
Перепишем запрос:
ВЫБРАТЬ ПЕРВЫЕ 1000 УчетЗатратРегл.Период, УчетЗатратРегл.Регистратор, УчетЗатратРегл.НомерСтроки, УчетЗатратРегл.Активность, УчетЗатратРегл.ВидДвижения, УчетЗатратРегл.АналитикаВидаУчета, УчетЗатратРегл.АналитикаУчетаЗатрат, УчетЗатратРегл.АналитикаУчетаПартий, УчетЗатратРегл.АналитикаРаспределенияЗатрат, УчетЗатратРегл.Количество, УчетЗатратРегл.КоличествоНУ, УчетЗатратРегл.Стоимость, УчетЗатратРегл.СтоимостьНУ, УчетЗатратРегл.ПостояннаяРазница, УчетЗатратРегл.КодОперации, УчетЗатратРегл.КорАналитикаВидаУчета, УчетЗатратРегл.КорАналитикаУчетаЗатрат, УчетЗатратРегл.КорАналитикаУчетаПартий, УчетЗатратРегл.КорАналитикаРаспределенияЗатрат, УчетЗатратРегл.МоментВремени ПОМЕСТИТЬ врУчетЗатратРегл ИЗ РегистрНакопления.УчетЗатратРегл КАК УчетЗатратРегл ГДЕ УчетЗатратРегл.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Расход) И УчетЗатратРегл.НомерСтроки >= -1 И УчетЗатратРегл.Регистратор = &Регистратор УПОРЯДОЧИТЬ ПО НомерСтроки ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ УчетЗатратРегл.АналитикаВидаУчета, УчетЗатратРегл.АналитикаУчетаЗатрат, УчетЗатратРегл.АналитикаУчетаПартий, УчетЗатратРегл.АналитикаРаспределенияЗатрат, УчетЗатратРегл.Количество, УчетЗатратРегл.КоличествоНУ, УчетЗатратРегл.Количество КАК КоличествоПолучатель, УчетЗатратРегл.КоличествоНУ КАК КоличествоНУПолучатель, УчетЗатратРегл.Стоимость, УчетЗатратРегл.Стоимость КАК СтоимостьПолучатель, УчетЗатратРегл.СтоимостьНУ, УчетЗатратРегл.СтоимостьНУ КАК СтоимостьНУПолучатель, УчетЗатратРегл.ПостояннаяРазница, УчетЗатратРегл.ПостояннаяРазница КАК ПостояннаяРазницаПолучатель, УчетЗатратРегл.КодОперации, УчетЗатратРегл.КорАналитикаВидаУчета, УчетЗатратРегл.КорАналитикаУчетаЗатрат, УчетЗатратРегл.КорАналитикаУчетаПартий, УчетЗатратРегл.КорАналитикаРаспределенияЗатрат, УчетЗатратРегл.НомерСтроки КАК НомерСтрокиВРегистре, РегАналитикаВидаУчета.РазделУчета, РегАналитикаВидаУчета.Организация, РегАналитикаВидаУчета.ПодразделениеОрганизации, РегАналитикаВидаУчета.Подразделение, РегАналитикаВидаУчета.СчетУчета, РегАналитикаВидаУчета.СчетУчетаНУ, РегАналитикаВидаУчета.Проект, РегАналитикаВидаУчета.Склад, РегАналитикаРаспределенияЗатрат.НоменклатурнаяГруппа, РегАналитикаРаспределенияЗатрат.Продукция, РегАналитикаРаспределенияЗатрат.ХарактеристикаПродукции, РегАналитикаРаспределенияЗатрат.СерияПродукции, РегАналитикаРаспределенияЗатрат.Спецификация, РегАналитикаУчетаЗатрат.СтатьяЗатрат, РегАналитикаУчетаЗатрат.Затрата, РегАналитикаУчетаЗатрат.ХарактеристикаЗатраты, РегАналитикаУчетаЗатрат.СерияЗатраты, РегАналитикаУчетаЗатрат.СпособРаспределенияЗатрат, РегАналитикаУчетаЗатрат.ХарактерЗатрат, РегАналитикаУчетаЗатрат.Качество, РегАналитикаУчетаПартий.СтатусПартии, РегАналитикаУчетаПартий.Заказ, РегАналитикаУчетаПартий.ДоговорКомитента, РегАналитикаУчетаПартий.ДоговорКомиссионера, РегАналитикаУчетаПартий.Комиссионер, КорРегАналитикаВидаУчета.РазделУчета КАК КорРазделУчета, КорРегАналитикаВидаУчета.Организация КАК КорОрганизация, КорРегАналитикаВидаУчета.ПодразделениеОрганизации КАК КорПодразделениеОрганизации, КорРегАналитикаВидаУчета.Подразделение КАК КорПодразделение, ЕСТЬNULL(КорРегАналитикаВидаУчета.СчетУчета, КорРегАналитикаУчетаПрочихЗатрат.СчетУчета) КАК КорСчетУчета, ЕСТЬNULL(КорРегАналитикаВидаУчета.СчетУчетаНУ, КорРегАналитикаУчетаПрочихЗатрат.СчетУчетаНУ) КАК КорСчетУчетаНУ, КорРегАналитикаВидаУчета.Проект КАК КорПроект, КорРегАналитикаВидаУчета.Склад КАК КорСклад, КорРегАналитикаВидаУчета.Склад КАК СкладПолучатель, КорРегАналитикаУчетаПрочихЗатрат.ОбъектСтроительства КАК КорОбъектСтроительства, КорРегАналитикаУчетаПрочихЗатрат.СпособСтроительства КАК КорСпособСтроительства, КорРегАналитикаУчетаПрочихЗатрат.Субконто1 КАК КорСубконто1, КорРегАналитикаУчетаПрочихЗатрат.Субконто2 КАК КорСубконто2, КорРегАналитикаУчетаПрочихЗатрат.Субконто3 КАК КорСубконто3, КорРегАналитикаУчетаПрочихЗатрат.СубконтоНУ1 КАК КорСубконтоНУ1, КорРегАналитикаУчетаПрочихЗатрат.СубконтоНУ2 КАК КорСубконтоНУ2, КорРегАналитикаУчетаПрочихЗатрат.СубконтоНУ3 КАК КорСубконтоНУ3, КорРегАналитикаУчетаПрочихЗатрат.ДатаИсправительнойЗаписи КАК ДатаИсправительнойЗаписи, КорРегАналитикаУчетаПрочихЗатрат.СчитатьРазницыПостоянными КАК СчитатьРазницыПостоянными, КорРегАналитикаРаспределенияЗатрат.НоменклатурнаяГруппа КАК КорНоменклатурнаяГруппа, КорРегАналитикаРаспределенияЗатрат.Продукция КАК КорПродукция, КорРегАналитикаРаспределенияЗатрат.ХарактеристикаПродукции КАК КорХарактеристикаПродукции, КорРегАналитикаРаспределенияЗатрат.СерияПродукции КАК КорСерияПродукции, КорРегАналитикаРаспределенияЗатрат.Спецификация КАК КорСпецификация, КорРегАналитикаУчетаЗатрат.СтатьяЗатрат КАК КорСтатьяЗатрат, КорРегАналитикаУчетаЗатрат.Затрата КАК КорЗатрата, КорРегАналитикаУчетаЗатрат.ХарактеристикаЗатраты КАК КорХарактеристикаЗатраты, КорРегАналитикаУчетаЗатрат.СерияЗатраты КАК КорСерияЗатраты, КорРегАналитикаУчетаЗатрат.СпособРаспределенияЗатрат КАК КорСпособРаспределенияЗатрат, КорРегАналитикаУчетаЗатрат.ХарактерЗатрат КАК КорХарактерЗатрат, КорРегАналитикаУчетаЗатрат.Качество КАК КорКачество, КорРегАналитикаУчетаПартий.СтатусПартии КАК КорСтатусПартии, КорРегАналитикаУчетаПартий.Заказ КАК КорЗаказ, КорРегАналитикаУчетаПартий.ДоговорКомитента КАК КорДоговорКомитента, КорРегАналитикаУчетаПартий.ДоговорКомиссионера КАК КорДоговорКомиссионера, КорРегАналитикаУчетаПартий.Комиссионер КАК КорКомиссионер, УчетЗатратРегл.Регистратор ИЗ врУчетЗатратРегл КАК УчетЗатратРегл ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаВидаУчета КАК РегАналитикаВидаУчета ПО УчетЗатратРегл.АналитикаВидаУчета = РегАналитикаВидаУчета.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаЗатрат КАК РегАналитикаУчетаЗатрат ПО УчетЗатратРегл.АналитикаУчетаЗатрат = РегАналитикаУчетаЗатрат.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПартий КАК РегАналитикаУчетаПартий ПО УчетЗатратРегл.АналитикаУчетаПартий = РегАналитикаУчетаПартий.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаРаспределенияЗатрат КАК РегАналитикаРаспределенияЗатрат ПО УчетЗатратРегл.АналитикаРаспределенияЗатрат = РегАналитикаРаспределенияЗатрат.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаВидаУчета КАК КорРегАналитикаВидаУчета ПО УчетЗатратРегл.КорАналитикаВидаУчета = КорРегАналитикаВидаУчета.Ссылка И (УчетЗатратРегл.КорАналитикаВидаУчета ССЫЛКА Справочник.КлючиАналитикиВидаУчета) ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПрочихЗатрат КАК КорРегАналитикаУчетаПрочихЗатрат ПО УчетЗатратРегл.КорАналитикаВидаУчета = КорРегАналитикаУчетаПрочихЗатрат.Ссылка И (УчетЗатратРегл.КорАналитикаВидаУчета ССЫЛКА Справочник.КлючиАналитикиУчетаПрочихЗатрат) ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаЗатрат КАК КорРегАналитикаУчетаЗатрат ПО УчетЗатратРегл.КорАналитикаУчетаЗатрат = КорРегАналитикаУчетаЗатрат.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПартий КАК КорРегАналитикаУчетаПартий ПО УчетЗатратРегл.КорАналитикаУчетаПартий = КорРегАналитикаУчетаПартий.Ссылка ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаРаспределенияЗатрат КАК КорРегАналитикаРаспределенияЗатрат ПО УчетЗатратРегл.КорАналитикаРаспределенияЗатрат = КорРегАналитикаРаспределенияЗатрат.Ссылка
Появился дополнительный план по созданию временной таблицы
Мы избавились от тяжелого оператора Key Lookup и добились значительных улучшений показателей производительности, несмотря на дополнительную операцию по созданию временной таблицы
Выводы
Мы отметили, что синтаксис запроса может влиять на выбор оптимизатором того или иного плана исполнения запроса, несмотря на то, что логически мы получаем один и тот же результат. Также можно отметить, что,например, синтаксис, содержащий «ОБЪЕДИНИТЬ ВСЕ» иногда может привести к выбору оптимизатором оптимального плана, иногда может ухудшить показатели. Использование временных таблиц также может улучшить показатели производительности.
Переписывание запросов и попытки использовать альтернативный синтаксис являются допустимой техникой «тюнинга» запросов, однако необходимо соблюдать некоторые меры предосторожности. Одним из рисков является то, что будущие изменения продукта (MS SQL Server) могут привести к тому, что конкретная форма запроса неожиданно перестанет генерировать лучший план, но это всегда риск и его можно уменьшить предварительным тестированием перед обновлением.
Существует также риск увлечься этой техникой и писать совсем уж необычные запросы и таким образом преступить черту. Граница между альтернативным синтаксисом и «необычным» очень размыта, можно лишь порекомендовать, чтобы запросы были максимально простыми.
