iBase.ru Forum

Всем привет!

Есть таблица архива счетов, назовем её AccArch. Вот такая (несущественное опущено):

CREATE TABLE AccArch (
    OperDate  DATE NOT NULL,
    AccID     INTEGER NOT NULL,
    Balance   DOUBLE PRECISION NOT NULL
);

В случае, если в некоторую дату по счету были проводки, в эту таблицу сохраняется баланс счета на конец даты. Если проводок не было - не сохраняется. Операция выяснения баланса по счету на дату является узким местом для многих отчетов. Время выборки было бы минимальным, если бы архивация происходила каждый день, но рост базы при этом получается неприемлемым.

Сейчас на таблице (в частности) создан индекс
CREATE UNIQUE DESCENDING INDEX ACCARCH_PK ON ACCARCH (ACCID, OPERDATE);

Поиск баланса ведется следующим запросом:

Код: Выделить всё

  SELECT FIRST 1 Balance
  FROM AccArch
  WHERE AccID = :i_AccID
    AND OperDate <= :i_Date
  PLAN (ACCARCH INDEX (ACCARCH_PK))
  ORDER BY AccID DESC, OperDate DESC

И это сейчас более-менее устраивает всех.

Используются ли оба сегмента индекса для поиска?

Может быть есть более эффективные способы решения этой задачи?

а

Код: Выделить всё

ORDER BY AccID DESC

зачем ? он же там один

WHERE AccID = :i_AccID

В случае, если в некоторую дату по счету были проводки, в эту таблицу сохраняется баланс счета на конец даты. Если проводок не было - не сохраняется. Операция выяснения баланса по счету на дату является узким местом для многих отчетов. Время выборки было бы максимальным, если бы архивация происходила каждый день, но рост базы при этом получается неприемлемым.

а что такое архивация ? чет никак не пойму уже второй топик =(
да и по слову архивация должно что-то уменьшатся, а у вас увеличивается.

Да, один. Видимо осталось от варианта, когда там был план ORDER. Да, некоторое время там был план ORDER и производительность тоже устраивала сотрудников Антона, но в другом случае оказалась неприемлемой, перешли к варианту с планом INDEX.

Под архивацией в данном случае понимается операция откладывания в архив. Цель ведения архива - возможность получения некоторых данных (баланса) на произвольную указанную дату, при этом данные именно на эту дату в архиве могут и не быть. Например есть такой архив

Код: Выделить всё

Дата          Баланс
01.01.2008      5.00
05.01.2008     10.00
12.01.2008     15.00

Так вот, на вопрос "каким был баланс на третье" ответ должен быть 5.00. На вопрос о балансе за пятое - 10.00, за седьмое - тоже 10.00.

У тебя кажись полуторка? Какой план она выдаёт, если этот прямо не писать?
ЕМНИП, полуторка умеет делать PLAN (ACCARCH ORDER ACCARCH_PK INDEX (ACCARCH_PK)), хотя и не показывает его правильно.

Вот тут кстати, посмотри, я жаловался на похожую проблему.

Да, сейчас у всех клиентов кроме одного используется 1.5. Насколько я знаю, возможность явно совмещать INDEX и ORDER появилась в 2.0. Согласен, много раз говорилось, что 1.5 может так делать и не говорить об этом в плане.

Если план не задавать явно, получаю
PLAN (ACCARCH ORDER ACCARCH_PK)

Попрошу Антона организовать тест четырех вырисовывающихся на данный момент вариантов на запасной базе.

Попробовал ваш вариант плана на своих данных - получился редкостный тормоз.
Как ни парадоксально, самым быстрым оказался вот такой вариант (на моих данных):

Код: Выделить всё

select first 1 Balance
  from AccArch
  where AccID = :i_AccID and OperDate = (select max(OperDate) from AccArch where AccID = :i_AccID and OperDate <= :i_Date
                                         PLAN (AccArch INDEX (ACCARCH_PK)))
  PLAN (AccArch INDEX (ACCARCH_PK))
  order by AccID desc, OperDate desc

Посмотрел то обсуждение на sql.ru. Влад там предложил вариант, в котором сегмент даты идет первым в индексе. Добавил к тестовой процедуре этот вариант и тот, что ты привел. Посмотрим, что получится.

В случае с датой тут есть тонкость - план будет перебирать в порядке индекса.
Т.е. если дата в основном сегодняшняя, то он будет её получать мгновенно. А вот если месячной давности - пока ещё до туда доберётся, пол-таблицы переберёт.

Мм, там ведь кроме даты есть AccID у меня и parent с ttype у тебя. То есть нечто, для чего хранятся данные о чем-то. И вроде как поиск по ним должен осуществляться. Одно из сомнений как раз и было в порядке следования сегментов индекса.

Другого порядка быть не может, если не используешь других индексов, потому как сегменты используются по равенству до первого "больше-меньше".
Так что если б дата стояла первая, дальше индекс бы не пошёл.
Влад мне советовал совсем по другому поводу, у меня несколько индексов, и он только предлагал модифицировать один, который только по дате.

Итак результаты измерений. В них участвуют следующие запросы:

Код: Выделить всё

0)  Текущая реализация (план INDEX).
    SELECT FIRST 1 Balance
    FROM AccArch
    WHERE AccID = :i_AccID
      AND OperDate <= :i_Date
    PLAN (ACCARCH INDEX (ACCARCH_PK))
    ORDER BY AccID DESC, OperDate DESC

