ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / быстрое получение остатков на дату
51 сообщений из 51, показаны все 3 страниц
  все  
быстрое получение остатков на дату
    #34970365
DobPilot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DobPilot
Участник
Дано таблица, в которой будут часто делаться запросы на получение остатка на дату, вида

select summ(balance) from table where date < :beginDate group by userAccount;

Возникает мысль, с увеличением числа строк в таблице будут медленнее выполняться запросы. -)

Вопрос позновательный, как бытовую проблему можно решить на различных СУБД? Заодно можно и тестики поделать, различных решений. )


з.ы.
Сам пользую postreSQL и задал вопрос в соотв. топике.
...
Рейтинг: 0 / 0
28.11.2007, 01:02
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #34970415
Yo.!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Yo.!
Гость
в оракле для такой задачи есть materialized view, которое будет хранить агрегты (например на день) и подсовывать оптимизатору это вью вместо оригинальной таблички.
...
Рейтинг: 0 / 0
28.11.2007, 01:55
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #34970750
Фотография Apex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Apex
Участник
Думаю эта задача во всех СУБД решается с помощью триггера и еще одной таблицы. В случае Оракла - триггер и таблица системные, что несомненно красивее.
-------------------------------------------------------
Автор благодарит алфавит за любезно предоставленные ему буквы.
...
Рейтинг: 0 / 0
28.11.2007, 10:02
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #34970832
pkarklin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
pkarklin
Участник
В MS SQL задача решается с помощью построения indexed view. Оптимизатор сможет использовать это представление и без переписывания оригинального запроса на использование представления в нем вместо базовой таблицы.
...
Рейтинг: 0 / 0
28.11.2007, 10:27
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #34971090
locky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
locky
Участник
Завести себе фиксированные остатки.
Тогда с ростом таблицы скорость запроса не будет расти выше
определенного уровня.
Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
28.11.2007, 11:31
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #34971498
Sergey Ch
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sergey Ch
Участник
DobPilotВозникает мысль, с увеличением числа строк в таблице будут медленнее выполняться запросы. -)...
Для этих целей, как Вам правильно подсказали выше - используется подход DataWarehouse...

Good luck!
...
Рейтинг: 0 / 0
28.11.2007, 13:07
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35016715
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Крутая у вас таблица получится.... Кол-во номенклатур * Кол-во дат.... = ? Со временем таблица еще больше получится....
...
Рейтинг: 0 / 0
18.12.2007, 10:23
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35016836
Фотография Гликоген
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гликоген
Участник
Не зависит от СУБД.
В тупых учетных системах - только движения и начальный+текущий остаток.
В чуть более продвинутых (1С) - снимок остатка на каждый отчетный период (обычно месяц) + движения между.
В хранилищах данных - как правило, на каждый день.
...
Рейтинг: 0 / 0
18.12.2007, 10:55
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35016860
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
мне кажется это вопрос больше в тему "Проектирование БД"
...
Рейтинг: 0 / 0
18.12.2007, 11:04
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35016930
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTКрутая у вас таблица получится.... Кол-во номенклатур * Кол-во дат.... = ? Со временем таблица еще больше получится....Ну если по большей части номенклатур остатки меняются относительно редко, то и не надо по ним остатки на каждую дату хранить. Добавлять записи, только при изменении остатка. Табличка будет не больше, чем таблица движений, а остатки безо всякого суммирования будут получаться.
...
Рейтинг: 0 / 0
18.12.2007, 11:20
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017180
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Если остатки меняются редко, то и записей о движении будет немного, а следовательно и
select sum(...) будет работать недолго....ИМХО... Зачем огород городить?
...
Рейтинг: 0 / 0
18.12.2007, 12:19
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017268
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
TORTЕсли остатки меняются редко, то и записей о движении будет немного, а следовательно и
select sum(...) будет работать недолго....ИМХО... Зачем огород городить?затем чтобы не суммировать все данные, а взять одну нужную
...
Рейтинг: 0 / 0
18.12.2007, 12:35
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017355
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTЕсли остатки меняются редко, то и записей о движении будет немного, а следовательно и
select sum(...) будет работать недолго....ИМХО... Зачем огород городить?
Типичная структура движений такова: из 10-20 тысноменклатурных единиц по 95 процентам движения происходят пару раз в месяц. Процентам по 4 - раз в два-три дня, а по оставшимся - несколько раз в день. Соответственно для 95 процентов вполне можно и суммировать (за год накопится 20-30 записей), но вот по этому проценту (а именно по нему остатки нужны наиболее часто) суммировть потребуется уже довольно много. Соответственно имея записи об остатках на моменты движений мы не раздуваем существенно таблицу остатков, но зато время получения остатка не зависит от активности номенклатуры.
...
Рейтинг: 0 / 0
18.12.2007, 13:02
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017380
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Если я правильно понял, то Вам придется хранить остатки товара на каждый день, в независимости от того было по нему движение в этот день или нет... Соответственно и таблица у вас будет размером Кол-во товаров * Кол_во дней = Много. А иначе Вам придется все равно по движению суммировать данные... КМК, нет смысла делать такую таблицу... Поправьте меня, если я заблуждаюсь...
...
Рейтинг: 0 / 0
18.12.2007, 13:07
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017388
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Быть может я не правильно понимаю уровень группировки(день, месяц, год) данных? Но по-поему это не играет никакой роли... Либо Вы храните остатки по всем! товарам на каждый день!...Либо СУБД проще и быстрее будет делать select sum(...)
...
Рейтинг: 0 / 0
18.12.2007, 13:10
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017436
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTПоправьте меня, если я заблуждаюсь...Поправляю.

