Зачем запросу план и кто его выполняет?

Публикация № 877736

Администрирование - Производительность и оптимизация (HighLoad)

Как определить, почему запрос выполняется слишком долго? Что происходит с запросом на стороне сервера баз данных? В статье приводится объяснение, что такое план запроса и для чего он нужен. А также говорится о том, в чем разница между потоком операторов и потоком данных, как работает оптимизатор и зачем нужна статистика.

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

 

Что такое план запроса?

Итак, начнем. Что здесь происходит?

  • Когда мы пишем запросы в среде 1С на SQL-подобном языке, мы используем декларативный подход к написанию: в тексте запроса мы не указываем способ получения данных, а просто говорим, какие данные хотим извлечь, что хотим получить.
  • Структура базы данных определяет, где эта информация находится.
  • А план запроса отвечает на вопрос «Как извлечь информацию из базы данных таким образом, чтобы набор данных соответствовал исходному тексту запроса».

Для лучшего понимания и запоминания попробуем провести аналогию, между строительством дома и формированием плана запроса. Понятно, что любая аналогия ложна, но это все же лучше, чем ничего.

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

  • Дальше оптимизатор запроса ищет, не делал ли он недавно похожий запрос, возможно, аналогичный способ извлечения данных уже есть в кэше (в специальной области данных, где сохраняются планы выполнения запросов для того, чтобы не строить его каждый раз). Поэтому он просто идет в кэш и смотрит, нет ли там “типового проекта”.

Выполнение запроса можно условно разделить на два этапа:

  • построение плана;
  • выполнение запроса согласно плану.

Зачастую построение плана занимает больше времени, чем само выполнение, потому что этот процесс – достаточно сложный и ресурсоемкий.

 

Дальше планировщик запросов принимает решение, как будет происходить выполнение запроса:

  • какими именно физическими операторами будут извлекаться данные из таблиц, и соединяться друг с другом;
  • в каком порядке будет происходить выполнение этих операторов –  сначала данные будут отфильтрованы, а потом отсортированы или сначала отсортированы, а потом отфильтрованы.

 

У каждого оператора есть своя стоимость в неких условных единицах процессорного времени, обращений к системе ввода-вывода и объема используемой памяти. Зная стоимость операторов, мы можем получить стоимость всего плана, просто сложив стоимость всех операторов.

Обычно оптимизатор строит несколько возможных планов выполнения запроса, т.е. каким именно образом можно извлекать данные и в каком порядке их можно обрабатывать. Он оценивает стоимость каждого плана и выбирает самый “дешевый”.

Так получается в большинстве случаев, но не всегда, потому что процесс построения плана и оценки его стоимости тоже требует ресурсов, а время на выполнение запроса не бесконечно, потому что время на поиск лучшего плана в итоге может превысить время исполнения запроса. Поэтому оптимизатор ограничивает время которое затрачивается на построение плана запроса. Если он успевает выбрать хороший план – значит все неплохо, но может и не успеть. Если оптимизатор не успел, то он выбирает первый (любой) попавшийся план, – какой успел построить, тот и будет. Дальше я покажу, как понять, хороший план выбран или просто первый попавшийся.

 

После этого происходит выполнение запроса – формируется набор данных.

 

И результат выполнения запроса возвращается инициатору вызова.

 

Теперь коротко еще раз то же самое по схеме:

  • анализ текста запроса, его разбиение на элементы;
  • поиск в кэше готового плана;
  • если в кэше есть подходящий план – выполнение запроса по этому плану и возврат результата;
  • если плана нет – его формирование, попытка выбрать наилучший, выполнение запроса, возврат результата.

На схеме можно увидеть аналогию со строительством.

 

Структура плана запроса

 

Давайте теперь рассмотрим подробнее, что представляет собой план запроса, из каких операторов он состоит.

Графически план запроса в SQL-сервере может выглядеть примерно так, как показано на слайде:

  • здесь каждая иконка – это некий оператор.
  • все операторы соединены между собой стрелками, которые показывают поток данных.

Есть два потока – поток операторов и поток данных. Поток операторов идет слева направо, поток данных идет справа налево.

С потоком данных все понятно:

  • сначала мы должны извлечь данные из каких-то физических объектов, таблиц, индексов, представлений;
  • далее мы должны обработать эти данные – соединить их между собой, выполнить сортировку, наложить отборы;
  • и в конце вернуть результат.

Поток операторов идет в обратную сторону:

  • начинается все с первого оператора (в данном случае это SELECT), который запрашивает данные у других операторов (в данном случае, у оператора соединения);
  • оператору соединения на вход должны прийти два набора данных, которые он будет соединять – он запрашивает эти данные у следующих операторов;
  • Таким образом, каждый оператор последовательно запрашивает данные у своих входов, они по очереди выполняются, и результат запроса возвращается инициатору.

Как это проверить, как в этом убедиться? Пишем запрос, в котором присутствует фраза «ВЫБРАТЬ ПЕРВЫЕ столько-то», проверяем. Этот запрос выполняется существенно быстрее, чем тот, который возвращает весь поток данных, потому что мы изначально ограничили размер выборки по количеству. Не так, что мы все данные сразу вытащили и отдали их следующему оператору – что хочешь, то и делай.

Мы сейчас говорим преимущественно про планы по выборке данных, потому что запросы, связанные с обновлением данных в контексте 1С, нам недоступны, их делает сама платформа, мы ничего с ними сделать не можем. Но на выборку (оператор SELECT) мы можем повлиять.

 

Основные операторы

Здесь указаны основные операторы, которые чаще всего присутствуют в плане запроса. У каждого из них есть своя иконка в SQL Server Management Studio – она позволяет понять, о чем идет речь, что оператор делает. В зависимости от локализации оператор может иметь русское или английское наименование.

Самих операторов SQL намного больше, но в контексте 1С встречаются далеко не все.

 

Свойства оператора

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

У разных операторов набор свойств будет немного отличаться. На слайде показаны свойства оператора Clustered Index Scan – это сканирование, (просмотр) кластеризованного индекса.

Что есть в данном случае?

  • Physical Operation (физическая операция) и Logical Operation (логическая операция) чаще всего совпадают – это суть оператора, то, что он делает. В данном случае он просматривает кластеризованный индекс.
  • Есть несколько свойств с указанием Estimated Cost – это то, из чего складывается цена самого оператора, та самая стоимость оператора, т.е. оценка количества вычислительных ресурсов, которые требуются на выполнение оператора. Стоимость может быть Estimated (ожидаемая), как в данном случае – это значит, что реально мы запрос не выполняли, а только строили для него план. Эта ожидаемая стоимость определяется на основе статистики. Кроме нее может быть еще Actual Cost – актуальная стоимость, она тоже определяется оценочным образом.
  • Estimated Number of Rows – это количество строк, которые будут возвращены в результате, т.е. какой объем данных будет этим оператором возвращен;
  • Estimated CPU Cost – это вычислительные затраты;
  • Estimated I/O Cost – это затраты по памяти;
  • Estimated Row Size – ожидаемый размер строки.
  • Ordered – признак того, отсортирован или нет набор данных, полученный на выходе от этого оператора. Для некоторых операций требуется сортировка. Если набор данных уже отсортирован, то ничего дополнительно сортировать не надо. Например, он будет отсортирован, если идет получение данных из индекса, а не из таблицы, либо если на одном из предыдущих этапов сортировка уже была сделана.
  • Если это оператор чтения данных, то у него есть Predicate – условие отбора, по которому читаются данные. Здесь идет обращение к конкретному полю таблицы и указывается фильтр по его конкретному значению.
  • Object – это указание объекта, из которого производится чтение.
  • А также здесь указывается Output List – список полей из этого объекта, которые будут возвращены.

 

Операции плана запроса

Что дальше? У нас есть две логические операции, которые имеют несколько физических. Это операции чтения данных и соединения таблиц.

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

 

Операторы доступа к данным

В контексте 1С в основном используются три физических операции доступа к данным:

  • Table Scan – чтение данных напрямую из физической таблицы;
  • Index Scan – чтение данных из физического индекса;
  • Index Seek – поиск в индексе.

Если говорить в целом про производительность и про скорость, то:

  • Index Seek – это хорошо, это быстро. Это значит, что у нас есть индекс и мы извлекаем не всю информацию, а только ту, которая удовлетворяет условиям отбора.
  • Index Scan – это чуть похуже, но тоже неплохо. У нас есть индекс, но мы не можем из него сразу вытащить нужную информацию, нам нужно его просмотреть. Но поскольку индекс изначально отсортирован, процесс просмотра получается быстрый.
  • Table Scan – это самое плохое, потому что нам нужно просмотреть всю таблицу. Но в некоторых случаях ничего другого не остается.

