Каковы различные способы замены ISNULL () в предложении WHERE, который использует только литеральные значения?

Что это не о:

Это не вопрос о catch-all запросов , которые принимают пользовательские ввод или используют переменные.

Это строго о запросах, в которых ISNULL () используется в выражении WHERE , чтобы заменить значения NULL канареечным значением для сравнения с предикат и различные способы переписать эти запросы как SARGable в SQL Server.

Почему у вас нет места там?

Наш примерный запрос относится к локальной копии базы данных Stack Overflow на SQL Server 2016 и ищет пользователей с возрастом NULL или возрастом <18.

  SELECT COUNT (*)
FROM dbo.Users AS u
ГДЕ ISNULL (u.Age, 17) 18;
 

В плане запроса отображается Сканирование достаточно продуманного некластеризованного индекса.

 Орехи

Оператор сканирования показывает (благодаря дополнениям к реальному плану плана выполнения в более поздних версиях SQL Server), которые мы читаем каждую строку stinkin.

 Орехи

В целом, мы читаем 9157 и используем примерно половину секунды времени процессора:

  Таблица «Пользователи». Число сканирования 1, логическое чтение 9157, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время процессора = 485 мс, прошедшее время = 483 мс.
 

Вопрос: Каковы способы переписать этот запрос, чтобы сделать его более эффективным и, возможно, даже SARGable?

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

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

Спасибо!

51 голос | спросил sp_BlitzErik 27 MarpmMon, 27 Mar 2017 18:30:57 +03002017-03-27T18:30:57+03:0006 2017, 18:30:57

6 ответов


51

Раздел ответов

Существуют различные способы переписать это с использованием различных конструкций T-SQL. Мы рассмотрим все плюсы и минусы и сделаем общее сравнение ниже.

Сначала : с помощью OR

  SELECT COUNT (*)
FROM dbo.Users AS u
WHERE u.Age <18
ИЛИ u.Age IS NULL;
 

Использование OR дает нам более эффективный план Seek, который читает точное количество строк, которые нам нужны, однако он добавляет то, что технический мир вызывает весь беспорядок malarkey к плану запроса.

 Орехи

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

 Орехи

  Таблица «Пользователи». Число сканирования 2, логическое чтение 8233, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 469 мс, прошедшее время = 473 мс.
 

Секундомер : использование производных таблиц с UNION ALL Наш запрос также можно переписать следующим образом

  SELECT SUM (Записи)
ИЗ
(
    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи);
 

Это дает один и тот же план с гораздо меньшим количеством malarkey и более очевидную степень честности в отношении того, сколько раз запрос искался (искал?) в.

 Орехи

Он выполняет то же количество чтений (8233), что и запрос OR , но бреет около 100 мс времени процессора.

  Время процессора = 313 мс, прошедшее время = 315 мс.
 

Однако вы должны быть осторожны really , потому что если этот план попытается перейти параллельно, две отдельные операции COUNT будут сериализованы, потому что каждый из них считается глобальным скалярным агрегатом. Если мы вынудим параллельный план с помощью Trace Flag 8649, проблема станет очевидной.

  SELECT SUM (Записи)
ИЗ
(
    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи)
ВАРИАНТ (QUERYTRACEON 8649);
 

 Орехи

Этого можно избежать, слегка изменив наш запрос.

  SELECT SUM (Записи)
ИЗ
(
    ВЫБРАТЬ 1
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    ВЫБРАТЬ 1
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи)
ВАРИАНТ (QUERYTRACEON 8649);
 

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

 Орехи

Для того, что это стоит, полностью параллельная версия имеет хорошую выгоду. За счет около 100 дополнительных чтений и около 90 мс дополнительного времени процессора истекшее время сокращается до 93 мс.

  Таблица «Пользователи». Число сканирования 12, логическое чтение 8317, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 500 мс, прошедшее время = 93 мс.
 

Как насчет CROSS APPLY? Нет ответа без магии CROSS APPLY !

К сожалению, у нас больше проблем с COUNT .

  SELECT SUM (Записи)
FROM dbo.Users AS u
КРЕСТ ПРИМЕНЯЕТСЯ
(
    SELECT COUNT (Id)
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    И u2.Age <18

    СОЮЗ ВСЕ

    SELECT COUNT (Id)
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    И u2.Age IS NULL
) x (Записи);
 