Имеем таблицу
Код: plaintext
1.
2.
3.
4.
Остатки
Номенклатура ссылка
Значение число
Начало дата
Конец дата
При совершении движения в этой таблице у текущей записи по номенклатуре выставляется "Конец" и добавляется новая. Если в один день происходит несколько двжиений, то мы просто модифицируем запись текущую запись.
Таким образом число записей не превышает количества движений, а остаток на любой день достается запросом без суммирования:
Код: plaintext
select Значение from Остатки where Номенклатура=:id and Начало <= :data and Конец > :data
Наличие индексов делает запрос быстрым. Особые эстеты могут его оптимизировать.
Я не говорю, что это единственное верное решение, но в некоторых случаях оно работает очень хорошо.
...
Рейтинг: 0 / 0
18.12.2007, 13:30
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017496
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Я не совсем понимаю зачем Начало и Конец... Поясните, если не трудно...
...
Рейтинг: 0 / 0
18.12.2007, 13:43
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017561
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTЯ не совсем понимаю зачем Начало и Конец... Поясните, если не трудно...Для того чтобы не делать sum и не хранить остатки за все даты.
...
Рейтинг: 0 / 0
18.12.2007, 13:57
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017571
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTЯ не совсем понимаю зачем Начало и Конец... Поясните, если не трудно...Да, я понял - вы хотите сказать, что можно обойтись только Началом. Можно, но в этом случае запрос для получения значения будет более сложным - без подзапроса не обойтись.
...
Рейтинг: 0 / 0
18.12.2007, 13:58
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017576
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Спасибо за подробное пояснение... Но все-таки хотелось бы уточнить... Таким образом Вы предлагаете хранить интервалы, на которых остатки были неизменными? Я правильно понял? Вы тестировали данный подход? Если не серк
...
Рейтинг: 0 / 0
18.12.2007, 13:59
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017581
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Если не секрет на каой СУБД? Какой интервал движения? И какое кол-во номенклатур?
...
Рейтинг: 0 / 0
18.12.2007, 13:59
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017589
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Интересен механиз реализации этого подхода...
...
Рейтинг: 0 / 0
18.12.2007, 14:01
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017620
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTСпасибо за подробное пояснение... Но все-таки хотелось бы уточнить... Таким образом Вы предлагаете хранить интервалы, на которых остатки были неизменными? Я правильно понял? Да, правильно.

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

