Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: быстрое получение остатков на дату  [new]
TORT
Member

Откуда:
Сообщений: 1115
Хм... Понятно... Скажите, а на основе MQT(суммарных таблиц) Вы это не пытались организовать? Вот это было бы весьма интересно....
18 дек 07, 15:30    [5063729]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Bogdanov Andrey


TORT
Вы тестировали данный подход? Если не серк
Не только тестировал, но и промышленно эксплуатировал. :)

Присоединяюсь.
У меня это сделано на MS SQL на триггерах.
18 дек 07, 15:41    [5063832]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
TORT
Скажите, а на основе MQT(суммарных таблиц) Вы это не пытались организовать? Вот это было бы весьма интересно....
Как я уже сказал - я не пытался проводить тесты и сравнивать различные варианты реализации. Ну а в первой половине девяностых материализованных представлений еще не было. Поэтому приходилось все делать руками. :)
18 дек 07, 15:51    [5063925]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
dekan
Member

Откуда: Москва
Сообщений: 140
Bogdanov Andrey
.....у нас один исключительный случай, когда учетных единиц было мало (порядка сотни), а конкурирующих транзакций - много (до полусотни процессов и десятки тысяч операций в час у каждого). Тут задержки на блокировках становятся посущественней. Для этого случая был другой вариант, когда заполнением этой таблицы занимался отдельный фоновый процесс.


А нельзя ли поподробнее вот с этого места? Как избавились от блокировок?
20 дек 07, 15:29    [5075513]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
sqllex
Member

Откуда: Kiev
Сообщений: 710
2 DobPilot
А как часто у вас движения происходят и сколько userAccount?
А то, например, в BOL MS SQL2005 есть примечание для Indexed View:
BOL

Indexed views typically do not improve the performance of the following types of queries:
-OLTP systems that have many writes.
-Databases that have many updates.
-Aggregations of data with a high degree of cardinality for the GROUP BY key. A high degree of cardinality means the key contains many different values.
....
20 дек 07, 16:29    [5076206]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Alex S
Member

Откуда:
Сообщений: 293
По мотивам:
Bogdanov Andrey

Имеем таблицу
Остатки
Номенклатура ссылка
Значение число
Начало дата
Конец дата
При совершении движения в этой таблице у текущей записи по номенклатуре выставляется "Конец" и добавляется новая. Если в один день происходит несколько двжиений, то мы просто модифицируем запись текущую запись.
Таким образом число записей не превышает количества движений, а остаток на любой день достается запросом без суммирования:
select Значение from Остатки where Номенклатура=:id and Начало <= :data and Конец > :data
Наличие индексов делает запрос быстрым. Особые эстеты могут его оптимизировать.
Я не говорю, что это единственное верное решение, но в некоторых случаях оно работает очень хорошо.

На MSSQL есть решение быстрее:
Имеем таблицу
Остатки
Номенклатура ссылка
Значение число
Начало дата
выборка:
select top 1 Значение from Остатки where Номенклатура=:id and Начало <= :data order by Начало desc

На Oracle нет top 1 (rownum применить не получится без подзапроса).
21 дек 07, 10:28    [5078969]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Alex S

На MSSQL есть решение быстрее:
...
select top 1 Значение from Остатки where Номенклатура=:id and Начало <= :data order by Начало desc
На Oracle нет top 1 (rownum применить не получится без подзапроса).

а если надо остатки по нескольким счетам? по одному счету то редко когда нужно
21 дек 07, 10:32    [5079006]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Alex S
Member

Откуда:
Сообщений: 293
SergSuper
а если надо остатки по нескольким счетам? по одному счету то редко когда нужно

select Kod
,(select top 1 Значение from Остатки where Номенклатура=Номенклатура.Номенклатура and Начало <= :data order by Начало desc) as Значение
from Номенклатура
но imho лучше сделать udf
select Kod
,Остаток(Номенклатура.Номенклатура,:data) as Значение
from Номенклатура
21 дек 07, 12:53    [5080214]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
!
Guest
SergSuper
Alex S

На MSSQL есть решение быстрее:
...
select top 1 Значение from Остатки where Номенклатура=:id and Начало <= :data order by Начало desc
На Oracle нет top 1 (rownum применить не получится без подзапроса).

а если надо остатки по нескольким счетам? по одному счету то редко когда нужно


Универсальный вариант:

select  name, value, from reminders r1 where date in ( select max(date) from  reminders r2 
where r2.acc=r1.acc)
and  acc in (  список необходимых  счетов).


Чем больше счетов в списке тем дольше будет работать.
Для каждого счета за одну дату должна быть одна запись об остатках.
21 дек 07, 13:11    [5080375]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
Alex S

