Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Windows Windows Serveur SQL Serveur question sur nombre d'index / performance / taille de base

question sur nombre d'index / performance / taille de base

8 réponses
Avatar
Lavergne guillaume
Bonjour,

je travaille sur une table qui a pour but de stocker des données d'un
process temps réel.
l'intégrité des données m'oblige à créer une clée primaire qui sur les
champs A B C D.
La taille de la table est approximativement à 10 Go. (251 540 283 rows)
La selectivite des champs:
le champ A peut avoir jusqu'a 20 000 valeur différentes
Le champ b est pratiquement unique (timestamp)
Il n'y a aucune recherche sur les champs C et D.

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) COLLATE French_CI_AS NOT NULL,
[B] [float] NOT NULL,
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Sur cette table, je pratique deux grands types de requete.

requete 1: select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and c < value2
(récuperation des toutes les données entre deux dates)

et

requete 2: select *
from TABLE1
where B < value1
(select en vue de supprimer les données les plus anciennes)

1) j'ai d'abord créer un index de type clustered
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[B] ASC,
[A] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 n'est pas efficace car B n'est pas très selectif
La requete 2 est très efficace.

2) j'ai ensuite changé mon index en
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 est très efficace
La requete 2 n'est pas du tout efficace


3) J'ai donc decidé de créer second index
CREATE NONCLUSTERED INDEX IX_TABLE1_1 ON dbo.TABLE1
(
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Mais désormais la taille de l'index (jusque la pratiquement nulle car index
clustered) est équivalente à la taille des données. La taile de la base
double !

avant creation de l'index reserved:3925168 KB data:3900424 KB index:24104 KB
apres creation de l'index reserved:7353552 KB data:3901600 KB index:3451072
KB

Existe t'il une solution pour mon probleme : Obtenir de bonne performance
sur les requete 1 et 2 sans doubler ma taile de base ?
Signaler un problème avec ce contenu

8 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Fred BROUARD
Bonjour,

1) créer un index primaire multicolonne en cluster sur 4 colonnes dont la
plupart des insertion arrivent dans un ordre aléatoire est contre performant.
Il vaudrait mieux, soit ajouter un auto incrément et en faire la clef primaire
cluster, soit réaliser cette clef en heap (non clustered).

2) aucune de vos deux requête n'utilise l'index primaire il faut donc rajouter
des index, sachant que chaque index doit être recalculé à chaque insert...

3) le type TIMESTAMP est considéré comme obsolète (V 2005) et ne sera plus
supporté dans les versions futures. Il est souhaitable de prendre un autre type
de données. A quoi vous eert-il concrétement dans votre table ?

4) fâites vous beaucoup d'UPDATE dans votre table ?

5) des index sur des VARCHAR en premier élément constituant l'index peuvent être
particulièrement contre performant si des UPDATE sont fait sur les colonnes
VARCHAR notamment lorsqu'il y a élargissement de la longueur de la données

6) si un smallint économise du coût de stockage il s'avère moins rapide dans les
opérations que de int pour les raisons de conversion au mot processeur (32 bits
= int).

7) de meilleurs performances de recherches sont obtenues si l'on utilise une
collation binaire dans le cas ou cette collation est celle d'installation du
serveur. Dans ce cas les comparaison des colonnes de type chaîne de caractères
s'effectue directement bit à bit.

8) les index ne sont utilisé que si la sélectivité calculée a priori est
importante. En général si le filtre doit rapporter moins de 10% l'index est
utilisé. Si le filtre doit rapporter plus de 20% un scan est opéré même en
présence de l'index.

Lavergne guillaume a écrit:
Bonjour,

je travaille sur une table qui a pour but de stocker des données d'un
process temps réel.
l'intégrité des données m'oblige à créer une clée primaire qui sur les
champs A B C D.
La taille de la table est approximativement à 10 Go. (251 540 283 rows)
La selectivite des champs:
le champ A peut avoir jusqu'a 20 000 valeur différentes
Le champ b est pratiquement unique (timestamp)
Il n'y a aucune recherche sur les champs C et D.

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) COLLATE French_CI_AS NOT NULL,
[B] [float] NOT NULL,
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Sur cette table, je pratique deux grands types de requete.