1)  Пожелание Attid убрать AccID из сортировки
    SELECT FIRST 1 Balance, DBTurns, CRTurns
    FROM AccArch
    WHERE AccID = :i_AccID
      AND OperDate <= :i_Date
    PLAN (ACCARCH INDEX (ACCARCH_PK))
    ORDER BY OperDate DESC

2)  Без явного указания плана
    SELECT FIRST 1 Balance, DBTurns, CRTurns
    FROM AccArch
    WHERE AccID = :i_AccID
      AND OperDate <= :i_Date
    ORDER BY AccID DESC, OperDate DESC
    INTO o_Balance, o_DBTurns, o_CRTurns;

3)  С планом ORDER вместо INDEX.
    SELECT FIRST 1 Balance, DBTurns, CRTurns
    FROM AccArch
    WHERE AccID = :i_AccID
      AND OperDate <= :i_Date
    PLAN (ACCARCH ORDER ACCARCH_PK)
    ORDER BY AccID DESC, OperDate DESC

4)  Без запроса. Хотелось посмотреть, сколько идет выборка самих счетов.

5)  Вариант со сменой порядка сегментов. Возник, как непонимание. Проверяли, результата действительно не было.

6)  Вариант, отработавший быстрее всего у WildSery.
    SELECT FIRST 1 Balance, DBTurns, CRTurns
    FROM AccArch
    WHERE AccID = :i_AccID 
      AND OperDate = (SELECT MAX(OperDate) 
                      FROM AccArch 
                      WHERE AccID = :i_AccID 
                        AND OperDate <= :i_Date
                      PLAN (AccArch INDEX (ACCARCH_PK)))
    PLAN (ACCARCH INDEX (ACCARCH_PK))
    ORDER BY AccID DESC, OperDate DESC

После каждого измерения делался reconnect. Естественно, большая часть данных была в кэше RAID или файловом кэше. На интересность результатов это не влияло. Замеров было два. Один по филиалу с ~20k счетов. По отработавшим за разумное время на 20k доплнительно проверили на ~700k счетов. Помеченных звездочкой мы не дождались. Оборвали генератором. Чтения приводятся по анализу производительности. И да, во втором методе показало десять чтений из архива.

Код: Выделить всё

Метод  Время выполнения  Чтения из
       (милисекунд)      AccArch

0        1563                 20759
1       19015 *              579380
2      111719 *                  10
3        1609                 20759
4         922                     0
6       54484 *              693565

Далее замеры на филиале с ~700k счетов.

Код: Выделить всё

Метод  Время выполнения  Чтения из
       (милисекунд)      AccArch

0       70156                767449 
3       24266                767449
4       10609                     0

Выводы.
1) Возможно, я ошибаюсь насчет того, что именно работает медленно. Вклад простого чтения счетов всё таки не пустой.

2) То, что план ORDER для организации Антона лучше, подтверждается. Сегодня попробуют его на рабочей. Но то, что в другом случае построение баланса в таком варианте не заканчивалось, тем не менее не могу считать опровергнутым. Было ж ведь.

WildSery писал(а):Попробовал ваш вариант плана на своих данных - получился редкостный тормоз.

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

Dmitry Dyachenko писал(а): Может быть есть более эффективные способы решения этой задачи?

