Obtenir le top des visites [Résolu]

[darKStein]

Bonjour,

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

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
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 :

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 :

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 :

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]

Envoyé par darKStein

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 :

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 :

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 :

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

[elsuket]

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...

Grossière erreur de la part de vos collègues ...
Pas d'index cluster sur la table ...

Vos bases de données auraient-elle des problèmes de conception, et par là-même de performances ?

@++

[darKStein]

Nos bases de données datent de 20 ans et sont une transcription d'une base qui à l'époque était sous VAX (je n'étais pas encore arrivé, je n'ai "que" 10 ans et suis loin d'être expert SQL... Je prends note de l'information. Dois-je mettre en cluster index l'e-mail (ce qui me semble le plus pertinent) ?

Merci du conseil, maintenant en 20 min. j'ai mes résultats ! Ca parait magique quand on est néophyte...

[SQLpro]

Non, le mieux serait de rajouter une colonne clef primaire ou index cluster unique en autoincrément.
Puis réindexer

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Exemple :
-- 1 suppression des index actuels :
DROP INDEX IDX_STAT_EMAIL_EMAIL ON dbo.STAT_EMAIL
DROP INDEX IDX_STAT_EMAIL_TYPE ON dbo.STAT_EMAIL
 
-- 2 implantation d'une clef pour clusterisation de la table
-- soit clef primaire (le mieux)
ALTER TABLE STAT_EMAIL
ADD _KEY_ BIGINT IDENTITY NOT NULL CONSTRAINT PK_EML PRIMARY KEY;
/* en cas de problème : 
ALTER TABLE STAT_EMAIL DROP CONSTRAINT PK_EML
ALTER TABLE STAT_EMAIL DROP COLUMN _KEY_
*/
-- sinon unicité NOT NULL :
ALTER TABLE STAT_EMAIL
ADD _KEY_ BIGINT IDENTITY NOT NULL CONSTRAINT UK_EML_KEY UNIQUE CLUSTERED;
 
-- 3 ajout des index optimums :

??? impossible car vous ne nous avez pas fournit les DDL des objets dba.V_ESEG3 et dba.V_ESEG4.

Merci de postez ces vues....

Avec cela, et un peu de RAM on devrait tomber à quelques secondes.

A +

[darKStein]

V_ESEG3 et V_ESEG4 sont des vues :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE VIEW dba.V_ESEG3 AS
SELECT	EMAIL, DATEPART(hour, [TIME]) AS SEG_HORAIRE, COUNT(*) AS NB
	FROM	dbo.STAT_EMAIL (NOLOCK)
	WHERE	DATEDIFF(month, [TIME], GETDATE()) <= 6
		AND [TYPE] = 'Open'
	GROUP	BY EMAIL, DATEPART(hour, [TIME]) 
 
GO
 
CREATE   VIEW dba.V_ESEG4 AS
SELECT	EMAIL, DATEPART(dw, [TIME]) AS SEG_JOUR, COUNT(*) AS NB
FROM	dbo.STAT_EMAIL (NOLOCK)
WHERE	DATEDIFF(month, [TIME], GETDATE()) <= 6
	AND [TYPE] = 'Open'
GROUP	BY EMAIL, DATEPART(dw, [TIME])

[elsuket]

Code :

WHERE	DATEDIFF(month, [TIME], GETDATE()) <= 6

Ça par exemple, c'est moche
En effet SQL Server maintient des statistiques de distribution des valeurs dans la colonne TIME.
Mais il ne peut pas maintenir de statistiques sur le mois, le jour, le seconde, ... de chacune des date qui sont stockées dans cette colonne !
Et encore moins sur une différence en mois avec la date courante

Votre prédicat conduit donc SQL Server à scanner toute la table, pour calculer la différence, et retourner seulement les lignes qui vérifient le prédicat.

En revanche, si vous écrivez :

Code :

WHERE [TIME] >= DATEADD(month, -6, GETDATE())

Vous obtiendrez le même résultat, mais cette fois, comme le membre droit de l'inégalité est une constante, un index sur la colonne TIME va être utilisé

De la même façon vous calculez des groupes sur la valeur suivante :

Code :

DATEPART(dw, [TIME])

Ici c'est plus compliqué à contourner parce que la fonction DATEPART() n'est pas déterministe.
On aurait pu en faire une colonne calculée (ce qui est possible même si la fonction n'est pas déterministe) mais pas l'indexer.

Il vous faudrait donc :

- au moment de l'insertion, calculer le jour de la semaine, puis réaliser l'insertion, c'est-à-dire modifier l'applicatif
- ou ajouter un trigger sur la table pour peupler une colonne supplémentaire et l'indexer.

Je vous invite à lire ce petit billet

Mais je ne crois pas que ce soit nécessaire dans votre cas.
Un index sur les colonnes TIME + TYPE devrait être suffisamment sélectif.

Enfin, en ce qui concerne NOLOCK, il vous amène à lire la table sur laquelle vous avez mis cet indicateur sous le niveau d'isolation de transaction READ UNCOMMITTED.

Sous ce niveau de transaction, chaque ligne vérifiant le prédicat de la requête est verrouillée le temps qu'elle soit lue ... mais pas tout le temps de la requête !
Donc si vous êtes en train d'exécuter une requête, et que dans le même temps je fais un UPDATE sur toute le table, vous allez en fait lire des données qui ne sont pas encore validées.
Si mon UPDATE plante pour une quelconque raison, vous aurez donc lu des données qui sont fausses !

Vos collègues en sont friands parce que les verrous sont donc maintenus pour une durée très courte, ce qui augmente la concurrence d'accès à la table ... mais diminue la qualité des données !

Je vous invite à lire la documentation à ce sujet.
Vous pouvez mettre les liens vers la documentation dans vos favoris ou télécharger celle-ci à partir des liens de ma signature.

Envoyé par SQLPro

Puis réindexer

Il me semble que lorsqu'on rajoute un index cluster sur une table qui a déjà des index non-cluster, les index non-cluster sont reconstruits (puisqu'alors la référence physique de la ligne change).
Ou tu voulais dire ALTER INDEX ALL ON dbo.maTable REBUILD ?

@++

[darKStein]

Concernant mon problème actuelle, la table est/sera mise à jour une fois par jour au maximum, et mon calcul se fera hebdomadairement, donc je pense que le NOLOCK est sans "danger". Merci pour ces précieux conseils cependant ! Je vais les appliquer sans tarder !

[SQLpro]

Essayez avec ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
-- création vue 
CREATE VIEW dbo.V_ESEG3_X
WITH SCHEMABINDING
AS
SELECT	EMAIL, 
        YEAR([TIME]) AS AN, MONTH([TIME]) AS MOIS, DAY([TIME]) AS JOUR,
        DATEPART(hour, [TIME]) AS SEG_HORAIRE, 
        COUNT_BIG(*) AS NB
FROM	dbo.STAT_EMAIL (NOLOCK)
WHERE	[TYPE] = 'Open'
GROUP	BY EMAIL, YEAR([TIME]), MONTH([TIME]), DAY([TIME]), DATEPART(hour, [TIME]);
GO

Code :

1
2
3
4
-- indexation vue 
CREATE UNIQUE CLUSTERED INDEX X_V_ESEG3_CK
  ON dbo.V_ESEG3_X (EMAIL, AN, MOIS, JOUR, SEG_HORAIRE);
GO

Code :

1
2
3
4
5
6
7
8
9
10
11
12
-- création vue 
CREATE VIEW dbo.V_ESEG4_X
WITH SCHEMABINDING
AS
SELECT	EMAIL, 
        YEAR([TIME]) AS AN, MONTH([TIME]) AS MOIS, DATEPART(isowk,[TIME]) AS SEMAINE,
        DATEPART(dw, [TIME]) AS SEG_JOUR, 
        COUNT_BIG(*) AS NB
FROM	dbo.STAT_EMAIL (NOLOCK)
WHERE	[TYPE] = 'Open'
GROUP	BY EMAIL, YEAR([TIME]), MONTH([TIME]),DATEPART(isowk,[TIME]), DATEPART(dw, [TIME]);
GO

Code :

1
2
3
4
-- indexation vue 
CREATE UNIQUE CLUSTERED INDEX X_V_ESEG4_CK
  ON dbo.V_ESEG3_X (EMAIL, AN, MOIS, JOUR, SEG_HORAIRE);
GO

Code :

1
2
3
4
5
6
-- modification vue actuelle
ALTER VIEW dba.V_ESEG3 AS
AS
SELECT EMAIL, SEG_HORAIRE, NB
FROM   dbo.V_ESEG3_X
WHERE  AN * 12 + (MOIS - 1) <= YEAR(GETDATE()) * 12 + ((MONTH(GETDATE()) - 1) - 6);

Code :

1
2
3
4
5
6
-- modification vue actuelle
ALTER VIEW dba.V_ESEG4 AS
AS
SELECT EMAIL, SEG_HORAIRE, NB
FROM   dbo.V_ESEG4_X
WHERE  AN * 12 + (MOIS - 1) <= YEAR(GETDATE()) * 12 + ((MONTH(GETDATE()) - 1) - 6)

Avec ça je pense que vous devriez tomber sous la barre des 10 secondes.....

Explication : les vues indexées, ça sert à ça !!!
A lire sur le sujet : http://sqlpro.developpez.com/optimisation/indexation/

A +

[darKStein]

Effectivement je n'aurais pas pu trouver ça tout seul...
Merci de ces conseils !

[darKStein]

Euh, question, pourquoi :
AN * 12 + (MOIS - 1)
et pas
AN * 100 + MOIS
?

[SQLpro]

As you like, cela devrait revenir au même, mais être plus lisible !

A +