Я не говорю о том, что нужно всегда добиваться наличия Index Seek. Просто нужно понимать, что происходит, потому что если у нас, например, есть индекс, и мы думаем, что он используется, а, открыв план запросов, видим там Table Scan – мы ошиблись, индекс не используется. Почему он не используется – это повод для дальнейшего разбирательства.

Очень просто понять, используется индекс или нет – если в плане запроса мы видим Table Scan, значит, индекс не используется.

 

Операторы соединения

Соединения таблиц – это одна из самых ресурсоемких операций. На реальных проектах пользователи в отчетах 1С любят выводить «простыню», где «все по всем и все сразу». В этих случаях крайне редко источником данных является одна таблица – обычно этих таблиц несколько. Даже если явные соединения в запросе не используются, то, когда мы в 1С через точку обращаемся к реквизитам объекта, неявно все равно производится соединение с таблицей этого объекта. Таким образом, почти всегда есть несколько таблиц, которые должны быть определенным образом соединены.

Операция соединения может быть реализована тремя различными операторами.

 

Первый способ соединения – это Nested Loops (или вложенные циклы). Оператор самый простой, самый надежный, работает всегда и везде, но не слишком быстро.

 

Если бы мы этот оператор написали на 1С, он мог бы выглядеть примерно так, как показано на слайде. Он очень простой, самый универсальный, но при этом и самый медленный (среди операторов соединения таблиц).

 

Преимущество Nested Loops в том, что:

  • он работает всегда, на любых данных, на любых условиях соединения;
  • когда у нас маленькие наборы данных, он работает быстро, потому что проходов циклов не так много;
  • он работает очень быстро, если нужно получить первые несколько строчек, потому что нам никаких дополнительных действий не нужно.

Его минус в том, что, если наборы данных большие, количество проходов цикла тоже большое, и тогда он работает медленно.

 

Второй способ – это Hash Match (соединение хэшированием). Способ более сложный, поэтому рассмотрим его чуть подробнее.

Для начала разберемся, что такое хэш-функция. Есть класс математических функций, которые на основе произвольного набора данных позволяют получить «слепок», сформированный по определенным правилам.

На слайде можно увидеть, к какому виду преобразовывается строка текста хэш-функцией md5.

Обратите внимание, что первые две исходные строки текста отличаются одним единственным символом. При этом хэш-функция отличается кардинально. Это особенность самих хэш-функций.

 

Соединение хэшированием особенно эффективно при соединении по нескольким условиям (когда в условии соединения используется не одно поле, а несколько). Тогда к комбинации из полей, входящих в условие, можно применить хэш-функцию и получить табличку с их хэш-кодами.

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

 

На языке 1С это могло бы выглядеть примерно так, как на слайде.

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

 

Преимущества и недостатки соединения хэшированием вы можете увидеть на слайде.

 

Третий способ – это Merge Join (соединение слиянием). Он самый быстрый с точки зрения выполнения, но применить его можно не всегда (если бы было по другому, то остальные способы были бы не нужны. Ведь зачем все остальные, если они более медленные).

В чем основное ограничение этого способа? В том, что он работает только тогда, когда на условиях соединения есть хотя бы одно равенство, при том, что исходные наборы данных должны быть отсортированы.

 

 

Алгоритм мог бы выглядеть примерно так, как показано на слайде.

 

Его существенный плюс в том, что у нас всего один проход по циклу – мы каждый из наборов данных считываем всего один раз.

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

 

Итак, что мы имеем?

У нас нет хорошего или плохого способа соединения.

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

Если к нам пришел уже отсортированный набор данных, оптимизатор будет использовать слияние. Или бывает ситуация, когда пришел неотсортированный набор, но оптимизатор может решить, что затраты на сортировку “отобьются” и отсортировать его отдельной командой Sort, а потом сделать соединение слиянием. В итоге это может получиться дешевле, чем вложенный цикл. А может и не получиться.

Есть важное замечание – на уровне 1С у нас отсутствует возможность выбирать, какой способ соединения таблиц использовать. На уровне SQL мы можем увидеть в плане запроса, какой способ был использован. При написании текста запроса на языке T-SQL можно (в некоторых случаях) явно указать, какой способ соединения использовать, а можно дать “хинт” (рекомендацию) планировщику, какой именно способ соединения ему использовать. Но на уровне 1С у нас такой возможности нет.

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

 

Как работает оптимизатор. При чем здесь статистика

 

Теперь несколько слов по поводу статистики – что это такое и зачем она нужна.

Когда оптимизатор выбирает способ выборки данных и соединения таблиц, он должен на чем-то основываться. Считывать для этого каждый раз все данные из таблицы – не самый оптимальный способ, поэтому используются вспомогательные механизмы, которые называются “статистика”.

С точки зрения 1С здесь ничего делать не нужно, сервер базы данных сам собирает и обновляет статистику. Чтобы он этого не делал, статистику нужно специально отключить на уровне базы.

Иногда на уровне 1С происходят события, которые приводят к существенному изменению данных. После этого желательно давать серверу базы данных принудительную команду на обновление статистики.

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

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

В обычном режиме, когда идет ввод документов или справочников, не бывает каких-то массовых серьезных изменений. Но если мы делаем групповую обработку документов с заменой значений – крайне желательно запускать обновление статистики.

 

На слайде показан упрощенный пример того, как могла бы выглядеть статистика – это распределение номенклатурных позиций по разным единицам измерения в демо-базе бухгалтерии.

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

Оптимизатор знает, что общее количество записей в таблице, допустим, 1000, и что распределение значений по единицам измерений такое-то, и, таким образом, он может еще до выполнения запроса получить оценку того, сколько записей вернется в результате.

 

В реальности понятие статистики несколько шире, т.е. в статистике хранится больше информации:

  • число строк в самой таблице;
  • информация о том, когда статистика была собрана, чтобы понимать ее актуальность;
  • гистограмма по селективности распределения самих значений (предыдущая картинка).

Здесь есть важный момент: для подзапросов никакой статистики нет. Оптимизатор может только очень приблизительно оценить, сколько данных вернется в результате выполнения подзапроса, пока он не выполнен. И чем больше вложенность подзапросов друг в друга, тем хуже точность этого предположения.

А если мы используем временную таблицу, то она, по сути, является физической таблицей, которая лежит не в основной базе данных, а в служебной (TempDB), поэтому для временных таблиц точно так же считается статистика. Оптимизатор при построении плана с использованием временных таблиц может более точно оценить, сколько данных будет возвращено, и принять решение о наиболее оптимальном способе соединения, чтобы запрос выполнялся максимально быстро.

 

К каким проблемам приводит неактуальная статистика?

У нас может быть выбран неправильный способ соединения, неправильный способ извлечения данных – запрос выполнится, вернет данные, которые нужны, но время выполнения будет далеко не лучшим.

Поэтому статистику нужно поддерживать в актуальном состоянии. И делается это только средствами базы данных, в 1С для этого средств нет.

База данных поддерживает актуальность статистики автоматически, но ей иногда нужно помогать. Обычно достаточно написать небольшой скрипт и либо поставить его на регламентное выполнение после тяжелых операций над базой, либо дергать его “руками”, если вы внепланово что-то натворили.

 

Несколько слов по поводу того, так ли хорош наш план запроса.

Прежде чем заниматься его оптимизацией, нужно понять, почему этот план такой. Если навести курсор мыши на оператор SELECT в схеме плана запроса, то появится меню с параметрами, среди которых будет параметр «Reason for Early Termination». С его помощью можно понять:

  • является ли анализируемый план первым попавшимся (будет значение «Time Out»), т.к. оптимизатор не успел посмотреть другие. Возможно, на выбор оптимального плана просто не хватило ресурсов, поэтому можно попробовать выполнить запрос еще раз;
  • или это план, который оптимизатор посчитал достаточно хорошим (значение «Good Enough Plan Found»), т.е. он сформировал несколько планов, оценил стоимость и выбрал наиболее подходящий (дешевый).

 

Ищем узкие места

 

По поводу поиска узких мест. Здесь все сложно. Каких-то общих однозначных рецептов не существует. Все очень индивидуально и упирается в конкретную ситуацию.

 

Но есть вещи, про которые нужно помнить. Это та же статистика. Когда мы смотрим на план запроса, то у нас внизу рядом с каждым оператором высвечивается его относительная стоимость. Не забывайте, что она всегда указывается в процентах, поэтому стоимость всех операторов плана всегда будет 100%. Уменьшение стоимости одного оператора всегда будет приводить к увеличению стоимости других. Нельзя взять и загнать их всех в 0. Нужно смотреть, какая из операций самая дорогая. Если самая дорогая операция – это Index Seek, то вряд ли мы что-то можем сделать, это поиск в индексе, быстрее уже ничего не бывает. Если самая дорогая операция – Join, то нужно смотреть, возможно, способ Join неправильный.

