Discussion :

[Hartdrooz]

Bonjour,

J'ai une table qui contient les activitées Internet des employés de ma compagnie. À chaque soir je charge environ 1 millions d'enregistrements dans cette table.

J'ai présentement tous le mois d'avril de contenu dans cette table ainsi que le mois de mai et la table fait contient maintenant 42 millions d'enregistrements. Je dois garder un historique de 6 mois dans la table donc si on fait un calcul rapide la table va contenir environ 200 millions de ligne à sa pleine capacité.

Voici le script de création de la table en question :

CREATE TABLE [Optimized_WebProxyReport] (
[ClientIP] [int] NULL ,
[ClientUserName] [int] NULL ,
[ClientAgent] [int] NULL ,
[ClientAuthenticate] [int] NULL ,
[logDate] [datetime] NULL ,
[logTime] [datetime] NULL ,
[service] [int] NULL ,
[servername] [int] NULL ,
[referredserver] [int] NULL ,
[DestHost] [int] NULL ,
[DestHostIP] [int] NULL ,
[DestHostPort] [int] NULL ,
[processingtime] [int] NULL ,
[bytesrecvd] [int] NULL ,
[bytessent] [int] NULL ,
[protocol] [int] NULL ,
[transport] [int] NULL ,
[operation] [int] NULL ,
[uri] [varchar] (255) COLLATE French_CI_AS NULL ,
[mimetype] [int] NULL ,
[objectsource] [int] NULL ,
[resultcode] [int] NULL ,
[CacheInfo] [int] NULL ,
[rule#1] [int] NULL ,
[rule#2] [int] NULL ,
[NoAuto] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_Optimized_WebProxyReport] PRIMARY KEY CLUSTERED
(
[NoAuto]
) ON [PRIMARY]
) ON [PRIMARY]
GO

J'ai mis un index sur la date, le clientIP et le ClientUserName car je dois sortir des rapports pour des dates précises (pour une semaine du mois) pour des adresses IP spécifique (ClientIP).

Je me demande est-ce que je serais mieux de garder seulement le mois courant dans cette table et de sauvegarder l'historique des 5 mois précédent dans une 5 tables question de réduire le volume de chaque table et ainsi que les requêtes se fassent toujours sur des tables d'environ 30 millions de lignes.

Où si mes indexes sont bien montées faire une requête sur une table de 200 millions d'enregistrements va être aussi rapide que sur une table de 30 millions ?

Merci

[ylarvor]

Où si mes indexes sont bien montées faire une requête sur une table de 200 millions d'enregistrements va être aussi rapide que sur une table de 30 millions ?

Cela dépend d'abord de la requête ou des requêtes, si tu fais une requete sur la totalite de la table genre un group by, il est certainement preferable de garder ta table d'une seule piece. De plus, il est possible d'avoir des architecture énorme si elles sont bien pensées au depart et que l'architecture suit...

Par exemple, concernant la normalisation, ta table a beaucoup de colonnes mais ce sont essentiellement des INT alors tu as peut être des chances pour que ça rentre dans une page sql serveur, tu pourrais essayer de faire le calcul, ce serait intéressant... si ta table est trop large, tu dois vérifier une dernière fois que tu respecte bien la 3 eme forme normale, et tu peux essayez de faire du fractionnement verticale, je crois que ca s'appelle, couper une table en deux pour rentrer dans une page sql serveur.
Avec un tel volume de données, il est nécessaire de bien réfléchir en amont à ce que tu fais...
par exemple, tu n'as pas moyen d'isoler ton uri dans une autre table... ce serait bien! casse ton modele si tu peux...
mieux vaut 2 tables de fractionnement verticale de dix millions d'enregistrement pesant 10 giga octet chaque une que 5 tables de fractionnement horizontale de 4 giga chaque une. La jointure ne coute pas tres chere...

coté serveur, tu dois réfléchir à ton architecture aussi pour optimiser les temps de réponses!

[seminoque]

Je trouve que les conseils de ylarvor sont intéressants.

Je pense que si tes requêtes restent simples et utilisent les index, il n'y aura pas beaucoup d'écart de performance dans le fait d'utiliser une ou plusieurs tables. Le fait de faire un chargement par soir ne pose pas de problème non plus. Ce serait tès différent avec de nombreux accès concurrents en lecture ou écriture.

Maintenant si tu veux créer plusieurs tables je te suggère de créer une vue partitionée qui pointe vers toutes les tables, comme ça tes requêtes restent simples.

Par ailleurs, plusieurs petites tables, c'est plus pratique à dupliquer, copier, restaurer etc. qu'une seule grosse. En terme d'administration tu peux y gagner du temps.


http://www.bingokaz.com

[SQLpro]

A priori la longeur des lignes de votre table devrait faire environ 200 octets. (108 octets fixes et 255 de variable uri pour lequel j'ai compté en moyenne 90 octets....)
Il y aura donc 40 lignes environ par page.
Avec 200 millions de lignes, cela va représenter 5 millions de pages.
Chacun des index que vous aller faire sur les colonnes de type INT aura une profondeur de 3 à 4 pages maximum.
Autrement dit, le parcours de la table en SCAN soit 5 millions de page mettra du temps. Mais la recherche dans un index IN T sera instantané, sauf si elle doit remonter un nombre considérable de lignes.

A +

PS :on ne parle pas d'enregistrement dans une base de données, mais de ligne. En effet les SGBDR n'écrivent heureusement pas à chaque ligne insérée sur le disque !