Временные таблицы и скорость работы

[glibs]

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

Описанная ниже информация непосредственно касается версии 5.0. Не проверял но почти уверен что и всех более ранних версий. Насчет более поздних — не знаю и прмолчу.

Я нередко слышал разговоры про падение производительности при работе с временными таблицами. Но не припомню конкретных конструктивных рекомендаций. И поиском с наскоку не нашел.

Возможно об этом уже писали. Но на всякий случай.

Недавно столкнулся с невысокой производительностью стандартной функциональности. Долго работал джоин двух временных таблиц. Поскольку знаний сильно не хватало а прочитат ьпро такое негде как обычно — пришлось провести следственный эксперимент. IMHO результаты заслуживают всеобщего внимания.

Следственный эксперимент производился с помощью следующего джоба.

[glibs]

static void glibs(Args _args)
{
    TmpAccountSum       tmpAccountSum1,
                        tmpAccountSum2;
    Counter             i;
    FreeText            text;
    Counter             startTime;

    #Define.cycles(1000)
    ;

    startTime = WinAPI::getTickCount();
    for (i = 0; i <= #cycles; i++)
    {
        tmpAccountSum1.clear();
        tmpAccountSum2.clear();
        text = int2str(i);
        tmpAccountSum1.AccountNum = strrep("0", 20 - strlen(text)) + text;
        tmpAccountSum2.AccountNum = strrep("0", 20 - strlen(text)) + text;
        tmpAccountSum1.Balance01 = 999999 * 888888;
        tmpAccountSum2.Balance01 = 999999 * 888888;
        tmpAccountSum1.Balance02 = 999999 * 888888;
        tmpAccountSum2.Balance02 = 999999 * 888888;
        tmpAccountSum1.Balance03 = 999999 * 888888;
        tmpAccountSum2.Balance03 = 999999 * 888888;
        tmpAccountSum1.Balance04 = 999999 * 888888;
        tmpAccountSum2.Balance04 = 999999 * 888888;
        tmpAccountSum1.Balance05 = 999999 * 888888;
        tmpAccountSum2.Balance05 = 999999 * 888888;
        tmpAccountSum1.Balance06 = 999999 * 888888;
        tmpAccountSum2.Balance06 = 999999 * 888888;
        tmpAccountSum1.Balance07 = 999999 * 888888;
        tmpAccountSum2.Balance07 = 999999 * 888888;
        tmpAccountSum1.Balance10 = 999999 * 888888;
        tmpAccountSum2.Balance10 = 999999 * 888888;
        tmpAccountSum1.TaxCode = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum2.TaxCode = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum1.CurrencyCode = "XXX";
        tmpAccountSum2.CurrencyCode = "XXX";
        tmpAccountSum1.Posting = 1;
        tmpAccountSum2.Posting = 1;
        tmpAccountSum1.Dimension[1] = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum2.Dimension[1] = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum1.Dimension[2] = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum2.Dimension[2] = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum1.Dimension[3] = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum2.Dimension[3] = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum1.OperationsTax = 1;
        tmpAccountSum2.OperationsTax = 1;
        tmpAccountSum1.TransDate = today();
        tmpAccountSum2.TransDate = today();
        tmpAccountSum1.Txt = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" +
                             "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" +
                             "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" +
                             "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum2.Txt = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" +
                             "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" +
                             "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" +
                             "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum1.SortDate = today();
        tmpAccountSum2.SortDate = today();
        tmpAccountSum1.Balance01Cur = 999999 * 888888;
        tmpAccountSum2.Balance01Cur = 999999 * 888888;
        tmpAccountSum1.Qty01 = 999999 * 888888;
        tmpAccountSum2.Qty01 = 999999 * 888888;
        tmpAccountSum1.Qty02 = 999999 * 888888;
        tmpAccountSum2.Qty02 = 999999 * 888888;
        tmpAccountSum1.Qty03 = 999999 * 888888;
        tmpAccountSum2.Qty03 = 999999 * 888888;
        tmpAccountSum1.Voucher = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum2.Voucher = "XXXXXXXXXXXXXXXXXXXX";
        tmpAccountSum1.insert();
        tmpAccountSum2.insert();
    }
    info (strfmt("%1", (WinAPI::getTickCount() - startTime) / 1000));

    startTime = WinAPI::getTickCount();
    i = 0;
    while select tmpAccountSum1
    join tmpAccountSum2
        where tmpAccountSum1.AccountNum == tmpAccountSum2.AccountNum
    {
        text = tmpAccountSum1.Voucher + tmpAccountSum2.Voucher;
        i++;
    }
    info (strfmt("%1", i));
    info (strfmt("%1", (WinAPI::getTickCount() - startTime) / 1000));
    
    startTime = WinAPI::getTickCount();
    i = 0;
    while select tmpAccountSum1
    {
        while select tmpAccountSum2
            where tmpAccountSum2.AccountNum == tmpAccountSum1.AccountNum
        {
            text = tmpAccountSum1.Voucher + tmpAccountSum2.Voucher;
            i++;
        }
    }
    info (strfmt("%1", i));
    info (strfmt("%1", (WinAPI::getTickCount() - startTime) / 1000));

}

[glibs]

Для тех кто не понимает по Х++совски.

Эксперимент проводится на таблице TmpAccountSum, которую можно найти в АОТ в стандартной поставке. Это временная таблица. В ней есть несколько десятков полей различного вида (точнее, зазличных типов данных). Подробнее вы можете познакомиться с ней открыв АОТ.

В начале джоба написан код, который создает два экземпляра этой таблицы и заполняет их откровенным хламом (левыми данными).

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

И только одно поле я заполнил немного интеллектуальнее. TmpAccountSum.AccountNum. Оно содержит порядковый номер записи переведенный в целочисленное строковое значение и добивается до максимальной длинны поля лидирующими нулями.

Такм образом TmpAccountSum.AccountNum содержит некий уникальный искусственно созданный ключ (на уровне структуры данных TmpAccountSum.AccountNum уникальным не является).

В результате TmpAccountSum.AccountNum заполняется примерно так:
00000000000000000000
00000000000000000001
00000000000000000002
00000000000000000003
и т.д.

Количество создаваемых строк задается в макросе #Define.cycles(1000) в конце списка определения переменных в джобе.

Суть эксперимента

В джобе написаны два блока кода. Они абсолютно равнозначны.

В первом блоке оба экземпляра временных таблиц джоинятся друг к другу по полю AccountNum.

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

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

То есть в результате выборки данные не мультиплицируются. Количество строк в результате выборки будет равно количеству записей в каждой таблице.

Сделано это специально, чтобы размер выборки был небольшим.

Итак...

[sukhanchik]

Все конечно замечательно - но напрягает прогонять джоб у себя на АХ (которая не всегда под рукой).
Но я прогнал

Видно, что джойн работает медленно (3-я цифра против 1-й и 5-й, а 2-я и 4-я - это кол-во записей в джойне).

[glibs]

На 1000 записей в каждом экземпляре временной раблицы
=============================================

0.28 - Времы вставки по 1000 записей в каждый из экземпляров временных таблиц
1001 - Количество выбранных записей в первой выборке
8.53 - Время выборки с джоином временных таблиц
1001 - Количество выбранных записей во второй выборке
0.05 - Время выборки с вложенным циклом вместо равнозначного первой выборке джоина временных таблиц


На 2000 записей в каждом экземпляре временной раблицы (увеличиваем количество строк временной таблицы вдвое)
==============================================================
0.53 - время вставки возросло почти вдвое
2001 - записей стало пракически вдвое больше
33.11 - времы выборки с джоином увеличилось вчетверо
2001 - нечего комментировать
0.08 - время выборки с вложенным циклом увеличилось меньше чем вдвое


Попробуем 4000 записей в каждом экземпляре временной раблицы (увеличиваем количество строк временной таблицы вдвое)
==============================================================
1.09 - опять чуть меньше чем вдвое
4001 - очень ожидаемо
136.34 - снова чуть больше чем вчетверо
4001 - нечего комментировать
0.17 - тоже чуть больше чем вдвое


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

Складывается впетатление, что джоин тупо перебирает все записи первой таблицы и для каждой аписи перебирает все записи второй таблицы. А потом уже смотрит на условия джоина.

Но это еще не все...

[glibs]

Про то что выборка с вложенным циклом работает радикально быстрее чем джоин я даже написать забыл в начале выводов.

И что интересно — с ростом количества записей во временной таблице ситуация усугубляется драматически.

[Logger]

Спасибо.
Очень ценная инфа.

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

1. Попробовал закомментировать код который забивает абракадаброй строки времянки (кроме ключевого поля). Для вложеных циклов время не изменилось. Для джоина - улучшилось с 7,59 до 4,55 секунд. Т.е. похоже джоин идет по схеме nested loop, а подзапрос в нестед лупе делает сканирование приждойненной таблички без использования индекса. Отсюда же понятно почему квадратичная зависимость. Если бы джоинили 3 таблички то была бы кубическая зависимость времени. Прикольно

2. Попробовал добавить индекс только по полю AccountNum. Потом отдельно по паре полей AccountNum, RecID - никакого положительного эффекта, только время заполнения таблицы увеличилось.

[Logger]

Кстати, а в каком месте вылезли тормоза ?
Надо вообще в BestPractice добавить проверку на запрет джоина времянок, чтобы компилер сразу посылал.

[Logger]

Цитата:

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

наверно имелся в виду джоин ? Опечатка ?

[Logger]

Ха, а каким чудом sukhanchik дважды подряд может одобрить одного участника ?
GODMODE ON для модератора ?

Я тоже так хочу

[glibs]

Итак...

Чтобы сберечь нервы я комментирую неэффективный код с джоином.

Отталкиваюсь от 4,000 записей
=======================
1.08 - время вставки
4001 - результат выборки
0.17 - время выборки вложенным циклом


Ну ка 40,000 записей
=======================
11.77 - время вставки
40001 - результат выборки
4.58 - время выборки вложенным циклом

Время выборки начало расти быстрее. Время выборки выросло в 25 раз против 10 кратного роста количества записей в таблицах.

Вероятно все-таки на больших объемах производительност ьвременных раблиц еще снижается за счет чего-то.

Если у кого-то хватит нервов — попробуйте посмотреть что на 40,000 выдаст джоин .

[glibs]

Мне это напоминает анекдот, когда у армянского радио спрашивают можно ли посреди центральной площади города изнасиловать женщину...

[Logger]

Отрубил вообще все индексы и получил такие времена :

Цитата:

Prepare data took 0,20 seconds
Join 1001 loops
Join 7,33 seconds
Sub while select 1001 loops
Sub while select 7,23 seconds

join времянок просто игнорит индексы на табличках.

[glibs]

Я уже много лет все думал на что же влияют индексы во временных таблицах.

Используются ли они для поиска данных?
Или только для контроля уникальности ключа?
Или вообще не используются?

У таблицы TmpAccountSum в стандартной поставке есть один индекс. Первым полем там идет AccountNum, а дальше еще несколько полей, но это не важно. Делаю ему Enanbled = "No" (т.е. отключаю).

На 40,000 записей
==============
Не дождался. Спать хочу.
Время вставки было 9.03


Вернусь на 4,000 записей
====================
Было:
1.08 - время вставки
4001 - результат выборки
0.17 - время выборки вложенным циклом

Стало:

0.86 - Вставка убыстрилась. То, что наличие индекса на таблице увеличивает время вставки, характерно для реляционных и индексно-последовательных баз данных. Временные таблицы тут не выделяются из общей тенденции.
4001 - Дождался
129.63 - Что-то мне это напоминает... кажется на результат джоина похоже .

В общем, индексы на временной таблице:
а) не фикция
б) ускоряют доступ к данным как и в клссических СУБД
в) почему-то не используются при джоинах