requete 1: select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and c < value2
(récuperation des toutes les données entre deux dates)

et

requete 2: select *
from TABLE1
where B < value1
(select en vue de supprimer les données les plus anciennes)

1) j'ai d'abord créer un index de type clustered
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[B] ASC,
[A] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 n'est pas efficace car B n'est pas très selectif
La requete 2 est très efficace.

2) j'ai ensuite changé mon index en
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 est très efficace
La requete 2 n'est pas du tout efficace


3) J'ai donc decidé de créer second index
CREATE NONCLUSTERED INDEX IX_TABLE1_1 ON dbo.TABLE1
(
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Mais désormais la taille de l'index (jusque la pratiquement nulle car index
clustered) est équivalente à la taille des données. La taile de la base
double !

avant creation de l'index reserved:3925168 KB data:3900424 KB index:24104 KB
apres creation de l'index reserved:7353552 KB data:3901600 KB index:3451072
KB

Existe t'il une solution pour mon probleme : Obtenir de bonne performance
sur les requete 1 et 2 sans doubler ma taile de base ?



Sans le connaissance sémantique de votre problématique toute optimisation
efficace est impossible.

Que me répondriez vous si je vous demandais quel est le meilleur camion pour
transporter 30 tonnes de marchandises ?

Il manque beaucoup d'information pour traiter cette demande : périssable ?
Fongible ? dangereuse ? Sur quelles routes ?

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Lavergne guillaume
1) A propos de l'index primaire multi colonne, l'ordre n'est pas aléatoire.
Le second champ de mon index (B) est de type float, il permet de stocker un
timestamp (filetime au sens C).
Les données issues du process temps réel arrivent à 99% en ordre
chronologique.

2) Oups il y avait une erreur sur la requête 1…
La requête devient donc :
select B,C,D from TABLE1
where A = 'exempleA'
and B > value1
and B < value2
order by B ASC
Le query analyzer m'indique bien un clustered index seek.

3) Le type timestamp n'est pas utilisé dans mon exemple. Je ne dois pas être
très clair dans mes explications. :(
Je stocke un filtetime au sens C dans le SQL Server a travers son type float
(j'ai besoin de la précision du filetime).

4) Il n'y a aucun update sur la table

5) Il n'y a aucun update sur la table

6) En tant qu'opération, vous entendez une fonction d'agrégation (sum, var)
ou simplement lecture / écriture?
Dans mon cas précis, il n'y a aucun calcul de réalisé sur les données
(simplement des écritures / lectures)

7) Merci du conseil, c'est une très bonne information.

8) une table standard de travail possède en général 251 540 283 lignes, la
requête 1 ne sélectionne jamais plus de 10 000 lignes. Sur mes tests, le
query analyser m'indique toujours clustered index seek.

J'ai l'impression que je n'ai pas été très clair, je vais tenter de faire
mieux. :)

Pour donner plus d'information sur mon système.
La base de travail a pour but de stocker sur une base avec une taille fixe
un nombre d'événement issu d'un process temps réel.
Je reçois donc des événements dans un ordre pratiquement toujours
chronologique.
Je ne fais aucun update sur la table.
Je ne réalise que deux types de requêtes.
Une requête de type "analyse" qui a pour but de demander les informations
sur un événement (champ A) sur une plage de temps (champ B).
Une requête de type "maintenance" qui a pour but de supprimer les plus
vielles informations. (La gestion de la table de l'extérieur s'apparente à
une FIFO)
La table possède cette structure est compte au minimum 250 000 000 lignes.
Le but est de faire en sorte que les deux requêtes « analyse » et «
maintenance » fonctionnent de façons optimisées.

Lors des mes essais d'optimisation, je n'ai pas réussi sans doubler la
taille de la base. Existe t'il une solution ?

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) NOT NULL, -- au maximum 20 000 valeurs différentes
[B] [float] NOT NULL, -- stocke une date (données unique)
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
)

requete "analyse" :
select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and B < value2
order by B ASC

requete "maintenance": select A,B,C,D
from TABLE1
where B < value1

Merci beaucoup



"Fred BROUARD" wrote:

Bonjour,