На MSSQL есть решение быстрее:
...
Давайте не будем в эту тему тащить сравнение MSSQL и Oracle. Да к тому же с отсутствием конкретных цифр. Я не уверен, что приведенное вами решение будет быстрее, чем аналогичное Oracle'овое.

dekan
А нельзя ли поподробнее вот с этого места? Как избавились от блокировок?

Ну так конфликт блокировок возникает только при попытке разными процессам модифицировать остаток по одной и той же учетной единице. Если модификацией остатков занимается выделенный процесс, то никакого конфликта у него с самим собой быть не может.
21 дек 07, 14:07    [5080817]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Alex S
SergSuper
а если надо остатки по нескольким счетам? по одному счету то редко когда нужно

select Kod
,(select top 1 Значение from Остатки where Номенклатура=Номенклатура.Номенклатура and Начало <= :data order by Начало desc) as Значение
from Номенклатура
но imho лучше сделать udf
select Kod
,Остаток(Номенклатура.Номенклатура,:data) as Значение
from Номенклатура

Это я всё знаю, спрашивал чтобы всем было понятно что с двумя датами проще :))
21 дек 07, 15:17    [5081420]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Alex S
Member

Откуда:
Сообщений: 293
Bogdanov Andrey
Alex S

На MSSQL есть решение быстрее:
...
Давайте не будем в эту тему тащить сравнение MSSQL и Oracle. Да к тому же с отсутствием конкретных цифр. Я не уверен, что приведенное вами решение будет быстрее, чем аналогичное Oracle'овое.
Топикстартер как раз хотел решений для различных СУБД. Быстрее - я имел ввиду не "MSSQL быстрее Oracle", а "реализация с одной датой и top 1 на MSSQL быстрее реализации с двумя датами на MSSQL". Тесты когда-то давно проводил, в итоге остановился на озвученном решении для MSSQL. Кроме того, с одной датой операция записи проще. Если будет время - постараюсь повторить тесты.
21 дек 07, 15:19    [5081437]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Alex S
Member

Откуда:
Сообщений: 293
SergSuper

Это я всё знаю, спрашивал чтобы всем было понятно что с двумя датами проще :))

А как это проще выглядит?
SergSuper
по одному счету то редко когда нужно

Кстати, в OLTP не так уж и редко
21 дек 07, 15:25    [5081484]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
Alex S
а "реализация с одной датой и top 1 на MSSQL быстрее реализации с двумя датами на MSSQL". Тесты когда-то давно проводил, в итоге остановился на озвученном решении для MSSQL. Кроме того, с одной датой операция записи проще.
Ну вторая дата - это избыточность данных и естественно, ее поддержка требует "накладных расходов". Но некоторые примущества две даты имеют. Например, просуммировать остатки по позициям на определенную дату (то есть любой аналитический запрос) будет попроще и, наверное, побыстрее даже в MS-SQL. Я MS-SQL-ким диалектом не владею. Как с помощью Top поизящней записать запрос нижеприведенному?
select sum(Значение) from Остатки where Начало<=:data and Конец > :data
21 дек 07, 16:10    [5081771]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
Alex S
SergSuper

Это я всё знаю, спрашивал чтобы всем было понятно что с двумя датами проще :))

А как это проще выглядит?


одна дата:
select Kod
,(select top 1 Значение from Остатки where Номенклатура=Номенклатура.Номенклатура and Начало <= :data order by Начало desc) as Значение
from Номенклатура
две даты:
select Kod
, Значение from Остатки join Номенклатура on Остатки.Номенклатура=Номенклатура.Номенклатура and  :data between Начало and Конец
разница будет еще заметней если надо из таблицы Остатков взять еще поля

Сообщение было отредактировано: 21 дек 07, 18:09
21 дек 07, 18:09    [5082702]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
drev
Member

Откуда: Одесса - Берег Красного Дерева - Красный мир
Сообщений: 564
ИМХО, остатки нужно хранить не по временным интервалам, а по определённому количеству записей между остатками. Т.о. мы получим более предсказуемую ситуацию.
23 дек 07, 13:30    [5084929]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
drev
ИМХО, остатки нужно хранить не по временным интервалам, а по определённому количеству записей между остатками. Т.о. мы получим более предсказуемую ситуацию.

продемонстрируйте, что ли...
23 дек 07, 22:38    [5085808]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
drev
Member

Откуда: Одесса - Берег Красного Дерева - Красный мир
Сообщений: 564
SergSuper
drev
ИМХО, остатки нужно хранить не по временным интервалам, а по определённому количеству записей между остатками. Т.о. мы получим более предсказуемую ситуацию.

продемонстрируйте, что ли...


Допустим, такая структура.


