Как SQL Server выполняет сценарий транзакции? (Тот же вопрос требует совершенно другого времени для выполнения)

#sql #sql-server #common-table-expression

Вопрос:

Я работаю в крупной компании, у которой есть огромные SQL-скрипты. Этот пользователь пожаловался, что его скрипт работает медленно, и я начал его просматривать. Чтобы найти то, что было медленным, я разделил вопросы и сделал select-insert во временные таблицы, а затем использовал временные таблицы в своем фактическом вопросе.

Но конечным результатом является то, что вопрос выполняется намного быстрее, если я сохраняю временные таблицы.

 All in one question: Just shy of 20 minutes.
My broken up question: A bit more than 2 minutes.

Существует существенная разница.

Итак, весь сценарий имеет длину 940 строк, а каждый отдельный вопрос, который я задаю во временных таблицах, составляет около 70-80 строк.

Результатом моей работы с временной таблицей является вопрос, который выглядит следующим образом:

 with cte_A
as (
   select *
   from #Temp1
   union all
   select *
   from #Temp2
   union all
   select *
   from #Temp3)
   , cte_B
as (select *
    from #Temp4)
   , cte_C
as (select *
    from #Temp5)
   , OB_BL_NotOverdue
as (select *
    from #Temp6)
   , cte_D
as (select *
    from #Temp7)
   , cte_E
as (select *
    from #Temp8
    union all
    select *
    from #Temp9)

SQL question based on the above.

Теперь в исходном вопросе был полный вопрос о 70-80 строках вместо select * from #TempX so, единственное отличие состоит в том, что вместо 70-80 строк SQL я выполняю этот SQL перед вопросом и выполняю select * .
Общее время выполнения (создание всех временных таблиц и выполнение вопроса) составляет 2 минуты. Итак, я выполняю одну и ту же работу в обоих исполнениях.

Сервер, на котором мы работаем, — это SQL Server 2016 (поэтому не последняя версия).

Кто-нибудь может объяснить, почему?

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

Ответ №1:

Прежде всего CTE , нет ничего лучше, чем писать код и выполнять разделение, но для движка он работает как подзапрос.

Итак, если вы помещаете все запросы внутрь CTE и если они обращаются к одним и тем же таблицам, можно запутать механизм SQL, и тогда это увеличит количество операций ввода-вывода.

Итак, после того, как вы сохранили эти вещи отдельно Temporary table , данные сохраняются в памяти SQL Server tempdb . Итак, для SQL Engine почти всегда проще считывать данные из памяти и базы данных tempdb, чем выбирать какой-то конкретный план для эффективного чтения данных с диска.