1) créer un index primaire multicolonne en cluster sur 4 colonnes dont la
plupart des insertion arrivent dans un ordre aléatoire est contre performant.
Il vaudrait mieux, soit ajouter un auto incrément et en faire la clef primaire
cluster, soit réaliser cette clef en heap (non clustered).

2) aucune de vos deux requête n'utilise l'index primaire il faut donc rajouter
des index, sachant que chaque index doit être recalculé à chaque insert...

3) le type TIMESTAMP est considéré comme obsolète (V 2005) et ne sera plus
supporté dans les versions futures. Il est souhaitable de prendre un autre type
de données. A quoi vous eert-il concrétement dans votre table ?

4) fâites vous beaucoup d'UPDATE dans votre table ?

5) des index sur des VARCHAR en premier élément constituant l'index peuvent être
particulièrement contre performant si des UPDATE sont fait sur les colonnes
VARCHAR notamment lorsqu'il y a élargissement de la longueur de la données

6) si un smallint économise du coût de stockage il s'avère moins rapide dans les
opérations que de int pour les raisons de conversion au mot processeur (32 bits
= int).

7) de meilleurs performances de recherches sont obtenues si l'on utilise une
collation binaire dans le cas ou cette collation est celle d'installation du
serveur. Dans ce cas les comparaison des colonnes de type chaîne de caractères
s'effectue directement bit à bit.

8) les index ne sont utilisé que si la sélectivité calculée a priori est
importante. En général si le filtre doit rapporter moins de 10% l'index est
utilisé. Si le filtre doit rapporter plus de 20% un scan est opéré même en
présence de l'index.

Lavergne guillaume a écrit:
> Bonjour,
>
> je travaille sur une table qui a pour but de stocker des données d'un
> process temps réel.
> l'intégrité des données m'oblige à créer une clée primaire qui sur les
> champs A B C D.
> La taille de la table est approximativement à 10 Go. (251 540 283 rows)
> La selectivite des champs:
> le champ A peut avoir jusqu'a 20 000 valeur différentes
> Le champ b est pratiquement unique (timestamp)
> Il n'y a aucune recherche sur les champs C et D.
>
> CREATE TABLE [dbo].[TABLE1](
> [A] [varchar](255) COLLATE French_CI_AS NOT NULL,
> [B] [float] NOT NULL,
> [C] [float] NOT NULL,
> [D] [smallint] NOT NULL,
> CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
> (
> [A] ASC,
> [B] ASC,
> [C] ASC,
> [D] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> Sur cette table, je pratique deux grands types de requete.
>
> requete 1: select B,C,D
> from TABLE1
> where A = 'exempleA'
> and B > value1
> and c < value2
> (récuperation des toutes les données entre deux dates)
>
> et
>
> requete 2: select *
> from TABLE1
> where B < value1
> (select en vue de supprimer les données les plus anciennes)
>
> 1) j'ai d'abord créer un index de type clustered
> CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
> (
> [B] ASC,
> [A] ASC,
> [C] ASC,
> [D] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> La requete 1 n'est pas efficace car B n'est pas très selectif
> La requete 2 est très efficace.
>
> 2) j'ai ensuite changé mon index en
> CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
> (
> [A] ASC,
> [B] ASC,
> [C] ASC,
> [D] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> La requete 1 est très efficace
> La requete 2 n'est pas du tout efficace
>
>
> 3) J'ai donc decidé de créer second index
> CREATE NONCLUSTERED INDEX IX_TABLE1_1 ON dbo.TABLE1
> (
> B
> ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
> = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> GO
> Mais désormais la taille de l'index (jusque la pratiquement nulle car index
> clustered) est équivalente à la taille des données. La taile de la base
> double !
>
> avant creation de l'index reserved:3925168 KB data:3900424 KB index:24104 KB
> apres creation de l'index reserved:7353552 KB data:3901600 KB index:3451072
> KB
>
> Existe t'il une solution pour mon probleme : Obtenir de bonne performance
> sur les requete 1 et 2 sans doubler ma taile de base ?

Sans le connaissance sémantique de votre problématique toute optimisation
efficace est impossible.

Que me répondriez vous si je vous demandais quel est le meilleur camion pour
transporter 30 tonnes de marchandises ?

Il manque beaucoup d'information pour traiter cette demande : périssable ?
Fongible ? dangereuse ? Sur quelles routes ?

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************




Avatar
Med Bouchenafa
La taille s'explique par le fait que le second index (l'index non cluster)
contient toutes les données de l'index cluster
Dans SQL Server, un index non cluster contient comme pointeur les données de
l'index cluster
C'est pourquoi, il faut toujours éviter d'avoir un index cluster important
en taille

