Оптимизация запроса - ranges

[kashperuk]

Привет, всем.
Если кто видел, как формируется запрос для, к примеру, Оборотно - сальдовой ведомости, прошу помочь:

Делаю запрос наподобие - только у меня строки должны отображаться в определенном порядке, и не все из них.

Проблема в том, что если строк больше 350 (приблизительно), то парсер обрезает эту строку запроса, и вылетает с ошибкой понятно.

Вот, примеры:
1).
SELECT * FROM KM_FundOnHandMovement ORDER BY KM_FundOnHandMovement.LineNum ASC WHERE ((LineNum = 1 OR LineNum = 2 OR LineNum = 3 OR LineNum = 4 OR LineNum = 5 OR LineNum = 6 OR ..... OR LineNum = 104))

Получим:
SELECT * FROM KM_FundOnHandMovement ORDER BY KM_FundOnHandMovement.LineNum ASC WHERE ((LineNum >= 1 AND LineNum <= 281))

2).
SELECT * FROM KM_FundOnHandMovement ORDER BY KM_FundOnHandMovement.LineNum ASC WHERE ((LineNum = 1 OR LineNum = 2 OR LineNum = 8 OR LineNum = 13 OR LineNum = 14 OR LineNum = 15 OR LineNum = 16 OR LineNum = 17 OR LineNum = 18 OR LineNum = 19 OR LineNum = 20 OR LineNum = 21 OR LineNum = 22 OR LineNum = 23 OR LineNum = 24 OR LineNum = 25 OR LineNum = 26 OR LineNum = 27 OR LineNum = 28 OR LineNum = 29 OR LineNum = 30 OR LineNum = 31 OR LineNum = 72 OR LineNum = 73 OR .... LineNum = 104))

Получится должно такое:

select * FROM KM_FundOnHandMovement ORDER BY KM_FundOnHandMovement.LineNum ASC WHERE (((LineNum >= 1 AND LineNum <= 2) OR LineNum == 8 OR (LineNum >= 13 AND LineNum <= 31) OR (LineNum >= 72 AND LineNum <= 104)))

3).
SELECT * FROM KM_FundOnHandMovement ORDER BY KM_FundOnHandMovement.LineNum ASC WHERE ((LineNum = 1 OR LineNum = 2 OR LineNum = 8 OR LineNum = 13 OR LineNum = 16 OR LineNum = 19 OR LineNum = 24 OR LineNum = 31 OR LineNum = 32 OR LineNum = 35 OR LineNum = 45 OR LineNum = 47 OR LineNum = 49 OR LineNum = 51 OR LineNum = 53 OR LineNum = 55 OR LineNum = 58 OR LineNum = 61 OR LineNum = 72 OR LineNum = 73 OR LineNum = 76 OR LineNum = 83 OR LineNum = 86 OR LineNum = 88 OR LineNum = 91 OR LineNum = 94 OR LineNum = 104))

А такое так и останется!



Как написать такой оптимизатор? В смысле, как выловить все интервалы, и пропустить все одиночные цифры?

Какие - то другие идеи может по оптимизации.

P.S. Забыл добавить про сам запрос - формируется через QueryBuildRange понятное дело

P.P.S. И еще, строки, которые нужно отображать, все есть - в Set(Types::Integer)

[Maxim Gorbunov]

Сделайте временную таблицу (можно, например, TmpRecId воспользоваться), заполните ее нужными Вам номерами строк и добавьте в запрос Exists Join'ом.

[kashperuk]

Все гениальное просто.

Попробую обязательно.
Спасибо, Максим!

[Wamr]

Ох, не люблю я join с временной таблицей :-\
Поэтому сразу предупрежу, что скорее всего при запуске запроса вы получите table scan по таблице KM_FundOnHandMovement с последующим анализом каждой строки внутри мозгов Аксапты, а уж насколько это страшно в краткосрочной и долгосрочной перспективе думайте сами

[db]

Zitat:

Изначально опубликовано kashperuk
[B]Все гениальное просто. B]