TORTЕсли не секрет на каой СУБД? Какой интервал движения? И какое кол-во номенклатур?О СУБД вы могли из моего профиля догадаться - Oracle. Но думаю, что такой подход мог бы на любой СУБД использоваться.
Количественные характеристики: из реально работающих есть примеры в которых более 100 тысяч учетных единиц и сотни тысяч движений в день. Но это мало о чем говорит, так как надо учитывать используемую технику и то, что реальные системы не только движения учитывают. Голый тестовый пример для сравнения этой модели с другими я не собирал.
...
Рейтинг: 0 / 0
18.12.2007, 14:09
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017674
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Вопрос с реализацией? Как формируется такая таблица? Триггеры, специальная ХП, клиентское приложение?
...
Рейтинг: 0 / 0
18.12.2007, 14:19
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017894
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTВопрос с реализацией? Как формируется такая таблица? Триггеры, специальная ХП, клиентское приложение?Триггеры или ХП - это не очень интересный вопрос. У нас было несколько реализаций. Стандартно была процедура, добавляющая движения, она же и остатком занималась. Но в триггере это выглядело бы точно так же.
Гораздо более интересный вопрос с обеспечением сериализации - если несколько сеансов одновременно делают движения по одной и той же учетной единице. В общем-то обычная блокировка здесь помогает, но был у нас один исключительный случай, когда учетных единиц было мало (порядка сотни), а конкурирующих транзакций - много (до полусотни процессов и десятки тысяч операций в час у каждого). Тут задержки на блокировках становятся посущественней. Для этого случая был другой вариант, когда заполнением этой таблицы занимался отдельный фоновый процесс.
...
Рейтинг: 0 / 0
18.12.2007, 15:22
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017930
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
Хм... Понятно... Скажите, а на основе MQT(суммарных таблиц) Вы это не пытались организовать? Вот это было бы весьма интересно....
...
Рейтинг: 0 / 0
18.12.2007, 15:30
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35017982
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
Bogdanov Andrey

TORTВы тестировали данный подход? Если не серкНе только тестировал, но и промышленно эксплуатировал. :)
Присоединяюсь.
У меня это сделано на MS SQL на триггерах.
...
Рейтинг: 0 / 0
18.12.2007, 15:41
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35018024
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
TORTСкажите, а на основе MQT(суммарных таблиц) Вы это не пытались организовать? Вот это было бы весьма интересно....Как я уже сказал - я не пытался проводить тесты и сравнивать различные варианты реализации. Ну а в первой половине девяностых материализованных представлений еще не было. Поэтому приходилось все делать руками. :)
...
Рейтинг: 0 / 0
18.12.2007, 15:51
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35023691
dekan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dekan
Участник
Bogdanov Andrey.....у нас один исключительный случай, когда учетных единиц было мало (порядка сотни), а конкурирующих транзакций - много (до полусотни процессов и десятки тысяч операций в час у каждого). Тут задержки на блокировках становятся посущественней. Для этого случая был другой вариант, когда заполнением этой таблицы занимался отдельный фоновый процесс.

А нельзя ли поподробнее вот с этого места? Как избавились от блокировок?
...
Рейтинг: 0 / 0
20.12.2007, 15:29
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35024037
sqllex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sqllex
Участник
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.
....
...
Рейтинг: 0 / 0
20.12.2007, 16:29
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35025444
Alex S
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex S
Участник
По мотивам:
Bogdanov Andrey
Имеем таблицу
Код: plaintext
1.
2.
3.
4.
Остатки
Номенклатура ссылка
Значение число
Начало дата
Конец дата
При совершении движения в этой таблице у текущей записи по номенклатуре выставляется "Конец" и добавляется новая. Если в один день происходит несколько двжиений, то мы просто модифицируем запись текущую запись.
Таким образом число записей не превышает количества движений, а остаток на любой день достается запросом без суммирования:
Код: plaintext
select Значение from Остатки where Номенклатура=:id and Начало <= :data and Конец > :data
Наличие индексов делает запрос быстрым. Особые эстеты могут его оптимизировать.
Я не говорю, что это единственное верное решение, но в некоторых случаях оно работает очень хорошо.
На MSSQL есть решение быстрее:
Имеем таблицу
Код: plaintext
1.
2.
3.
4.
Остатки
Номенклатура ссылка
Значение число
Начало дата
выборка:
Код: plaintext
select top  1  Значение from Остатки where Номенклатура=:id and Начало <= :data order by Начало desc

