Optimisation table avec des millions de lignes

**aserf** · 25/11/2016, 13h52

SqlPro

Bien sur je fait ce type de test sur plusieurs Server et je rapproche ensuite les résultats.

dorinf

Maintenant je comprend mieux, votre technique marche effectivement mais vous parler d'un fichier de 15mo. elle contient combien de ligne ? 1000 ? 10 000 ?
je ne passe pas que cette technique fonctionnerai avec des fichiers de 1go ? voir même de 100go ? (je vais essayer de faire un test sur un forte volumétrie et regarder le résultat)

Avec ma technique je charge des fichiers entre 10go, 20go, ... voir même plus, ces fichiers ne sont pas en xml, mais des fichiers plat multiniveau (le xml est beaucoup plus verbeux)
je ne pense pas que sql server arrivera a lire des fichiers xml supérieure à quelle que giga avec cette technique.
Et la technique que vous me sitez marche avec du XML. Mais est ce quelle fonctionne avec du JSOn ? ou d'autre format agnostique ?

**SQLpro** · 25/11/2016, 14h38

Le seuil max de volumétrie en XML est de 2 Go.

JSon est supporté à partir de la version 2016 de SQL Server.

A +

**François DORIN** · 25/11/2016, 14h41

Envoyé par aserf

Maintenant je comprend mieux, votre technique marche effectivement mais vous parler d'un fichier de 15mo. elle contient combien de ligne ? 1000 ? 10 000 ?
je ne passe pas que cette technique fonctionnerai avec des fichiers de 1go ? voir même de 100go ? (je vais essayer de faire un test sur un forte volumétrie et regarder le résultat)

Pour donner une idée de la volumétrie, je suis à plus de 80000 enregistrements insérés en 45s environ, avec traitement des données (j'ai des jointures qui prennent un peu de temps et qui ont facilement doublé le temps d'insertion). Il serait intéressant de faire une insertion avec des données prêtes à être insérées.

Envoyé par aserf

Avec ma technique je charge des fichiers entre 10go, 20go, ... voir même plus, ces fichiers ne sont pas en xml, mais des fichiers plat multiniveau (le xml est beaucoup plus verbeux)
je ne pense pas que sql server arrivera a lire des fichiers xml supérieure à quelle que giga avec cette technique.

Effectivement, il y a une limite, qui va dépendre de la configuration de la machine réalisant l'importation, avec au max une limitation de 2Go du côté de SQL Server. Mais il est tout à fait possible aussi de découper le fichier XML.

Envoyé par aserf

Et la technique que vous me sitez marche avec du XML. Mais est ce quelle fonctionne avec du JSOn ? ou d'autre format agnostique ?

Avec SQL Server 2016 qui apporte le support JSON, il devrait y avoir de faire des choses dans ce sens. Mais je n'ai pas encore eu le temps de tester cette fonctionnalité.

**SQLpro** · 25/11/2016, 14h46

Au niveau fichier, SQL Server accepte n'importe quelle taille via un BULK LOAD (bcp.exe). Néanmoins il est handicapant de traiter d'un seul coup un fichier de plusieurs centaines de Go, car cela obère le cache. La technique dans ce cas est de découper le fichiers en sous lots via les paramètres KILOBYTE_PER_BATCH ou ROWS_PER_BATCH...

Dans le cas de très grands fichiers ou de lots de multiples fichiers, SSIS est à préférer....

Il y a 8 ans, j’insérai environ 1000 fichiers XML en 20 secondes pour une volumétrie de l'ordre de 10 Go...

A +

**François DORIN** · 25/11/2016, 14h51

Envoyé par SQLpro

Il y a 8 ans, j’insérai environ 1000 fichiers XML en 20 secondes pour une volumétrie de l'ordre de 10 Go...

Merci pour ce retour

**aserf** · 25/11/2016, 14h59

Je tenais a vous remerciez de votre patiente et de l'exemple fournis, nous pouvons maintenant parler de chose concrète.

J'ai fait un fichier de teste avec 100 000 facture et 11 ligne par facture. que vous pouvez charger
http://ootary.com/test.7z

j'ai fait ce script

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
DECLARE @FileData XML
 
SELECT @FileData = BulkColumn FROM OPENROWSET(BULK 'd:\test.xml', SINGLE_BLOB) AS x
 
INSERT INTO facture(numero) SELECT XmlTable.x.value('@numero', 'VARCHAR(20)') FROM @FileData.nodes('/factures/facture') AS XmlTable(x);
 
INSERT INTO ligne(facture_ref, designation, montant)
SELECT F.id, XmlLigne.x.value('@designation', 'VARCHAR(20)'), XmlLigne.x.value('@montant', 'DECIMAL(18,2)')
FROM @FileData.nodes('/factures/facture') AS XmlFacture(x)
CROSS APPLY XmlFacture.x.nodes('ligne') AS XmlLigne(x)  
INNER JOIN facture AS F ON F.numero = XmlFacture.x.value('@numero', 'VARCHAR(20)')

il as importé ce fichier en un peu plus de 16mn sur ma machine, pour 100 000 facture et 2200022 ligne (retour de sql server)
le même traitement prend environ 2 mn 30s avec ma technique de bulk, et prend 2mn avec mon bulk optimisé (plus gourmand en RAM).

Est ce que le script peu être optimisé, la méthode de lecture du xml était elle la plus performante ?

**François DORIN** · 25/11/2016, 15h21

Envoyé par aserf

il as importé ce fichier en un peu plus de 16mn sur ma machine, pour 100 000 facture et 2200022 ligne (retour de sql server)
le même traitement prend environ 2 mn 30s avec ma technique de bulk, et prend 2mn avec mon bulk optimisé (plus gourmand en RAM).

Je viens de l'exécuter sur ma machine. L'import s'est fait en 26s.

Envoyé par aserf

Est ce que le script peu être optimisé, la méthode de lecture du xml était elle la plus performante ?

Peut être en typant le XML en précisant son schéma.

**aserf** · 25/11/2016, 15h29

En utilisant le script que j'ai fait ? ou vous l'avez modifié ? vous avez biens 100 000 facture et 2 000 000 de lignes ?
si c'est le cas votre machine est plus rapide que la mienne ...

**François DORIN** · 25/11/2016, 15h38

Pas de modification du script, je l'ai pris tel quel. Et c'est un vieux PC que j'ai remis au gout du jour (disque dur remplacé par un SSD + rajout de RAM)

**aserf** · 25/11/2016, 15h43

Est ce que vous êtes sur d’être a 26s ?
je viens de tourner ce fichier sur un autre serveur et 2mn sont déjà passé et toujours pas fini ...

je trouverai étrange que sur 2 machine différentes je n'arrive pas en dessous de la minute alors que vous êtes a 26s pour 2 300 000 lignes au total.

**François DORIN** · 25/11/2016, 15h54

Envoyé par aserf

Est ce que vous êtes sur d’être a 26s ?

Oui oui, j'en suis sûr !

**aserf** · 25/11/2016, 15h59

Quelle est l'options magic que vous avez activé, sur un autre serveur je suis a 16mn également ?
est ce que vous avez activé quelle que chose que je n'ai pas ?

mon pc local est installé avec sql server 2016 et valeur par défaut, et mon serveur de production un sql 2012 par un prestataire.
est ce que qq d'autre pourrai tester également et me dire le temps et vérifier si il y as bien 100 000 lignes dans factures et 2 000 000 dans lignes ?

**François DORIN** · 25/11/2016, 16h04

Envoyé par aserf

Quelle est l'options magic que vous avez activé, sur un autre serveur je suis a 16mn également ?
est ce que vous avez activé quelle que chose que je n'ai pas ?

J'utilise la config par défaut. Je suis sous 2014 en EXPRESS (29s) et DEVELOPPER édition (26s)

Envoyé par aserf

est ce que qq d'autre pourrai tester également et me dire le temps et vérifier si il y as bien 100 000 lignes dans factures et 2 000 000 dans lignes ?

J'ai bien 100000 factures mais seulement 1 100 00 lignes (le XML décrit 11 lignes par facture, donc le compte est bon).

Pour info, j'ai testé avec un SCHEMA XML. L'étape de validation doit ralentir le tout, car je suis à plus de 40s d'importation... au lieu de mes 26s

**aserf** · 25/11/2016, 16h14

C'est bon, j'ai compris, j'avais virer l'index unique sur la colonne numéro de la table facture pour permettre l'import de plusieurs fois le fichier et tester avec plus de volume dans la base.
je suis au même temps maintenant !

je vais essayer de faire un test sur un fichier de 2go pour voir la vitesse.

J'ai le fin mot de l’histoire, j'avais d'abord fait dans le fichier de teste que les entêtes, et j'avais insérer 100 000 entête, puis j'ai rajouter les lignes et quand sa a planté a cause de l'index je l'avais supprimer pour insérer mes lignes.
donc j'avais 200 000 lignes et 2 000 000 car il a du faire la relation avec mes 100 000 ligne déjà insérer.

j'ai donc refait avec ma technique et les même paramétrés, et je suis 10% plus lent. je vais maintenant essayer sur des plus gros volume pour voir comment il ce comporte.
je vais aussi essayer de voir si on peu le transposer à des fichiers plat multiligne (c'est le format de fichier que j'ai le plus souvent).

**SQLpro** · 25/11/2016, 16h30

Envoyé par aserf

C'est bon, j'ai compris, j'avais virer l'index unique sur la colonne numéro de la table facture pour permettre l'import de plusieurs fois le fichier et tester avec plus de volume dans la base.
je suis au même temps maintenant !

je vais essayer de faire un test sur un fichier de 2go pour voir la vitesse.

Pensez à bien dimensionner vos espaces de stockage et tout particulièrement :
1) le JT de votre base
2) multiplier les fichiers de data de tempdb.

A +

**Donpi** · 27/12/2016, 16h35

J'enfonce peut être une porte ouverte en revenant la dessus mais ...

Envoyé par devnet75

...
la table principale contient aujourd'hui 716 millions de lignes....
...
La table possède un nombre élevé de colonnes : 50. Toutes les colonnes doivent être remontées lors d'une recherche.
...
Actuellement, l'outil exécute une requête en 45 - 60 secondes environ.
...

Envoyé par devnet75

...
En effet, l'outil qui intègre les stats est fiable et ne peut pas être modifié
...
Sachant que les 716 millions de lignes actuelles ne correspondent qu'aux 4 derniers mois de 2016

...

716 Millions de records sur 50 colonnes

et

45 à 60 secondes de temps de requête pour 4 MOIS de données ?!?!?

et 4 mois de vie pour un outil fiable qui ne peut pas être modifié ?!?!?

Je trouve ces informations incompatibles.

Optimisation table avec des millions de lignes

MS SQL Server

Discussions similaires

Partager

Partager