Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 целесообразность партиционирования таблиц  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Доброго времени суток!
Обдумываю вот такую задачку, есть пара мест в довольно старой покупной системе с большим объемом накопленных данных.
Мне кажется, что там есть узкое место , и хочется его сгладить. Суть вот в чем (структуру максимально упростил):

-- есть таблица, например с некими заданиями/нарядами и т.д.
create table dbo.Document(doc_id int identity, date_doc datetime, order_id, cehid .....)
-- есть таблица с параметрами документа
create table dbo.Document_par(doc_id int , par_id int, value varchar(255) )

В отчетах основные поиски чаще всего выполняются или по дате документа (месяц, год квартал) и/или по заказам, подразделениям (order_id, cehid ), плохая новость в том, что date_doc - это просто дата ввода,
а дата отнесения документа хранится в параметре (с опр типом (par_id) в виде текста яля convert(varchar(10) , @dt, 104) ).
И поскольку искать надо именно по этому параметру, в отчетах есть куча ухищрений, чтобы делать это побыстрее. Но все равно, не быстро.

Мысли вот какие:

1. Приравнять Document.date_doc к значению параметра (или сделать отдельное поле). Хотябы в триггере на апдейт/инсерт таблицы параметров апдейтить дату в основной таблице.
Как минимум поиск по дате будет без преобразования типов и разных ухищрений.
2. Есть еще мысль о партиционировании таблицы, хотябы по году. Но тут вопрос не будет ли намного хуже при поиске по заказу или подразделению?


З.Ы. Т.е. с чем пытаюсь бороться. Параметры хранящиеся в преобразованном к Varchar виде по которым надо искать, и отсутствие в функционале механизмов некоей архивации что ли, когда неактуальный уже лет 13 объект лежит там же,
где и текущие, с которыми идет работа, ежедневно бэкапится. И соотношение активных к закрытым наверное 10 на 90 %.
17 дек 19, 16:34    [22042320]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7754
denis_viktorovich,

создайте persistent вычисляемый столбец, создайте индекс по столбцу.
17 дек 19, 16:48    [22042341]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
L_argo
Member

Откуда:
Сообщений: 1209
У вас, что есть возможность безболезненно менять структуру таблицы покупной системы ???
17 дек 19, 17:58    [22042433]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33644
Блог
denis_viktorovich,

>>а дата отнесения документа хранится в параметре (с опр типом (par_id) в виде текста яля convert(varchar(10) , @dt, 104) ).

У вас же наверняка куча отчетов завязана на эту дату, попробуйте обойтись фильтрованным индексом по par_id
17 дек 19, 18:07    [22042442]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
denis_viktorovich
Доброго времени суток!
Обдумываю вот такую задачку, есть пара мест в довольно старой покупной системе с большим объемом накопленных данных.
Мне кажется, что там есть узкое место , и хочется его сгладить. Суть вот в чем (структуру максимально упростил):

-- есть таблица, например с некими заданиями/нарядами и т.д.
create table dbo.Document(doc_id int identity, date_doc datetime, order_id, cehid .....)
-- есть таблица с параметрами документа
create table dbo.Document_par(doc_id int , par_id int, value varchar(255) )

В отчетах основные поиски чаще всего выполняются или по дате документа (месяц, год квартал) и/или по заказам, подразделениям (order_id, cehid ), плохая новость в том, что date_doc - это просто дата ввода,
а дата отнесения документа хранится в параметре (с опр типом (par_id) в виде текста яля convert(varchar(10) , @dt, 104) ).
И поскольку искать надо именно по этому параметру, в отчетах есть куча ухищрений, чтобы делать это побыстрее. Но все равно, не быстро.

Мысли вот какие:

1. Приравнять Document.date_doc к значению параметра (или сделать отдельное поле). Хотябы в триггере на апдейт/инсерт таблицы параметров апдейтить дату в основной таблице.
Как минимум поиск по дате будет без преобразования типов и разных ухищрений.
2. Есть еще мысль о партиционировании таблицы, хотябы по году. Но тут вопрос не будет ли намного хуже при поиске по заказу или подразделению?


З.Ы. Т.е. с чем пытаюсь бороться. Параметры хранящиеся в преобразованном к Varchar виде по которым надо искать, и отсутствие в функционале механизмов некоей архивации что ли, когда неактуальный уже лет 13 объект лежит там же,
где и текущие, с которыми идет работа, ежедневно бэкапится. И соотношение активных к закрытым наверное 10 на 90 %.


Секционирование таблиц помогает исключительно в случаях изменения данных в этих талицах (нет есть конечно у оптимизатора функциональность когда он не лазит в лишние секции если запрос написан правильно, но это лишь малая часть в работе оптимизатора и врядли вам это поможет), у вас вроде как проблема с чтением этих данных, а это значит, копать вам надо в сторону индексов, они гораздо эффективнее борются со сканами таблиц:)
Плюс ничего не сказано про обьемы, секционирование оправдано бывает при объемах от сотен миллионов строк.
17 дек 19, 18:43    [22042478]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
denis_viktorovich,

Создайте индепксированное представление. Примерно такое
create view dbo.vDocument_date
with schemabinding
as
select
 doc_id,
 convert(datetime, value, 104) as dt
from
 dbo.Document_par
where
 par_id = ...
go

create unique clustered index UCIX_vDocument_date__doc_id on dbo.vDocument_date (doc_id);
create index UCIX_vDocument_date__dt on dbo.vDocument_date (dt);
go
17 дек 19, 19:17    [22042505]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
denis_viktorovich
Member

Откуда: Екб
Сообщений: 1206
Спасибо за ответы!
Сделал некие предварительные выводы для себя - объем таблицы с параметрами порядка одной сотни миллионов записей, основная таблица - пара десятков миллионов. На вскидку думать о секционировании надо в последнюю очередь.
С индексированными вьюхами попробую аккуратно, насколько я понимаю в запросе с условием вида convert(datetime, doc_date, 104) > .... оптимизатор может использовать данную вьюху и индекс без необходимости переписывания запроса.
18 дек 19, 07:11    [22042704]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
ShIgor
Member

Откуда: Нижний Новгород
Сообщений: 2341
denis_viktorovich,

а Вы не думали партицировать по par_id глобально для всех параметров или применительно к конкретной задаче на 2 части, параметр дата и все остальные?
18 дек 19, 12:46    [22042985]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
ShIgor
а Вы не думали партицировать по par_id глобально для всех параметров или применительно к конкретной задаче на 2 части, параметр дата и все остальные?
Если есть кластерный или покрывающий индекс на par_id, то эффект будет тот же, что и для секционирования по par_id, но без излишнего усложнения.
denis_viktorovich
думать о секционировании надо в последнюю очередь
В этой задаче секционирование ещё может замедлить, но ускорить точно не может.
18 дек 19, 18:34    [22043505]     Ответить | Цитировать Сообщить модератору
 Re: целесообразность партиционирования таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
denis_viktorovich
С индексированными вьюхами попробую аккуратно, насколько я понимаю в запросе с условием вида convert(datetime, doc_date, 104) > .... оптимизатор может использовать данную вьюху и индекс без необходимости переписывания запроса.
Да, это хорошие варианты, может, обойдётесь без добавления поля в документы, с переписыванием запросов.
К тому же подход можно будет использовать для некоторых других параметров, для которых часто делается поиск.
18 дек 19, 18:37    [22043510]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить