Медленный отдельный запрос в SQL Server по большому набору данных

Ответ 1

Вы неправильно понимаете индекс. Даже если бы он использовал индекс, он все равно выполнил бы сканирование индекса через записи 200M. Это займет много времени, плюс время, необходимое для выполнения DISTINCT (вызывает сортировку), и это плохо для запуска. Увидев DISTINCT в запросе, всегда возникает красный флаг и заставляет меня дважды проверять запрос. В этом случае, возможно, у вас проблема нормализации?

Ответ 2

Я сомневаюсь, что SQL Server даже попытается использовать индекс, он должен будет выполнять практически ту же работу (учитывая узкую таблицу), читая все строки 200M, независимо от того, смотрит ли он на таблицу или индекс. Если индекс в typeName был кластеризован, он может сократить время, которое не нужно сортировать перед группировкой.

Если мощность ваших типов низкая, как насчет сохранения сводной таблицы, которая содержит список различных значений type? Триггер при вставке/обновлении основной таблицы выполнит проверку сводной таблицы и вставляет новую запись при обнаружении нового типа.

Ответ 3

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

Таким образом, мы также запрашиваем такие запросы, как:

SELECT DISTINCT [typeName] FROM [types] WITH (nolock);

и разбить его на следующие

SELECT typeName INTO #tempTable1 FROM types WITH (NOLOCK)
SELECT DISTINCT typeName FROM #tempTable1

Другой способ обойти это - использовать GROUP BY, который получает другой план оптимизации.

Ответ 4

Моя первая мысль - статистика. Чтобы найти последнее обновление:

SELECT
    name AS index_name, 
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM
    sys.indexes 
WHERE
    object_id = OBJECT_ID('MyTable');

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

Моя вторая мысль заключается в том, что индекс все еще существует? Запрос TOP должен по-прежнему использовать индекс. Я только что тестировал одну из своих таблиц с 57 миллионами строк и оба использовали индекс.

Ответ 5

Как уже указывали другие, когда вы выполняете SELECT DISTINCT (typename) над вашей таблицей, вы получите полное сканирование таблицы независимо от того, что.

Так что это действительно вопрос ограничения количества строк, которые нужно отсканировать.

Вопрос: для чего нужны ваши имена DISTINCT? И сколько из ваших 200M строк отличается? У вас есть только несколько (не более нескольких сотен) разных типов имен?

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

Таким образом, у вас будет отдельная небольшая таблица с отдельными записями TypeName, которая будет молниеносно запросить и/или отобразить.

Марк

Ответ 6

Циклический подход должен использовать несколько запросов (но теряет parallelism). Возможно, стоит попробовать для случаев с относительно небольшим количеством различных значений по сравнению с общим количеством строк (малая мощность).

Идея была из question:

select typeName into #Result from Types where 1=0;

declare @t varchar(100) = (select min(typeName) from Types);
while @t is not null
begin
    set @t = (select top 1 typeName from Types where typeName > @t order by typeName);    
    if (@t is not null)
        insert into #Result values (@t);
end

select * from #Result;

И похоже, что есть и другие методы (в частности, рекурсивный CTE @Paul White):

different-ways-to-find-distinct-values-faster-methods

sqlservercentral Тема 873124-338-5

Ответ 7

Второй запрос работает на 1000000 записей, но первый - 200M. Я думаю, что это большая разница:)

Ответ 8

Я должен попробовать что-то вроде этого:

SELECT typeName FROM [types] WITH (nolock)
group by typeName;

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

Ответ 9

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

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

select type
from nightlyscan
union
select distinct type
from verybigtable
where rowid > lastscannedid

Другой вариант - нормализовать большую таблицу на две таблицы:

talbe1: id, guid, typeid
type table: typeid, typename

Это было бы очень полезно, если бы количество типов было относительно небольшим.

Ответ 10

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

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

Он задает вопрос, сколько пишет по сравнению с тем, как часто вы хотите различать и важность скорости, когда вы делаете.

Ответ 11

Индексированный просмотр может сделать это быстрее.

create view alltypes
with schemabinding as
select typename, count_big(*) as kount
from dbo.types
group by typename

create unique clustered index idx
on alltypes (typename)

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

В качестве альтернативы вы можете сделать небольшую таблицу со всеми значениями:

select distinct typename
into alltypes
from types

alter table alltypes
add primary key (typename)

alter table types add foreign key (typename) references alltypes

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