Этот план ужасен. Это тот план, в котором вы закончите, когда вы появляетесь последним в День Святого Патрика. Несмотря на красивую параллель, по какой-то причине он сканирует PK /CX. Еа. План имеет стоимость 2198 запросов долларов.

 Орехи

  Таблица «Пользователи». Число сканирования 7, логическое чтение 31676233, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.
Таблица «Рабочий стол». Число сканирования 0, логическое чтение 0, физическое чтение 0, чтение вперед 0, логическое чтение lob 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 29532 мс, прошедшее время = 5828 мс.
 

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

  SELECT SUM (Записи)
FROM dbo.Users AS u
КРЕСТ ПРИМЕНЯЕТСЯ
(
    SELECT COUNT (Id)
    FROM dbo.Users AS u2 WITH (INDEX (ix_Id_Age))
    WHERE u2.Id = u.Id
    И u2.Age <18

    СОЮЗ ВСЕ

    SELECT COUNT (Id)
    FROM dbo.Users AS u2 WITH (INDEX (ix_Id_Age))
    WHERE u2.Id = u.Id
    И u2.Age IS NULL
) x (Записи);
 

Эй, он ищет! Проверьте вас там. Также обратите внимание, что с магией CROSS APPLY нам не нужно делать ничего глупого, чтобы иметь в основном полностью параллельный план.

 Орехи

  Таблица «Пользователи». Число сканирования 5277838, логическое чтение 31685303, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.
Таблица «Рабочий стол». Число сканирования 0, логическое чтение 0, физическое чтение 0, чтение вперед 0, логическое чтение lob 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 27625 мс, прошедшее время = 4909 мс.
 

Cross apply действительно улучшается без использования COUNT .

  SELECT SUM (Записи)
FROM dbo.Users AS u
КРЕСТ ПРИМЕНЯЕТСЯ
(
    ВЫБРАТЬ 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    И u2.Age <18

    СОЮЗ ВСЕ

    ВЫБРАТЬ 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    И u2.Age IS NULL
) x (Записи);
 

План выглядит неплохо, но чтение и процессор не являются улучшением.

 Орехи

  Таблица «Пользователи». Число сканирования 20, логическое чтение 17564, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.
Таблица «Рабочий файл». Число сканирования 0, логическое чтение 0, физическое чтение 0, чтение вперед 0, логическое чтение lob 0, физическое чтение lob 0, чтение с чтением lob 0.
Таблица «Рабочий стол». Число сканирования 0, логическое чтение 0, физическое чтение 0, чтение вперед 0, логическое чтение lob 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 4844 мс, прошедшее время = 863 мс.
 

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

  SELECT COUNT (u.Id)
FROM dbo.Users AS u
ПРИСОЕДИНИТЬСЯ
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x ON x.Id = u.Id;
 

Реляционная алгебра : Чтобы быть тщательным, и чтобы Джо Селко не преследовал мои мечты, нам нужно хотя бы попробовать какой-то странный реляционный материал. Здесь нет ничего!

Попытка с INTERSECT

  SELECT COUNT (*)
FROM dbo.Users AS u
ГДЕ НЕ СУЩЕСТВУЕТ (SELECT u.Age WHERE u.Age> = 18
                   ПЕРЕСЕЧЕНИЕ
                   SELECT u.Age WHERE u.Age IS NOT NULL);
 

 Орехи

  Таблица «Пользователи». Число сканирования 1, логическое чтение 9157, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 1094 мс, прошедшее время = 1090 мс.
 

И вот попытка с EXCEPT

  SELECT COUNT (*)
FROM dbo.Users AS u
ГДЕ НЕ СУЩЕСТВУЕТ (SELECT u.Age WHERE u.Age> = 18
                   КРОМЕ
                   SELECT u.Age WHERE u.Age IS NULL);
 

 Орехи

  Таблица «Пользователи». Число сканирования 7, логическое чтение 9247, физическое считывание 0, чтение вперед 0, логическое чтение логических чисел 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 2126 мс, прошедшее время = 376 мс.
 

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

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

  SELECT SUM (CASE WHEN u.Age <18 THEN 1
                КОГДА U.Age NULL THEN 1
                ELSE 0 END)
FROM dbo.Users AS u

SELECT SUM (CASE WHEN u.Age <18 ИЛИ u.Age IS NULL THEN 1
                ELSE 0 END)
FROM dbo.Users AS u
 

Оба они получают одинаковыеплана и имеют те же характеристики процессора и чтения.

 Орехи

  Таблица «Пользователи». Число сканирования 1, логическое чтение 9157, физическое чтение 0, чтение вперед 0, логическое чтение лоб 0, физическое чтение lob 0, чтение с чтением lob 0.

 Время выполнения SQL Server:
   Время CPU = 719 мс, прошедшее время = 719 мс.
 

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

  SELECT SUM (Записи)
ИЗ
(
    ВЫБРАТЬ 1
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    ВЫБРАТЬ 1
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи)
ВАРИАНТ (QUERYTRACEON 8649);
 

Спасибо!

ответил sp_BlitzErik 27 MarpmMon, 27 Mar 2017 18:31:24 +03002017-03-27T18:31:24+03:0006 2017, 18:31:24
16

Я не играл для восстановления базы данных объемом 110 ГБ только для одной таблицы, поэтому я создал свои собственные данные . Возрастные распределения должны соответствовать тому, что указано в Stack Overflow, но, очевидно, сама таблица не будет соответствовать. Я не думаю, что это слишком большая проблема, потому что запросы все равно попадут в индексы. Я тестирую на 4-процессорном компьютере с пакетом обновления 1 (SP1) для SQL Server 2016. Следует отметить, что для запросов, которые быстро заканчиваются, важно не включать фактический план выполнения. Это может немного замедлить работу.

Я начал с рассмотрения некоторых решений в превосходном ответе Эрика. Для этого:

  SELECT SUM (Записи)
ИЗ
(
    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи);
 

Я получил следующие результаты sys.dm_exec_sessions в течение 10 проб (запрос, естественно, прошел параллельно для меня):

  â • "â • â • â • â • â • â • â • â • â • â • â • |â • â • â • â • â • â • â • â • â • â • â • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • â • â • â • -
â • 'cpu_time â •' total_elapsed_time â • 'logical_reads â •'
â • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • £
â • '3532 â •' 975 â • '60830 â •'
• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •
 

Запрос, который работал лучше для Erik, на самом деле хуже выполнялся на моей машине:

  SELECT SUM (Записи)
ИЗ
(
    ВЫБРАТЬ 1
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    ВЫБРАТЬ 1
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи)
ВАРИАНТ (QUERYTRACEON 8649);
 

Результаты 10 испытаний:

  â • "â • â • â • â • â • â • â • â • â • â • â • |â • â • â • â • â • â • â • â • â • â • â • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • â • â • â • -
â • 'cpu_time â •' total_elapsed_time â • 'logical_reads â •'
â • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • £
â • '5704 â •' 1636 â • '60850 â •'
• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •
 

Я не могу сразу объяснить, почему это так плохо, но непонятно, почему мы хотим заставить почти каждого оператора в плане запроса пойти параллельно. В исходном плане у нас есть последовательная зона, которая находит все строки с AGE <18 . Всего несколько тысяч строк. На моей машине я получаю 9 логических чтений для этой части запроса и 9 мс сообщенного времени процессора и прошедшего времени. Существует также последовательная зона глобального агрегата для строк с AGE IS NULL , но она обрабатывает только одну строку на DOP. На моей машине это всего четыре строки.

Мой взнос заключается в том, что наиболее важно оптимизировать часть запроса, которая находит строки с NULL для Age , потому что есть миллионы этих строк. Мне не удалось создать индекс с меньшим количеством страниц, которые покрывали данные, чем простой сжатый в столбце столбец. Я предполагаю, что минимальный размер индекса для каждой строки или что много пространства индекса нельзя избежать с помощью трюков, которые я пробовал. Поэтому, если мы зацикливаемся на том же количестве логических чтений, чтобы получить данные, тогда единственный способ сделать это быстрее - сделать запрос более параллельным, но это нужно сделать иначе, чем запрос Эрика, который использовал TF 8649. В вышеприведенном запросе мы имеем отношение 3,62 для процессорного времени к прошедшему времени, что довольно хорошо. Идеал был бы отношением 4.0 на моей машине.

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

 lazy thread

Индексное сканирование является одним из немногих операторов, которые могут быть реализованы параллельно, и мы ничего не можем сделать о том, как строки распределяются по потокам. Там есть элемент случайности, но довольно последовательно я видел одну недоработанную нить. Один из способов обойти это - сделать параллелизм трудным путем: во внутренней части вложенного объединения цикла. Все, что входит во внутреннюю часть вложенного цикла, будет реализовано последовательным образом, но многие последовательные потоки могут запускаться одновременно. Пока мы получаем благоприятный метод параллельного распределения (например, round robin), мы можем точно контролировать, сколькостроки отправляются в каждый поток.

Я выполняю запросы с DOP 4, поэтому мне нужно равномерно разделить строки NULL в таблице на четыре ведра. Один из способов сделать это - создать кучу индексов для вычисленных столбцов:

  ALTER TABLE dbo.Users
ADD Compute_bucket_0 AS (СЛУЧАЙ, КОГДА ЭТО НЕТ И ИЛ. ИД.% 4 = 0 ТО, ЧТО 1 ELSE NULL END),
Compute_bucket_1 AS (СЛУЧАЙ, КОГДА ЭТО ИМЕЕТ NULL И ИД.% 4 = 1 ТО, ЧТО 1 ELSE NULL END),
Compute_bucket_2 AS (СЛУЧАЙ, КОГДА ВЕС ИМЕЕТ NULL И ИД.% 4 = 2 ТО, ЧТО 1 ELSE NULL END),
Compute_bucket_3 AS (СЛУЧАЙ, КОГДА ЭТО ИМЕЕТ NULL И ИД.% 4 = 3 ТО, ЧТО 1 ELSE NULL END);

CREATE INDEX IX_Compute_bucket_0 ON dbo.Users (Compute_bucket_0) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_1 ON dbo.Users (Compute_bucket_1) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_2 ON dbo.Users (Compute_bucket_2) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_3 ON dbo.Users (Compute_bucket_3) WITH (DATA_COMPRESSION = PAGE);
 

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

Чтобы получить параллельный вложенный цикл цикла, я собираюсь использовать недокументированные флаг трассировки 8649 . Я также собираюсь написать код немного странно, чтобы оптимизатор не обрабатывал больше строк, чем необходимо. Ниже приведена одна реализация, которая, как представляется, хорошо работает:

  SELECT SUM (t.cnt) + (SELECT COUNT (*) FROM dbo.Users AS u WHERE u.Age <18)
ИЗ
(VALUES (0), (1), (2), (3)) v (x)
КРЕСТ ПРИМЕНЯЕТСЯ
(
    SELECT COUNT (*) cnt
    FROM dbo.Users
    WHERE Compute_bucket_0 = CASE WHEN v.x = 0 THEN 1 ELSE NULL END

    СОЮЗ ВСЕ

    SELECT COUNT (*) cnt
    FROM dbo.Users
    WHERE Compute_bucket_1 = CASE WHEN v.x = 1 THEN 1 ELSE NULL END

    СОЮЗ ВСЕ

    SELECT COUNT (*) cnt
    FROM dbo.Users
    WHERE Compute_bucket_2 = CASE WHEN v.x = 2 THEN 1 ELSE NULL END

    СОЮЗ ВСЕ

    SELECT COUNT (*) cnt
    FROM dbo.Users
    WHERE Compute_bucket_3 = CASE WHEN v.x = 3 THEN 1 ELSE NULL END
) t
ВАРИАНТ (QUERYTRACEON 8649);
 

Результаты десяти испытаний:

  â • "â • â • â • â • â • â • â • â • â • â • â • |â • â • â • â • â • â • â • â • â • â • â • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • â • â • â • -
â • 'cpu_time â •' total_elapsed_time â • 'logical_reads â •'
â • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • £
â • '3093 â •' 803 â • '62008 â •'
• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • •
 

С этим запросом у нас есть процессор с коэффициентом истекшего времени 3,85! Мы отделили 17 мс от времени выполнения, и для этого потребовалось всего 4 вычисленных столбца и индексы! Каждый поток обрабатывается очень близко к тому же количеству строк в целом, потому что каждый индекс имеет очень близкое к тому же количеству строк, и каждый поток проверяет только один индекс:

 хорошо разделенная работа