Также нужно оценивать размеры таблиц. Если таблицы маленькие, чтение данных из них может быть быстрым. А если таблицы большие, то это чтение и должно занимать какое-то время – нельзя же мгновенно прочитать миллион строк.

 

Рекомендации, которые можно считать общими, и которые будут работать практически для любого плана.

  • Логично, что мы выбираем самый дорогой оператор и начинаем смотреть, можно ли с ним что-то сделать. Если самый дорогой оператор – это «Поиск в индексе», т.е. чтение первичных данных из исходной таблицы, то вряд ли мы что-то можем сделать, потому что нельзя выполнить запрос, не получив данные из таблицы.
  • Наличие оператора Table Scan (просмотр таблицы) говорит о том, что подходящего индекса нет. Если навести на «Свойства», то можно увидеть Output List (список возвращаемых колонок) и Predicate (условие отбора) – с их помощью можно понять, почему не может использоваться индекс:
    • либо в списке возвращаемых колонок присутствует поле, которого нет в индексе;
    • либо в списке условий в Predicate присутствуют колонки, которых нет в индексе.
  • Что мы делаем в таком случае? Добавляем индекс, выполняем тот же запрос, смотрим план. Если Table Scan сменился на Index Scan или Index Seek, значит, индекс подходит. Если индекс есть, но по-прежнему Table Scan, значит что-то не так с индексом – либо в нем отсутствуют нужные поля, либо оптимизатор решил, что использовать индекс нельзя.

Здесь есть важный момент: индекс может быть покрывающим и не покрывающим.

  • Покрывающий индекс – это когда в нем содержатся все данные по колонкам, указанным в запросе.
  • Не покрывающий индекс – это, когда в индексе есть только часть данных, а другая часть данных лежит в самой таблице, и после того, как мы получили данные с помощью индекса, нам оставшиеся данные нужно забрать из таблицы. Признаком такой ситуации является оператор Bookmark Lookup или Key Lookup, т.е. оператор со словом Lookup в названии. Это значит, что мы ходим куда-то, уточняя, “добирая” оставшиеся данные.

В некоторых случаях оптимизатор может решить, что лучше он прочтет сразу из таблицы, чем будет сначала считать из индекса, а потом ходить в таблицу и добирать из нее (одно из объяснений ситуации, когда индекс есть, состав полей соответствует, но индекс все равно не используется). Это особенно актуально на маленьких таблицах. Если у вас справочник единиц измерения, в котором редко бывает больше нескольких десятков записей, на него нет особого смысла делать какие-то индексы – на маленьких таблицах индексы чаще вредят, чем помогают.

 

Полезные ссылки

Полезные ссылки, которые использовались при подготовке доклада.

Обслуживание индексов и статистик MS SQL Server 
Анализ запросов с помощью SQL Profiler 
Планы запросов - это просто!
Как читать план запроса в SQL Server 
MS SQL Books Online. Query Tuning
 

На Инфостарте есть публикации на тему планов запроса, статистики, но я считаю, что обучающей информации много не бывает, она всеми воспринимается по-разному – кому-то понятней так, кому-то по-другому. Когда ты ознакомился с большим объемом информации, сложил его в голове по кусочкам, тебе стало немного легче.

 

Вопросы:

– Вы говорили, что временные таблицы запросов помещаются в Tempdb, но она при этом слишком сильно разрастается. Как этого избежать?

– Tempdb – это исключительно служебная база данных. Ее нужно просто регулярно чистить, ей нужно делать TRUNCATE. Средствами самого SQL делается небольшой скрипт, который эту базу данных очищает. Его можно поставить на регламентный запуск. Если у вас приложение работает не в режиме 24/7 (если вы ночью отдыхаете), то вы просто запускаете его ночью. Если этого не хватает, и Tempdb разрастается слишком быстро, можно запустить этот скрипт в обеденный перерыв, еще в какие-то промежутки времени. Tempdb – это именно служебная база для хранения каких-то промежуточных данных – она используется только в процессе исполнения запросов. Соответственно, даже если ее “уронить” в неподходящий момент, самое худшее, что случится – это просто откатятся какие-то транзакции. Данные продуктивной, реальной базы вы не потеряете.

Есть еще один важный момент про статистику – когда используется соединение с помощью хэширования, построенную хэш-таблицу оптимизатор тоже кладет в Tempdb. Но прежде чем строить хэш-таблицу, оптимизатор на основе статистики пытается понять, какой объем данных придет на вход, и, исходя из этого, определяет, сколько памяти ему нужно для построения хэш-таблицы на этом объеме данных.

Но если у нас статистика плохая, и оптимизатор не угадал с объемом памяти, начинается «геморрой». Он хэш-таблицу строит, а выделенная память кончилась. Что делать? Надо положить в Tempdb кусок хэш-таблицы, достроить его дальше – если не хватило, опять положить. После того, как всю таблицу достроили, начать соединяться, периодически подтягивая ее из Tempdb. Поэтому, если у вас было все нормально, а потом внезапно на выполнение запросов стало тратиться много времени – и вы видите, что используется соединение хэшированием, возможная причина – это неактуальная статистика, из-за которой неправильно определился объем памяти под хэш-таблицу.

Первое, что нужно сделать – это обновить статистику, посмотреть. Если помогло – ура, если не помогло – смотрим дальше.

– Эта Tempdb не очищается автоматически встроенными средствами?

– Tempdb очищается при перезагрузке – там есть таблицы, которые создаются для сеанса, они удаляются при отключении сеанса. И есть таблицы, которые создаются для всех сеансов – они удаляются при перезагрузке. Следует помнить, что Tempdb – это тоже база данных и у нее есть своя модель восстановления. Если вы поставите ей полную (Full) модель восстановления, у вас для нее будет вестись журнал транзакций, который тоже будет разрастаться. Но, конечно, полная модель восстановления для нее не нужна.

– У меня вопрос по поводу того, что стоимость всех операторов в плане запроса всегда будет 100%. У меня был опыт – использование кластеризованного индекса занимало 90% от общего плана. Я создал обычный, не кластеризованный индекс, и производительность этой операции стала 70%. Но при этом поменялись процентные соотношения внутри других гистограмм, и в целом, осталось непонятно, стало ли оно быстрее работать или медленнее.

– Любое действие, изменяющее план запроса, ведет к тому, что у нас поменяется именно процентное соотношение стоимости операторов.

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

– Сам план запроса не дает ответа на вопрос – быстро выполняется запрос или медленно. План запроса говорит о том, как именно происходит выполнение запроса. Чтобы измерить скорость выполнения конкретного запроса, мы его запускаем и засекаем время. Если после оптимизации стало работать быстрее, значит, все хорошо. План запроса показывает, что поменялось внутри. Если у вас была оценка Index Scan – 90%, а стала 70%, это значит, теперь меньшая часть времени тратится на получение данных. Соответственно, большая часть времени тратится на их дальнейшую обработку. И, наверное, нужно смотреть, что там дальше с этими данными происходит.

– Получается, что общей оценки времени я в этом плане не найду?

Когда у нас есть план, у него есть оператор SELECT. Если на него навести и посмотреть его свойства, у него тоже будут Cost’ы. И можно будет по Cost’ам получить оценку –  сколько вычислительных ресурсов было задействовано – сколько было потрачено процессорного времени, операций ввода-вывода, какой объем памяти. Если эта оценка уменьшилась – наверное, стало быстрее.

– Какой конкретно  показатель нужно смотреть?

– Основное – это сам Operator Cost – стоимость самого оператора, суммарно CPU и I/O. Здесь есть значение в абсолютных единицах и в процентах. Ориентируйтесь на значение в абсолютных единицах.

– А вы одобряете распространенную практику запускания каждую ночь обновления статистики?

– Обновление статистики и full scan – это совершенно нормальная вещь. Если это делается по ночам, когда сервер все равно простаивает, хуже от этого точно не будет. Здесь есть важный момент – если у вас вечером был запущен какой-то тяжелый регламент, который что-то перепроводит, пересчитывает, меняет, а потом ночью прошло обновление статистики – это хорошо. Но если ну вас ночью прошло обновление статистики, а потом в обед делается перепроведение, то толку не будет. Потому что у вас данные сильно поменялись, и вы полдня живете с неактуальной статистикой.

Периодически SQL-сервер сам обновляет статистику. У него в настройках есть определенный параметр – он отслеживает, какая доля строк в таблице была изменена. Когда он видит, что после последнего обновления статистики был изменен определенный процент строк в таблице, он автоматически запускает операцию по обновлению статистики.

