Discussion :

[darKStein]

Bonjour,

Je possède une table qui me donne les statistiques d'ouvertures d'e-mails envoyés à mes clients

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
CREATE TABLE [STAT_EMAIL]
	([lv_email] [varchar] (255),
	[ReportId] [int],
	[type] [varchar] (10) ,
	[time] [smalldatetime])
GO
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7090632, 'Send', '2010-10-27 11:53:14'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7090632, 'Open', '2010-10-27 12:26:27'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7091760, 'Send', '2010-11-09 12:24:15'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7091760, 'Open', '2010-11-09 17:29:07'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7091760, 'Open', '2010-11-09 17:29:12'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7092523, 'Send', '2010-11-17 14:12:24'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7093070, 'Send', '2010-11-23 15:08:34'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7093452, 'Send', '2010-11-26 17:23:53'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7093970, 'Send', '2010-12-02 16:40:49'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094065, 'Send', '2010-12-03 14:31:03'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094065, 'Open', '2010-12-03 18:57:43'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094264, 'Send', '2010-12-06 16:48:31'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094264, 'Open', '2010-12-06 17:35:59'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094800, 'Send', '2010-12-10 17:12:56'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094800, 'Open', '2010-12-11 19:14:51'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094800, 'Open', '2010-12-13 08:14:14'
INSERT STAT_EMAIL SELECT 'toto@hotmail.com', 7094800, 'Click', '2010-12-13 08:14:28'
...

Je souhaite connaître pour chaque e-mail de ma table donné le jour où il est le plus susceptible d'ouvrir son e-mail : [type] = 'Open'. J'ai bien compris qu'avec mon DATEPART(dw, [time]) j'obtenais le jour en question, j'ai un premier élément de réflexion avec :

Code :

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

1
2
3
4
SELECT	EMAIL, DATEPART(dw, [TIME]) AS SEG_JOUR, COUNT(*) AS NB
FROM	STAT_EMAIL (NOLOCK)
WHERE	[TYPE] = 'Open'
GROUP	BY EMAIL, DATEPART(dw, [TIME])

Mais ensuite, comment extraire pour chaque e-mail le jour où NB est le max ?

PS : Je suis actuellement sous SQL Server 2000

[darKStein]

Une solution serait la suivante :
Création d'une vue V_SEG_JOUR

Code :

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

1
2
3
4
5
6
 
CREATE VIEW V_SEG_JOUR AS
SELECT	EMAIL, DATEPART(dw, [TIME]) AS SEG_JOUR, COUNT(*) AS NB
FROM	STAT_EMAIL (NOLOCK)
WHERE	[TYPE] = 'Open'
GROUP	BY EMAIL, DATEPART(dw, [TIME])

Puis requêtage du type

Code :

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

1
2
3
4
5
6
7
 
SELECT	EMAIL, SEG_JOUR
FROM	V_SEG_JOUR v1 (NOLOCK)
WHERE	NB = (SELECT	MAX(NB)
		FROM	V_SEG_JOUR v2 (NOLOCK)
		WHERE	v1.EMAIL = v2.EMAIL
		GROUP	BY EMAIL, SEG_JOUR)

Sauf que ça ne peut pas marcher si j'ai à concurrence d'un même NB plusieurs jours correspondant.
Après, je travaille sur un volume d'environ 960 000 lignes et je dois faire cela aussi pour la tranche horaire (plus d'autres indicateurs du même acabit) et je pense optimisation, optimisation (à mon humble maîtrise du SQL).

[aieeeuuuuu]

PS : Je suis actuellement sous SQL Server 2000

C'est bien dommage, car à partir de la version 2005, votre problème n'en serait plus un


Quel comportement attendez vous en cas d'ex aequo ? (edit : je vois que vous vous etes posé la même question, mais visiblement vous n'avez pas la réponse )

[darKStein]

A mon sens on pourra prendre dans l'ordre croissant du numéro de jour, le jour "le plus petit".
Une solution :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT	EMAIL, MIN(SEG_JOUR)
FROM	dba.V_ESEG3 v1 (NOLOCK)
WHERE	NB IN (SELECT	MAX(NB)
		FROM	dba.V_ESEG3 v2 (NOLOCK)
		WHERE	v1.EMAIL = v2.EMAIL
		GROUP	BY EMAIL, SEG_JOUR)
GROUP BY EMAIL

[darKStein]

Gros souci de performances pour le coup avec la requête suivante :

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
 
SELECT	eseg3.EMAIL, ESEG3, ESEG4
FROM	(SELECT	EMAIL, MIN(SEG_HORAIRE) AS ESEG3
	FROM	dba.V_ESEG3 v1 (NOLOCK)
	WHERE	NB IN (SELECT	MAX(NB)
			FROM	dba.V_ESEG3 v2 (NOLOCK)
			WHERE	v1.EMAIL = v2.EMAIL
			GROUP	BY EMAIL, SEG_HORAIRE)
	GROUP BY EMAIL) eseg3
INNER JOIN
	(SELECT	EMAIL, MIN(SEG_JOUR) AS ESEG4
	FROM	dba.V_ESEG4 v1 (NOLOCK)
	WHERE	NB IN (SELECT	MAX(NB)
			FROM	dba.V_ESEG4 v2 (NOLOCK)
			WHERE	v1.EMAIL = v2.EMAIL
			GROUP	BY EMAIL, SEG_JOUR)
	GROUP BY EMAIL) eseg4
	ON eseg3.EMAIL = eseg4.EMAIL

En 50 min. toujours aucun résultat (chaque vue a une volumétrie d'environ 900 000 enr. ; V_ESEG3 et V_ESEG4 ont des SELECT identiques (même table, mêmes champs) sauf pour les champs SEG_JOUR (=DATEPART(dw, [TIME]) et SEG_HORAIRE (=DATEPART(hour, [TIME]))

L'optimiseur m'indique un coup important sur des SORT (liés, je suppose, aux GROUP BY) et sur Index Spool/Eager Spool (quid ?)

[SQLpro]

Déjà mettre des NOLOCK partout est une aberration. Ceci est anormal, alors que la norme consisterais tout simplement à lancer un SET ISOLATION TRANSACTION LEVEL READ UNCOMMITTED...
Ensuite tout dépend des index que vous avez dans la table. Postez le DDL de vos tables avec le DDL de TOUS les index des tables.

A +

[darKStein]

Bonjour,

Pour les NOLOCK je ne savais pas. Il y a 10 lorsque je suis arrivé dans la boîte on m'a dit qu'il fallait impérativement mettre des NOLOCK partout, du coup bête et discipliné comme je suis j'ai appliqué la rêgle de l'entreprise à la lettre...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE [STAT_EMAIL] (
	[EMAIL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TYPE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TIME] [smalldatetime] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
	) ON [PRIMARY]
GO
 
 CREATE  INDEX [IDX_STAT_EMAIL_EMAIL] ON [dbo].[STAT_EMAIL]([EMAIL]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO
 
 CREATE  INDEX [IDX_STAT_EMAIL_TYPE] ON [dbo].[STAT_EMAIL]([TYPE]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO