CONCEPTION + REQUETE [MSSQL 2005]

[JmL40]

Bonjour,

Je suis dans une impasse concernant ma gestion de données sur SQL SERVER 2005. Je tente d'extraire à l'aide d'une requête des jeux de données sur une table qui contient environ 3 millions de lignes.

Le but de cette opération est de générer des résultats au niveau du SGBD pour éviter toute surcharge logiciel tant les traitements sont long. Pour cela, je part d'une table LAB_ANALYSE contenant toutes les données en lignes avec comme clé primaire un CODE_ANALYSE et un CODE_FICHE plus des colonnes MARQUEURS, VALEURS et LOT.

La requête doit permettre de faire des comparaisons lignes à lignes sur cette table, c'est-à-dire, que je veux comparer chaque CODE_FICHE - LOT entre eux une seule fois et en ne prenant en compte que les MARQUEURS communs.
De plus, je dois récupérer au moins une infos. par jointure sur une autre table.

Voici la structure de ma table LAB_ANALYSE :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE [dbo].[LAB_ANALYSE_3](
	[CODE_ANALYSE] [int] IDENTITY(1,1) NOT NULL,
	[CODE_FICHE] [int] NOT NULL,
	[MARQUEUR] [char](50) COLLATE French_CI_AS NULL,
	[LOT] [char](50) COLLATE French_CI_AS NULL,
	[VALEUR] [char](20) COLLATE French_CI_AS NULL,
 CONSTRAINT [PK_LAB_ANALYSE_3] PRIMARY KEY CLUSTERED 
(
	[CODE_ANALYSE] ASC,
	[CODE_FICHE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Exemple de données dans la table LAB_ANALYSE :

CODE_ANALYSE -- CODE_FICHE -- MARQUEUR -- LOT -- VALEUR
6734 -- 436 -- 1811 -- 0/0 -- 206.214*
6735 -- 436 -- 1839 -- 0/0 -- 205*
6736 -- 436 -- 1866 -- 0/0 -- 124.132*
6737 -- 436 -- 1867 -- 0/0 -- 209*
6738 -- 436 -- 1885 -- 0/0 -- 235.261*
3062 -- 251 -- 1811 -- 20CAB/2460* -- 187*
3063 -- 251 -- 1811 -- 20CAC/2461* -- 187*
3064 -- 251 -- 1811 -- 20CAC/2462* -- 187*
3065 -- 251 -- 1811 -- 22BA/2463 -- 187*
3066 -- 251 -- 1811 -- 22BA/2464 -- 187*
3067 -- 251 -- 1839 -- 20CAA/2458* -- 230*
3068 -- 251 -- 1839 -- 22BA/2463 -- 195*
6378 -- 431 -- 1792 -- 0/0 -- 134.140*
6379 -- 431 -- 1805 -- 0/0 -- 264*
6380 -- 431 -- 1811 -- 0/0 -- 206*
6381 -- 431 -- 1839 -- 0/0 -- 195.230*

Structure de la table LAB_FICHE :

Code :

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
 
CREATE TABLE [dbo].[LAB_FICHE](
	[CODE_FICHE] [int] IDENTITY(1,1) NOT NULL,
	[CODE_EXP] [varchar](50) COLLATE French_CI_AS NOT NULL,
	[LOT] [varchar](100) COLLATE French_CI_AS NULL,
	[TYPE_FICHE] [varchar](10) COLLATE French_CI_AS NULL,
	[DATE_DEMANDE] [datetime] NULL,
	[DATE_ECHEANCE] [datetime] NULL,
	[DATE_PREVISION] [datetime] NULL,
	[DATE_ANALYSE] [datetime] NULL,
	[DEMANDEUR] [varchar](10) COLLATE French_CI_AS NULL,
	[PROGRAMME] [varchar](10) COLLATE French_CI_AS NULL,
	[PERSONNE] [varchar](10) COLLATE French_CI_AS NULL,
	[RESPONSABLE] [varchar](10) COLLATE French_CI_AS NULL,
	[NB_MARQUEURS] [int] NULL,
	[NB_PLANTES] [int] NULL,
	[NB_MULTIPLEX] [int] NULL,
	[PRIORITE] [int] NULL,
	[SERIE] [int] NULL,
	[COMMENTAIRE] [varchar](255) COLLATE French_CI_AS NULL,
	[STADE] [varchar](50) COLLATE French_CI_AS NULL,
 CONSTRAINT [PK_LABORATOIRE_FICHE] PRIMARY KEY CLUSTERED 
(
	[CODE_FICHE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Exemple de données dans la table LAB_FICHE :

Code :

1
2
3
4
 
CODE_FICHE -- CODE_EXP
1 -- 17
2 -- 677

J'ai construit la requete suivante en utilisant les fonctions ROWNUMBER avec création de partition sur les CODE_FICHE, MARQUEUR, LOT :

Code :

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
 
WITH T AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY CODE_FICHE,LOT ORDER BY CODE_FICHE,MARQUEUR,LOT) AS N FROM LAB_ANALYSE_3)
SELECT 
	F1.CODE_EXP,
	T1.CODE_FICHE,
	T1.MARQUEUR, 
	T1.LOT,
	T1.VALEUR,
	CASE WHEN T1.VALEUR LIKE '%.%' THEN 1 ELSE 0 END AS HETEROZYGOTE_1, 
	F2.CODE_EXP AS CODE_EXP_2,
	T2.CODE_FICHE AS CODE_FICHE_2,
	T2.MARQUEUR MARQUEUR_2, 
	T2.LOT AS LOT_2,
	T2.VALEUR AS VALEUR_2,
	CASE WHEN T2.VALEUR LIKE '%.%' THEN 1 ELSE 0 END AS HETEROZYGOTE_2,
	CASE WHEN T2.VALEUR = T1.VALEUR THEN 1 ELSE  0 END AS COMMUN_CM,
	CASE WHEN T2.VALEUR = T1.VALEUR THEN 1 ELSE  0 END AS DIFFERENT_CM,
FROM 
(
	(T AS T2 LEFT JOIN LAB_FICHE AS F2 ON T2.CODE_FICHE = F2.CODE_FICHE LEFT JOIN STOCK AS S2 ON F2.CODE_EXP = S2.CODE_EXP)
	LEFT OUTER JOIN 
	(T AS T1 LEFT JOIN LAB_FICHE AS F1 ON T1.CODE_FICHE = F1.CODE_FICHE LEFT JOIN STOCK AS S1 ON F1.CODE_EXP = S1.CODE_EXP) 
	ON T2.N >= T1.N AND T2.MARQUEUR = T1.MARQUEUR AND T2.CODE_FICHE < T1.CODE_FICHE
)						
WHERE 
	(S1.TYPE_STOCK = 'L' AND S2.TYPE_STOCK = 'L')

Mon soucis vient du faite que j'ai trois millions de lignes que les temps d'exécution sont tellement long que j'arrive à un "MEMORY EXCEPTION".

Je souhaiterai votre aide car je n'utilise peut-être pas les bonnes méthodes.

Cordialement

PS : J'ai oublié de spécifier qu'il existe un index sur la table LAB_ANALYSE contenant CODE_FICHE - MARQUEUR - LOT.

[SQLpro]

Erreur : pourquoi une PK avec IDENTITY + une autre colonne ? Cela ne sert à rien qu'a pénaliser les performances.

Postez la requête que vous devez faire afin que l'on puisse optimiser votre table.

A +

[JmL40]

Bonjour,

Pardon, erreur de ma part, double-post : mon premier POST étant en cours de rédaction, j'ai mis à jour celui-ci avec la requete et les informations complémentaires.

Cordialement

[SQLpro]

Plusieurs horreurs :
Pourquoi un SELECT * ? Toutes les colonne sont-elles utilisée dans la sous requête avec T1 et T2 ???
Le LIKE '%.%' oblige à parcourir toutes les lignes et tous les caractères un a un des colonnes VALEUR, d'où l'explosion combinatoire....
Avez vous respecté la première forme norme dans votre MCD, qui veut que toutes les données soient atomique ? Visiblement non !!!

Donc, commencez par modéliser correctement en rendant vos données atomiques

Pourquoi des informations redondantes ?

Code :

1
2
	CASE WHEN T2.VALEUR = T1.VALEUR THEN 1 ELSE  0 END AS COMMUN_CM,
	CASE WHEN T2.VALEUR = T1.VALEUR THEN 1 ELSE  0 END AS DIFFERENT_CM,

Pour générer du process afin de perdre du temps pour obtenir de meilleurs performances ?

Pourquoi tant de parenthèses dans le FROM ? Vous aviez un stock de parenthèses à placer ????

Code :

1
2
3
4
5
6
7
8
9
10
11
FROM T AS T2 
     LEFT OUTER JOIN LAB_FICHE AS F2 
          ON T2.CODE_FICHE = F2.CODE_FICHE 
          LEFT OUTER JOIN STOCK AS S2 
               ON F2.CODE_EXP = S2.CODE_EXP
	      LEFT OUTER JOIN T AS T1 
	           ON T2.N >= T1.N AND T2.MARQUEUR = T1.MARQUEUR AND T2.CODE_FICHE < T1.CODE_FICHE
	           LEFT JOIN LAB_FICHE AS F1 
	                ON T1.CODE_FICHE = F1.CODE_FICHE 
	                LEFT JOIN STOCK AS S1 
                         ON F1.CODE_EXP = S1.CODE_EXP

Là voici récrite proprement !

Pourquoi une virgule à la dernière ligne de la clause SELECT ?

Code :

	CASE WHEN T2.VALEUR = T1.VALEUR THEN 1 ELSE  0 END AS DIFFERENT_CM,

N'avez vous pas remarqué l'erreur de syntaxe ?

Votre numérotation est bizarre :

Code :

ROW_NUMBER() OVER(PARTITION BY CODE_FICHE, LOT ORDER BY CODE_FICHE, MARQUEUR, LOT)

Vous faites une rupture sur CODE_FICHE et LOT et numérotez sur CODE_FICHE, MARQUEUR, LOT cela me parait tout à fait idiot !!!
En effet soit vous mettez CODE_FICHE et LOT dans le PARTITION, soit dans le ORDER BY, mais pas dans les 2 !!!

ENFIN... Quels sont les index ?
En particulier, y a t-il un index sur :
1) (CODE_FICHE, LOT) dans LAB_ANALYSE_3 (éventuellement INCLUDE (CODE_FICHE, MARQUEUR, LOT))
2) (TYPE_STOCK, CODE_EXP) dans STOCK ?
3) (CODE_FICHE, CODE_EXP) dans LAB_FICHE
...


A +