Здравствуйте, Fox007, Вы писали:
F>Стандартная задача для бухгалтерских систем: F>имеется таблица содержащая бухгалтерские проводки. Нужно как можно быстрее считать остаток на определенную дату. Дополнительное условие: любые проводки могут отредактированы с течением времени.
F>В данный момент это реализовано на MySQL 4 простой выборкой по всей таблице (суммирование значения поля). В таблице пока немного записей, однако уже сейчас появляются проблемы с большой длительностью запроса. В дальнейшем таблица ещё увеличится раз в 10.
F>Какие способы решения задачи существуют применительно к различным СУБД? У меня пока только один вариант — сделать доп. таблицу с остатками на определ. даты (на неделю или месяц) и задать триггеры F>на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
Завести поле в этой же таблице, и при изменении пересчитывать это поле у данной проводки и у всех последующих.
Здравствуйте, Fox007, Вы писали:
F>Какие способы решения задачи существуют применительно к различным СУБД? У меня пока только один вариант — сделать доп. таблицу с остатками на определ. даты (на неделю или месяц) и задать триггеры F>на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
Вроде только один — индексированные вью. (существуют в оракле и в MS SQL). Фактически, это те же самые триггеры, только встроенные. Ну и конечно надо будет правильно вычислять остаток на дату, пользуясь строкой из вью и детальными записями за период.
... << RSDN@Home 1.1.4 beta 3 rev. 185>>
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, Fox007, Вы писали:
V>>пользуетесь ли группировкой? V>>насколько оптимально составлен запрос (все ли индексы, чего говорит explain)? F>упрощённо запрос выглядит так: F>SELECT SUM(Amount) FROM Stock_Trans WHERE Date_Time < '<date>';
ну по крайней мере по date то индекс есть?
а может даже будет быстрее если будет индекс (date,Amount)
тогда база в саму таблицу наверно вообше лезть не будет,
только просмотром индекса обойдется
F>конечно на самом деле там ещё кое-какие условия, однако F>это не суть важно.
важно важно..
F>как здесь можно применить индексы? F>а что должен говорить explain? у меня он просто выдаёт F>типы столбцов.
я имел ввиду explain <весь запрос>..
а не только таблицу
как трактовать и оптимизировать описано здесь: http://dev.mysql.com/doc/mysql/ru/EXPLAIN.html
V>>1. триггеры только только появились в новой альфа версии mysql-5.0 и очень сырые F>в принципе я не исключаю перехода на другую СУБД ради решения этой задачи.
ну тогда может быть стоит посмотреть в сторону OLAP..
хотя имхо триггеров должно вам хватить
Стандартная задача для бухгалтерских систем:
имеется таблица содержащая бухгалтерские проводки. Нужно как можно быстрее считать остаток на определенную дату. Дополнительное условие: любые проводки могут отредактированы с течением времени.
В данный момент это реализовано на MySQL 4 простой выборкой по всей таблице (суммирование значения поля). В таблице пока немного записей, однако уже сейчас появляются проблемы с большой длительностью запроса. В дальнейшем таблица ещё увеличится раз в 10.
Какие способы решения задачи существуют применительно к различным СУБД? У меня пока только один вариант — сделать доп. таблицу с остатками на определ. даты (на неделю или месяц) и задать триггеры
на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
Здравствуйте, Fox007, Вы писали:
F>Стандартная задача для бухгалтерских систем: F>имеется таблица содержащая бухгалтерские проводки. Нужно как можно быстрее считать остаток на определенную дату. Дополнительное условие: любые проводки могут отредактированы с течением времени.
F>В данный момент это реализовано на MySQL 4 простой выборкой по всей таблице (суммирование значения поля). В таблице пока немного записей, однако уже сейчас появляются проблемы с большой длительностью запроса. В дальнейшем таблица ещё увеличится раз в 10.
пользуетесь ли группировкой?
насколько оптимально составлен запрос (все ли индексы, чего говорит explain)?
F>Какие способы решения задачи существуют применительно к различным СУБД? У меня пока только один вариант — сделать доп. таблицу с остатками на определ. даты (на неделю или месяц) и задать триггеры F>на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
1. триггеры только только появились в новой альфа версии mysql-5.0 и очень сырые
2. наверно как то еще view можно приспособить (та же самая альфа)
Здравствуйте, Fox007, Вы писали:
F>на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Fox007, Вы писали:
F>>на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
W>Куда же проще? Только ИМХО не триггеры, а ХП.
V>пользуетесь ли группировкой? V>насколько оптимально составлен запрос (все ли индексы, чего говорит explain)?
упрощённо запрос выглядит так:
SELECT SUM(Amount) FROM Stock_Trans WHERE Date_Time < '<date>';
конечно на самом деле там ещё кое-какие условия, однако
это не суть важно.
как здесь можно применить индексы?
а что должен говорить explain? у меня он просто выдаёт
типы столбцов.
V>1. триггеры только только появились в новой альфа версии mysql-5.0 и очень сырые
в принципе я не исключаю перехода на другую СУБД ради решения этой задачи.
V>2. наверно как то еще view можно приспособить (та же самая альфа)
и как это сделать?
Здравствуйте, Fox007, Вы писали:
F>Какие способы решения задачи существуют применительно к различным СУБД?
Навскидку — я бы попробовал indexed view (в терминологии MS SQL), которая бы агрегировала операции за определенный период, например день, и потом считал бы остатки уже по этой view. Есть ли аналог indexed view в MySQL — не знаю.
по моему мнению нужно пересмотреть архитектуру системы, по вашим словам для того чтобы посчитать остаток по какому нибуть счёту нужно полностью пересчитать все документы которые изменяют этот счёт.
Я предложил бы вам следующюю архитектуру:
1. Создать сущность СЧЁТ в ней описать необходимые реквизиты счёта(указать его тип, возможные ограничения ну и другие.., в том числе текущий остаток) только по этой сущности можно получить текущие остатки !
2. Создать сущность документов на основании которых будут изменяться остатки по счетам, основные реквизиты документов:
— счёт по дебету (связь с таблицей счетов)
— счёт по кредиту (связь с таблицей счетов)
— сумма в валюте учёта
— дата проводки
— состояние (характеризует текущее состояние документа например: не обработан, проведён, ликвидирован.. и др.)
3. Создать сущность ВЫПИСКА ПО СЧЁТУ эта сущность должна быть связана с сущностью СЧЁТ и хранить детальную выписку по счёту. основные реквизиты здесь будут:
— счёт (связь с таблицей счетов)
— документ (связь с таблицей документов) отсюда мы легко вытянем сумму и другие реквизиты проводки..
— сальдо до проводки и после проводки
— для удобства можно хранить прямо в этой таблице и сумму причём развёрнуто по дебету и кредиту..
4. Создать сущность, назовём её так АРХИВ УЧЁТНЫХ СЧЕТОВ где будем хранить остатки по счетам на определённые даты например на начало каждого учётного периода. Познее по этой сущности можно будет легко получать отчёты при минимальной загрузке системы.
теперь основное внимание стоит уделить такому понятию как "проводка" документа и "ликвидация" проведённого документа, варианты реализации различны: клиентское приложение с прямым редактированием таблиц, хранимые процедуры, пакеты ORACLE; последние с моей точки зрения более предпочтительны. Операции реализовать в рамках транзакции, в операции соответствующим образом изменять вышеуказанные таблицы, при проводке:
— дебетуем один счёт и кредитуем другой (изменяем таблицу СЧЁТ)
— добавляем запись в таблицу ВЫПИСКА ПО СЧЁТУ
— устанавливаем дату проводки документа
— изменяем реквизит документа СОСТОЯНИЕ на проведён
расчёт архива производить периодически при закрытии периода
ну и ликвидация в обратном порядке:
— дебетуем один счёт и кредитуем другой (изменяем таблицу СЧЁТ в обратном порядке)
— удаляем запись в таблице ВЫПИСКА ПО СЧЁТУ
— изменяем реквизит документа СОСТОЯНИЕ на ликвидирован
— если необходимо то пересчитываем архив
вот такая примерная структура учётного ядра системы, которая опробована и успешно работает
примерные характеристики системы:
количество пользователей ~1000 из них постоянно находящихся в рабочее время в системе ~500-600
количиство активных сессий ~15-20
количество документов за месяц ~350 000
количество открытых счетов ~130 000
AC>Поясни тупому-убогому, что есть "Table API" ?! w>> подход, который расписал EXELENS.
Вместо SQL-команд insert, update и dеlete, приложение вызывает соответствующие ХП. Иногда и вместо select. И это — единственный путь для изменения данных в таблице.
Привет, wildwind!
Вы пишешь 12 октября 2004:
AC>> Поясни тупому-убогому, что есть "Table API" ?! w>>> подход, который расписал EXELENS.
w> Вместо SQL-команд insert, update и dеlete, приложение вызывает соответствующие ХП. w> Иногда и вместо select. И это — единственный путь для изменения данных в таблице.
Слово API, тут "ни пришей — ни пристегни"...
Я не читал чего там расписывал товарисчщ EXELENS,
но "нАрмАльныЕ пАцАннЫ", делают в таких случаях VIEW-обёртку
вокруг таблицы, с написанием соответствующих триггеров для оной.
Здравствуйте, vvaizh, Вы писали:
F>>как здесь можно применить индексы? F>>а что должен говорить explain? у меня он просто выдаёт F>>типы столбцов.
V>я имел ввиду explain <весь запрос>.. V>а не только таблицу V>как трактовать и оптимизировать описано здесь: V>http://dev.mysql.com/doc/mysql/ru/EXPLAIN.html
посмотрел explain <весь запрос>.
оказалось что у меня все foreign key не проиндексированы (а я то наивный полагал что они по-умолчанию индексированы).
проиндексировал. теперь все просто летает!!!
Здравствуйте, EXELENS, Вы писали:
EXE>1. Создать сущность СЧЁТ в ней описать необходимые реквизиты счёта(указать его тип, возможные ограничения ну и другие.., в том числе текущий остаток) только по этой сущности можно получить текущие остатки !
к сожалению текущие остатки почти никогда не требуются пользователем (т.к. транзакции заносятся постоянно, а надо знать, к примеру, на пред. день или квартал или неделю).
EXE>4. Создать сущность, назовём её так АРХИВ УЧЁТНЫХ СЧЕТОВ где будем хранить остатки по счетам на определённые даты например на начало каждого учётного периода. Познее по этой сущности можно будет легко получать отчёты при минимальной загрузке системы.
кажется, такой вариант я и предложил в качестве своего..
Здравствуйте, Sinclair, Вы писали:
S>Здравствуйте, Fox007, Вы писали:
F>>Какие способы решения задачи существуют применительно к различным СУБД? У меня пока только один вариант — сделать доп. таблицу с остатками на определ. даты (на неделю или месяц) и задать триггеры F>>на редактирование таблицы проводок, чтобы они изменяли соответствующие остатки в доп. таблице. Нет ли более простых способов?
S>Вроде только один — индексированные вью. (существуют в оракле и в MS SQL). Фактически, это те же самые триггеры, только встроенные. Ну и конечно надо будет правильно вычислять остаток на дату, пользуясь строкой из вью и детальными записями за период.
И еще кое что — в индексированных представлениях хранить лучше не остатки, а обороты группированные, например по кварталам. Вычисление остатка на любой момент или оборота или за любой период — как два пальца... Без всяких архивов и т.д. выборка остатков по всем товарам (~ 20000 наименований, несколько лет работы сети магазинов, сервер HP tc4100) — несколько секунд.
Здравствуйте, Alex.Che, Вы писали:
AC>Слово API, тут "ни пришей — ни пристегни"...
IMHO это довольно устоявшийся термин, хотя возможно уже уходящий в прошлое.
AC>но "нАрмАльныЕ пАцАннЫ", делают в таких случаях VIEW-обёртку AC>вокруг таблицы, с написанием соответствующих триггеров для оной.
Подходы могут быть разные, и такой тоже. Однако он не всегда возможен (СУБД не поддерживает instead-of триггеры), и не всегда выгоден (например, набор полей для "select" и "insert" может отличаться).
Привет, wildwind!
Вы пишешь 13 октября 2004:
AC>> Слово API, тут "ни пришей — ни пристегни"...
w> IMHO это довольно устоявшийся термин, хотя возможно уже уходящий в прошлое.
Здравствуйте, _d_m_, Вы писали: ___>И еще кое что — в индексированных представлениях хранить лучше не остатки, а обороты группированные, например по кварталам. Вычисление остатка на любой момент или оборота или за любой период — как два пальца... Без всяких архивов и т.д. выборка остатков по всем товарам (~ 20000 наименований, несколько лет работы сети магазинов, сервер HP tc4100) — несколько секунд.
Именно это я и имел в виду
... << RSDN@Home 1.1.4 beta 3 rev. 185>>
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
