Самый быстрый SQL-запрос для неиндексированных данных

Я создаю несколько пользовательских отчетов для базы данных SQL Server 2005. База данных принадлежит стороннему приложению управления, которое мы запускаем. Данные, которые я извлекаю, не входят в число основных целей сайта, поэтому данные в основном не индексируются, за исключением столбца меток времени. На данный момент задействована только одна таблица — таблица примерно из 700 миллионов строк. Поэтому, когда я запускаю к ​​нему запрос, который должен вернуть только 50 строк, он должен опросить все 700 мил.

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

Хранимая процедура не кажется лучшим вариантом. Индексированное представление может быть лучшей идеей? Мысли?

Вот схема таблицы:

DeviceGuid (PK, uniqueidentifier, not null)
DeviceID (int, not null)
WindowsEventID (PK, int, not null) (indexed)
EventLog (varchar(64), not null)
EventSource (varchar(64), not null)
EventID (int, not null)
Severity (int, not null)
Description (nvarchar(max), not null)
TimeOfEvent (PK, datetime, not null) (indexed)
OccurrenceNbr (int, not null)

Вот пример запроса:

SELECT COUNT(*) AS NumOcc, EventID, EventLog, EventSource, Severity, TimeOfEvent, Description
FROM WindowsEvent
WHERE DeviceID='34818'
    AND Severity=1
    AND TimeOfEvent >= DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), '2010/10/27 12:00:00 AM')
    AND TimeOfEvent <= DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), '2010/11/3 12:00:00 AM')
    AND EventID<>34113
    AND EventID<>34114
    AND EventID<>34112
    AND EventID<>57755
    AND EventSource<>'AutoImportSvc.exe'
    AND EventLog='Application'
GROUP BY EventID, EventLog, EventSource, Severity, Description
ORDER BY NumOcc DESC

Возможно, запрос отстой... он возвращает 53 строки за 4,5 минуты.


stored-procedures indexing sql-server-2005 indexed-view
person Nathan Loding    schedule 09.11.2010    source источник
comment
Должны ли ваши данные для вашего запроса всегда обновляться, или можно работать с моментальным снимком данных? Вы можете попробовать создать вспомогательное задание, которое каждую ночь анализирует все данные и записывает их в структуру отчета, где вы отфильтровали/агрегировали данные, чтобы количество строк, которые вам нужно запросить, было меньше. И эта таблица также может иметь необходимые индексы, если это необходимо.   -  person Knubo    schedule 10.11.2010
comment
@Knubo - это то, что мы рассматриваем. Обычно мы просматриваем данные за предыдущие недели, чтобы еженедельно архивировать их в другую базу данных.   -  person Nathan Loding    schedule 10.11.2010
comment
700 миллионов строк, скажем, по 100 байт на каждую, означают 70 ГБ данных для линейного поиска. На самом деле неплохо выжать из этого 4,5 минуты. Хотя я предполагаю, что тогда база данных заблокирована и ни о чем больше не идет. В зависимости от того, какие поисковые запросы вы ищете, вы можете рассмотреть возможность использования Lucine или другого программного обеспечения для индексации для индексации ваших данных. Некоторые мои бывшие коллеги успешно проделали это с некоторыми сложными базами данных mysql.   -  person Knubo    schedule 10.11.2010

Ответы (4)


arrow_upward
0
arrow_downward

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

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

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

person bobs    schedule 09.11.2010
comment
Я согласен, что добавление индексов поможет, но что, если я не могу добавить индексы. Есть ли обходные пути, которые увеличат скорость? - person Nathan Loding; 10.11.2010
comment
Ах, я неправильно понял. Я думал, что индексация была опцией. Похоже, тема обсуждения с @Knubo будет самой многообещающей. - person bobs; 10.11.2010

arrow_upward
0
arrow_downward

попробуйте этот метод, используя двойной трюк row_number:

SELECT  RN_Desc as NumOcc, *
FROM    (
        SELECT  row_number() Over(partition by EventId order by EventLog, EventSource, Severity, Description) as RN_Asc,
                row_number() Over(partition by EventId order by EventLog desc, EventSource desc, Severity desc, Description desc) as RN_Desc,
                *
        FROM    WindowsEvent 
        WHERE   DeviceID='34818' 
                AND Severity=1 
                AND TimeOfEvent >= DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), '2010/10/27 12:00:00 AM') 
                AND TimeOfEvent <= DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), '2010/11/3 12:00:00 AM') 
                AND EventID<>34113 
                AND EventID<>34114 
                AND EventID<>34112 
                AND EventID<>57755 
                AND EventSource<>'AutoImportSvc.exe' 
                AND EventLog='Application' 
        ) t
WHERE   RN_Asc = 1 
ORDER BY NumOcc DESC

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

person Mladen Prajdic    schedule 10.11.2010
comment
Это сэкономило около минуты и значительно сократило количество строк, но все же недостаточно быстро для того, что нам нужно. Я думаю, что создание отдельной базы данных и экспорт данных в нее - это ответ. - person Nathan Loding; 11.11.2010

arrow_upward
0
arrow_downward

Окончательным решением здесь было запустить запрос к проиндексированным полям, а затем отфильтровать их в приложении, выполняющем запрос. В итоге два поля содержали достаточно схожую информацию, которую я мог запросить по одному индексу и получить очень близкое приближение к нужным мне данным. Я просмотрел и удалил все несоответствующие объекты из списка результатов. Заняло НАМНОГО меньше времени!

person Nathan Loding    schedule 17.03.2011

arrow_upward
0
arrow_downward

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

person user1032402    schedule 14.07.2016