Здесь важен еще один момент – сам запрос может выполняться в двух режимах. В зависимости от настройки SQL-сервера, оптимизатор, прежде чем строить план запроса, сначала обращается к статистике. Он определился с перечнем объектов, которые ему нужны, и дальше ему по этим объектам нужна статистика, чтобы выбрать оптимальные способы доступа к данным. Движок возвращает ему статистику, но предупреждает, что она неактуальная. И здесь в зависимости от настроек есть два варианта. Либо оптимизатор говорит: «Хорошо, я выполняю запрос на том, что есть». Либо второй вариант – он дождется, когда будет получена актуальная статистика, и только после этого начнет строить план. Если у вас запрос, который выполнялся нормально, вдруг внезапно начал тормозить, возможно, как раз SQL решил, что статистика стала неактуальной, и оптимизатор ждет, пока она обновится. И здесь регулярное принудительное обновление статистики может помочь, потому что у вас может быть таблица, которая изменяется нечасто, изменения копятся, и в какой-то момент перешагивают через порог, когда SQL решает, что пора обновлять статистику. И чтобы этот момент не попал на неудачное время, когда нужно активно работать с базой, можно профилактически пересчитывать по ночам.

– А можно отключить автоматическое обновление статистики или не стоит?

– Можно. Но если вы отключаете автоматическое обновление, значит, должно быть ручное. Полностью отключать обновление статистики крайне не рекомендуется, это очень негативно скажется на производительности. Само по себе обновление статистики – нересурсоемкая операция. Она ресурсы потребляет, но не драматически. Важно, что статистику можно обновлять в процессе работы с базой, она не требует монопольного доступа. Пользователи так же работают с документами, в фоне идет обновление статистики, никто ничего не замечает.

Можно обновлять статистику полностью, можно только по конкретным таблицам, можно обновлять конкретные виды статистики, можно обновлять статистику по конкретным столбцам.

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

– Он, в зависимости от настройки, либо ждет, когда обновление закончится, либо строит план на той статистике, которая есть.

– Еще один вопрос по созданию индекса. Можно ли создать индекс непосредственно в SQL, минуя конфигуратор?

– Крайне не рекомендую создание индекса через SQL, потому что как только вы сделаете «Обновить конфигурацию базы данных» из конфигуратора, все эти индексы “улетят”, сделаете реструктуризацию – тоже “улетят”. Платформа 1С то про них “не в курсе”. Если мы говорим про 1С, единственный правильный способ создания индексов – это зайти в конфигуратор, правой кнопкой нажать на реквизите и поставить для него «Индексировать». Или включить реквизит в состав критерия отбора.

– А покрывающий индекс как создать?

– 1С в основном, создает кластеризованные индексы, поэтому с этим особых проблем нет. Потом опять же, если мы говорим про регистры (и накопления, и сведений), то при создании регистра для него всегда создается индекс, куда включаются все измерения регистра. И если мы ставим на каком-то измерении «Индексировать», то добавляется индекс в котором опять присутсвуют все измерения регистра, но выбранное измерение просто идет первым в этом списке. А дальше они идут в том порядке, в котором они указаны в дереве конфигурации. Про этот момент некоторые не знают, но если мы говорим про регистры накопления и регистры сведений, то порядок измерений очень важен, его нельзя задавать произвольным образом. Потому что индекс для регистра создается всегда, и измерения в индексе всегда идут в том порядке, в котором они перечислены в дереве конфигурации. А если вы делаете дополнительный индекс, то он у вас отдельно будет вынесен вперед, но остальные индексы все равно останутся.

– У меня недавно на проекте были рекомендации – поменять порядок столбцов в регистре. Если я поставлю для измерения «Индексировать» – оно само перенесется вперед?

– Оно не перенесется вперед, будет создан дополнительный индекс, в котором этот столбец будет первым, но кроме него всегда будет существовать другой индекс, где перечислены все измерения в том порядке, в котором они указаны в дереве метаданных.

– Общий индекс создается для регистра по умолчанию?

– Индекс, включающий все измерения в том порядке, в котором они указаны в дереве метаданных, создается по умолчанию. Наиболее часто используемые – лучше выносить вверх.

Например, у вас есть регистр «Товары на складах» с измерениями «Склад» и «Номенклатура». Если вы чаще используете отбор по складам, то на первом месте должен быть склад. Но если вы чаще смотрите номенклатуру и вам все равно, на каком складе остаток, то наверху должна быть Номенклатура. А если и так и так, то ставим первым – склад, а для номенклатуры ставим «Индексировать». Для склада ставить «Индексировать» смысла нет, потому что если он идет первым, то индекс по нему в любом случае создастся.

Здесь еще момент такой – если у вас, к примеру, в регистре три измерения, и вы используете запрос, в котором идет отбор по первому и по третьему измерению, а по второму измерению отбора нет, то будет частичное использование индекса. Он с помощью индекса отберет данные по первому изменению, и дальше уже по этому набору пробежит оператором Filter и отберет все остальное. А если у вас указан отбор по всем трем измерениям, то он индексом сразу выдернет целиком ту часть, которую нужно. Поэтому даже если вы знаете, что у вас во втором измерении только одно значение – не надо лениться, лучше все равно включить его в отбор, потому что тогда оптимизатор сможет использовать индекс целиком, а не частично.

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

– Часто в 1С какой-нибудь несложный отчет (например, ведомость по взаиморасчетам с контрагентами) внутри содержит много соединений – из-за того, что многие поля указаны через точку, даже в типовом решении. Плюс, на каждый объект ведется RLS. Смотря на план такого запроса, даже люди, которые отлично себе представляют, что такое TableScan, теряются, потому что это – пять страниц вправо, 10 страниц вниз. Как проанализировать эти 120 кубиков и понять – что, откуда и куда? Есть ли какой-нибудь Whitepaper в таких случаях? Нужно ли вообще пытаться анализировать план запроса, когда в нем так много операторов?

– Если вы нашли запрос, который является источником проблемы, вам нужно понять – почему он тормозит. Что внутри него не так? Не заглядывая в план запроса, мы можем только предполагать, основываясь на знании, опыте и т.д. Точно знать мы не можем. Да, есть общие рекомендации самого SQL-сервера (в MS – свои, у Postgres – свои), есть общие рекомендации фирмы «1С». На сайте ИТС есть отдельный раздел по общим правилам оптимизации запросов. Можно просмотреть, соответствует ли запрос общим требованиям. Если там все нормально, все правильно, то единственный возможный вариант – это лезть в план, потому что больше некуда.

Если огромный запрос получается из-за RLS – можно попробовать выключить RLS или запустить этот же отчет под пользователем, на которого RLS не действует, сравнить. Если скорость примерно одинаковая, значит, дело не в RLS и можно анализировать запрос, на котором этого RLS нет. Если скорость существенно отличается, то, наверное, это RLS виноват.

****************

Данная статья написана по итогам доклада, прочитанного на конференции INFOSTART EVENT 2017 COMMUNITY. Больше статей можно прочитать здесь.

В 2020 году приглашаем всех принять участие в 7 региональных митапах, а также юбилейной INFOSTART EVENT 2020 в Москве.

Выбрать мероприятие.

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо развёрнутое
Свернуть все
1. Крококот 09.08.18 10:38 Сейчас в теме
Пара дополнений/уточнений:
Для эффективности Nested Loops критически важен размер именно внешней таблицы; внутренняя таблица может быть и большой (в этом случае желательно соединение по ключу с хорошей селективностью, правда).
Table Scan (как и Clustered Index Scan) эффективны не только для малых таблиц, но и тогда, когда условия поиска в таблице имеют небольшую селективность, либо когда получается таблица полностью. Если необходимо получить, к примеру, 70% записей таблицы, то проще получить её полностью, чем заморачиваться работой с индексами.
Ну и последнее. Не замечал того, что Index Scan выполняется быстрее, чем Table Scan. Разве в том случае, когда отбор по индексируемым полям задействовать не получается как-то используется то, что индекс сортирован?
3. vitkhv 09.08.18 12:44 Сейчас в теме
(1)
Не замечал того, что Index Scan выполняется быстрее, чем Table Scan.