table sales_details
(
    id int primary key,
    doc_id int,  --FK
    item_id int, --FK
    amount float
)

table summary
(
    id int primary key,
    start_date datetime,
    end_date datetime,
    counter int
)
table summary_details
(
    id int primary key,
    summary_id int, --FK
    item_id int, --FK
    amount float
)


Добавляем триггер на sales_details, который находит соответствующую запись в таблице summary, и либо инкрементирует counter, либо добавляет в таблицу summary новую запись (если значение поля counter достигло некоторого порога).

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

Если чаще требуются остатки по конкретному товару, то вместо двух последних таблиц получаем одну:


table summary
(
    id int primary key,
    start_date datetime,
    end_date datetime,
    counter int
    item_id int, --FK
    amount float
)
24 дек 07, 08:44    [5086312]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Alex S
Member

Откуда:
Сообщений: 293
SergSuper
...
select Kod
, Значение from Остатки join Номенклатура on Остатки.Номенклатура=Номенклатура.Номенклатура and  :data between Начало and Конец
разница будет еще заметней если надо из таблицы Остатков взять еще поля

Ну незнаю, в таблице остатков у меня только Id номенклатуры, плоскость остатка, дата , два оборота и остаток. Все остальное в таблице номенклатуры, и так исторически складывается, что основная выборка идет по ней. Ну там, например, не закрытые счета такой-то группы. И одна UDF-ка "остаток" в перечне полей в select.
Bogdanov Andrey
...Ну вторая дата - это избыточность данных и естественно, ее поддержка требует "накладных расходов". Но некоторые примущества две даты имеют. Например, просуммировать остатки по позициям на определенную дату (то есть любой аналитический запрос) будет попроще и, наверное, побыстрее даже в MS-SQL. Я MS-SQL-ким диалектом не владею. Как с помощью Top поизящней записать запрос нижеприведенному?
select sum(Значение) from Остатки where Начало<=:data and Конец > :data
Да такой запрос быстрее - согласен - на моих данных где-то в 1,5-2 раза. Странно тут то, что мне не приходилось за свою практику задумываться что такой подход:
select A....,SUM(Остаток(A.id,...)) from Номенклатура A group by ...
надо ускорять. Вроде никогда не было проблем с доступностью данных для аналитических отчетов.
При этом такой показатель как "операций в секунду" не страдает.

Кстати, хотел спросить про "отложенный" расчет отдельным потоком. Я так понимаю, существует лаг между записью фактического движения и "наличием" фактического остатка по учетной единице. Каким способом обеспечивалась "непротиворечивость" данных?, к примеру если алгоритму требуется величина остатка после записи движения, для следующей операции? Или было управление из алгоритма "можно отложить"/"нельзя отложить" расчет? И еще, например, у меня в расчете есть различные проверки на выход за разные пределы остатка и т.п., в большинстве случаев они являются критерием возможности проведения операции и этим проверкам нужен остаток. Если существует пул необработанных движений, как работают такие механизмы?
24 дек 07, 11:11    [5086900]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
Alex S
Кстати, хотел спросить про "отложенный" расчет отдельным потоком. Я так понимаю, существует лаг между записью фактического движения и "наличием" фактического остатка по учетной единице. Каким способом обеспечивалась "непротиворечивость" данных?, к примеру если алгоритму требуется величина остатка после записи движения, для следующей операции? Или было управление из алгоритма "можно отложить"/"нельзя отложить" расчет? И еще, например, у меня в расчете есть различные проверки на выход за разные пределы остатка и т.п., в большинстве случаев они являются критерием возможности проведения операции и этим проверкам нужен остаток. Если существует пул необработанных движений, как работают такие механизмы?

Для всякой аналитики используются только "обработанные" движения - то есть работа идет с таблицей остатков. Ну а для проверок естественно надо учесть необработанные. Но тут хитрость в том, что необработанных движений крайне мало, поэтому дополнительный запрос считающий сумму необработанных движений работает быстро. То есть у нас есть два остатка - аналитический (время получения которого не зависит от количества движений) и оперативный (получение которого больше на время суммирования маленького списка неучтенных движений).
Сам пул необработанных движений можно организовать по-разному.
Первый способ - колонка в списке движений, принимающая значения 1 (необработано) и null (обработано) - индекс по такой колонке очень мал и поиск по нему осуществляется быстро.
Второй - складывать необработанные движения в отдельную таблицу (а после обработке оттуда удалять). Мы исползовали именно этот способ - так как хотелось минимизировать воздействие "отложенного" расчета на код системы (чтобы это было этаким дополнительным функционалом, устанавливаемым/убираемым по желанию).
24 дек 07, 11:54    [5087172]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Alex S
Member

