Discussion :

[Glouferu]

Bonjour,

J’ai une table de STOCK qui possède légèrement moins d’un milliard d’enregistrements (928 031 950 rows pour être exacte).
Je dois identifier 4 ou 5 rows de tests dans toutes ces données et les supprimer.
Néanmoins, mes requêtes sont plus qu’ultra longue… à tel point que j’en ai jamais vu le bout ! (stoppée après 32min ...)


Pour identifier les rows a supprimer, je n'ai qu'une information :
les valeurs de CD_POLICY (integer) à trouver !!

-> Si quelqu'un à une idée de traitement, ou de requêtes magiques, je suis prêt a payer en ticket resto :p


On m'a refilé la patate chaude et je suis vraiment embêter.
Je vous remercie par avance pour vos réponses (constructives ou pas ).



Voici l'aperçu de ma table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
USE [DTMPFO]
GO
/****** Object:  Table [dbo].[DTMPFO_STK_PORTOFLIO_FULL]    Script Date: 03/04/2009 12:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTMPFO_STK_PORTOFLIO_FULL](
	[DT_DAY_STK] [int] NOT NULL,
	[CD_PARTNER] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_MAILING] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_PRODUCT] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_PRODUCT_POLICE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_INSEE] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_POLICY_STATE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_POLICY_STATUS] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_PAYMENT_TYPE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_POLICY] [int] NOT NULL,
	[CD_SCHEDULE] [int] NOT NULL,
	[NB_POLICY_OPTION] [int] NULL,
	[CD_ADDRESSES] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[CD_CUSTOMERS] [int] NOT NULL,
	[CD_CUSTOMERS_TYPE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[VERSION] [int] NOT NULL,
	[DT_DEB_VERSION] [datetime] NULL,
	[DT_DEB_PERIOD_COUV] [datetime] NULL,
	[DT_FIN_PERIOD_COUV] [datetime] NULL,
	[DT_DEB_PERIOD_VALID] [datetime] NULL,
	[DT_FIN_PERIOD_VALID] [datetime] NULL,
	[CD_AGENT] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CD_MAILING_OPTION] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CHARGEGRP] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[NB_POLICIES] [int] NULL,
	[VERSION_GLOBALE] [int] NOT NULL,
	[DT_ECHEANCE] [datetime] NOT NULL,
 CONSTRAINT [PK__DTMPFO_STK_PORTO__58F12BAE] PRIMARY KEY CLUSTERED 
(
	[DT_DAY_STK] ASC,
	[CD_PARTNER] ASC,
	[CD_MAILING] ASC,
	[CD_PRODUCT] ASC,
	[CD_PRODUCT_POLICE] ASC,
	[CD_INSEE] ASC,
	[CD_POLICY_STATE] ASC,
	[CD_POLICY_STATUS] ASC,
	[CD_PAYMENT_TYPE] ASC,
	[CD_POLICY] ASC,
	[CD_SCHEDULE] ASC,
	[CD_ADDRESSES] ASC,
	[CD_CUSTOMERS] ASC,
	[VERSION] ASC,
	[VERSION_GLOBALE] ASC,
	[DT_ECHEANCE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

[elsuket]

Bonjour,

C'est normal que votre requête soit longue : la colonne CD_POLICY fait partie de la clé primaire de votre table, donc de l'index cluster qui contient toutes les données des colonnes clé de votre index (donc de votre clé primaire)
Donc SQL Server est obligé de parcourir toute la table pour rechercher vos valeurs.

Votre clé primaire est trop large (elle contient trop de colonnes), et votre table a trop de colonnes.
Comme vous avez beaucoup de colonne dans les deux cas, les pages de niveau intermédiaire de l'arbre de tri de votre index contiennent "peu d'information", et c'est pareil pour les pages de vos tables.

Donc vous devez lire plus de pages, donc vos requêtes sont d'autant plus lentes.

Il aurait fallu :

- normaliser votre table,
- utiliser comme clé primaire une colonne de type entier avec la propriété de compteur auto-incrémenté
- créer un index non-cluster sur la colonne CD_POLICY, par exemple.

J'espère que ce n'est pas vous qui avez créé la table

La solution qui vous reste, qui est lente et pas propre, c'est de créer un index non-cluster sur la colonne CD_POLICY, et peut-être d'autres ...

Pour bien spécifier votre index, donnez-nous votre requête.

@++

[Glouferu]

Non, je ne suis pas à l'origine de cette belle table de 155giga.

Les index ne sont pas idéalement placés pour ma requête.

Je pense que pour simplifier mes démarches(et ne pas modifier la structure de DTMPFO_STK), je vais mettre en place un flux bodi (ETL), qui va me permettre de naviguer par tranche de date (voir DT_DAY_STK).

En effet, j'ai pu constater que mes requêtes de sélection sont relativement plus rapide en utilisant cette clé primaire.
-> Cette clé doit correspondre à la première valeur ou nœud de mon B-Tree (enfin c'est ce que j'en ai compris).


Merci en tout cas pour votre réponse

[SQLpro]

Reste que la structure de votre table pour une table devant contenir près d'un milliard de ligne est plus qu'épouvantable. Celui qui l'a modélisé doit être un crétin fini qui n'a jamais dépassé le stade de Cobol et des fichiers plats dans ses raisonnement.

Lorsque je fais des audits MS SQL Server :
toutes les tables dont les clefs primaires dépassent les 3 colonnes sont marquées comme potentiellement contre performantes. La votre en a 16 !
Toutes celles dont le clef d'index (les colonnes concaténées) font plus de 16 octets sont marquées comme potentiellement contre performantes. La votre en a 198 !!!
Tous les index ayant des colonnes de type VARCHAR sont marquées comme potentiellement contre performantes. La votre en à 8 !!!!!

Enfin l'utilisation d'une clef sémantique genère des problèmes de fragmentation qu'une clef purement informatique (N° autoincrémenté) évite totalement !

De plus avoir fait des colonnes en VARCHAR 2 ou VARCHAR 1 est d'une connerie impalpable sachant que chaque VARCHAR allonge la donnée de la mesure réelle avec au moins 2 octets. C'est ainsi qu'un VARCHAR(1) prend entre 2 et 3 octets, alors qu'un CHAR 1 ne prend qu'un octet.
Enfin, le varchar génère systématiquement de la fragmentation d'index...

D'où vos temps de réponse catastrophiques. Ils sont liés au modèle et tant que vous n'aurez pas restructurer cette table, vos temps de réponse seront toujours aussi affreux !

Lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/optimisation/3/

A +