Если индекс кластерный, формально разницы быть не должно.
4. Dem1urg 309 09.08.18 12:52 Сейчас в теме
(3) Если нужно отобрать из таблицы данные по условию, и условие частично покрывается кластерным индексом, в некоторых случаях IndexScan будет быстрее. Зависит от селективности условия отбора.
6. vitkhv 09.08.18 13:21 Сейчас в теме
Хотелось бы на тестах это увидеть, а то сколько не тестировал разницы не увидел.
16. Dem1urg 309 09.08.18 15:44 Сейчас в теме
(6) In a table without a clustered index (a heap table), data pages are not linked together - so traversing pages requires a lookup into the Index Allocation Map.
A clustered table, however, has it's data pages linked in a doubly linked list - making sequential scans a bit faster

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

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177443(v=sql.105)
26. vitkhv 09.08.18 17:31 Сейчас в теме
(16) Из этого - на HDD будет быстрее, на SSD пофиг. Давно я с HDD не работал, надо будет подключить и протестировать.
7. Крококот 09.08.18 13:25 Сейчас в теме
(4)
Если условие покрывается кластерным индексом хотя бы частично, то будет использован оператор Clustered Index Seek, с использованием в предикате конструкции WHERE. Нет?
2. Alex_CheST 1 09.08.18 12:38 Сейчас в теме
Огромное спасибо. Очень понятно и доступно написано. Я как раз хотел разобраться что это за зверь. А то поверхностно пока знаю
alex-l19041; +1 Ответить
5. vitkhv 09.08.18 13:02 Сейчас в теме
Вообще для MSSQL сервер раньше говорилось, что его соединение методом вложенных циклов самое эффективное, в отличии от соединения хэшированием, которое более эффективно у Oracle. Это даже в рассылке "SQL сервер дело тонкое" отражено.

Да и сортировка слиянием, если таблицы уже отсортированы, тоже проходит быстрее соединения хэшированием.
8. vitkhv 09.08.18 13:54 Сейчас в теме
(5) А адаптивные соединения которые появились в 2016 MSSQL во многом решают проблемы с устареванием статистики. Жаль только они не применимы для архитектуры таблиц 1С.
11. Dem1urg 309 09.08.18 14:35 Сейчас в теме
(8) В новых MS SQL вообще очень много интересных фишек, которые, к сожалению, неприменимы в контексте 1С.
9. boln 1009 09.08.18 14:03 Сейчас в теме
Еще бы только поменьше картинок на архитектурную тематику и сами картинки поменьше размером. Множество картинок с домиками аналогию не усиливает, только текст загромождает.
kuza2000; +1 Ответить
10. Dem1urg 309 09.08.18 14:29 Сейчас в теме
(9) Статья написана на основе доклада. Все картинки - это слайды из презентации.
12. nicxxx 236 09.08.18 15:10 Сейчас в теме
"для подзапроса статистики нет" - вот тут автор неправ. Каждый подзапроса - это выборка откуда-то, другого подзапроса или таблицы. Оптимизатор, раскручивая вложенные подзапросы, добирается до исходной таблицы, а там уже статистика есть. Вот она и будет использоваться в дальнейшем.
15. Dem1urg 309 09.08.18 15:40 Сейчас в теме
(12) Согласитесь, что "качество" такой "раскрученной" статистики может быть сильно не очень. И чем глубже вложенность, тем выше вероятность, что толку от подобной статистики не будет.
kuza2000; +1 Ответить
17. vitkhv 09.08.18 15:49 Сейчас в теме
(15)ну да именно поэтому и тормозят запросы к ВТ РС. Потому как там жуткий подзапрос.
20. nicxxx 236 09.08.18 16:05 Сейчас в теме
(17) Не соглашусь. Подзапроса не жуткий. MSSQL довольно хорошо понимает его и строит план, который изначально предполагает применение даже внешних условий к самому первому оператору ( определение MAX(_period)). Ну а дальше уже проще, работает с ограниченной выборкой.
23. vitkhv 09.08.18 16:33 Сейчас в теме
(20) понимает и строит. Но при выносе max(_period) в #temp таблицу, понимает и строит гораздо лучше, особенно на версиях ниже 2012 сервера. 1С даже из за плохого понимания оптимизатором подзапроса ввела механизм представлений.
kuza2000; +1 Ответить
24. nicxxx 236 09.08.18 17:16 Сейчас в теме
(23) Мне пока не удалось ускорить получение среза таким образом. Наоборот, тратится время на создание временной таблицы и дальнейшее чтение из нее. Планы получаются одинаковые в части формирования временной таблицы с MAX(_period).
25. vitkhv 09.08.18 17:20 Сейчас в теме
(24) А мне удалось и не раз. Теперь после этого только так и пишу. Хотя таблица среза последних, путает карты иногда.

А вообще время на построение #temp таблицы не существенно.

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

А простыми запросы на третьем шаге получаются только с использованием #temp таблиц, правда многоэтажными.
alex-l19041; +1 Ответить
13. nicxxx 236 09.08.18 15:19 Сейчас в теме
Здесь тоже неправда: "Крайне не рекомендую создание индекса через SQL, потому что как только вы сделаете «Обновить конфигурацию базы данных» из конфигуратора, все эти индексы “улетят”,". Никуда они не улетят, пока изменения не коснутся конкретной таблицы с таким индексом, причем нужна именно реструктуризация.
14. Dem1urg 309 09.08.18 15:39 Сейчас в теме
(13) Спасибо за уточнение. Речь шла о том, что созданные "вручную" индексы могут быть уничтожены платформой, т.к. она не знает об их существовании. И что не нужно удивляться, если в определенный момент времени "ручной" индекс внезапно исчезнет.
28. logarifm 1072 12.08.18 19:28 Сейчас в теме
(13) Тут вы частично правы, а частично нет. Это еще много зависит от версии платформы
18. ctpayc 09.08.18 15:51 Сейчас в теме
Вам пора писать книгу "SQL для чайника бухгалтера". А не кажется, что ваши читатели могут быть чуть умнее инфузории? Работа оптимизатора рассмотрена на непонятном уровне, где рассказ о том, как оптимизатор выбирает индексы, как считает время на исполнение каждой операции, как именно определяется оптимальный план.
А по фактическим утверждениям вообще огонь:
1. "Index Scan чуть хуже чем Index Seek, но тоже неплохо, и только Table Scan это плохо". Да, ладно, чем это Clustered Index Scan сильно лучше чем Table Scan? А почему оптимизатор выбирает Table Scan на таблице со 100 записями, хотя у него есть подходящий индекс?
2. "Для подзапросов никакой статистики нет, оптимизатор может только очень приблизительно оценить …". О_О Это сейчас серьезно? Совсем не может оптимизатор с ним справиться... какой глупенький... Вот так и рождаются мифы, что вложенный запрос это всегда плохо и надо всегда переписывать их на временные таблицы. Был у меня такой: "Ну я это где-то слышал...", потом сидел переделывал, все что поисправлял… Он не может определить сколько будет записей, только если во вложенном запросе была группировка! Не надо эту ахинею больше писать - люди в нее верят.
3. "В tempdb надо регулярно выполнять truncate"... Что сказать, без комментариев - позовите Лаврова, он точнее выразит мои мысли.
4. "Индекс, включающий все измерения в том порядке, в котором они указаны в дереве метаданных, создается по умолчанию. Наиболее часто используемые – лучше выносить вверх". А о каком регистре мы для начала говорим? А что если у нас по частоте использования на первом месте измерение Склад, потом Номенклатура, а еще есть Контрагент и Партия... Нам в каком порядке их выносить? И ведь люди последуют этой рекомендации вместо того чтобы подумать и построить порядок правильно...
...
21. nicxxx 236 09.08.18 16:10 Сейчас в теме
(18) Даже если была группировка, он может оценить кардинальность с определенной степенью точности. Довольно высокой степенью.
22. Dem1urg 309 09.08.18 16:17 Сейчас в теме
(18) О, эксперты подтянулись. И зачем было утруждать себя чтением настолько ненужной и глупой статьи?
PoZiTiFFF; mytg; vitkhv; sank84; HiGHT; +5 1 Ответить
29. ADirks 182 13.08.18 10:56 Сейчас в теме
(22) А затем, что 1Сники слепо верят в эту ахинею про "подзапросы - зло" и "tempdb - это благо". И все эту идиотию бездумно повторяют.
30. Dem1urg 309 13.08.18 15:06 Сейчас в теме
(29) Невозможно в рамках 30 минутного доклада рассказать обо всех особенностях формирования планов запросов. Да еще и людям, которые имеют весьма общие представления о реляционных базах данных. Да, в докладе есть неточности, есть упрощения, есть ошибки. И я буду благодарен за указание на них. Но не в формате
"Что сказать, без комментариев - позовите Лаврова, он точнее выразит мои мысли. "


Про "подапросы - зло, tempdb - добро". Нет универсальных рецептов. Все зависит от конкретной ситуации. Но чтобы принять правильное решение нужно понимать как оно там "внутри" работает. Хотя бы в общих чертах.