На Oracle нет top 1 (rownum применить не получится без подзапроса).
...
Рейтинг: 0 / 0
21.12.2007, 10:28
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35025465
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
Alex S
На MSSQL есть решение быстрее:
...
Код: plaintext
select top  1  Значение from Остатки where Номенклатура=:id and Начало <= :data order by Начало desc
На Oracle нет top 1 (rownum применить не получится без подзапроса).
а если надо остатки по нескольким счетам? по одному счету то редко когда нужно
...
Рейтинг: 0 / 0
21.12.2007, 10:32
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026012
Alex S
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex S
Участник
SergSuperа если надо остатки по нескольким счетам? по одному счету то редко когда нужно
Код: plaintext
1.
2.
select Kod
,(select top  1  Значение from Остатки where Номенклатура=Номенклатура.Номенклатура and Начало <= :data order by Начало desc) as Значение
from Номенклатура
но imho лучше сделать udf
Код: plaintext
1.
2.
select Kod
,Остаток(Номенклатура.Номенклатура,:data) as Значение
from Номенклатура
...
Рейтинг: 0 / 0
21.12.2007, 12:53
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026098
!
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
!
Гость
SergSuper Alex S
На MSSQL есть решение быстрее:
...
Код: plaintext
select top  1  Значение from Остатки where Номенклатура=:id and Начало <= :data order by Начало desc
На Oracle нет top 1 (rownum применить не получится без подзапроса).
а если надо остатки по нескольким счетам? по одному счету то редко когда нужно

Универсальный вариант:
Код: plaintext
1.
2.
3.
4.
select  name, value, from reminders r1 where date in ( select max(date) from  reminders r2 
where r2.acc=r1.acc)
and  acc in (  список необходимых  счетов).

Чем больше счетов в списке тем дольше будет работать.
Для каждого счета за одну дату должна быть одна запись об остатках.
...
Рейтинг: 0 / 0
21.12.2007, 13:11
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026315
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
Alex S
На MSSQL есть решение быстрее:
...Давайте не будем в эту тему тащить сравнение MSSQL и Oracle. Да к тому же с отсутствием конкретных цифр. Я не уверен, что приведенное вами решение будет быстрее, чем аналогичное Oracle'овое.

dekanА нельзя ли поподробнее вот с этого места? Как избавились от блокировок?
Ну так конфликт блокировок возникает только при попытке разными процессам модифицировать остаток по одной и той же учетной единице. Если модификацией остатков занимается выделенный процесс, то никакого конфликта у него с самим собой быть не может.
...
Рейтинг: 0 / 0
21.12.2007, 14:07
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026605
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
Alex S SergSuperа если надо остатки по нескольким счетам? по одному счету то редко когда нужно
Код: plaintext
1.
2.
select Kod
,(select top  1  Значение from Остатки where Номенклатура=Номенклатура.Номенклатура and Начало <= :data order by Начало desc) as Значение
from Номенклатура
но imho лучше сделать udf
Код: plaintext
1.
2.
select Kod
,Остаток(Номенклатура.Номенклатура,:data) as Значение
from Номенклатура