В заключительной заметке мы также можем нажать кнопку «easy» и добавить некластерный CCI в столбец Age :

  СОЗДАТЬ НЕПРЕРЫВНЫЙ КОЛОНКУРТНЫЙ ИНДЕКС X_NCCI ON dbo.Users (Age);
 

Следующий запрос заканчивается через 3 мс на моей машине:

  SELECT COUNT (*)
FROM dbo.Users AS u
WHERE u.Age <18 ИЛИ u.Age IS NULL;
 

Это будет сложно победить.

ответил Joe Obbish 28 MaramTue, 28 Mar 2017 04:56:30 +03002017-03-28T04:56:30+03:0004 2017, 04:56:30
7

Хотя у меня нет локальной копии базы данных Stack Overflow, я смог опробовать несколько запросов. Моя мысль заключалась в том, чтобы получить количество пользователей из представления системного каталога (в отличие от прямого получения количества строк из базовой таблицы). Затем получите количество строк, которые (или, возможно, нет) соответствуют критериям Эрика, и сделайте некоторую простую математику.

Я использовал Stack Exchange Data Explorer (наряду с SET STATISTICS TIME ON; и SET STATISTICS IO ON; ) для проверки запросов. Для справки здесь приведены некоторые запросы и статистика CPU /IO:

QUERY 1

  - запрос Эрика Из исходного вопроса.
SELECT COUNT (*)
FROM dbo.Users AS u
ГДЕ ISNULL (u.Age, 17) 18;
 
  

Время выполнения SQL Server: время CPU = 0 мс, прошедшее время = 0 мс.   (Возвращается 1 строка (ы))

     

Таблица «Пользователи». Число сканирования 17, логическое считывание 201567, физическое чтение 0,   read-ahead читает 2740, логическое чтение lob 0, физическое чтение lob 0, lob   read-ahead читает 0.

     

Время выполнения SQL Server: время процессора = 1829 мс, прошедшее время =   296 мс.

QUERY 2

  - запрос «OR» Эрика.
SELECT COUNT (*)
FROM dbo.Users AS u
WHERE u.Age <18
ИЛИ u.Age IS NULL;
 
  

Время выполнения SQL Server: время CPU = 0 мс, прошедшее время = 0 мс.   (Возвращается 1 строка (ы))

     

Таблица «Пользователи». Число сканирования 17, логическое считывание 201567, физическое чтение 0,   read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob   read-ahead читает 0.

     

Время выполнения SQL Server: время CPU = 2500 мс, прошедшее время =   147 мс.

QUERY 3

  - производные таблицы Эрика /запрос UNION ALL.
SELECT SUM (Записи)
ИЗ
(
    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE u.Age <18

    СОЮЗ ВСЕ

    SELECT COUNT (Id)
    FROM dbo.Users AS u
    WHERE uAge IS NULL
) x (Записи);
 
  

Время выполнения SQL Server: время CPU = 0 мс, прошедшее время = 0 мс.   (Возвращается 1 строка (ы))

     

Таблица «Пользователи». Число сканирования 34, логическое считывание 403134, физическое считывание 0,   read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob   read-ahead читает 0.

     

Время выполнения SQL Server: время процессора = 3156 мс, прошедшее время =   215 мс.

1-я попытка

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

  SELECT SUM (p.Rows) -
  (
    SELECT COUNT (*)
    FROM dbo.Users AS u
    WHERE u.Age> = 18
  )
FROM sys.objects o
JOIN sys.partitions p
    ON p.object_id = o.object_id
WHERE p.index_id <2
AND o.name = 'Пользователи'
И SCHEMA_NAME (o.schema_id) = 'dbo'
GROUP BY o.schema_id, o.name
 
  

Время выполнения SQL Server: время CPU = 0 мс, прошедшее время = 0 мс.   (Возвращается 1 строка (ы))

     

Таблица «Рабочий стол». Число сканирования 0, логическое чтение 0, физическое чтение 0,   read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob   read-ahead читает 0. Таблица «sysrowsets». Число сканирования 2, логическое чтение   10, физическое чтение 0, чтение вперед - 0, логическое чтение логических чисел 0, lob   физическое считывание 0, чтение с открытым номером читает 0. Таблица «sysschobjs». сканирование   счет 1, логическое чтение 4, физическое чтение 0, чтение вперед 0, lob   логическое чтение 0, физическое чтение lob 0, чтение с чтением lob читает 0. Таблица   «Пользователи». Число сканирования 1, логическое чтение 201567, физическое чтение 0,   read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob   read-ahead читает 0.

     