Напишите хорошую и правильную статью, про подзапросы, tempdb и "вот это вот всё". Все будут только благодарны.
antonio_i; LordKim; +2 Ответить
31. ADirks 182 14.08.18 07:52 Сейчас в теме
(30) В том то и дело, что универсальных рецептов нет, и надо каждый раз думать. Но почему-то мало кто хочет этим заниматься. 1С-никам же методичка от 1С по видимому представляется наивысшим авторитетом, а там такое как раз и пишут.
Хороших же статей и книжек на эту тему чуть более чем дофига.
32. Dem1urg 309 14.08.18 09:44 Сейчас в теме
(31) Если можете что-то порекомендовать - киньте ссылки, прям сюда, в комментарий.
antonio_i; +1 Ответить
27. vitkhv 10.08.18 11:36 Сейчас в теме
(18)
Он не может определить сколько будет записей, только если во вложенном запросе была группировка!


Т.е. если во вложенном подзапросе будет SELECT DISTINCT FROM то уже сможет определить сколько будет записей?
33. ADirks 182 14.08.18 11:12 Сейчас в теме
Неплохая книжка: Дэн Тоу - Настройка SQL для профессионалов
Не тупо "делай раз, делай два", а хорошая методика. Соединения правда пишет в секции WHERE, немного неудобно.
введение: http://www.sql.ru/articles/mssql/2005/122801sql.shtml
скачать в djvu: https://proklondike.net/books/dbobshee/tou_sql_tuning.html
34. ADirks 182 14.08.18 13:54 Сейчас в теме
про tempdb есть хорошая публикация https://infostart.ru/public/850217/
где наглядно показано, что и как происходит, если её нагружать
r.zdorkin; +1 Ответить
35. Solikamsk 2 15.08.18 10:40 Сейчас в теме
Небольшая ошибка в "Соединение слиянием"

Пока .. Цикл
Строка1 = Таблица1[Сч1]
Строка2 = Таблица1[Сч2] //здесь должна быть Таблица2
39. Dem1urg 309 15.08.18 20:14 Сейчас в теме
(35) Да, все верно. В слайде опечатка. Должно быть

Строка2 = Таблица2[Сч2];
36. Solikamsk 2 15.08.18 13:22 Сейчас в теме
И всё-таки мне, господа, совсем не понятен алгоритм "слияния". Допустим:

Таб1: Таб2:

2 ; 1
1 ;

т.е. простые две таблицы. В первой две строки (2;1), во второй одна (1).
По Вашему алгоритму она один раз зайдет в цикл, выйдет и не найдет совпадений...

Что не так?
38. Dem1urg 309 15.08.18 20:12 Сейчас в теме
(36) https://ru.wikipedia.org/wiki/Алгоритм_соединения_слиянием_сортированных_списков

Соединение слиянием может быть выполнено только для отсортированных списков. Именно за счет того, что списки перед слиянием уже отсортированы и достигается существенное снижение вычислительной емкости и количества итераций необходимых для выполнения соединения.
37. Solikamsk 2 15.08.18 13:35 Сейчас в теме
Здесь не слова про сортировку :) Начал в других источниках смотреть.
40. YanSergey 25.09.18 13:38 Сейчас в теме
Просто нужно понимать, что происходит, потому что если у нас, например, есть индекс, и мы думаем, что он используется, а, открыв план запросов, видим там Table Scan – мы ошиблись, индекс не используется.


А разве может быть Table Scan на таблице у которой есть индекс?
Table Scan бывает только у таблиц, у которых отсутствуют индексы, а это непроиндексированная таблица в TempDB.
41. kirinalex 2 20.12.18 12:56 Сейчас в теме
(40)
А разве может быть Table Scan на таблице у которой есть индекс?

почему не может?
42. YanSergey 20.12.18 18:02 Сейчас в теме
(41)
Я имел ввиду, что если у таблицы есть кластерный индекс - то не может быть Table Scan, поскольку кластерный индекс и есть сама таблица. Значит, если и будет сканирование, то или Clustered Index Scan или Index Scan. А в 1С почти все таблицы с кластерным индексом.
kuza2000; +1 Ответить
43. kirinalex 2 25.12.18 08:12 Сейчас в теме
(42) а теперь перечитай свой комментарий в (40))
44. goodron 08.01.19 15:23 Сейчас в теме
Автору + за хорошую статью. На практике чаще использую анализ плана в текстовом виде, в окне командной строки или в "Management Studio".
Кстати, когда используете вывод статистики операций чтения-записи:
SET STATISTICS IO ON;
тут ваш запрос
SET STATISTICS IO OFF;

то надо помнить, что числа "логических чтений" и "физических чтений" выражены в кол-ве страниц размером 8кб, и что "логическое чтение"=чтение из буфера страниц (памяти), а "физическое чтение"=чтение с диска

A Microsoft white paper that's a part of the Retired SQL Server 2000 technical documentation (p387, originally from SQL Server Architecture (SQL Server 2000)) has a good definition:

The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.
45. akor77 918 16.03.19 11:26 Сейчас в теме
Статей много, а вот отличных статей мало. Статья блестящая!
antonio_i; +1 Ответить
46. kuza2000 110 12.01.20 01:37 Сейчас в теме
Как-то ну очень странно описано hash join... Причем здесь, вообще md5?
Не люблю критиковать статьи. Понятно, что критиковать проще, чем писать, но тут мимо пройти не смог.
Как описано в статье - это создает совершенно неверное представление о работе hash join и хэш-таблиц в принципе.

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

То есть, алгоритм поиска в хэш-таблице совсем другой. Берем ключ, применяем хэш-функцию, она возвращает адрес корзины (bucket). По этому адресу берем строки, и ищем уже в них, сравнивая ключ.

В википедии, кстати, хорошо описана хэш-таблица: https://ru.wikipedia.org/wiki/Хеш-таблица
Оставьте свое сообщение

См. также

3 онлайн-курса по 1С-программированию: обмен данными, расчетные задачи и бухгалтерские задачи с 12 мая по 8 июля 2020 г. Промо

Практика программирования v8 Бесплатно (free)

Пакет из 3-х курсов по 1С-программированию. Основная цель - сформировать у слушателей практические навыки, связанные с реализацией задач обмена для прикладных решений, работающих на платформе “1С:Предприятие”, а также с разработкой прикладных решений, предназначенных для автоматизации расчета заработной платы и задач бухгалтерского учета.

22.04.2020    3332    23    infostart    2    

Выявляем и оптимизируем ресурсоемкие запросы 1С:Предприятия

Производительность и оптимизация (HighLoad) Администрирование СУБД Технологический журнал Структура метаданных v8::Запросы Бесплатно (free)

Обычно предметом оптимизации являются заранее определенные ключевые операции, т.е. действия, время выполнения которых значимо для пользователей. Причиной недостаточно быстрого выполнения ключевых операций может быть неоптимальный код, неоптимальные запросы либо же проблемы параллельности. Если выясняется, что основная доля времени выполнения ключевой операции приходится на запросы, то осуществляется оптимизация этих запросов. При высоких нагрузках на сервер СУБД в оптимизации нуждаются и те запросы, которые потребляют наибольшие ресурсы. Такие запросы не обязательно связаны с ключевыми операциями и заранее неизвестны. Но их также легко выявить и определить контекст их выполнения, чтобы оптимизировать стандартными методами.

24.05.2020    4444    0    DataReducer    22    

СКД. Использование Менеджера временных таблиц в системе компоновки

Практика программирования v8 v8::Запросы v8::СКД 1cv8.cf Бесплатно (free)

С выпуском платформы 8.3.17 фирма 1С анонсировала возможность передачи в механизм системы компоновки данных менеджера временных таблиц. Платформа вышла из беты, самое время разобраться с новым механизмом, тем более в комментариях я встретил непонимание принципов работы этого нововведения. Постараюсь кратко.

29.04.2020    3849    0    the1    27    

Работа с запросами в 1С СКД. Отладка СКД

Практика программирования v8::Запросы v8::СКД Россия Бесплатно (free)

В предыдущем разделе мы изучали с вами особенности работы с запросами в СКД. Для изучения (отладки) работы различных схем компоновки данных я использовал консоль компоновки данных.

15.04.2020    2200    0    ProfessionStore    2    

Использование программных перечислений, ч.1: строковые константы Промо

Практика программирования v8 1cv8.cf Бесплатно (free)

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

10.12.2016    34309    0    unichkin    45    

Работа с запросами в 1С СКД. Особенности работы запросов в СКД. Часть 3

Практика программирования v8::Запросы v8::СКД Россия Бесплатно (free)

Почему при отборе по контрагенту наш отчет работает неправильно. Посмотрим на примере другого отчета, в котором нам нужно выбрать данные из расходных накладных и связанные с номенклатурой данные по ВСЕМ заявкам.

13.04.2020    3720    0    ProfessionStore    4    

Работа с запросами в 1С СКД. Особенности работы запросов в СКД. Часть 2

Практика программирования v8::Запросы v8::СКД Россия Бесплатно (free)

Все примеры разобраны с помощью консолей компоновки данных, речь о которых пойдет в предпоследнем разделе статьи.

