Comment réduire le nombre d'appels API / lignes mises à jour dans une instruction UPDATE ?

Invantive SQL facilite la mise à jour massive et la synchronisation des données en gardant la technique sous le capot. Avec l’instruction update et/ou l’instruction synchronize, vous pouvez facilement mettre à jour en masse des données dans des applications cloud afin que tous vos processus métier utilisent les mêmes données.

Certaines applications cloud limitent, pour des raisons techniques, de juste usage et/ou commerciales, le nombre d’appels API. En cas de dépassement, un message d’erreur est souvent renvoyé, tel que « Too many requests » ou un code de statut HTTP 429.

Le terme courant pour désigner une telle limitation des appels API est un « rate limit » (limite de débit). Parfois, plusieurs limites de débit s’appliquent simultanément, par exemple par jour, par mois, par minute, par API, par opération API (lecture/écriture) ou par résultat API : avec ou sans message d’erreur.

Il est souvent possible d’acheter des appels API supplémentaires afin d’augmenter la limite de débit, soit avec paiement anticipé, soit avec facturation ultérieure. Sur certaines plateformes, l’augmentation de la limite de débit des appels API vers des seuils plus élevés ou supplémentaires est relativement coûteuse, avec des pics pouvant atteindre plusieurs dizaines d’euros par appel complexe, tandis que sur d’autres, c’est extrêmement bon marché.

Il est judicieux d’utiliser l’espace disponible dans les limites de débit en termes d’appels API aux moments appropriés lorsque les quantités sont limitées par unité de temps, comme le mois ou le jour. Les modifications de faible priorité doivent être effectuées en fin de période afin de tirer le meilleur rendement possible de votre investissement dans la plateforme cloud.

Ne faites varier le nombre d’appels API dans le temps qu’après avoir d’abord limité le nombre d’appels API ; vous trouverez des conseils fonctionnels à ce sujet sur Hoe plan ik mijn Exact Online-omgeving op het beschikbare aantal API-calls? et des conseils techniques, par exemple, sur Snel en met minder API-calls Exact Online uitlezen met de incrementele "sync API"-tabellen.

Invantive SQL propose des fonctionnalités permettant d’utiliser aussi efficacement que possible l’espace disponible dans les limites de débit en termes d’appels API. Sur la plupart des plateformes, l’espace disponible est visible dans les colonnes de la vue du dictionnaire de données SystemPartitions@DataDictionary. On y trouve des colonnes pour les limites de débit par jour, par heure et par minute, ainsi que le nombre de requêtes effectuées au cours du jour, de l’heure et de la minute en cours.

Pour l’instruction synchronize, vous pouvez indiquer le nombre maximal de modifications à effectuer. Cela est également possible avec les instructions SQL traditionnelles update, delete et insert.

Le nombre maximal de modifications pour update, delete, insert peut être défini via limit NOMBRE, tout comme limit limite le nombre de lignes issues d’une sélection via select.

Ceci est illustré à l’aide d’un cas pratique utilisant l’API Exact Online.

Cas pratique : modification des pièces jointes de factures

Dans le système comptable Exact Online, vous pouvez définir, pour chaque relation d’une administration, les formats dans lesquels les factures sont envoyées. Il peut s’agir d’une combinaison de formats, combinés en un nombre via le « bitwise AND ». La valeur « 1 » signifie par exemple uniquement les factures de vente au format PDF, la valeur « 2 » signifie le format UBL et la valeur « 3 » signifie à la fois le format PDF et UBL.

Dans une base de données Exact Online comportant des administrations possédant chacune 50 000 relations, le format des pièces jointes de factures doit être converti, pour toutes les administrations, de PDF plus UBL vers PDF uniquement. Cependant, le nombre d’appels API par jour sur Exact Online ne représente qu’une fraction des 50 000 appels API nécessaires, et cette conversion a une priorité inférieure aux traitements quotidiens.

C’est pourquoi les modifications sont appliquées dans Exact Online en fin de journée, afin de consommer avec ces requêtes supplémentaires le budget d’appels API éventuellement restant.

Cette modification a la priorité la plus élevée pour les clients de vente ; les autres relations n’ont pas encore de statut client.

Dans la tâche, le nombre de modifications sur Exact Online peut être limité à 10 via la mise à jour suivante :

use SELECTIE_ADMINISTRATIES@eol

