Ускоряем работу QlikView: Оптимизация индексов ключевых полей

Решил написать сегодня о том, как увеличить скорость работы приложения QlikView и уменьшить объем занимаемой им оперативной памяти при помощи оптимизации индексов ключевых полей.

Когда актуальна оптимизация индексов ключевых полей:

Исходное ключевое поле, которое требуется оптимизировать, может быть как простым, например датой, числом или простым текстовым полем, так и составным (комплексным). Такое поле может включать комбинацию из нескольких первичных ключевых полей справочников, а также дат, между которыми вставлен символ-разделитель (например «|»).

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

Суть оптимизации:

При оптимизации ключевых полей на основе исходного значения ключевого поля генерируется новое значение ключевого поля, представляющее из себя целое число. После генерирования в таблицах модели данных новых целочисленных ключевых полей  старые ключевые поля из таблиц удаляются.

НА ЗАМЕТКУ!

  • Оптимизируемое поле не должно иметь «пробелов».
  • Значения поля не должны быть отрицательными.

Пример оптимизации ключевого поля MAINKEY:

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

Замена в связанных по ключевому полю таблицах старого неоптимального ключевого поля на оптимальное целочисленное ключевое поле выполняется в 3 шага:

  1. Для примера возьмем модель данных, в которой таблицы [Товарные Проводки] и [StarLinkTable] связаны по текстовому комплексному ключевому полю [MAINKEY]. Используя функцию FieldValue() создаем временную таблицу, содержащую старое ключевое поле таблицы, которое требуется заменить, и новое целочисленное ключевое поле, сгенерированное с помощью функции autonumber():

В примере:

[MAINKEY] – старое ключевое поле, для которого выполняется оптимизация,

[%MainKey] – новое целочисленное ключевое поле.

  1. Используя Left Join новое ключевое поле [%MainKey] из временной таблицы tmpData добавляется в каждую из таблиц, содержащих старое ключевое поле [MAINKEY]: 

— пример скрипта для добавления нового ключевого поля [%MainKey] в таблицу  [Товарные Проводки]

— пример скрипта для добавления нового ключевого поля [%MainKey] в таблицу  [StarLinkTable]

  1. После добавления нового ключевого поля [%MainKey] в связанные по старому полю таблицы временная таблица tmpData и старое ключевое поле удаляются [MAINKEY], а новое поле переименовывается в старое:

Источник данных QlikView

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

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

Комментарии

  • Июль 9, 2015 at 10:45
    Permalink

    А как Вы измеряли скорость работы приложения при оптимизированных и не оптимизированных ключах? Безусловно хранить интэджеры в памяти менее затратно, но действительно ли уменьшается процессорное время обработки при их использовании? На практике я не замечал существенной разницы в быстродействии (17-18млн записей в факт таблице) хотя и всегда “шлифую” ключи по своей любви к правильному и логичному)
    В примере используется дополнительная табличка со старыми и оптимизированными ключами. А почему Вы не делаете сразу autonumber() нужного поля в факт/дименшн/линк табличках? Ведь принцип работы функции именно такой-создание временной таблички с уникальными значениями полей и их сопоставлени к интеджерам и замена старых ключей на новые?

    Ответить
    • Июль 9, 2015 at 10:48
      Permalink

      Изменение скорости работы приложения до оптимизации и после оптимизации становится заметным при формировании отчетов по таблицам, содержащим большее количество строк (50 миллионов записей и выше). Также скорость работы приложения зависит от отношения объема занимаемой приложением оперативной памяти к общему объему доступной оперативной памяти. Чем это отношение меньше, тем быстрее будет работать приложение. Поскольку после оптимизации объем занимаемой приложением оперативной памяти уменьшается, то общая скорость работы приложения увеличивается.
      В данном примере оптимизация выделена в скрипте загрузки как отдельная задача и выполняется в примере последней, когда все таблицы в модель данных уже загружены.
      В отдельную задачу оптимизация была выделена для того, чтобы более конкретно показать, в чем состоит суть оптимизации.
      Конечно, при желании можно генерировать ключ integer непосредственно в момент загрузки каждой оптимизируемой таблицы, в этом случае выделять оптимизацию как отдельную задачу в процессе загрузки данных в модель нет необходимости.

      Ответить
      • Июль 9, 2015 at 10:51
        Permalink

        Метод понятен, но непонятно почему нельзя просто хэш от строки посчитать? На больших объемах скрипт быстрее отработает, нежели чем использование autogenerate(1). autogenerate(1) утилизирует только одно ядро из всех доступных, а расчет хэша от строки – все доступные ядра…

        Ответить
        • Июль 9, 2015 at 10:55
          Permalink

          Для формирования числовых ключей вместо текстовых составных ключей можно использовать следующие функции: autonumber(), autonumberhash128() и autonumberha sh256().
          В процессе выполнения тестовой загрузки конкретного объема данных можно установить, какая из этих 3-х функций работает быстрее и именно ее использовать в рабочем скрипте загрузки
          для формирования ключей.

          Ответить

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Подпишись на Data-Daily!

Введите email и будьте в курсе!

Подпишись!