--
Avec mes meilleurs voeux 2006
Med Bouchenafa

"Lavergne guillaume" a écrit
dans le message de news:

Bonjour,

je travaille sur une table qui a pour but de stocker des données d'un
process temps réel.
l'intégrité des données m'oblige à créer une clée primaire qui sur les
champs A B C D.
La taille de la table est approximativement à 10 Go. (251 540 283 rows)
La selectivite des champs:
le champ A peut avoir jusqu'a 20 000 valeur différentes
Le champ b est pratiquement unique (timestamp)
Il n'y a aucune recherche sur les champs C et D.

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) COLLATE French_CI_AS NOT NULL,
[B] [float] NOT NULL,
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Sur cette table, je pratique deux grands types de requete.

requete 1: select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and c < value2
(récuperation des toutes les données entre deux dates)

et

requete 2: select *
from TABLE1
where B < value1
(select en vue de supprimer les données les plus anciennes)

1) j'ai d'abord créer un index de type clustered
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[B] ASC,
[A] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 n'est pas efficace car B n'est pas très selectif
La requete 2 est très efficace.

2) j'ai ensuite changé mon index en
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 est très efficace
La requete 2 n'est pas du tout efficace


3) J'ai donc decidé de créer second index
CREATE NONCLUSTERED INDEX IX_TABLE1_1 ON dbo.TABLE1
(
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Mais désormais la taille de l'index (jusque la pratiquement nulle car
index
clustered) est équivalente à la taille des données. La taile de la base
double !

avant creation de l'index reserved:3925168 KB data:3900424 KB index:24104
KB
apres creation de l'index reserved:7353552 KB data:3901600 KB
index:3451072
KB

Existe t'il une solution pour mon probleme : Obtenir de bonne performance
sur les requete 1 et 2 sans doubler ma taile de base ?


Avatar
bruno reiter [MVP]
à mon avis,

comme il y a une requete de selection avec pratiquement toutes les colonnes,
il est bon qu'elle se fasse sur l'index cluster, la selectivité la plus
grande (à travers la requete, pas dans le stockage) étant sur la colonne A
elle doit être la première de l'index.
Donc la PK que tu décris est la meilleure.

les colonnes B et C en float ne paraissent pas une bonne idée, si tu as
besoin de nombreuses valeurs, un bigint serait sans doute plus adapté car ce
n'est pas de l'approximatif, ce qui est nettement mieux pour une clé
d'index, surtout quand il il a des comparaisons dessus.

pour la deuxième requete, un index uniquement sur B résoud le problème, si
la requête ramène moins de 10% des lignes.

Donc ta solution était bien la bonne et dans tous les cas, la taille de la
table doublera:
si le cluster est sur A,B,C,D l'index non cluster doit référencer le cluster
pour l'accès aux données donc à chaque nouvel index non cluster tu auras à
nouveau toutes les valeurs du cluster donc la meme taille.
si tu as un identifiant, tu auras les données une fois dans l'index cluster,
puis une nouvelle fois la meme taille pour l'index A,B,C,D
si tu n'as pas de cluster, tu auras les données une fois dans le heap, puis
une nouvelle fois la meme taille pour l'index A,B,C,D

Donc impossible de diminuer la taille si tu veux 2 index.

De plus tu dois avoir un sérieux problème de tmps pour la réindexation.

br

"Lavergne guillaume" wrote in
message news:
Bonjour,

je travaille sur une table qui a pour but de stocker des données d'un
process temps réel.
l'intégrité des données m'oblige à créer une clée primaire qui sur les
champs A B C D.
La taille de la table est approximativement à 10 Go. (251 540 283 rows)
La selectivite des champs:
le champ A peut avoir jusqu'a 20 000 valeur différentes
Le champ b est pratiquement unique (timestamp)
Il n'y a aucune recherche sur les champs C et D.

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) COLLATE French_CI_AS NOT NULL,
[B] [float] NOT NULL,
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Sur cette table, je pratique deux grands types de requete.