12.04.2020    2810    0    ProfessionStore    11    

Работа с запросами в 1С СКД. Особенности работы запросов в СКД. Часть 1

Практика программирования v8::Запросы v8::СКД 1cv8.cf Россия Бесплатно (free)

Все примеры разобраны с помощью консолей компоновки данных, речь о которых пойдет в предпоследнем разделе статьи.

11.04.2020    3217    0    ProfessionStore    8    

Вспомогательные инструкции в коде 1С Промо

Практика программирования v8 1cv8.cf Бесплатно (free)

Помогаем редактору кода 1С помогать нам писать и анализировать код.

15.10.2018    26332    0    tormozit    100    

Работа с запросами в 1С СКД. Язык выражений СКД и подмена запросов

Практика программирования v8::Запросы v8::СКД Россия Бесплатно (free)

Хотя эта статья называется «Работа с запросами в 1С СКД», нельзя не упомянуть про язык выражений СКД. Да – у системы компоновки данных есть свой язык! Это не язык платформы 1С, это не язык запросов. Это еще один язык, который используется 1С в СКД для обработки полученного набора (ов) данных.

11.04.2020    3769    0    ProfessionStore    23    

Работа с запросами в 1С СКД. Примеры использования расширения языка запросов СКД

Практика программирования v8::Запросы v8::СКД Россия Бесплатно (free)

Все тонкости использования расширения языка запросов 1С для СКД вы можете изучить по ссылкам, которые приведены в предыдущей статье. Здесь мы рассмотрим конкретные кейсы применения данного расширения.

10.04.2020    3607    0    ProfessionStore    4    

Запросы 1С СКД. Возможности и ограничения

Практика программирования v8::Запросы v8::СКД Россия Бесплатно (free)

В этом цикле статей мы рассмотрим особенности работы системы компоновки данных с запросами в 1С.

10.04.2020    4621    0    ProfessionStore    14    

Оформление и рефакторинг сложных логических выражений Промо

Практика программирования v8 Россия Бесплатно (free)

В сложных логических выражениях нередко самому автору спустя какое-то время тяжело разобраться, не говоря уже о других программистах. Предлагаемая методика позволяет повысить наглядность таких выражений путем оформления в виде И-ИЛИ дерева и одновременно выполнять их рефакторинг.

20.09.2012    73827    0    tormozit    129    

Планы запросов - это просто! Разбор оптимизаций запросов PostgreSQL на живых примерах

Производительность и оптимизация (HighLoad) v8::Запросы Бесплатно (free)

Проблема быстродействия 1С напрямую зависит от производительности запросов. Но как понять механику работы СУБД с помощью плана запроса? Андрей Овсянкин и Никита Грызлов на конференции Infostart Event 2019 Inception подробно рассмотрели алгоритм работы с планом запроса СУБД PostgreSQL, полученным из технологического журнала, и рассказали, на что обратить внимание, чтобы оптимизировать работу системы.

17.02.2020    7078    0    Evil Beaver    13    

[СКД] Программное создание схемы компоновки данных

Практика программирования v8 v8::СКД 1cv8.cf Бесплатно (free)

Сделаем отчет на СКД полностью программно, без использования макета "схема компоновки данных".

15.01.2020    15915    0    John_d    22    

Запись значения в поле ввода/формы со срабатыванием события ПриИзменении Промо

Практика программирования v8 1cv8.cf Россия Бесплатно (free)

Иногда возникает необходимость после записи значения в какое либо поле ввода/формы вызвать для него обработчик события ПриИзменении, а о вызове самого события приходится только мечтать. В этой статье приводится программный способ вызова этого события.

11.07.2007    44880    0    tormozit    38    

Нумерация строк в запросе методами платформы

Практика программирования v8::Запросы 1cv8.cf Казахстан Бесплатно (free)

Простая реализация с помощью встроенных методов. Пригодится тем, кому нужно пронумеровать запрос без СКД и переборов.

09.01.2020    6481    0    user602678_maxipunchik    25    

Налогообложение сотрудников, работающих по ГПХ (доработка 1С:Зарплата и управление персоналом КОРП, редакция 3.1)

Зарплата Практика программирования v8::СПР v8::Запросы ЗУП3.x Россия БУ ФОМС, ПФ, ФСС Бесплатно (free)

Цель этой статьи - быстро доработать ЗУП для налогообложения сотрудников, работающих по ГПХ. Эта статья решает задачи, связанные с отсутствием в ЗУП функционала по обложению налогом работников ГПХ. На вопросы ниже вы найдете ответ в статье и решите эту задачу. Как настроить ЗУП 3, чтобы по договору ГПХ начислялись страховые взносы? У нас ситуация: есть договор ГПХ с одним человеком, который носит систематический характер и надо по нему начислять взносы, но ЗУП 3 не начисляет.

26.11.2019    3130    0    Lupeykin    7    

Таблица значений. Нюансы

Практика программирования v8 Бесплатно (free)

Обзор некоторых аспектов использования общеизвестного инструмента 1С.

01.10.2019    25659    0    Yashazz    45    

Как сделать из &НаКлиентеНаСервереБезКонтекста почти &НаКлиентеНаСервере Промо

Практика программирования v8 1cv8.cf Россия Бесплатно (free)

Как сделать метод формы, доступный на клиенте и на сервере одновременно, и сохранить при этом удобство разработки

10.09.2017    40705    0    tormozit    72    

СКД. Отчеты с картинками

Практика программирования Работа с интерфейсом v8::СКД 1cv8.cf Бесплатно (free)

Решение популярных кейсов с картинками в отчетах на СКД.

25.09.2019    16144    0    YPermitin    33    

Группировка данных в запросе (СГРУППИРОВАТЬ ПО [ГРУППИРУЮЩИМ НАБОРАМ], ИТОГИ ПО [ОБЩИЕ])

Практика программирования v8 v8::Запросы 1cv8.cf Бесплатно (free)

Для группировки данных в языке запросов 1С существуют конструкции СГРУППИРОВАТЬ ПО [ГРУППИРУЮЩИМ НАБОРАМ], ИТОГИ ПО [ОБЩИЕ]. Для новичков назначение этих конструкций не всегда очевидно, попробуем разобраться на примерах, для чего предназначена каждая из них и в чем отличие от аналогичных конструкций в языке SQL.

19.09.2019    14031    0    sertak    9    

Количество NULL в запросе

Практика программирования v8 v8::Запросы Россия Бесплатно (free)

При определении количества элементов в виде "NULL" в результирующей таблице запроса нам возвращается значение "0".

17.09.2019    3113    0    toxilamer    18    

Выгрузка документа по условию Промо

Практика программирования Разработка v8 Бесплатно (free)

Что делать, если документы нужно выгружать не все подряд, а по какому-то фильтру: статусу, дате, набору условий... А что если он соответствовал этим условиям, а потом перестал? А если потом опять начал? Такие ситуации заставили попотеть не одного программиста.

25.04.2019    14252    0    m-rv    2    

[Шпаргалка] Программное создание элементов формы

Практика программирования Работа с интерфейсом v8 1cv8.cf Бесплатно (free)

Программное создание практически всех популярных элементов формы.

06.09.2019    35235    0    rpgshnik    62    

Агрегатные функции СКД, о которых мало кто знает

Практика программирования v8 v8::СКД 1cv8.cf Бесплатно (free)

Пользуетесь ли Вы всеми возможными агрегатными функциями, которые предоставляет система компоновки данных? Если Вы используете только: СУММА, КОЛИЧЕСТВО, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ, то эта статья для Вас.

05.09.2019    37396    0    ids79    52    

Функции СКД: ВычислитьВыражение, ВычислитьВыражениеСГруппировкойМассив

Практика программирования v8 v8::СКД 1cv8.cf Бесплатно (free)

Подробное описание и использование внутренних функций системы компоновки данных: Вычислить, ВычислитьВыражение, ВычислитьВыражениеСГруппировкойМассив, ВычислитьВыражениеСГруппировкойТаблицаЗначений.

08.08.2019    56956    0    ids79    37    

Как прикрутить ГУИД к регистру сведений Промо

Практика программирования Перенос данных из 1C8 в 1C8 Разработка v8 Бесплатно (free)

... и немного теории обмена данными. В частности, разберем боль всех, кто пишет небанальные обмены данными: как набору записей регистра сведений назначить гуид и далее использовать его в обмене для идентификации этого набора.

16.04.2019    17846    0    m-rv    17    

СКД - наборы данных и связи между ними, создание собственной иерархии, вложенные отчеты

Практика программирования v8 v8::СКД 1cv8.cf Бесплатно (free)

Набор данных объект. Использование в схеме компоновки нескольких наборов данных. Различные варианты связи наборов: объединение, соединение. Использование иерархии в отчетах на СКД. Создание собственной иерархии, иерархия детальных записей. Использование вложенных схем в отчетах на СКД.

