Массивная таблица в базе данных SQL 2005 требует большей производительности!
Я работаю над веб-приложением, управляемым данными, которое использует базу данных SQL 2005 (стандартная версия).
Одна из таблиц довольно большая (8 миллионов + строк с примерно 30 столбцами). Размер таблицы, очевидно, влияет на производительность веб-сайта, который выбирает элементы из таблицы с помощью хранимых процедур. Таблица индексируется, но производительность остается низкой из-за большого количества строк в таблице - это часть проблемы - таблица одинаково читается и обновляется, поэтому мы не можем добавлять / удалять индексы, не выполнив одно из следующих действий: операции хуже.
Цель, которую я здесь поставил, - повысить производительность при выборе элементов из таблицы. Таблица содержит "текущие" данные и старые / почти не затронутые данные. Наиболее эффективное решение, которое мы можем придумать на данном этапе, состоит в том, чтобы разделить таблицу на 2, то есть одну для старых элементов (до определенной даты, скажем, 1 января 2005 года) и одну для более новых элементов (равную или до 1 января 2005 года).,
Мы знаем о таких вещах, как распределенные разделенные представления, но для всех этих функций требуется Enterprise Edition, которую клиент не купит (и нет, аппаратное оборудование для этого также не произойдет).
3 ответа
Вы всегда можете бросить свой собственный "разделение бедного человека / DPV", даже если это не пахнет как правильный способ сделать это. Это просто широкий концептуальный подход:
Создайте новую таблицу для данных текущего года - та же структура, те же индексы. Настройте хранимую процедуру, которая записывает в основную, большую таблицу, для записи в обе таблицы (только временно). Я рекомендую сделать логику в хранимой процедуре, скажем, IF CURRENT_TIMESTAMP >= '[некоторая целая дата без времени]' - это упростит обратную засыпку данных в этой таблице, которая предшествует изменению процедуры, которая начинает там регистрироваться.
Создайте новую таблицу для каждого года в вашей истории, используя SELECT INTO из основной таблицы. Вы можете сделать это в другой базе данных на том же экземпляре, чтобы избежать накладных расходов в текущей базе данных. Я предполагаю, что исторические данные не будут меняться, поэтому в этой другой базе данных вы можете даже сделать ее доступной только для чтения (что значительно повысит производительность чтения).
Получив копию всей таблицы, вы можете создать представления, которые ссылаются только на текущий год, другое представление, которое ссылается на 2005 год на текущий год (используя UNION ALL между текущей таблицей и данными в другой базе данных, которые>= 2005) и другой, который ссылается на все три набора таблиц (упомянутых и таблицы, предшествовавшие 2005 году). Конечно, вы можете разбить это еще больше, но я просто хотел сохранить концепцию минимальной.
Измените ваши хранимые процедуры, которые считывают данные, чтобы они были "умнее" - если запрашиваемый диапазон дат попадает в текущий календарный год, используйте наименьшее представление, которое является только локальным; если диапазон дат> = 2005, тогда используйте второе представление, иначе используйте третье представление. Вы можете следовать аналогичной логике с хранимыми процедурами, которые пишут, если вы делаете больше, чем просто вставляете новые данные, которые относятся только к текущему году.
На этом этапе вы сможете прекратить вставку в массивную таблицу и, как только все будет работать, отбросьте ее и освободите некоторое место на диске (и под этим я подразумеваю освобождение места в файлах данных для повторного использования, не выполнять сжатие дБ - так как вы будете использовать это пространство снова).
У меня нет всех деталей вашей ситуации, но, пожалуйста, следите, если у вас есть вопросы или проблемы. Я использовал этот подход в нескольких миграционных проектах, в том числе и в том, что происходит сейчас.
Перестройте все ваши индексы. Это повысит производительность запросов. Как это сделать и подробнее о влиянии на перестроение кластерного и некластеризованного индекса здесь
Во-вторых, выполните дефрагментацию на вашем диске, на котором хранится БД.
производительность низкая из-за огромного количества строк в таблице
8 миллионов строк звучат не так уж безумно. Вы проверили свои планы запросов?
таблица одинаково читается и обновляется
Вы действительно обновляете индексированный столбец или он одинаково читается и вставляется?
(и нет, аппаратное сбрасывание тоже не произойдет)
Жаль, потому что ОЗУ очень дешево.