Design de bases de donnees sous SQL server 2008

Question

Bonjour,

Je poste ici le problème sur lequel je planche actuellement à mon taff. Comme le design de BD n'est pas ma spécialité (plutot le dev), j'aimerai avoir quelques avis de pro.

Le projet consiste à migrer une application implantée chez 400 clients représentant chacun une micro structure (entre 1 et 20 personnes).
Auparavant chacun des clients stockait sa propre BD en réseau local et l'appli était un exe.

La prochaine version sera un RIA avec un serveur de bases de données qui devra gérer l'accés aux données des 400 clients.

La Bd sera sous SQLserver 2008. J'ai beaucoup cherché et testé ces dernières semaines et j'ai découvert que certains sont trés au point sur le sujet.

Les BD de chaques clients sont de tailles modestes. Les fichiers les plus utilisés peuvent se répartir entre 2 catégories : les fichiers histo et agenda ont peu de colonnes mais beaucoup d'enregistrements sont générés (certains clients ont dépassé les 200000 lignes sur 5 ans). Ensuite 2 fichiers ont été créés il ya longtemps pardes gens "pas experts du tout" en BD :beaucoup de colonnes (200+) beaucoup d'index (~=35) et beaucoup de mémos (type text en SQL2008).

Aprés ce "rapide" énoncé du problème j'ai testé plusieurs techniques :
- regrouper les 400 BD en une seule (obligatoire je pense) et utiliser une technique de "row level security" basée sur des vues. Chaque client a un id que j'ai rajouté dans chaque table et que j'ai mis en index clustered. POUR CHAQUE CLIENT j'ai crée une connexion ayant le même nom que son ID. Ensuite j'ai créé une vue par table avec la syntaxe suivante :

CREATE VIEW maVue AS
SELECT * FROM maTable WHERE id=SUSER_SNAME() 

Concernant histo et agenda j'ai mis en place un partitionnement par an basé sur la date pour éviter d'avoir un seul énorme fichier et en sachant aussi que les interrogations sur ces fichiers concernent à 95% les 3 derniers mois.

Concernant les 2fichiers ayant trop de colonnes je  les ai partitionné verticalement en 3 fichiers: 
- clé primaire et index et idclient 
- champ text et champ varchar de grandes tailles
- le reste

J'utilise ensuite une vue pour regrouper les colonnes si bien que la vue possède les mêmes colonnes que lefichier original plus la colonne id.

Je suis pour le moment au tout début de ma première phase de test. Cela fonctionne car les données sont bien isolées et que selon l'id de connexion on "atterri" chez un client différent.
Par contre je n'ai pas pu injecter les données des 400 clients et je me fais des soucis niveau perf...

Si quelqu'un a eu le courage de tout lire et si il a un avis à donner (ou des questions ) il est le bienvenu....

D'avance merci


Philippe

gperuch · Answer

Salut,
Dans un premier temps, je pense que tu es bien parti. Vu le sac de noeux que tu avais au derpart il sera difficilement imaginable que tu fasses un truc parfait du premier coup.
Pour ce qui est des pref, le nombre de lignes n'est pas un problème, si les index sont bien utilisé. Donc importe tout dans ta table et ensuite quant ton programme vivra tu pourras toujours utiliser un outils qui s'appele le "profiler" et tu veras bien la ou ca rame.(pour info j'ai une table de plus 1milliard de lignes qui donne des reponse quasi instantanées)
ATTENTION, utilise bien une version autre que express, car tu risques de depasser 4GO qui est des limitations (j'ai jamais testé) de la version express.

Bonne chance

Greg

aieeeuuuuu · Answer

salut

tout a fait d'accord avec gperuch.

je ne pense pas qu'il soit utile de faire plusieurs tables pour les même données. au contraire, ça va énormément compliquer tes requêtes pour pouvoir gérer les 5% des cas ou les client vont avoir besoin des données plus ancienne que 3 mois.
il sera effectivement plus simple de faire des bons index, et perfs seront meilleurs qu'avec deux tables.

a premiere vue, je dirai un index sur l'ID du client puis sur la date, mais pour créer les bons index, il faut non seulement connaitre la structure de la base, mais surtout son utilisation (les principales requetes).

elfifoo · Answer

salut et merci de vos réponses.

J'ai commencé à tester et à débugger l'applicatif qui accède aux bases aujourd'hui. Pour l'instant va y avoir des trucs à optimiser mais c'est la loi du sport.

gperuch : merci de ta remarque je m'étais renseigné vite fait sur le profiler et comme je commence les tests......
Pour SQLserver j'ai la version entreprise et pour les tables partitionnées c'est surtout du fait que plus un enregistrement est vieux moins il sert, SQL server le gère tout seul comme un grand et d'un point de vue logique l'applicatif ne voit qu'une table.

aieeeuuuuu :  j'ai mis des index sur le ID en clustered, si j'ai bien compris cela veut dire que SqlServer écrit dans la table les lignes de façon à ce que les mêmes valeurs de l'index se suivent. Normalement tous les enregistrements avec le même ID sont donc groupés. Savez vous si c'est réellement performant

Autre question : je me suis arrêté sur le "execution plan" d'une requete sur laquelle je dois travailler car elle est trés utilisée et trés lente (736ms !) alors que le "plan count" est à 1.

je vous tiendrai au courant ....

A+

Philippe

aieeeuuuuu · Answer

re,

les index ??? oui c'est EXTREMENT performant quand ils sont bien placés.

disons que pour s'en rendre compte, il suffit de s'imagnier en train de chercher un mot dans un dictionnaire non classé par ordre alphabetique, mai sou les mots seraient dans un ordre quelconque...

pour ce qui est des indexs clustered, c'est bien ca, les enregistrements se suivent de facon logique. c'est très performant en lecture, par contre, en insertion, ca peut bcp ralentir, car le serveur devra réorganiser les données déjà présentes afin d'intercaler les nouvelle données au milieu... enfin en gros, car il y a pas mal de réglages subtils qui permettent de trouver un bon compromis dans pas mal de cas, de recalculer les indexes a des moment précis (la nuit...)...

Design de bases de donnees sous SQL server 2008

4 réponses

Votre réponse

Discussions similaires