повысить производительность запросов к таблице SQL Server, содержащей 3,5 миллиона строк и растущей

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

В настоящее время таблица содержит около 3,5 миллионов строк и продолжает расти, определение таблицы приведено ниже.

[DEVICE_ID] [decimal](19, 5) NULL,
[METER_ID] [decimal](19, 5)  NULL,
[DATE_TIME] [decimal](19, 5)  NULL,
[COL1] [decimal](19, 5)  NULL,
[COL2] [decimal](19, 5)  NULL,
.
.
.
.
[COL25] [decimal](19, 5) NULL

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

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

Чтобы получить первое значение col1 за первый день, запрос

select top (1) COL1 from VALUEDATA where DeviceId=@DId and MeterId =@MId and Date_Time between @StartDateTime and @EndDateTime order by Date_Time

Чтобы получить последнее значение col1 за последний день, запрос

select top (1) COL1 from VALUEDATA where DeviceId=@DId and MeterId =@MId and Date_Time between @StartDateTime and @EndDateTime order by Date_Time desc

Но когда я запускаю вышеуказанные запросы, это занимает около 20–30 секунд, я считаю, что это можно оптимизировать, но не знаю, что делать дальше.

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

Очень ценно, если кто-то может предоставить исходные данные для того же.


database c# sql-server database-design query-performance
person Real Master    schedule 03.09.2016    source источник
comment
Почему все типы данных decimal(19,5)? Особенно для Date_Time это плохая идея.   -  person i486    schedule 11.09.2016

Ответы (2)


arrow_upward
1
arrow_downward

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

CREATE INDEX ix_valuedata_asc ON VALUEDATA (DeviceId, MeterId, Date_Time);
CREATE INDEX ix_valuedata_des ON VALUEDATA (DeviceId, MeterId, Date_Time DESC);
person Marc Guillot    schedule 03.09.2016
comment
Я бы сначала протестировал, но я подозреваю, что второй индекс даст только минимальный выигрыш, если таковой вообще будет. - person ESG; 03.09.2016
comment
Первая индексация имеет смысл, позвольте мне проверить. - person Real Master; 03.09.2016
comment
Ваш второй запрос предпочел бы нисходящий индекс. Но да, выигрыш будет совсем небольшим. - person Marc Guillot; 03.09.2016
comment
@MarcGuillot, нужен только первый индекс, поскольку SQL Server может выполнять упорядоченное обратное сканирование после поиска по предикату равенства. - person Dan Guzman; 03.09.2016
comment
Спасибо, я не знал, что SQL Server может перебирать индекс в обратном порядке. - person Marc Guillot; 03.09.2016
comment
@MarcGuillot Спасибо, я тестировал его последние 2 дня, и это значительно улучшило производительность моих запросов! - person Real Master; 06.09.2016
comment
Я не уверен, должно ли Date_Time быть последним или первым полем в индексе. Это зависит от количества DeviceIds и Date_Times. - person i486; 11.09.2016

arrow_upward
1
arrow_downward

У меня есть еще одно предложение: если ваша цель — получить значения COL1, COL2 и т. д. после поиска индекса, решение только с некластеризованным индексом в столбцах фильтрации все равно должно присоединиться к основной таблице, т.е. выполните поиск по закладкам/RID.

Ваша информация создает впечатление, что ваша базовая таблица не кластеризована (не имеет кластеризованного индекса); на самом деле таблица кучи

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

Если основным, наиболее часто ИСПОЛЬЗУЕМЫМ критерием выбора/фильтрации в этой таблице является дата и время, я бы изменил таблицу на следующую кластеризованную структуру:

  1. Сначала удалите все некластеризованные индексы.
  2. Затем добавьте следующий кластеризованный индекс:

CREATE CLUSTERED INDEX clix_valuedata ON VALUEDATA (Date_Time, DeviceId, MeterId);

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

person Rogier Werschkull    schedule 11.09.2016