Ох уж эти гении.... Предложили типичный пример борьбы с производительностью системы. именно "с", а не "за"

Может лучше по нашему, по рабоче-крестьянски, но зато с толком и расстановкой


Аксапты под рукой нет, поэтому вместо примера получилось вот такое руководство по программированию

делаете постоянную таблицу, например RecIdFilterTable (saveDataPerCompany=NO)
с тремя int столбцами
RefRecId
SessionId
Cookie

Строим индексы
первый по RefRecId
второй составной по SessioId и Cookie

заносите в таблицу RecId тех записей, которые нужно будет отобрать в каком нито вашем крутом запросе
в SessionId заносите код текущей сессии Axapta - получаем из одноименной функции
в Cookie любую хрень, уникальную в пределах сессии - можно, например,
через Sequence создать свою системную серию или "украсть" очередной RecId через SystemSequence

Связка SessionId - Cookie будет уникальным идентификатором набора RecId в пределах всей Аксапты, запоминаем их значения.
На самом деле достаточно и Cookie, но об этом позже

Теперь пишем свой запрос и дописываем к нему кусок со связкой к RecIdFilterTable

selеct МояСуперТаблица
....
....
exists join RecIdFilterTable
where МояСуперТаблица.RecId == RecIdFilterTable.RefRecId
&& RecIdFilterTable.SessionId == Запомненный код сессии
&& RecIdFilterTable.Cookie == Запомненный COOKIE

Не забываем создать индекс по RecId в МояСуперТаблица
Записи в RecIdFilterTable вставляем через RecordInsertList

В результате имеем все что хотели - быструю выборку неограниченного количества произвольных записей

Осталось только определиться с уборкой мусора в этой таблице

1. После того как запрос отработал чистим таблицу, передавая в качестве критерия для команды delete_from запомненные значения SessionID и Cookie

2. Для того, чтобы убирать мусор, который может появится в случае аварийного падения аксапты между набиванием RecId в таблицу и последующим стриранием этих данных, при старте сессии чистим эту таблицу, используя в качестве критерия только SessionId. Такую чистку следует вызвывать из application.startupPost()

Прочитав все это огромная просьба сначала попробовать, прежде чем начать все это "опускать" (как так, лишняя таблица, запись куда то, лучше мы напишем супервыернутый запрос на 3 экрана с 200 ранжами и т.д., или как предлагали временную таблицу сделаем) Все затраты вычислительных ресуров связанные с описанной инфраструктурой при ее грамотной реализации многократно компенсируются за счет существенного уменьшения времени выполнения запроса.

Для любителей экономить RecId которые так безалаберно растранжириваются здесь на временные записи: класс SystemSequence позволяет запретить генерацию RecId для определенных таблиц - точно сейчас не скажу - нет аксапты под руками - кажется
suspendRecIdGeneration(). Вызовите ее с tablenum(RecIdFilterTable) и никто не тронет драгоценных RecId. Вместо них будут набиваться по очереди 2 "левых" значения, но в данном случае это абсолютно не важно

Кто будет делать, не поленитесь оформить все это в виде класса - вещь универсальная на 100 %
и не забудьте там метод который приделывает фильтр к произвольному QueryBuildDataSource

Добавка:
Про автоматичкскую генерацию "левых" RecId наврал. это оказался побочный эффект одного алгоритма. Так что если не хотите тратить настоящие RecId заполняйте это поле вручную любым значением

[kashperuk]

Сделал все-таки через existJoin с табличкой с номерами строк.

2db:
За совет спасибо. Только поздно. Только сейчас прочитал. А переделывать как сами понимаете желания нет. Возможно в будущем учту.
Отдельная благодарность за то, что напомнили про RecordInsertList - увеличило быстродействие реально в разы.

Не судите строго.

[Wamr]

Есть еще способ, о котором часто забывают

PHP-Code:

static void Job143(Args _args)
{
    Query       q = new Query();
    QueryRun    qr;
    int         i, j;
    str         range;
    ;

    q.addDataSource(tableNum(InventTable));
    for( i = 0; i < 20; i++)
    {
        range = '';
        for( j = 1; j <= 100; j++)
        {
            range += (range ? ',' : '') + int2str(i*100 + j);
        }
        q.dataSourceNo(1).addRange(fieldNum(InventTable, RecId)).value(range);
    }

    qr = new QueryRun(q);

    qr.next();

    info(qr.query().dataSourceNo(1).toString());
} 


Т.е. на одно поле можно накладывать несколько раз range, которые в запросе будут объединяться по ИЛИ.

Как видно из примера для MS-SQL можно спокойно запихнуть 2000 ограничений, а для Oracle мне удалось и более 5000. И никаких join

[db]

2 Wamr


1. Сравните производительность запроса с 2000 ранжами по ИЛИ с join-ом
2. Голову на отсечение дадите, что никакой "вумный товарищ" не выберет 2001 строчку

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

2 kashperuk:
можно все таки посужу строго?

Какими критериями вы руководствуетесь для определения готовности той или иной модификации? Только что на экране появились правильные цифирьки? У вас есть план тестирования производительности? Вы проводите нагрузочные испытания для каждой новой модификации или все это творчество благополучно будут испытывать пользователи после запуска? Вы собирали требования к производельности - время отклика, загрузку, чувствительность к загрузке, мощность, масштабируемость и т.д

Так уж случилось, что 90 % моих проектов отличаются нехилыми для аксапты объемами данных - были базы от сотни гигабайт уже при запуске. Пришлось выработать для себя принцип тестировать все сразу на предполагаемом среднем рабочем объеме данных умноженным на 6, при этом время выполнения операции должно быть в 6 раз меньше максимально допустимого.
Возможно слишком жестко и жестоко в первую очередь по отношению к себе, но позволяет не сильно гадать на кофейной гуще. На самом деле я никогда не могу знать куда дальше пойдет то или иное решение, и какой сумасшедший сейлз продаст что нито, разработанное для системы с 50 пользователями клиенту, который загонит туда 500 человек. Да все не предусмотришь, но по крайней мере у меня в 90% случаев останется запас. И самое интересное, когда привыкаешь так делать, то дополнительные затраты оказываются просто "на уровне шума"

[Wamr]

Zitat:

Сравните производительность запроса с 2000 ранжами по ИЛИ с join-ом

Сравнил, хотя и так было ясно.. а теперь сравните на 100-200 записей и сравните накладные расходы на заполнение, очистку и периодическую чистку от мусора "временной" таблицы.
Нет универсального решения на все случаи жизни. Всегда надо анализировать текущие объемы и будущие объемы обрабатываемой информации.

[db]

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

[ALES]

Zitat:

Изначально опубликовано db
Пошел дальше отдыхать

Ох уж эти гении....

[Maxim Gorbunov]

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

[kashperuk]

Да я, собственно, и не думал.
Идея хорошая, работает отлично, и, кстати сказать, довольно быстро - заполняю табличку через RecordInsertList.
Поэтому, спасибо еще раз.

[IKA]

db:
(Старая тема, но все еще , думаю, актуальная.)

Zitat:

Для того, чтобы убирать мусор, который может появится в случае аварийного падения аксапты между набиванием RecId в таблицу и последующим стриранием этих данных, при старте сессии чистим эту таблицу, используя в качестве критерия только SessionId. Такую чистку следует вызвывать из application.startupPost()

Мне кажется, в качестве Cookie удобно использовать LoginDateTime, а потом очищать таблицу по SessionId and LoginDateTime, как сделано в BatchRun классе, метод serverCleanUpDeadTasks:

  update_recordset batch setting RetryCount = batch.RetryCount + 1, Status = BatchStatus::Ready
    where batch.Status == BatchStatus::Executing
        && batch.RetryCount < batch.RetriesOnFailure
    notexists join clientSessions
        where batch.SessionIdx == clientSessions.SessionId
            && batch.SessionLoginDateTime == clientSessions.LoginDateTime
            && clientSessions.Status != 0;

очищать в самом классе при повторном вызове, а не application.startupPost().