26.07.2019    43162    0    ids79    11    

"Меньше копипаста!", или как Вася универсальную процедуру писал

Практика программирования Разработка v8 v8::СКД 1cv8.cf Бесплатно (free)

Программист Вася разбирает подход создания универсальных методов на примере программного вывода СКД.

04.07.2019    16530    0    SeiOkami    50    

Выборка дней рождения

Практика программирования Разработка v8 v8::Запросы 1cv8.cf Бесплатно (free)

Выборка позволяет создать таблицу значений из справочника (или из чего-то подобного) с днями рождения с наперед заданными условиями.

26.06.2019    2932    0    user654641_yaga_m    6    

Как сделать запрос на изменение данных Промо

Практика программирования v8 v8::Запросы 1cv8.cf Бесплатно (free)

В статье приведены особенности внутренней архитектуры и примеры работы с расширением языка запросов 1С.

01.06.2018    27525    0    m-rv    21    

Создание отчетов с помощью СКД - основные понятия и элементы

Практика программирования Математика и алгоритмы v8 v8::СКД Бесплатно (free)

Основные принципы работы СКД. Понятия схемы компоновки и макета компоновки. Описание основных элементов схемы компоновки: наборы данных, поля, вычисляемые поля, ресурсы, параметры.

25.06.2019    42607    0    ids79    20    

Многопоточное ускорение однопользовательских нагрузок в 1С + Microsoft SQL Server 2017

Практика программирования Производительность и оптимизация (HighLoad) v8 v8::Запросы Бесплатно (free)

Взаимодействие с Microsoft SQL Server нередко вызывает трудности у 1С-ников, а потому интересны любые моменты, связанные с его использованием. О своем опыте работы с новым SQL Server 2017 участникам конференции Infostart-2018 рассказал директор ООО «Аналитика софт» Дмитрий Дудин.

11.06.2019    21548    0    dmurk    144    

Почему вообще работает мой запрос? или Ещё раз о планах запросов

Математика и алгоритмы Практика программирования Разработка v8::Запросы Бесплатно (free)

Другие статьи на эту тему объясняют, что такое план выполнения запроса, но не рассказывают о том, как его получить. Эта заметка призвана заполнить этот пробел. Её цель - популяризировать общедоступные инструменты получения плана запроса среди разработчиков, которые ещё не начали их использовать.

10.06.2019    8679    0    DataReducer    12    

Метод формирования движений в типовых регистрах нетиповыми регистраторами Промо

Практика программирования v8 1cv8.cf Бесплатно (free)

Вариант решения задач с проведением по типовым регистрам нетиповыми регистраторами. Зачем - чтобы при сравнении конфигурации не обращать внимание на свойства регистров и исключить вероятность допущения горькой оплошности при обновлении информационных баз, заменив типы регистраторов основной конфигурации типами конфигурации поставщика. Для программных продуктов, имеющих в своем составе метаданных документ "Корректировка регистров"("Корректировка записей регистров").

05.12.2017    25809    0    itriot11    34    

Работа с запросами. Получение последней цены товара из табличной части документа. Несколько вариантов

Практика программирования Разработка v8 v8::Запросы УПП1 Бесплатно (free)

На нескольких собеседованиях это спрашивали, решил поделиться. Обычно я использую вариант №2. Остальные варианты нашел в интернете.

05.06.2019    5225    0    wowik    26    

Нюансы преобразования запроса СКД

Практика программирования v8 v8::Запросы v8::СКД 1cv8.cf Бесплатно (free)

Описание некоторых нюансов преобразования запроса в СКД.

31.05.2019    12161    0    waol    14    

Регистры накопления. Структура хранения в базе данных

Практика программирования Разработка v8 1cv8.cf Бесплатно (free)

Структура хранения регистров накопления в базе данных для платформы 1С:Предприятие 8.x. Первая часть в серии публикаций.

16.05.2019    35601    0    YPermitin    30    

Использование классов .Net в 1С для новичков Промо

Практика программирования Разработка внешних компонент Универсальные функции v7.7 v8 Бесплатно (free)

Руководство для новичков. Написав статью http://infostart.ru/public/238584/, я понял, что многие не понимают того, что написано. Поэтому в этой статье постараюсь более подробно остановиться на азах и без кода на вражеском языке (C#)

27.01.2016    71306    0    Serginio    108    

Не вполне очевидные приемы в конструкторе запросов

Практика программирования Разработка v8 v8::Запросы 1cv8.cf Бесплатно (free)

Приёмы работы с конструктором запросов (4 приёма).

23.04.2019    4201    0    ignor    2    

5 простых шагов и 15 минут на разворачивание инструмента мониторинга проблем производительности базы 1С

Производительность и оптимизация (HighLoad) v8 Бесплатно (free)

В этой статье мы разберем механизм использования конфигурации "Анализ технологического журнала" на практике, и всего через 15 минут работы вы получите функциональный, удобный инструмент мониторинга проблем производительности базы 1С.

18.04.2019    26410    0    ivanov660    77    

Заметки по SQL: Запрос, получающий изменения ресурса в регистрах сведений по датам изменения за период

Практика программирования v8 v8::Запросы 1cv8.cf Россия Бесплатно (free)

В статье описан метод получения изменения ресурса регистра сведений по датам изменения, построенный на основе запроса "Сумма накоплением".

11.04.2019    4420    0    IVC_goal    11    

Автоматические и управляемые блокировки применительно к типовым конфигурациям 1С Промо

Математика и алгоритмы Практика программирования v8 v8::blocking 1cv8.cf Бесплатно (free)

Основные принципы работы с режимами автоматических и управляемых блокировок в 1С Предприятие 8. Теория и применение в типовых конфигурациях: БП, УТ, ЕРП

10.11.2018    30767    0    ids79    40    

Альтернативный вариант расчета возраста (лет, месяцев, дней) запросом

Практика программирования Разработка v8::Запросы 1cv8.cf Украина Бесплатно (free)

Появилась задача вычислить возраст человека на определенную дату. Сначала был написан вариант кодом, через цикл, но замер производительности показал, что подобное решение "тяжеловесное". Более того, я знаю, что задача будет повторяться, причем данные нужно будет выдавать запросом. Было решено сделать определенный шаблон, на основании которого можно было бы писать другие решения.

11.04.2019    3424    0    lex_hrabovskyi    12    

О расширениях замолвите слово...

Практика программирования Разработка v8 Бесплатно (free)

О чём стоит задуматься при принятии решения о создании расширения конфигурации…

07.04.2019    29590    0    ellavs    126    

Git-репозитории для 1С-кода (опыт использования при небольших проектах)

Практика программирования v8 Бесплатно (free)

Инструкции по взаимодействию с Git-репозиторием, которые писались для тех наших программистов, которые вообще никогда не работали с Git (руководства в духе "Как получить код из git-репозитория?", "Как отправить код в git-репозиторий")...

28.03.2019    23590    0    ellavs    86    

Тестер: частые вопросы Промо

Практика программирования v8 Бесплатно (free)

Ошибкам бой - тесты норма жизни!

25.07.2018    26363    0    grumagargler    26    

Добавление отчетов в типовые конфигурации 1С

Практика программирования Универсальные функции БСП (Библиотека стандартных подсистем) v8::УФ v8::СКД 1cv8.cf Бесплатно (free)

Описание различных способов добавления общих и контекстных отчетов в конфигурации 1С, построенные на базе БСП. Основные моменты и нюансы.

07.03.2019    45400    0    ids79    45    

Заметки по SQL: Генерация ряда дат и данные из периодических регистров на каждый день

Практика программирования Разработка v8::Запросы Бесплатно (free)

В публикации описывается запрос на классическом SQL, позволяющий получить периодические данные на каждый день на примере формирования цен номенклатуры и остатков регистра накопления "ТоварыНаСкладах". Генератор ряда дат для запроса, построен на основе порождающего запроса.

05.03.2019    4344    0    IVC_goal    0    

Функциональные опции 1С 8.3 – все возможные варианты использования

Практика программирования Разработка v8::УФ 1cv8.cf Бесплатно (free)

Примеры использования функциональных опций для управления интерфейсом и элементами управляемых форм, с различными вариантами хранения значений: константа, реквизит справочника, ресурс регистра сведений.

22.02.2019    29154    0    ids79    6    

Информирование пользователя. Работа с объектом «СообщениеПользователю»

Практика программирования Разработка v8::УФ 1cv8.cf Бесплатно (free)

Различные варианты и нюансы использования объекта СообщениеПользователю и другие способы информирования пользователя: без привязки к какой-либо форме и с выводом сообщения в отдельном окне.

15.02.2019    39199    0    ids79    47