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

Я написал одно приложение в C #, которое подключено к SQL Server SQL Express, с передней стороны. Я заполняю конкретную таблицу в базе данных каждые несколько секунд и вставляю приблизительно 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 и отмечаю, что нет уникального столбца, если он требует, чтобы я мог создать столбец идентификатора (Auto increment), но моя логика генерации отчетов полностью основана на столбце Date_Time.

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

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

 select top (1) COL1 from VALUEDATA where [email protected] and MeterId [email protected] and Date_Time between @StartDateTime and @EndDateTime order by Date_Time 

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

 select top (1) COL1 from VALUEDATA where [email protected] and MeterId [email protected] and Date_Time between @StartDateTime and @EndDateTime order by Date_Time desc 

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

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

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

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

 CREATE INDEX ix_valuedata_asc ON VALUEDATA (DeviceId, MeterId, Date_Time); CREATE INDEX ix_valuedata_des ON VALUEDATA (DeviceId, MeterId, Date_Time DESC); 

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

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

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

Если основным, наиболее часто используемым критерием выбора / фильтра в этой таблице является дата времени, я бы изменил таблицу на следующую кластерную структуру:

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

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

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

  • Могу ли я использовать Руководство по планированию для оптимизации медленного выполнения запроса?
  • Как повысить производительность кластеризованного индекса
  • Один и тот же блок операций занимает много времени в двух довольно похожих запросах
  • Interesting Posts

    SQL Server: есть ли накладные расходы на производительность между операторами «=» и «как»?

    Пакетный файл «For / f» дважды дублирует каждую папку и записывает файлы дважды

    Очень сложная проблема с SQL-запросом

    ISNULL / COALESCE для нескольких полей

    Односторонние хэш-функции

    Коллизионный конфликт ERROR, установка сортировки для хранимой процедуры как базы данных по умолчанию

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

    Получение нулевой ошибки даже там должно быть значение через внешний ключ

    SqlIaaSExtension.Service, поврежденный на Azure SQL Server 2016 VM

    Microsoft.SqlServer.Management.Smo.FailedOperationException: Ошибка резервного копирования для сервера '\\. \ Pipe \ 3F103E6E-3FD4-47 \ tsql \ query'

    Как добавить пользователей в базу данных SQL Server в C # Winform

    Соединение с базой данных C # SQL Server без встроенного приложения

    Как умножить значения в столбцах с использованием SQL

    Оптимальный исполняемый запрос для последней записи для каждого N

    Как подавить подсказку для решения Solution1 в SQL Server Management Studio

    Давайте будем гением компьютера.