Comment faciliter l'utilisation d'une table lourde ?

[mohe27]

bonjour à tous,

voilà au boulot je dispose d'une table appelée 'TblClient' au niveau d'un serveur Sql contenant pas mal de colonnes intitulé comme suit:
-ID (numero sequentiel autoincement)
-Nomclient
-NumTel
-Articlecommande
-Quantite
-DateCommande

cette table est alimentée au quotidien avec des informations concernant quelques 35000 clients ce qui fait qu'au bout du mois je me retrouve avec pratiquement 1 Million d'enregistrements. cette table sert aussi à dresser des tableaux de bord pour détailler l'activité commerciale avec chiffres (quantité commandée/ quel article marche le plus /.....) ce qui implique des calculs par client et par période donnée (se référer à la DateCommande). évidement ces tableaux sont dressés à partir de feuilles Excel avec connexion ODBC sur le serveurSql.

actuellement et après quelque mois de mise ne place de cette table, cette dernière contient à peu prêt 6 Millions d'enregistrements dont les dits calculs cités plus haut deviennent très lourds dessus parfois impossible d'où la nécessité de trouver une solution pour la rendre plus fluide.

j'ai entendu parler de partitionnement de table ou bien Clustered Index mais je ne sais pas si la solution à celà se trouve dans ces concepts ou pas, si oui comment m'y prendre et sinon si possible de m'aider à résoudre ce souci de lourdeur compte tenu de l'énorme quantité de donnée à calculer et à afficher via Excel.

en attente de vos avis là-dessus.

merci les amis

[iberserk]

Pour les calculs d'agregats (count etc...) une solution peut être une vue indexée...