Для точечного поиска одной записи это оптимально и довольно быстро. Единственно - на некоторых данных одной и той же структуры этот композит хорош, на некоторых более полезными могут оказаться два раздельных индекса по инту и дате. Если же нужно "табличное" представление большой выборки, да ещё, скажем, с сортировкой натуралом, или полный перебор для выполнения каких-то расчётов - начинаем кряхтеть. И тут приходится отступить на шаг и подумать о том, насколько незыблемы исходные представления о решаемой задаче и насколько нужно то, что кажется интуитивно нужным. Один намёк такого рода сегодня ты уже пропустил мимо ушей. Но это в общем свойственно человеческой природе

О чём навскидку можно подумать.

1. А настолько ли уж пухнет база, если хранить ежедневный итог независимо от наличия движений? Сколько там счетов-то? Множим на 365 и за год, очень может быть, получим не такую уж астрономическую величину, чтобы жертвовать возможностью простейших джойнов.
2. А нужно ли на самом деле знать сальдо на любой день? Может достаточно на конец закрытых отчётных периодов (месяц) и текущее в первом незакрытом? Будут чутка разные запросы по истории и по оперативке и всего делов.

Ну и так далее, ты свою задачу лучше меня видишь.

Merlin писал(а): Единственно - на некоторых данных одной и той же структуры этот композит хорош, на некоторых более полезными могут оказаться два раздельных индекса по инту и дате.

Похоже, что такое и происходит с различием эффективности INDEX и ORDER у разных клиентов. Попробуем раздельные индексы.

Merlin писал(а): Если же нужно "табличное" представление большой выборки, да ещё, скажем, с сортировкой натуралом, или полный перебор для выполнения каких-то расчётов - начинаем кряхтеть.

Делаем такое процедурами, вызывающими ту, ищущую баланс на дату.

Merlin писал(а): 1. А настолько ли уж пухнет база, если хранить ежедневный итог независимо от наличия движений? Сколько там счетов-то?

Единицы миллионов у Антона. Сильно. В архиве кроме ид. и даты ещё пять полей DOUBLE PRECISION. Отказались от полной архивации давно, так что конкретных значений никто не помнит. Хотя один из клиентов до недавнего времени использовал полную архивацию. Потом и они забросили, свернули.

Merlin писал(а): 2. А нужно ли на самом деле знать сальдо на любой день? Может достаточно на конец закрытых отчётных периодов (месяц) и текущее в первом незакрытом?

Ага. Выписки строят постоянно и на любые даты, балансы тоже могут. И редко на конец цикла. Хранить данные на конец цикла, а на указанную дату получать по оборотам? Можно попробовать, но что-то кажется, что хуже будет.

Merlin писал(а): И тут приходится отступить на шаг и подумать о том, насколько незыблемы исходные представления о решаемой задаче и насколько нужно то, что кажется интуитивно нужным. Один намёк такого рода сегодня ты уже пропустил мимо ушей.

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

Мда. Судя по тестам и возникшей дополнительной информации о запросах, эти данные получаются все 700к раз. Ужас.

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

WildSery писал(а):Мда. Судя по тестам и возникшей дополнительной информации о запросах, эти данные получаются все 700к раз. Ужас.

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

Dmitry Dyachenko, трудно понять чужую структуру но

Ага. Выписки строят постоянно и на любые даты, балансы тоже могут. И редко на конец цикла. Хранить данные на конец цикла, а на указанную дату получать по оборотам? Можно попробовать, но что-то кажется, что хуже будет.

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

еще одна мысль может глупая но вроде же

Код: Выделить всё

AND OperDate <= :i_Date

это типа две операции не будет ли ускорения от

Код: Выделить всё

AND OperDate < :i_Date+1

Attid писал(а):никак не пойму как этот запрос может влиять, когда сам он доли секунд выполняется =/

В смысле? Он же 700 тыс. раз выполняется. Сравни время выполнения с пустой прогонкой - больше половины времени занимает.

Attid писал(а):не будет ли ускорения от

Не будет.

Что быстрее - JE или JLE и влияние этого фактора на эффективность использования пахотных земель Чукотки.

WildSery писал(а):У меня хотя бы отсортировано по дате выбирается во внешнем, и новое значение получается только при смене даты...

Ничего не понял. У тебя в запросах есть некая сортировка по дате. Наверное по архиву. Структура таблицы похожа? Ты выбираешь сразу поток данных из архива, а не 700k раз по разу?

iBase.ru Forum

Вопрос оптимизации чтения данных из архива

Вопрос оптимизации чтения данных из архива

Re: Вопрос оптимизации чтения данных из архива

Re: Вопрос оптимизации чтения данных из архива

Re: Вопрос оптимизации чтения данных из архива