Это я всё знаю, спрашивал чтобы всем было понятно что с двумя датами проще :))
...
Рейтинг: 0 / 0
21.12.2007, 15:17
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026617
Alex S
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex S
Участник
Bogdanov Andrey Alex S
На MSSQL есть решение быстрее:
...Давайте не будем в эту тему тащить сравнение MSSQL и Oracle. Да к тому же с отсутствием конкретных цифр. Я не уверен, что приведенное вами решение будет быстрее, чем аналогичное Oracle'овое.
Топикстартер как раз хотел решений для различных СУБД. Быстрее - я имел ввиду не "MSSQL быстрее Oracle", а "реализация с одной датой и top 1 на MSSQL быстрее реализации с двумя датами на MSSQL". Тесты когда-то давно проводил, в итоге остановился на озвученном решении для MSSQL. Кроме того, с одной датой операция записи проще. Если будет время - постараюсь повторить тесты.
...
Рейтинг: 0 / 0
21.12.2007, 15:19
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026646
Alex S
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex S
Участник
SergSuper
Это я всё знаю, спрашивал чтобы всем было понятно что с двумя датами проще :))
А как это проще выглядит?
SergSuperпо одному счету то редко когда нужно
Кстати, в OLTP не так уж и редко
...
Рейтинг: 0 / 0
21.12.2007, 15:25
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35026792
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
Alex Sа "реализация с одной датой и top 1 на MSSQL быстрее реализации с двумя датами на MSSQL". Тесты когда-то давно проводил, в итоге остановился на озвученном решении для MSSQL. Кроме того, с одной датой операция записи проще.Ну вторая дата - это избыточность данных и естественно, ее поддержка требует "накладных расходов". Но некоторые примущества две даты имеют. Например, просуммировать остатки по позициям на определенную дату (то есть любой аналитический запрос) будет попроще и, наверное, побыстрее даже в MS-SQL. Я MS-SQL-ким диалектом не владею. Как с помощью Top поизящней записать запрос нижеприведенному?
Код: plaintext
select sum(Значение) from Остатки where Начало<=:data and Конец > :data
...
Рейтинг: 0 / 0
21.12.2007, 16:10
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35027175
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
Alex S SergSuper
Это я всё знаю, спрашивал чтобы всем было понятно что с двумя датами проще :))
А как это проще выглядит?

одна дата:
Код: plaintext
1.
2.
select Kod
,(select top  1  Значение from Остатки where Номенклатура=Номенклатура.Номенклатура and Начало <= :data order by Начало desc) as Значение
from Номенклатура
две даты:
Код: plaintext
1.
select Kod
, Значение from Остатки join Номенклатура on Остатки.Номенклатура=Номенклатура.Номенклатура and  :data between Начало and Конец
разница будет еще заметней если надо из таблицы Остатков взять еще поля
...
Рейтинг: 0 / 0
21.12.2007, 18:09
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35028382
drev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drev
Участник
ИМХО, остатки нужно хранить не по временным интервалам, а по определённому количеству записей между остатками. Т.о. мы получим более предсказуемую ситуацию.
...
Рейтинг: 0 / 0
23.12.2007, 13:30
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35028800
Фотография SergSuper
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SergSuper
Участник
drevИМХО, остатки нужно хранить не по временным интервалам, а по определённому количеству записей между остатками. Т.о. мы получим более предсказуемую ситуацию.
продемонстрируйте, что ли...
...
Рейтинг: 0 / 0
23.12.2007, 22:38
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35029064
drev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
drev
Участник
SergSuper drevИМХО, остатки нужно хранить не по временным интервалам, а по определённому количеству записей между остатками. Т.о. мы получим более предсказуемую ситуацию.
продемонстрируйте, что ли...

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


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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 достигло некоторого порога).

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

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


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
table summary
(
    id int primary key,
    start_date datetime,
    end_date datetime,
    counter int
    item_id int, --FK
    amount float
)
...
Рейтинг: 0 / 0
24.12.2007, 08:44
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35029427
Alex S
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex S
Участник
SergSuper...
Код: plaintext
1.
select Kod
, Значение from Остатки join Номенклатура on Остатки.Номенклатура=Номенклатура.Номенклатура and  :data between Начало and Конец
разница будет еще заметней если надо из таблицы Остатков взять еще поля
Ну незнаю, в таблице остатков у меня только Id номенклатуры, плоскость остатка, дата , два оборота и остаток. Все остальное в таблице номенклатуры, и так исторически складывается, что основная выборка идет по ней. Ну там, например, не закрытые счета такой-то группы. И одна UDF-ка "остаток" в перечне полей в select.
Bogdanov Andrey...Ну вторая дата - это избыточность данных и естественно, ее поддержка требует "накладных расходов". Но некоторые примущества две даты имеют. Например, просуммировать остатки по позициям на определенную дату (то есть любой аналитический запрос) будет попроще и, наверное, побыстрее даже в MS-SQL. Я MS-SQL-ким диалектом не владею. Как с помощью Top поизящней записать запрос нижеприведенному?
Код: plaintext
select sum(Значение) from Остатки where Начало<=:data and Конец > :data
Да такой запрос быстрее - согласен - на моих данных где-то в 1,5-2 раза. Странно тут то, что мне не приходилось за свою практику задумываться что такой подход:
Код: plaintext
select A....,SUM(Остаток(A.id,...)) from Номенклатура A group by ...
надо ускорять. Вроде никогда не было проблем с доступностью данных для аналитических отчетов.
При этом такой показатель как "операций в секунду" не страдает.