update exactonlinerest..accounts@eol
set    InvoiceAttachmentType = 1
where  InvoiceAttachmentType = 3
and    status = 'C'
limit  100

Notez que cette opération n’utilise pas la table Exact Online AccountsIncremental ; celle-ci est en lecture seule et nécessiterait une instruction synchronize plus complexe, alors que la clause where peut être transmise directement à Exact Online grâce au filtrage côté serveur d’Invantive et au filtre d’Exact Online, de sorte qu’un seul appel API Exact Online est nécessaire pour récupérer les lignes à mettre à jour.

Cette instruction sera terminée - s’il reste des clients nécessitant une modification - après environ 3 secondes.

Un éventuel reliquat de budget d’appels API peut être utilisé pour définir également le type de pièces jointes de factures pour les non-clients :

use SELECTIE_ADMINISTRATIES@eol

update exactonlinerest..accounts@eol
set    InvoiceAttachmentType = 1
where  InvoiceAttachmentType = 3
limit  100

Raffinement : davantage d’administrations

La modification en masse peut facilement être étendue à plusieurs administrations. Indiquez d’abord la sélection d’administrations, par exemple pour toutes les administrations Exact Online :

use all@eol

update exactonlinerest..accounts@eol
set    InvoiceAttachmentType = 1
where  InvoiceAttachmentType = 3
and    status = 'C'
limit  1000

update exactonlinerest..accounts@eol
set    InvoiceAttachmentType = 1
where  InvoiceAttachmentType = 3
limit  1000

Étant donné que les limites d’API s’appliquent actuellement par administration sur Exact Online, il est possible de fixer la limite plus haut. Dans cet exemple, on part de 10 administrations, où les 1000 mises à jour seront réparties à peu près uniformément sur les 10 grâce au traitement parallèle, sauf si la quantité de mises à jour API nécessaires varie significativement d’une administration à l’autre.

Raffinement : uniquement l’espace d’appels API disponible dans le délai le plus court possible

Un raffinement plus avancé consiste à limiter le nombre d’appels API au nombre disponible pour une administration, tout en minimisant le temps d’exécution (sans parallélisation).

Le bloc Invantive PSQL suivant déclenche à chaque fois de courtes séries de mises à jour, dont la longueur est choisie de manière à atteindre le plus rapidement possible la limite par minute d’Exact Online de 60 appels API par minute :

declare
  l_did_work  boolean;
  l_dummy_cnt number;
  g_max_call_per_minute pls_integer := 50 /* Un peu moins que 60 pour pouvoir changer plus souvent. */;
begin
  use all@eol;
  --
  -- Veillez à ce que les chiffres concernant les appels API restants soient à jour
  -- en touchant brièvement toutes les administrations.
  --
  select /*+ http_disk_cache(false) http_memory_cache(false) */ count(*)
  into   l_dummy_cnt
  from   exactonlinerest..journals@eol
  ;
  l_did_work := true;
  while l_did_work
  loop
    dbms_output.put_line('Pendant une minute, essayer d\'atteindre les limites d\'appels API par minute pour chaque administration.');
    l_did_work := false;
    for r
    in 
    ( select 'update exactonlinerest..accounts@eol'
              || ' set InvoiceAttachmentType = 1'
              || ' where  InvoiceAttachmentType = 3'
              || ' limit  ' 
              || to_char
                 ( case
                   when DAILY_REMAINING > g_max_call_per_minute
                   then g_max_call_per_minute
                   else DAILY_REMAINING
                        --
                        -- Laisser 500 appels API Exact Online disponibles pour des 
                        -- priorités imprévues plus tard dans la journée.
                        --
                        - 500
                   end
                 )
              stmt
       ,      'Mettre à jour au maximum ' 
              || g_max_call_per_minute 
              || ' lignes dans l\'administration ' 
              || code
              || ' - ' 
              || name 
              txt
       from   SYSTEMPARTITIONS@DataDictionary
       where  IS_SELECTED = true
       and    PROVIDER_NAME = 'ExactOnlineAll'
       and    PROVIDER_DATA_CONTAINER_ALIAS = 'eol'
       --
       -- Il doit rester au moins 500 appels API
       -- aujourd'hui pour cette administration.
       --
       and    DAILY_REMAINING > 500
    )
    loop
      dbms_output.put_line(r.txt);
      execute immediate r.stmt;
      l_did_work := true;
    end loop;
  end loop;
end;