Решил написать сегодня о том, как увеличить скорость работы приложения QlikView и уменьшить объем занимаемой им оперативной памяти при помощи оптимизации индексов ключевых полей.
Когда актуальна оптимизация индексов ключевых полей:
Исходное ключевое поле, которое требуется оптимизировать, может быть как простым, например датой, числом или простым текстовым полем, так и составным (комплексным). Такое поле может включать комбинацию из нескольких первичных ключевых полей справочников, а также дат, между которыми вставлен символ-разделитель (например «|»).
Составные ключевые поля обычно имеют большой размер и занимают много места в оперативной памяти, поэтому я рекомендую выполнять оптимизацию ключевых полей, в первую очередь, в тех таблицах модели, в которых используются комплексные ключевые поля. Такую оптимизацию лучше всего выполнять на последнем этапе формирования модели данных, когда в модель уже загружены все таблицы фактов и справочники.
Суть оптимизации:
При оптимизации ключевых полей на основе исходного значения ключевого поля генерируется новое значение ключевого поля, представляющее из себя целое число. После генерирования в таблицах модели данных новых целочисленных ключевых полей старые ключевые поля из таблиц удаляются.
НА ЗАМЕТКУ!
- Оптимизируемое поле не должно иметь «пробелов».
- Значения поля не должны быть отрицательными.
Пример оптимизации ключевого поля MAINKEY:
Ниже привожу пример скрипта, в котором выполняется оптимизация индекса ключевого поля в таблице фактов и связанной с ней таблицей линков.
Замена в связанных по ключевому полю таблицах старого неоптимального ключевого поля на оптимальное целочисленное ключевое поле выполняется в 3 шага:
- Для примера возьмем модель данных, в которой таблицы [Товарные Проводки] и [StarLinkTable] связаны по текстовому комплексному ключевому полю [MAINKEY]. Используя функцию FieldValue() создаем временную таблицу, содержащую старое ключевое поле таблицы, которое требуется заменить, и новое целочисленное ключевое поле, сгенерированное с помощью функции autonumber():
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
TRACE ОПТИМИЗАЦИЯ; tmpData: LOAD [MAINKEY], Autonumber([MAINKEY],'MAINKEY') as [%MainKey]; LOAD FieldValue('MAINKEY',IterNo()) as [MAINKEY] AUTOGENERATE(1) WHILE NOT IsNull(FieldValue('MAINKEY',IterNo())); |
В примере:
[MAINKEY] – старое ключевое поле, для которого выполняется оптимизация,
[%MainKey] – новое целочисленное ключевое поле.
- Используя Left Join новое ключевое поле [%MainKey] из временной таблицы tmpData добавляется в каждую из таблиц, содержащих старое ключевое поле [MAINKEY]:
|
1 2 3 4 5 6 7 8 9 |
LEFT JOIN ([Товарные Проводки]) LOAD [MAINKEY], [%MainKey] RESIDENT tmpData; |
— пример скрипта для добавления нового ключевого поля [%MainKey] в таблицу [Товарные Проводки]
|
1 2 3 4 5 6 7 8 9 |
LEFT JOIN ([StarLinkTable]) LOAD [MAINKEY], [%MainKey] RESIDENT tmpData; |
— пример скрипта для добавления нового ключевого поля [%MainKey] в таблицу [StarLinkTable]
- После добавления нового ключевого поля [%MainKey] в связанные по старому полю таблицы временная таблица tmpData и старое ключевое поле удаляются [MAINKEY], а новое поле переименовывается в старое:
|
1 2 3 4 5 |
DROP TABLE tmpData; DROP FIELD [MAINKEY]; RENAME FIELD [%MainKey] to [MAINKEY]; |
Описанные выше три шага необходимо выполнить для всех ключевых полей, для которых выполняется оптимизация. При этом, таблицы в модели данных должны быть связаны между собой.
Наибольший эффект от оптимизации ключевых полей достигается в случаях, когда оптимизация выполняется на больших таблицах фактов, содержащих комплексные ключевые поля.
Василий Петренко
B3
Максим
Stepan
Павел Карасев
А как Вы измеряли скорость работы приложения при оптимизированных и не оптимизированных ключах? Безусловно хранить интэджеры в памяти менее затратно, но действительно ли уменьшается процессорное время обработки при их использовании? На практике я не замечал существенной разницы в быстродействии (17-18млн записей в факт таблице) хотя и всегда “шлифую” ключи по своей любви к правильному и логичному)
В примере используется дополнительная табличка со старыми и оптимизированными ключами. А почему Вы не делаете сразу autonumber() нужного поля в факт/дименшн/линк табличках? Ведь принцип работы функции именно такой-создание временной таблички с уникальными значениями полей и их сопоставлени к интеджерам и замена старых ключей на новые?
Изменение скорости работы приложения до оптимизации и после оптимизации становится заметным при формировании отчетов по таблицам, содержащим большее количество строк (50 миллионов записей и выше). Также скорость работы приложения зависит от отношения объема занимаемой приложением оперативной памяти к общему объему доступной оперативной памяти. Чем это отношение меньше, тем быстрее будет работать приложение. Поскольку после оптимизации объем занимаемой приложением оперативной памяти уменьшается, то общая скорость работы приложения увеличивается.
В данном примере оптимизация выделена в скрипте загрузки как отдельная задача и выполняется в примере последней, когда все таблицы в модель данных уже загружены.
В отдельную задачу оптимизация была выделена для того, чтобы более конкретно показать, в чем состоит суть оптимизации.
Конечно, при желании можно генерировать ключ integer непосредственно в момент загрузки каждой оптимизируемой таблицы, в этом случае выделять оптимизацию как отдельную задачу в процессе загрузки данных в модель нет необходимости.
Метод понятен, но непонятно почему нельзя просто хэш от строки посчитать? На больших объемах скрипт быстрее отработает, нежели чем использование autogenerate(1). autogenerate(1) утилизирует только одно ядро из всех доступных, а расчет хэша от строки – все доступные ядра…
Для формирования числовых ключей вместо текстовых составных ключей можно использовать следующие функции: autonumber(), autonumberhash128() и autonumberha sh256().
В процессе выполнения тестовой загрузки конкретного объема данных можно установить, какая из этих 3-х функций работает быстрее и именно ее использовать в рабочем скрипте загрузки
для формирования ключей.