requete 1: select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and c < value2
(récuperation des toutes les données entre deux dates)

et

requete 2: select *
from TABLE1
where B < value1
(select en vue de supprimer les données les plus anciennes)

1) j'ai d'abord créer un index de type clustered
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[B] ASC,
[A] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 n'est pas efficace car B n'est pas très selectif
La requete 2 est très efficace.

2) j'ai ensuite changé mon index en
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 est très efficace
La requete 2 n'est pas du tout efficace


3) J'ai donc decidé de créer second index
CREATE NONCLUSTERED INDEX IX_TABLE1_1 ON dbo.TABLE1
(
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,


ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Mais désormais la taille de l'index (jusque la pratiquement nulle car


index
clustered) est équivalente à la taille des données. La taile de la base
double !

avant creation de l'index reserved:3925168 KB data:3900424 KB index:24104


KB
apres creation de l'index reserved:7353552 KB data:3901600 KB


index:3451072
KB

Existe t'il une solution pour mon probleme : Obtenir de bonne performance
sur les requete 1 et 2 sans doubler ma taile de base ?


Avatar
Fred BROUARD
Lavergne guillaume a écrit:
1) A propos de l'index primaire multi colonne, l'ordre n'est pas aléatoire.
Le second champ de mon index (B) est de type float, il permet de stocker un
timestamp (filetime au sens C).
Les données issues du process temps réel arrivent à 99% en ordre
chronologique.



Pas bon pour un index en cluster. Le mieux serait 100% c'est à dire TOUJOURS
ordonnné sur toutes les colonnes concaténées.



2) Oups il y avait une erreur sur la requête 1…
La requête devient donc :
select B,C,D from TABLE1
where A = 'exempleA'
and B > value1
and B < value2
order by B ASC
Le query analyzer m'indique bien un clustered index seek.

3) Le type timestamp n'est pas utilisé dans mon exemple. Je ne dois pas être
très clair dans mes explications. :(
Je stocke un filtetime au sens C dans le SQL Server a travers son type float
(j'ai besoin de la précision du filetime).

4) Il n'y a aucun update sur la table

5) Il n'y a aucun update sur la table

6) En tant qu'opération, vous entendez une fonction d'agrégation (sum, var)
ou simplement lecture / écriture?
Dans mon cas précis, il n'y a aucun calcul de réalisé sur les données
(simplement des écritures / lectures)



Même pour de simple lectures et écriture le smallint est plus long que le int.


7) Merci du conseil, c'est une très bonne information.

8) une table standard de travail possède en général 251 540 283 lignes, la
requête 1 ne sélectionne jamais plus de 10 000 lignes. Sur mes tests, le
query analyser m'indique toujours clustered index seek.

J'ai l'impression que je n'ai pas été très clair, je vais tenter de faire
mieux. :)

Pour donner plus d'information sur mon système.
La base de travail a pour but de stocker sur une base avec une taille fixe
un nombre d'événement issu d'un process temps réel.
Je reçois donc des événements dans un ordre pratiquement toujours
chronologique.
Je ne fais aucun update sur la table.
Je ne réalise que deux types de requêtes.
Une requête de type "analyse" qui a pour but de demander les informations
sur un événement (champ A)



ne pouvez vous pas codifier vos événements dans une table de référence et
stocker dans votre table primale uniquement la clef sous la forme d'un INTEGER ?
Cela évitera la comparaison de chaine de car toujours très couteuses et
diminuera sensiblement le stockage et l'index, donc de meilleurs perf à tous les
niveaux

