Test du stockage en colonnes

Comme la plupart d'entre vous le savent probablement déjà, depuis environ la fin de l'année 2022, InterSystems IRIS a inclus la fonctionnalité de stockage en colonnes dans sa base de données. Dans l'article d'aujourd'hui, nous allons la mettre à l'épreuve en la comparant au stockage en lignes habituel.

Stockage en colonnes

Quelle est la principale caractéristique de ce type de stockage ? Eh bien, si l'on consulte la documentation officielle nous verrons ce fantastique tableau qui explique les principales caractéristiques des deux types de stockage (par lignes ou par colonnes) :

(ndt : je n'ai pas pu trduire le tableau ci-dessous car c'est une image)

Comme vous pouvez le constater, le stockage en colonnes est conçu principalement pour les tâches analytiques dans lesquelles des requêtes sont lancées sur des champs spécifiques de notre table, tandis que le stockage en lignes est plus optimal lorsqu'un grand nombre d'opérations d'insertion, de mise à jour et de suppression sont nécessaires, ainsi que pour l'obtention d'enregistrements complets.

Si vous continuez à lire la documentation, vous verrez à quel point il est simple de configurer notre table pour pouvoir utiliser le stockage en colonnes :

CREATE TABLE table (column type, column2 type2, column3 type3) WITH STORAGETYPE = COLUMNAR
SQL
SQL

En utilisant cette commande, nous définissons toutes les colonnes de notre tableau avec un stockage en colonnes, mais nous pouvons opter pour un modèle mixte dans lequel notre tableau dispose d'un stockage en lignes mais où certaines colonnes utilisent un stockage en colonnes.

Ce scénario mixte peut être intéressant dans les cas où les opérations d'agrégation telles que les sommes, les moyennes, etc. sont courantes. Dans ce cas, nous pourrions définir la colonne qui utilisera ce stockage :

CREATE TABLE table (column type, column2 type2, column3 type3 WITH STORAGETYPE = COLUMNAR)
SQL
SQL

Dans l'exemple précédent, nous avons défini une table avec un stockage en ligne et une colonne (column3) avec un stockage en colonne.

Comparatif

Pour comparer le temps passé par le stockage en colonnes et le stockage en lignes dans différentes requêtes, nous avons créé un petit exercice en utilisant Jupyter Notebook qui insérera une série d'enregistrements que nous générerons dans deux tables, la première avec un stockage en lignes ( Test.PurchaseOrderRow) et la seconde avec un stockage en colonnes dans deux de ses colonnes (Test.PurchaseOrderColumnar).

Test.PurchaseOrderRow

CREATE TABLE Test.PurchaseOrderRow (
    Reference INTEGER,
    Customer VARCHAR(225),
    PaymentDate DATE,
    Vat NUMERIC(10,2),
    Amount NUMERIC(10,2),
    Status VARCHAR(10))
SQL
SQL

Test.PurchaseOrderColumnar

CREATE TABLE Test.PurchaseOrderColumnar (
    Reference INTEGER,
    Customer VARCHAR(225),
    PaymentDate DATE,
    Vat NUMERIC(10,2),
    Amount NUMERIC(10,2) WITH STORAGETYPE = COLUMNAR,
    Status VARCHAR(10) WITH STORAGETYPE = COLUMNAR)
SQL
SQL

Si vous téléchargez le projet Open Exchange et le déployez dans votre Docker local, vous pouvez accéder à l'instance de Jupyter Notebook et examiner le fichier PerformanceTests.ipynb, qui sera responsable de la génération des données aléatoires que nous allons stocker en différentes phases dans nos tables et enfin il nous montrera un graphique avec la performance des opérations de requête.

Jetons un coup d'œil rapide à la configuration de notre projet :

docker-compose.yml

version: '3.7'
services:
  # iris
  iris:
    init: true
    container_name: iris
    build:
      context: .
      dockerfile: iris/Dockerfile
    ports:
      - 52774:52773
      - 51774:1972
    volumes:
    - ./shared:/shared
    environment:
    - ISC_DATA_DIRECTORY=/shared/durable
    command: --check-caps false --ISCAgent false
  # jupyter notebook
  jupyter:
    build:
      context: .
      dockerfile: jupyter/Dockerfile
    container_name: jupyter
    ports:
      - "8888:8888"
    environment:
      - JUPYTER_ENABLE_LAB=yes
      - JUPYTER_ALLOW_INSECURE_WRITES=true
    volumes:
      - ./jupyter:/home/jovyan
      - ./data:/app/data
    command: "start-notebook.sh --NotebookApp.token='' --NotebookApp.password=''" 
YAML
YAML

Nous déployons les conteneurs IRIS et Jupyter dans notre docker, en configurant initialement IRIS avec l'espace de noms "TEST" et les deux tables nécessaires au test.

Pour éviter de vous ennuyer avec du code, vous pouvez consulter le document PerformanceTests.ipynb à partir duquel nous nous connecterons à IRIS, générerons les enregistrements à insérer et les stockerons dans IRIS.

Exécution des tests

Les résultats ont été les suivants (en secondes) :

Inserts:

Les insertions effectuées sont de type "bulk" :

INSERT INTO Test.PurchaseOrderColumnar (Reference, Customer, PaymentDate, Vat, Amount, Status) VALUES (?, ?, ?, ?, ?, ?)
SQL
SQL

Le temps nécessaire pour chaque lot d'insertions est le suivant :

Selects:

La fonction Select lancée comprend une fonction d'agrégation et une condition, toutes deux sur des colonnes avec stockage en colonnes :

SELECT AVG(Amount) FROM Test.PurchaseOrderColumnar WHERE Status = 'SENT'
SQL
SQL

Conclusions

Comme vous pouvez le voir dans les résultats obtenus, l'opération est exactement ce qui est indiqué dans la documentation. L'inclusion de colonnes avec stockage en colonnes a légèrement pénalisé les performances lors de l'insertion (environ 18% plus lent pour notre exemple) alors que les requêtes sur ces mêmes colonnes ont considérablement amélioré le temps de réponse (258 fois plus rapide).

Il s'agit sans aucun doute d'un élément à prendre en compte lors de la planification du développement d'une application.