Кстати, хотел спросить про "отложенный" расчет отдельным потоком. Я так понимаю, существует лаг между записью фактического движения и "наличием" фактического остатка по учетной единице. Каким способом обеспечивалась "непротиворечивость" данных?, к примеру если алгоритму требуется величина остатка после записи движения, для следующей операции? Или было управление из алгоритма "можно отложить"/"нельзя отложить" расчет? И еще, например, у меня в расчете есть различные проверки на выход за разные пределы остатка и т.п., в большинстве случаев они являются критерием возможности проведения операции и этим проверкам нужен остаток. Если существует пул необработанных движений, как работают такие механизмы?
...
Рейтинг: 0 / 0
24.12.2007, 11:11
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35029593
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
Alex SКстати, хотел спросить про "отложенный" расчет отдельным потоком. Я так понимаю, существует лаг между записью фактического движения и "наличием" фактического остатка по учетной единице. Каким способом обеспечивалась "непротиворечивость" данных?, к примеру если алгоритму требуется величина остатка после записи движения, для следующей операции? Или было управление из алгоритма "можно отложить"/"нельзя отложить" расчет? И еще, например, у меня в расчете есть различные проверки на выход за разные пределы остатка и т.п., в большинстве случаев они являются критерием возможности проведения операции и этим проверкам нужен остаток. Если существует пул необработанных движений, как работают такие механизмы?
Для всякой аналитики используются только "обработанные" движения - то есть работа идет с таблицей остатков. Ну а для проверок естественно надо учесть необработанные. Но тут хитрость в том, что необработанных движений крайне мало, поэтому дополнительный запрос считающий сумму необработанных движений работает быстро. То есть у нас есть два остатка - аналитический (время получения которого не зависит от количества движений) и оперативный (получение которого больше на время суммирования маленького списка неучтенных движений).
Сам пул необработанных движений можно организовать по-разному.
Первый способ - колонка в списке движений, принимающая значения 1 (необработано) и null (обработано) - индекс по такой колонке очень мал и поиск по нему осуществляется быстро.
Второй - складывать необработанные движения в отдельную таблицу (а после обработке оттуда удалять). Мы исползовали именно этот способ - так как хотелось минимизировать воздействие "отложенного" расчета на код системы (чтобы это было этаким дополнительным функционалом, устанавливаемым/убираемым по желанию).
...
Рейтинг: 0 / 0
24.12.2007, 11:54
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35032810
Alex S
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex S
Участник
Bogdanov AndreyДля всякой аналитики используются только "обработанные" движения - то есть работа идет с таблицей остатков. Ну а для проверок естественно надо учесть необработанные. Но тут хитрость в том, что необработанных движений крайне мало, поэтому дополнительный запрос считающий сумму необработанных движений работает быстро. То есть у нас есть два остатка - аналитический (время получения которого не зависит от количества движений) и оперативный (получение которого больше на время суммирования маленького списка неучтенных движений).
Сам пул необработанных движений можно организовать по-разному.
Первый способ - колонка в списке движений, принимающая значения 1 (необработано) и null (обработано) - индекс по такой колонке очень мал и поиск по нему осуществляется быстро.
Второй - складывать необработанные движения в отдельную таблицу (а после обработке оттуда удалять). Мы исползовали именно этот способ - так как хотелось минимизировать воздействие "отложенного" расчета на код системы (чтобы это было этаким дополнительным функционалом, устанавливаемым/убираемым по желанию). Спасибо за ответы. А в целом - реальные тесты показывали значительный/заметный/(другое) прирост производительности с пулом?
...
Рейтинг: 0 / 0
25.12.2007, 15:14
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35033045
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
Alex S Спасибо за ответы. А в целом - реальные тесты показывали значительный/заметный/(другое) прирост производительности с пулом?
Да, в описанной выше ситуации: Bogdanov Andreyучетных единиц было мало (порядка сотни), а конкурирующих транзакций - много (до полусотни процессов и десятки тысяч операций в час у каждого).количество обрабатываемых транзакций возросло в несколько раз (раза в три-семь, точнее не скажу - не помню). Вполне вероятно, что итоговая схема не была оптимальной именно для этого случая, но это было решение полученное путем минимальных модификаций уже работавшей системы. И обеспечило простую поддежрку версии совместно с остальными.
...
Рейтинг: 0 / 0
25.12.2007, 16:05
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35035935
a7exander
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a7exander
Участник
хочу рассказать о своем методе расчета остатков на любую дату, несмотря на то что метод очень прост никто его почемуто не предложил.

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

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

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