[glibs]

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

Временные таблицы индексировать нужно. Но как и в случае с обычными таблицами делать это нужно грамотно и со знанием дела.

Спасибо тем кто дождался конца моего спича. Теперь буду рад вашим комментариям. Особенноесли вы тоже нароете что-то полезное.

[Logger]

Вам спасибо.

Вот тут
Оптимизация класса Tax
проверял как влияет вынесение хранения времянок с диска в память, пришел к таким же выводам относительно индексов. Поиск по индексированным полям сильно не менялся с переносов времянок в память (т.е. он и так был быстр), а по неиндексированным - значительно ускорился (значит было неэффективное сканирование). В общем, индексы на времянках очень полезная вещь.

И, кстати, когда я добавлял дополнительные индексы (вдруг окажутся эффективнее при джоине), то время вставки увеличивалось процентов на 30-50 с каждым новым индексом. В общем, все как и в случае постоянных табличек.

[glibs]

Цитата:

Ха, а каким чудом sukhanchik дважды подряд может одобрить одного участника ?
GODMODE ON для модератора ?

Я тоже так хочу

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

Вы уверены что хотите взять на себя ответственность?

[Logger]

Кто говорил про ответственность ?
Я всего лишь хотел иметь возможность давать одобрение участнику более одного раза в течение 3 дней.

[glibs]

Вернул индекс. Запустил на 400,000. Аксапта загрызла диск. Браузер подвисает сильно.

Прав был Wamr, когда советовал на больших объемах временные таблицы не использовать...

Оооооо! Вот и результат приготовился (400,000 vs 40,000).
=============================
204.05 vs 11.77 = 17.34
400001 vs 40001 = 10
461.88 vs 4.58 = 100

Уже как при джоине чисто квадратично получилось. Предчувствую что дальше будет еще хуже.

[glibs]

А я говорю про ОТВЕТСВЕННОСТЬ за свои действия.

И чего вы так переживаете по поводу этиц цифр на форуме... они того не стоят.