Почему оптимизатор SqlServer становится настолько перепутанным с параметрами?

Question

Почему оптимизатор SqlServer становится настолько перепутанным с параметрами?

Ниже для BigQuery Standard SQL и отвечает только на точный вопрос в заголовке вашего сообщения:

Как получить комбинацию значений из одного столбца?

#standardSQL
CREATE TEMP FUNCTION test(a ARRAY<INT64>) 
RETURNS ARRAY<STRING>
LANGUAGE js AS '''
  var combine = function(a) {
    var fn = function(n, src, got, all) {
      if (n == 0) {
        if (got.length > 0) {
          all[all.length] = got;
        } return;
      }
      for (var j = 0; j < src.length; j++) {
        fn(n - 1, src.slice(j + 1), got.concat([src[j]]), all);
      } return;
    }
    var all = [];
    for (var i = 1; i < a.length; i++) {
      fn(i, a, [], all);
    }
    all.push(a);
    return all;
  } 
  return combine(a)
''';
WITH types AS (
  SELECT DISTINCT type, CAST(DENSE_RANK() OVER(ORDER BY type) AS STRING) type_num
  FROM `project.dataset.order`
  WHERE status = 'OK'
)
SELECT items, STRING_AGG(type ORDER BY type_num) types
FROM UNNEST(test(GENERATE_ARRAY(1,(SELECT COUNT(1) FROM types)))) AS items, 
UNNEST(SPLIT(items)) AS pos
JOIN types ON pos = type_num
GROUP BY items

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

#standardSQL
CREATE TEMP FUNCTION test(a ARRAY<INT64>) 
RETURNS ARRAY<STRING>
LANGUAGE js AS '''
  var combine = function(a) {
    var fn = function(n, src, got, all) {
      if (n == 0) {
        if (got.length > 0) {
          all[all.length] = got;
        } return;
      }
      for (var j = 0; j < src.length; j++) {
        fn(n - 1, src.slice(j + 1), got.concat([src[j]]), all);
      } return;
    }
    var all = [];
    for (var i = 1; i < a.length; i++) {
      fn(i, a, [], all);
    }
    all.push(a);
    return all;
  } 
  return combine(a)
''';
WITH `project.dataset.order` AS (
  SELECT '2019-01-02' dt, 'Shirt' type, 'Cashless' payment, 101 customer_no, 'Cancel' status UNION ALL
  SELECT '2019-01-02', 'Jeans', 'Cashless', 133, 'OK' UNION ALL
  SELECT '2019-01-02', 'Jeans', 'Cash', 102, 'OK' UNION ALL
  SELECT '2019-01-02', 'Cap', 'Cash', 144, 'OK' UNION ALL
  SELECT '2019-01-02', 'Shirt', 'Cash', 132, 'OK' UNION ALL
  SELECT '2019-01-01', 'Jeans', 'Cash', 111, 'Cancel' UNION ALL
  SELECT '2019-01-01', 'Cap', 'Cash', 141, 'OK' UNION ALL
  SELECT '2019-01-01', 'Shirt', 'Cash', 101, 'OK' UNION ALL
  SELECT '2019-01-01', 'Jeans', 'Cash', 105, 'OK' 
), types AS (
  SELECT DISTINCT type, CAST(DENSE_RANK() OVER(ORDER BY type) AS STRING) type_num
  FROM `project.dataset.order`
  WHERE status = 'OK'
)
SELECT items, STRING_AGG(type ORDER BY type_num) types
FROM UNNEST(test(GENERATE_ARRAY(1,(SELECT COUNT(1) FROM types)))) AS items, 
UNNEST(SPLIT(items)) AS pos
JOIN types ON pos = type_num
GROUP BY items

с результатом

Row items   types    
1   1       Cap  
2   2       Jeans    
3   3       Shirt    
4   1,2     Cap,Jeans    
5   1,3     Cap,Shirt    
6   2,3     Jeans,Shirt  
7   1,2,3   Cap,Jeans,Shirt

7

optimization sql-server sql

задан Community 23 May 2017 в 12:26