1. если построен индекс по полю reestr.dat то любым способ добится того чтобы он НЕ ИСПОЛЬЗОВАЛСЯ в данном запросе, например так:
Код: plaintext
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 года. тогда получать остатки можно запросом
Код: plaintext
select SUM(quant),name from reestr where dat>=:d group by name
а строчку с остатками на сейчас поддерживать актуальной при помощи триггеров. И это при том что фактически возможность считать по этой же таблице старым методом НЕ ТЕРЯЕТСЯ, надо лишь только в запросах не забывать указывать по какую дату считать ;)

оба метода успешной мной эксплуатируются :)
...
Рейтинг: 0 / 0
26.12.2007, 16:51
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35036131
TORT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TORT
Участник
У нас похожий метод расчета... Только хранятся две вещи... Итоговый остаток по товарам, то есть сумма по всему движению... Ну и обсчитывается все движение от нужной даты... Строк в БД примерно 150 млн...
...
Рейтинг: 0 / 0
26.12.2007, 17:44
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35036220
Bogdanov Andrey
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bogdanov Andrey
Участник
a7exander
1. если построен индекс по полю reestr.dat то любым способ добится того чтобы он НЕ ИСПОЛЬЗОВАЛСЯ в данном запросе, например так:
У Oracle оптимизатор, собака, умный, и индекс в таком случае и сам не всегда использует.

a7exanderдля этого в реестр для каждого склада и каждой номенклатуры добавляется одна запись, которая будет содержать остаток на сейчас со знаком минус
Ну если уж хранить остаток, то можно тогда не только на сейчас хранить, но и на промежуточные дату. Все равно на проблему с блокировками уже нарвались.
...
Рейтинг: 0 / 0
26.12.2007, 18:20
| Ответить | Цитировать | Написать  
быстрое получение остатков на дату
    #35036257
a7exander
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a7exander
Участник
Bogdanov Andrey
a7exanderдля этого в реестр для каждого склада и каждой номенклатуры добавляется одна запись, которая будет содержать остаток на сейчас со знаком минус
Ну если уж хранить остаток, то можно тогда не только на сейчас хранить, но и на промежуточные дату. Все равно на проблему с блокировками уже нарвались.

ну тут тоже можно коечто придумать ;) можно хранить остаток на сейчас не одной строкой. тоесть при проводке каждого документа не апдейтить остаток, а дописывать еще одну строку к остатку, чтобы они в сумме давали тотже остаток, а потом шедулером тупо их "компактить".

а если делать с промежуточными значениями запросы на выборку имхо сильно усложняются и опятьже блокировки, хотя может и меньше.
...
Рейтинг: 0 / 0
26.12.2007, 18:45
| Ответить | Цитировать | Написать  
51 сообщений из 51, показаны все 3 страниц
  все  
Форумы / Сравнение СУБД [игнор отключен] [закрыт для гостей] / быстрое получение остатков на дату
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=35&tid=1553196]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 67ms
get tp. blocked users:
 1ms
others: 228ms
total:  373ms
созд. / загр.: 373ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]