Время выполнения SQL Server: время процессора = 593 мс, прошедшее время = 598   мс.

Вторая попытка

Здесь я выбрал переменную для хранения общего количества пользователей (вместо подзапроса). Количество сканирования увеличилось с 1 до 17 по сравнению с первой попыткой. Логические чтения остались прежними. Однако прошедшее время значительно сократилось.

  DECLARE @Total INT;

SELECT @Total = SUM (p.Rows)
FROM sys.objects o
JOIN sys.partitions p
    ON p.object_id = o.object_id
WHERE p.index_id <2
AND o.name = 'Пользователи'
И SCHEMA_NAME (o.schema_id) = 'dbo'
GROUP BY o.schema_id, o.name

SELECT @Total - COUNT (*)
FROM dbo.Users AS u
WHERE u.Age> = 18
 
  

Время выполнения SQL Server: время CPU = 0 мс, прошедшее время = 0 мс.   Таблица «Рабочий стол». Число сканирования 0, логическое чтение 0, физическое чтение 0,   read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob   read-ahead читает 0. Таблица «sysrowsets». Число сканирования 2, логическое чтение   10, физическое чтение 0, чтение вперед - 0, логическое чтение логических чисел 0, lob   физическое считывание 0, чтение с открытым номером читает 0. Таблица «sysschobjs». сканирование   счет 1, логическое чтение 4, физическое чтение 0, чтение вперед 0, lob   логическое чтение 0, физическое чтение lob 0, чтение с чтением lob 0.

     

Время выполнения SQL Server: время процессора = 0 мс, прошедшее время = 1 мс.(Возвращается 1 строка (ы))

     

Таблица «Пользователи». Число сканирования 17, логическое считывание 201567, физическое чтение 0,   read-ahead читает 0, lob логическое чтение 0, физическое чтение lob 0, lob   read-ahead читает 0.

     

Время выполнения SQL Server: время процессора = 1471 мс, прошедшее время = 98   мс.

Другие примечания: DBCC TRACEON не разрешен в проводнике данных стека Exchange, как указано ниже:

  

Пользователь «STACKEXCHANGE \ svc_sede» не имеет разрешения на запуск DBCC   TRACEON.

ответил Dave Mason 27 MarpmMon, 27 Mar 2017 22:27:50 +03002017-03-27T22:27:50+03:0010 2017, 22:27:50
1

Использовать переменные?

  declare @ int1 int = (выберите count (*) из таблицы_1, где bb <= 1)
declare @ int2 int = (выберите count (*) из таблицы_1, где bb равно null)
выберите @ int1 + @ int2;
 

В комментарии может пропустить переменные

  SELECT (выберите count (*) из таблицы_1, где bb <= 1)
     + (выберите count (*) из таблицы_1, где bb равно null);
 
ответил paparazzo 27 MarpmMon, 27 Mar 2017 18:45:46 +03002017-03-27T18:45:46+03:0006 2017, 18:45:46
0

Ну, используя SET ANSI_NULLS OFF;

  SET ANSI_NULLS OFF;
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT COUNT (*)
FROM dbo.Users AS u
WHERE age = NULL или возраст <18

Таблица «Пользователи». Число сканирования 17, логическое чтение 201567

 Время выполнения SQL Server:
 Время CPU = 2344 мс, прошедшее время = 166 мс.
 

Это что-то просто появилось в моем уме. Просто выполнил это в https://data.stackexchange.com

Но не так эффективен, как @blitz_erik, хотя

ответил Biju jose 28 MaramTue, 28 Mar 2017 11:10:49 +03002017-03-28T11:10:49+03:0011 2017, 11:10:49
0

Тривиальное решение состоит в вычислении count (*) - count (age> = 18):

  SELECT
(SELECT COUNT (*) FROM Users) -
(SELECT COUNT (*) FROM Пользователи WHERE Age> = 18);
 

Результаты здесь

ответил Salman A 24 PMpTue, 24 Apr 2018 23:09:09 +030009Tuesday 2018, 23:09:09

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

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132