Discussion :

[nicobest]

Bonjour,

je développe actuellement une application console ASP.NET dont le but est de charger un fichier texte dans une base SQL Server 2008.

Ces fichiers texte ne contiennent que des chaînes de caractères (délimitées par des " " donc) et des nombres.

Le problème est que la longueur des chaînes de caractères n'est pas fixe. Par exemple :

Code :

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

1
2
"chaine1","chaine2",123,"chaine3"
"machaine1","uneautrechaine2",87654,"ch3"

Je dois évidemment créer la table avant de pouvoir y insérer les données (la copie en elle-même se fait par SqlBulkCopy et ne pose aucun problème).

Sachant que :
- les fichiers comportent plusieurs centaines de milliers de lignes et des dizaines de colonnes
- plusieurs fichiers sont à insérer dans la même table (ce qui exclut la possibilité de parcourir le fichier une première fois pour déterminer la taille max des champs texte)
- j'ai un impératif à respecter en terme de performance, pas tant sur la création de la table et l'insertion de ces données (encore que, évidemment plus c'est rapide mieux c'est) mais surtout sur la lecture de la table par la suite.

Quelle est d'après vous la meilleure méthode pour créer la table et choisir un bon type de donnée pour les chaînes de caractères ?


Personnellement, je ne vois que deux solutions pour le moment, mais je ne m'y connais pas assez en SQL Server pour en trouver tous les avantages et inconvénients en terme de performance (et aussi de stockage, mais j'imagine que c'est assez lié).

1/ Dans le cas du premier fichier à insérer (donc la table doit être créée), parcourir le fichier en entier pour déterminer la valeur max de chaque champ texte et créer la table avec des VARCHAR(N) où N est donc cette valeur max calculée. Dans le cas de tous les autres fichiers à insérer dans la même table, calculer la taille max des champs texte à nouveau (je dois parcourir le fichier en entier de toute manière pour le mettre en mémoire dans une DataTable donc j'imagine que je peux le faire en même temps) et faire un ALTER TABLE pour augmenter la taille des VARCHAR quand nécessaire.

2/ Utiliser VARCHAR(MAX) pour chaque champ de texte.

Les champs de texte ne sont généralement pas très long (<200 chars) mais je n'ai aucun moyen de prédire l'avenir.


Pouvez-vous SVP me dire ce que vous pensez, notamment en terme de performance, de mes 2 solutions (la 1 me rebute un peu, la 2 me semble trop facile...) ? Et avez-vous d'autres idées ?

Merci !
Nicolas

[elsuket]

Bonjour,

Pouvez-vous SVP me dire ce que vous pensez, notamment en terme de performance, de mes 2 solutions (la 1 me rebute un peu, la 2 me semble trop facile...)

Vois idées sont bonnes.

Il faut simplement savoir que stocker un champ text est coûteux, parce que chaque ligne enregistrée avec ce type de données nécessite un pointeur de 16 octets (les autres colonnes de la table et la chaîne elle-même sont stockés dans des pages différentes) en sus des données elles-mêmes.

Si vous êtes absolument certain que vos chaînes ne dépassent pas 200 caractères, créez votre table avec des colonnes de type VARCHAR(300) par exemple, de sorte à avoir une marge de sécurité pour ne pas avoir à réaliser un ALTER TABLE ALTER COLUMN.
Un varchar nécessite 1 octet par caractère + 2 octets pour le pointeur indiquant la position de celui-ci dans la ligne.
Néanmoins ce type de stockage engendre de la fragmentation dans les pages.
Comme vous faites du BULK ce n'est pas très important, mais si vous devez indexer celles-ci, pensez à vérifier le taux de fragmentation de vos index régulièrement.

Enfin si vos chaînes sont de longueur peu variable, le mieux est encore de les stocker dans des colonnes de type CHAR, mais sachez que dans ce cas, tout caractère manquant pour atteindre la longueur que vous avez spécifiée pour la colonne cible, sera remplacé par un espace.

@++

[nicobest]

Merci pour cette réponse !

Il faut simplement savoir que stocker un champ text est coûteux, parce que chaque ligne enregistrée avec ce type de données nécessite un pointeur de 16 octets (les autres colonnes de la table et la chaîne elle-même sont stockés dans des pages différentes) en sus des données elles-mêmes.

En essayant de me documenter un peu sur le sujet (même si pour moi le monde SQL reste assez obscur ), j'avais cru comprendre que le type VARCHAR(MAX) était différent en ce qu'il était plus performant le type TEXT qu'il est supposé remplacé.
Ma solution 2 supposait donc que cela soit véritablement le cas, et j'aurai aimé savoir dans quelle mesure VARCHAR(MAX) est une amélioration de TEXT. J'ai lu diverses choses dans divers articles - selon quoi parfois les données sont stockées "en ligne", parfois ailleurs et en ligne on ne laisse qu'un pointeur comme avec TEXT, que ça dépendait de la taille totale de la ligne... Bref je suis un peu perdu, j'ai besoin de ces explications dans un langage de niveau plus à ma hauteur (=sql noob)

Néanmoins ce type de stockage engendre de la fragmentation dans les pages.
Comme vous faites du BULK ce n'est pas très important, mais si vous devez indexer celles-ci, pensez à vérifier le taux de fragmentation de vos index régulièrement.

Pareillement, j'ai peur de ne pas vraiment avoir la notion de page ni d'index (en tous cas d'index de pages)... Je crains d'avoir besoin d'une explication plus simple


Plus précisément, mes repères à moi en termes de performances sont assez simples (simplistes) :
- l'espace occupé sur le disque (par rapport à l'espace réel pris par les données)
- le temps d'exécution d'une requête qui va faire un SELECT * FROM maTable ORDER BY id

Je ne me suis jamais penché sur la question de fragmentation causée par des UPDATE sur des champs de type VARCHAR car dans mon cas, il n'y a jamais d'UPDATE (une fois les données insérées, elles ne sont jamais modifées, donc les champs textes ne se retrouvent pas coupés en deux par ci par là). Ou bien j'ai mal compris la notion de fragmentation à la base.

Donc concrètement, certes un type VARCHAR(MAX) (anciennement TEXT) est moins performant qu'un VARCHAR(300) (est-ce encore réellement le cas d'ailleurs avec VARCHAR(MAX) comme c'était le cas avec TEXT ?), mais dans quelle mesure ?


Merci !