Si je lit plus en avant : vous dites :
"[A] [varchar](255) NOT NULL, -- au maximum 20 000 valeurs différentes"
Mettons qu'en moyenne un événement, ce soit 25 caractères =>
26 caractères => 26 octets
Le processeur ne peut lire qu'un mot de 32 bits à chaque passe => 4 octets.
Il faut donc 7 passes pour lire un événement.
Si vous codifiez vos 20 000 événements à l'aide d'une table de référence (EVT_ID
INT, EVT_LIBELLE VARCHAR(255)), vous passez à une seule passe pour lire un
évenement c'est donc 7 fois plus rapide. En ajoutant les problématiques de
collation, on avoisine les 15 fois plus rapide...
En matière de stockage maintenant,
la version VARCHAR dans la table => 26 octets * 250 millions => 6,2 Go
la version INT dans la table => 4 octets * 250 millions => 0,9 Go.
Votre table diminue donc de 5,3 Go
la table de référence faisant : (26 + 4 =) 30 * 20 000 = 0,57 Go
Votre base passe donc de 10 Go à 6 Go...
Donc les performances devrait pratiquement doubler.

Faîtes des essais et dites m'en des nouvelles !!!


sur une plage de temps (champ B).
Une requête de type "maintenance" qui a pour but de supprimer les plus
vielles informations. (La gestion de la table de l'extérieur s'apparente à
une FIFO)
La table possède cette structure est compte au minimum 250 000 000 lignes.
Le but est de faire en sorte que les deux requêtes « analyse » et «
maintenance » fonctionnent de façons optimisées.

Lors des mes essais d'optimisation, je n'ai pas réussi sans doubler la
taille de la base. Existe t'il une solution ?

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) NOT NULL, -- au maximum 20 000 valeurs différentes
[B] [float] NOT NULL, -- stocke une date (données unique)
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
)

requete "analyse" :
select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and B < value2
order by B ASC

requete "maintenance": select A,B,C,D
from TABLE1
where B < value1

Merci beaucoup



"Fred BROUARD" wrote:


Bonjour,

1) créer un index primaire multicolonne en cluster sur 4 colonnes dont la
plupart des insertion arrivent dans un ordre aléatoire est contre performant.
Il vaudrait mieux, soit ajouter un auto incrément et en faire la clef primaire
cluster, soit réaliser cette clef en heap (non clustered).

2) aucune de vos deux requête n'utilise l'index primaire il faut donc rajouter
des index, sachant que chaque index doit être recalculé à chaque insert...

3) le type TIMESTAMP est considéré comme obsolète (V 2005) et ne sera plus
supporté dans les versions futures. Il est souhaitable de prendre un autre type
de données. A quoi vous eert-il concrétement dans votre table ?

4) fâites vous beaucoup d'UPDATE dans votre table ?

5) des index sur des VARCHAR en premier élément constituant l'index peuvent être
particulièrement contre performant si des UPDATE sont fait sur les colonnes
VARCHAR notamment lorsqu'il y a élargissement de la longueur de la données

6) si un smallint économise du coût de stockage il s'avère moins rapide dans les
opérations que de int pour les raisons de conversion au mot processeur (32 bits
= int).

7) de meilleurs performances de recherches sont obtenues si l'on utilise une
collation binaire dans le cas ou cette collation est celle d'installation du
serveur. Dans ce cas les comparaison des colonnes de type chaîne de caractères
s'effectue directement bit à bit.

8) les index ne sont utilisé que si la sélectivité calculée a priori est
importante. En général si le filtre doit rapporter moins de 10% l'index est
utilisé. Si le filtre doit rapporter plus de 20% un scan est opéré même en
présence de l'index.

Lavergne guillaume a écrit:

Bonjour,

je travaille sur une table qui a pour but de stocker des données d'un
process temps réel.
l'intégrité des données m'oblige à créer une clée primaire qui sur les
champs A B C D.
La taille de la table est approximativement à 10 Go. (251 540 283 rows)
La selectivite des champs:
le champ A peut avoir jusqu'a 20 000 valeur différentes
Le champ b est pratiquement unique (timestamp)
Il n'y a aucune recherche sur les champs C et D.

CREATE TABLE [dbo].[TABLE1](
[A] [varchar](255) COLLATE French_CI_AS NOT NULL,
[B] [float] NOT NULL,
[C] [float] NOT NULL,
[D] [smallint] NOT NULL,
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Sur cette table, je pratique deux grands types de requete.

requete 1: select B,C,D
from TABLE1
where A = 'exempleA'
and B > value1
and c < value2
(récuperation des toutes les données entre deux dates)

et

requete 2: select *
from TABLE1
where B < value1
(select en vue de supprimer les données les plus anciennes)

1) j'ai d'abord créer un index de type clustered
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[B] ASC,
[A] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 n'est pas efficace car B n'est pas très selectif
La requete 2 est très efficace.

