Optimiser l'écriture sur SQL Server

[psyrio]

Bonjour,

J'ai une problématique importante sur la lecture et l'écriture de données à partir d'un SQL Server (v2017).

Après avoir fait le tour du forum, j'ai mis en place plusieurs recommandations mais qui semble encore ne pas permettre d'atteindre un résultat suffisant.

En effet, j'ai une succession de workflow qui prend aujourd'hui au minimum 9 heures de chargement, avec une volumétrie qui augmente régulièrement et le temps de chargement qui lui suit une courbe presque exponentielle.

Mes workflows se lancent les uns après les autres afin de ne pas avoir des workflows trop complexes, j'utilise ALTERYX en version 2020.3 et le moteur AMP sur certains workflows complexes. J'ai 8 vCPU et 120 Go de RAM sur mon serveur hébergeant ALTERYX. J'ai autorisé 4 process pour le moteur AMP avec chacun 27 Go de RAM Max.

J'utilise en lecture le pilote OLDB car d'après plusieurs sources, il est plus efficace en lecture.

Pour l'écriture, j'utilise l'ODBC (en version 03.80) avec Bulk avec 10000 en taille de package.

La volumétrie dépend des workflows mais peut attendre plusieurs millions de lignes.

J'utilise déjà aussi dans mes workflows le tool "Auto Field" permettant d'avoir la meilleur taille et type pour mes colonnes.

J'utilise des tools Stop Until Done pour ne pas écrire en même temps deux tables dans le même workflow afin de ne pas saturer la base de données.

J'ai aussi changer le "recovery model" de ma base de données a "SIMPLE" et mis les options "AUTO_CRATE_STATISTICS" and "AUTO_UPDATE_STATISTICS" and "AUTOSHRINK" to "OFF".

J'avoue être à cours d'idée, c'est pourquoi je poste ce message afin de savoir si vous avez des conseils, bests practices qui pourraient m'aider.

Merci d'avance et bon dimanche.

Cordialement,

Psyrio

[Ladarthure]

Bonjour @psyrio,

beaucoup de questions peuvent se poser dans ce genre de situations.

Je commencerais par bien comprendre votre process, si je comprends bien, vous lisez depuis SQL serveur puis écrivez de nouveau sur SQL server? Si c'est le cas, avez-vous essayé d'utiliser le mode In-Database qui permet de décharger le traitement sur le serveur SQL?

D'autre part, avez-vous mesuré vos performances afin de savoir où cela prend du temps et qu'est ce qui cause ces 9 heures?

Parfois vous serez peut être bloqué et il n'y aura pas de moyen d'optimiser. 

De même si vous pouviez donner quelques informations sur le process derrière, cela peut aider!

Arthur

[psyrio]

Bonjour @Ladarthure ,

Je vous remercie pour cette réponse rapide.

En effet 90 % des workflows (j'en ai plus de 250) font de la lecture sur SQL Server et de l'écriture sur SQL Server. Le problème est que je fait des traitements entre cette lecture et écriture qui fait que je suis dans l'incapacité d'utiliser le InDataBase pour une majorité (pour ceux où c'est possible je l'ai déjà mis en place).

Aujourd'hui ce qui prend le plus de temps malheureusement c'est la lecture et l'écriture en base des tables les plus volumineuses. Je pensais qu'on pourrait trouver des optimisations dans les configurations d'ALTERYX, dans celui du Bulk SQL Server (notamment cette notion de transaction size que je comprend mal).

Ce qui est le plus embêtant est de ne pas pouvoir prédire l'évolution du temps de chargement des workflows par rapport à l'augmentation de la volumétrie et quels sont les leviers (augmentation RAM, CPU...) qui permettent d'absorber cette volumétrie.

Cordialement,

Psyrio

[Ladarthure]

Cela dépend de pas mal d'éléments et de droits que vous auriez ou non, mais par exemple une possibiltié qui fait gagner pas mal de temps serait d'utiliser du in-db pour avoir toutes vos données en in-db et de créer des tables temporaires pour faire les jointures. Est ce qu'il y a dans vos process des éléments que vous ne pouvez pas faire en in-db?

Dans le même ordre d'idée, on peut aussi envisager de faire un traitement par exemple que sur les données du dernier mois pour ne pas recharger les données qui sont déjà traitées, cela peut être source de gain de temps énorme.

[Ladarthure]

Une autre possibilité serait aussi d'optimiser les tailles de données en évitant par exemple les formats de base des champs texte de plus 1000 caractères, cela peut optimiser de manière forte vos temps de chargement!

[psyrio]

Merci pour ces retours.

Pour l'optimisation des temps j'ai déjà utilisé le Auto field tool qui permet justement d'éviter d'avoir des tailles de champs (notamment pour les champs textes) trop important.

Concernant le chargement en mode delta, ce n'est pas aujourd'hui possible partout. En effet, il y des données qui ont des effets sur des données antérieures et donc il faut reprendre les calculs sur l'intégralité des données pour prendre en compte ces évolutions.

Au niveau des droits j'ai un compte ADMIN mais après je ne maitrise pas assez SQL server. 

A mon avis il doit y avoir des paramètres côté ALTERYX et/ou SQL server qui peuvent faire en sorte d'améliorer ce temps de chargement.

[psyrio]

Pour le INDB, j'ai mis tous les workflows "simples" en INDB mais le reste malheureusement nécessite un travail sur ALTERYX.

[StephaneP]

Salut @psyrio ,

Mes pistes de recherche:

1. optimiser les index de la base SQL Server.

C'est la plus prometteuse. Je ne sais pas si tu connais ou pas. Le principe d'un index sur une base de donnée est le principe d'un sommaire. Plutôt que d'attaquer ton livre de donnée page par page en retenant celles qui t'intéresse, tu attaque par le sommaire et va directement à la page qui t'intéresse.
Comme pour un sommaire cela ne fonctionne que si les thématiques qui apapraissent dans ton sommaire correspondent à celle que tu cherches.

C'est là où arrivent les experts des bases, les DBA. Ceux ci peuvent créer des sommaires (INDEX) en fonction de tes requetes. Et ainsi les performances peuvent s'améliorer dans des proportions impressionnates (x2 àx10 x 100) suivant.
Pour cette piste il faut donc:
a. trouver un DBA si tu en as un et voir avec lui si il peux te créer les index nécessaire en fonction de la requete que tu lances. Parfois ils ne veulent pas laisser des index en dur dans leur base car cela prends de l'espace de stockage.

b. tu peux aussi créer "à la volée" dans ta connexion via les étapes "Pre SQL" puis les supprimer via le "Post SQL".

Détails de la méthode expliqués ici: Create Database Table Primary Key in Alteryx 

La pose d'index va améliorer la phase de lecture mais aussi d'écriture.

2. Attention au AutoField. Il est pertinent à utiliser ponctuellement mais pas à chaque chargement. En effet, pour bien faire son boulot, il scanne l'intégralité des lignes de toutes les colonnes pour identifier la taille mini à garder. Mais du coup lui même prends du temps. Donc c'est bien d'y avoir penser et il faut conserver cette approche, mais l est préférable de le faire une fois et de retenir ce qu'il propose puis de l'imposer via un Select. Pense à le relancer régulièrement au cas ou. Idem il se peut que si un format augmente il y ai une erreur. Mais tu pourras la corriger.

3. Et après je suis comme @Ladarthure , si la pose d'index n'est pas possible il faut réfléchir à comment revoir ton traitement et passer par des tables intermédiaires pour pousser le plus possible en In-Db. A noter que là aussi il faut penser à utiliser le pre et post SQL des inputs pour créer/ alimenter/ gérer ces tables temporaires et leurs index.

Globalement là regle est de centraliser les données dans la base qui en contient le plus pour limiter les transferts.

Piste de réflexion: Où se trouve le server SQL et est il canibalisé par d'autres traitements/ applications ?

Si il est là où se trouve Alteryx il vas y avoir concurrence de ressources. De grosses variations de temps à données équivalentes viennent justement de l'occupation du serveur de base de données.

Hope it helps

[psyrio]

Bonjour @StephaneP 

Merci pour ces différents retours.

Concernant les INDEX, nous avons mis en place certaines index mais en effet n'étant pas un expert des bases de données c'est très limité... J'utilise justement les Post SQL Statement pour créer ces INDEX quand je créé la table avec ALTERYX dans le Output Tool.

Je vais essayé de voir avec un DBA pour optimiser cette partie

N'y a t-il pas des recommandations au niveau des paramétrages du Output Tool notamment avec le bulk SQL Server pour optimiser l'écriture (Taille de la transaction VS RAM/CPU...) ?

Concernant l'Autofield, vu que je recréé les tables de restitution à chaque chargement à partir de mes tables d"historique et qu'elles peuvent charger de format il est important de garder le AutoField non ? Ce tool m'a permis de gagner énormément il y a quelques mois en performance et en place en réduisant la taille des données. Mais en effet j'ai l'impression qu'il est assez gourmant. Quel est la recommandation dans mon cas ?

Le serveur SQL est sur un serveur à part dans notre réseau. Il est seul sur son serveur comme alteryx est seul sur le sien. 

Je sais que ce n'est pas facile d'aider sans avoir l'ensemble des données :(. J'ai parcouru pas mal le forum à la recherche d'astuces même si l'anglais peut être un frein des fois sur des sujets très techniques d'où ma sollicitation.

Merci encore pour votre aide, je suis sûr qu'on arrivera à trouver quelque chose ^^

Cordialement,
Bruno

[mathieuf]

Salut Bruno 🙂

Je testerais 3 points :

• Mode bulk : qui permet en théorie des gains de performance dans sa manière d'écrire
• AMP : connais tu l'option AMP dans Alteryx
• Si tu peux ajouter de la RAM, je serais curieux de voir les apports

Sinon à voir côté bases de données. Je suis récemment tombé sur ce post, à voir si il peut t'aider :