Postez votre modèle complet en rapport avec cette table car celui ci ne me parait pas correct (n'avez vous pas un table commande en plus de votre table client? etc.)

[mohe27]

bonjour iberserk,

merci pour ton retour, en fait le modèle est comme tel, c'est à dire qu'il y a une seule table contenant toutes ces informations et non pas deux comme vous venez de supposer (table commande + table Client avec clef étrangère).

l'information est brute c'est à dire qu'elle est récupérée puis injectées telle qu'elle est dans la table 'TblClient'.

sinon par rapport à la vue indexée comment puis-je la mettre en place, c'est vrai que j'ai une vue dans laquelle j'ai tous les calculs 'count, sum' seulement pour l'indexer je ne vois pas comment et sur quelle critère mettre cet index !!!

merci de m'éclairer la dessus

cdt

[iberserk]

Commencez par revoir votre modèle en le normalisant... vous verrez que la volumétrie baissera d'elle même car vous supprimerez les redondances...

Actuellement vous répetez autant de fois qu'il ya de lignes dans votre table:
lenom du client,le telephone, la date de la commande.

Faites quatres tables:

Une table client avec ID,tel et nom.
Une table commande avec IDcommande, date et Idclient.
Une table produit (id produit et ce que vous voulez avec, nom,désignation,ref,code barre etc...).

Enfin une table d'association entre votre commande et les produits.

Vous vous retrouvez avec 3 petites tables sans redondances et une tables contenant d'avantage de ligne mais extremement petite puisqu'uniquement composée de 3 colonnes:

IdCommande.(entier)-> 4 octets
IdProduit. (entier)->4 octets
Quantite (smallint)->2 octets

En gros une dizaine d'octets par lignes... vous pouvez voir venir...

Pour comparaison vous avez actuellement au moins 40 octets par lignes avec votre 'modèle'...

[mohe27]

Merci beaucoup iberserk,

je vais suivre votre conseil et voir comment fragmenter cette information selon vos recommandations.

je sais que ça ne vas pas être facile mais faisable.

une fois cette architecture mise sur pied, recourir à la vue indexées sera necessaire lui aussi ??

merci

[iberserk]

Une vue indexée prend tout son intérêt lorsque vous effectuez des calculs d’agrégats (COUNT...) donc oui elle peut-être une piste à étudier...
Mais il faut garder à l'esprit que SQL SERVER devra maintenir à jour cette vue indexée et impliquera donc un surcoût lors des opérations d'insertions/mise à jour/suppressions.

[elsuket]

Bonjour,

Après avoir réalisé la normalisation que vous a proposé iberserk, commencez par indexer vos tables suivant :

- les prédicats de jointure
- les colonnes qui participent à la clause WHERE
- les colonnes qui participent à un GROUP BY

Mettez toujours la colonne la plus sélective (celle qui a le plus de valeurs distinctes) à gauche de votre clé d'index.
Enfin pour les index cluster et non-cluster, vous pouvez en apprendre un peu plus ici.

Le choix de l'index cluster est crucial, puisqu'il est référencé par les index non-cluster.

Pour quelques millions de lignes, vous ne devriez pas avoir besoin de vues indexées.
Néanmoins il est clair que, comme l'a dit iberserk, elles permettent de fournir une grande vélocité, surtout pour les calculs d'agrégats (comme COUNT(), SUM(), AVG(), ...).
Si votre table est donc chargée de temps en temps (par exemple une fois par jour) comme vous l'évoquez, c'est une très bonne solution.
Si en revanche les tables sous-jacentes subissent de nombreuses modifications (INSERT / UPDATE / DELETE), cela peut pénaliser les performances.

@++

[mohe27]

merci à tous,

en effet là je suis obligé d'utiliser des calculs d'agrégats donc utiliser une vue indexée me semble également utile afin de gagner en performance.

seulement là j'ai une question qui me travaille tjs.
si je prend le modèle que j'ai actuellement c'est à dire la table 'TblClient' avec les informations qui se repetent dans chaque ligne et qui consomme pas mal d'espace, en comparant ça avec le modèle que m'a proposé iberserk c'est à dire eclater le tout en plusieurs table interconnectées entre elle par des clefs etrangeres selon la necessité et par lasuite faire ressortir sur une vue le même resultat qu'il y a sur la table 'TblClient', il est où le gain en performance entre les deux approche vue que le resultat est le même ?

merci

[elsuket]

en effet là je suis obligé d'utiliser des calculs d'agrégats donc utiliser une vue indexée me semble également utile afin de gagner en performance.

Attention : ce n'est parce qu'on doit effectuer des calculs d'agrégats que l'on doit forcément créer une vue indexée.
Il se trouve que dans votre cas, cela semble une bonne option.

il est où le gain en performance entre les deux approche vue que le resultat est le même ?

Ce n'est pas parce que le résultat est le même que le gain de performances sera identique !
Entre deux requêtes qui vous retournent le résultat en 1h et l'autre en 200ms, vous préférez quoi ? moi la 2e
Il est clair qu'avec un modèle normalisé comme celui que vous a proposé iberserk vous y gagnerez :

- la taille de vos lignes sera plus courte. En conséquence, les pages de données qui stockent les valeurs de vos tables seront plus riches en données.
Une page de données permet de stocker 8060 octets de données utilisateurs.
Donc moins vos tables ont de colonnes, plus vos pages sont denses.
- Cela vous permet d'effectuer des jointures, c'est-à-dire de ne manipuler que les données qui ont besoin de l'être, et de les filtrer proprement en amont
- Que vous le vouliez ou non, cela simplifiera grandement l'écriture de requêtes sur ces tables par rapport à votre table monolithique

@++

[mohe27]

Merci Beaucoup elsuket,

pour ces éclaircissments, c'est vrai qu'au début ça parrait tout à fait identique le fait d'avoir une seule table contenant ces données et de l'autre côté opérer des jointures pour en ressortir avec le même résultat mais là est tout l'intéret d'avoir un bon modèle avec simplification de l'utilisation des requêtes sur ces tables.

je vais tâcher de faire de mon mieux pour mettre en place cette mécanique et je reviendrai vers vous en cas de besoin ce qui est sûr

Mes amitiés