2) j'ai ensuite changé mon index en
CONSTRAINT [PKTABLE1] PRIMARY KEY CLUSTERED
(
[A] ASC,
[B] ASC,
[C] ASC,
[D] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
La requete 1 est très efficace
La requete 2 n'est pas du tout efficace


3) J'ai donc decidé de créer second index
CREATE NONCLUSTERED INDEX IX_TABLE1_1 ON dbo.TABLE1
(
B
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Mais désormais la taille de l'index (jusque la pratiquement nulle car index
clustered) est équivalente à la taille des données. La taile de la base
double !

avant creation de l'index reserved:3925168 KB data:3900424 KB index:24104 KB
apres creation de l'index reserved:7353552 KB data:3901600 KB index:3451072
KB

Existe t'il une solution pour mon probleme : Obtenir de bonne performance
sur les requete 1 et 2 sans doubler ma taile de base ?



Sans le connaissance sémantique de votre problématique toute optimisation
efficace est impossible.

Que me répondriez vous si je vous demandais quel est le meilleur camion pour
transporter 30 tonnes de marchandises ?

Il manque beaucoup d'information pour traiter cette demande : périssable ?
Fongible ? dangereuse ? Sur quelles routes ?

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************







--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Pierre Goiffon
Fred BROUARD wrote:
7) de meilleurs performances de recherches sont obtenues si l'on utilise une
collation binaire dans le cas ou cette collation est celle d'installation du
serveur. Dans ce cas les comparaison des colonnes de type chaîne de caractères
s'effectue directement bit à bit.



Quelle est cette "collation d'installation du serveur" ?? Celle du
serveur SQL Server ? Cad, si la collation de la base nest pas spécifique ?
Avatar
Fred BROUARD
Pierre Goiffon a écrit:
Fred BROUARD wrote:

7) de meilleurs performances de recherches sont obtenues si l'on
utilise une collation binaire dans le cas ou cette collation est celle
d'installation du serveur. Dans ce cas les comparaison des colonnes de
type chaîne de caractères s'effectue directement bit à bit.




Quelle est cette "collation d'installation du serveur" ?? Celle du
serveur SQL Server ? Cad, si la collation de la base nest pas spécifique ?



Oui, collation à l'installation du serveur.

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Avatar
Lavergne guillaume
merci beaucoup.

"bruno reiter [MVP]" wrote:

à mon avis,

comme il y a une requete de selection avec pratiquement toutes les colonnes,
il est bon qu'elle se fasse sur l'index cluster, la selectivité la plus
grande (à travers la requete, pas dans le stockage) étant sur la colonne A
elle doit être la première de l'index.
Donc la PK que tu décris est la meilleure.

les colonnes B et C en float ne paraissent pas une bonne idée, si tu as
besoin de nombreuses valeurs, un bigint serait sans doute plus adapté car ce
n'est pas de l'approximatif, ce qui est nettement mieux pour une clé
d'index, surtout quand il il a des comparaisons dessus.

pour la deuxième requete, un index uniquement sur B résoud le problème, si
la requête ramène moins de 10% des lignes.

Donc ta solution était bien la bonne et dans tous les cas, la taille de la
table doublera:
si le cluster est sur A,B,C,D l'index non cluster doit référencer le cluster
pour l'accès aux données donc à chaque nouvel index non cluster tu auras à
nouveau toutes les valeurs du cluster donc la meme taille.
si tu as un identifiant, tu auras les données une fois dans l'index cluster,
puis une nouvelle fois la meme taille pour l'index A,B,C,D
si tu n'as pas de cluster, tu auras les données une fois dans le heap, puis
une nouvelle fois la meme taille pour l'index A,B,C,D

Donc impossible de diminuer la taille si tu veux 2 index.

De plus tu dois avoir un sérieux problème de tmps pour la réindexation.

br