8 ответов

Другие вопросы по тегам:

optimization sql-server sql

Похожие вопросы:

score 5 · Answer 1

1 в 10 дает неправильный план, который кэшируется.

ПЕРЕКОМПИЛИРУЙТЕ добавляют издержки, маскирование позволяет каждому параметру быть оцененным на своих собственных достоинствах (очень просто).

Неправильным планом, что, если 1 в 10 генерирует сканирование на индексе 1, но другие 9 производят искание на индексе 2? например, 1 в 10 - скажем, 50% строк?

Править: другие вопросы

Редактирование 2:

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

Эта статья объясняет...

...parameter values are sniffed during compilation or recompilation...

Наконец (редактируют 3):

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

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

score 2 · Answer 2

У меня неоднократно была эта проблема при перемещении моего кода от тестового сервера до производства - на двух различных сборках SQL Server 2005. Я думаю, что существуют некоторые большие проблемы со сниффингом параметра в некоторых сборках SQL Server 2005. У меня никогда не было этой проблемы на dev сервере, или на двух локальных полях выпуска разработчика. Я никогда не видел, что он он такая большая проблема на SQL Server 2000 или любой версии, возвращающейся к 6,5 также.

Случаи, где я нашел его, единственное обходное решение, должны были использовать маскирование параметра, и я все еще надеюсь, что DBAs исправит рабочий сервер к SP3, таким образом, это, возможно, уйдет. Вещи, которые не работали:

использование С ПЕРЕКОМПИЛИРОВАЛО подсказку на ДОЛЖНОСТНОМ ЛИЦЕ или в самом SP.
отбрасывание и воссоздание SP
использование sp_recompile

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

О, и если SQL Server не может обработать это без маскирования, они должны добавить модификатор параметра NOSNIFF или что-то. Что происходит, если Вы маскируете все свои параметры, таким образом, у Вас есть @Something_parm и @Something_var, и кто-то изменяет код для использования несправедливости все до одного внезапного, у Вас есть проблема сниффинга снова? Плюс Вы загрязняют пространство имен в SP. Все они, SPS, который я "фиксирую", сводит меня с ума, потому что я знаю, что они собираются быть кошмаром обслуживания для менее опытного штата, я буду передавать этот проект к одному дню.

score 1 · Answer 3

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

EXECUTE MyProcedure [parameters] WITH RECOMPILE

WITH RECOMPILE опция вынудит SQL Server проигнорировать кэшируемый план.

score 1 · Answer 4

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

score 0 · Answer 5

Как обозначено это быть проблемой компиляции. Это выходит, все еще происходят, если Вы возвращаетесь процедура? Одна вещь, которую можно попробовать, если это происходит снова для принуждения перекомпиляции, состоит в том, чтобы использовать:

sp_recompile [@objname =] 'объект'

Прямо от BOL в отношении @objname параметра:

Квалифицированное или неполное название хранимой процедуры, триггера, таблицы или представления в текущей базе данных. объект является nvarchar (776) без значения по умолчанию. Если объект будет названием хранимой процедуры или триггера, то хранимая процедура или триггер будут перекомпилированы в следующий раз, когда это выполняется. Если объект является названием таблицы или представления, все хранимые процедуры, которые ссылаются на таблицу, или представление будет перекомпилировано в следующий раз, когда они выполняются.

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

score 0 · Answer 6

Есть ли шанс, что обеспечиваемое значение параметра иногда является не интервалом?

0

ответ дан 7 December 2019 в 03:21

score 0 · Answer 7

Является ли каждый запрос ссылкой на параметр, сравнивающий его со значениями int, без функций и без приведения?

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

score 0 · Answer 8

Это проблема с кэшированием плана, и она не всегда связана с параметрами, как это было в вашем сценарии ,

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

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

Оказывается, часто используемый хранимый процесс был перекомпилирован в тот момент, когда таблица была почти пуста, и он кэшировал чрезвычайно плохой план выполнения («эй, здесь только 50 записей, может также выполнить сканирование таблицы!») ,