Откуда:
Сообщений: 293
Bogdanov Andrey
Для всякой аналитики используются только "обработанные" движения - то есть работа идет с таблицей остатков. Ну а для проверок естественно надо учесть необработанные. Но тут хитрость в том, что необработанных движений крайне мало, поэтому дополнительный запрос считающий сумму необработанных движений работает быстро. То есть у нас есть два остатка - аналитический (время получения которого не зависит от количества движений) и оперативный (получение которого больше на время суммирования маленького списка неучтенных движений).
Сам пул необработанных движений можно организовать по-разному.
Первый способ - колонка в списке движений, принимающая значения 1 (необработано) и null (обработано) - индекс по такой колонке очень мал и поиск по нему осуществляется быстро.
Второй - складывать необработанные движения в отдельную таблицу (а после обработке оттуда удалять). Мы исползовали именно этот способ - так как хотелось минимизировать воздействие "отложенного" расчета на код системы (чтобы это было этаким дополнительным функционалом, устанавливаемым/убираемым по желанию).
Спасибо за ответы. А в целом - реальные тесты показывали значительный/заметный/(другое) прирост производительности с пулом?
25 дек 07, 15:14    [5094200]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
Alex S
Спасибо за ответы. А в целом - реальные тесты показывали значительный/заметный/(другое) прирост производительности с пулом?

Да, в описанной выше ситуации:
Bogdanov Andrey
учетных единиц было мало (порядка сотни), а конкурирующих транзакций - много (до полусотни процессов и десятки тысяч операций в час у каждого).
количество обрабатываемых транзакций возросло в несколько раз (раза в три-семь, точнее не скажу - не помню). Вполне вероятно, что итоговая схема не была оптимальной именно для этого случая, но это было решение полученное путем минимальных модификаций уже работавшей системы. И обеспечило простую поддежрку версии совместно с остальными.
25 дек 07, 16:05    [5094675]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
a7exander
Member

Откуда:
Сообщений: 39
хочу рассказать о своем методе расчета остатков на любую дату, несмотря на то что метод очень прост никто его почемуто не предложил.

сперва у нас использовалось тупое суммирование по указанную дату:
select SUM(quant),name from reestr where dat<:d group by name

но когда реестр дорос до 3млн записей стало подтормаживать.

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

1. если построен индекс по полю reestr.dat то любым способ добится того чтобы он НЕ ИСПОЛЬЗОВАЛСЯ в данном запросе, например так:
select SUM(quant),name from reestr where coalesce(dat,dat)<:d group by name
разницы на небольших таблицах заметно не будет, но на таблицах с миллионами строк у меня только одним этим удалось поднять скорость расчета в 50-100(!!!) раз. поскольку селективность индекса по dat очень высока оптимизатор запросов пытается использовать его в первую очередь. После такой оптимизации запрос на многомиллионной таблице стал отрабатывать вместо 15секунд 200миллисекунд.

2. если не хочется суммировать многие тысячи строк с начала времен то можно суммировать строки с конца!
для этого в реестр для каждого склада и каждой номенклатуры добавляется одна запись, которая будет содержать остаток на сейчас со знаком минус и датой движения для которой (reestr.dat) будет к примеру 01.01.2100 года. тогда получать остатки можно запросом
select SUM(quant),name from reestr where dat>=:d group by name
а строчку с остатками на сейчас поддерживать актуальной при помощи триггеров. И это при том что фактически возможность считать по этой же таблице старым методом НЕ ТЕРЯЕТСЯ, надо лишь только в запросах не забывать указывать по какую дату считать ;)

оба метода успешной мной эксплуатируются :)
26 дек 07, 16:51    [5100149]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
TORT
Member

Откуда:
Сообщений: 1115
У нас похожий метод расчета... Только хранятся две вещи... Итоговый остаток по товарам, то есть сумма по всему движению... Ну и обсчитывается все движение от нужной даты... Строк в БД примерно 150 млн...
26 дек 07, 17:44    [5100481]     Ответить | Цитировать Сообщить модератору
 Re: быстрое получение остатков на дату  [new]
Bogdanov Andrey
Member

Откуда: Да уже и сам не знаю...
Сообщений: 2203
a7exander

1. если построен индекс по полю reestr.dat то любым способ добится того чтобы он НЕ ИСПОЛЬЗОВАЛСЯ в данном запросе, например так:

У Oracle оптимизатор, собака, умный, и индекс в таком случае и сам не всегда использует.

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

Ну если уж хранить остаток, то можно тогда не только на сейчас хранить, но и на промежуточные дату. Все равно на проблему с блокировками уже нарвались.
26 дек 07, 18:20    [5100653]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Сравнение СУБД Ответить