[SQL-Server 2005] Requêtes qui prennent très longtemps [Résolu]

[Kropernic]

Bonjour,

Je m'interroge.

Avant, j'avais une DB composée de plusieurs table non-indexée, avec des dates en char(8) et sans clef primaire.

J'ai refactorisé toute la DB comme expliqué ici et des requêtes qui prenaient 2 minutes avant en prennent plus de 30 maintenant.

Le fait d'avoir des types de données correct et des indexes n'est-il pas sensé améliorer le temps d'exécution des requêtes ?

Voici un exemple de requête incriminée
ancienne requête

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
27
28
29
30
31
32
33
34
35
WITH 
retours(siege, date, caisse, ticket, qt, ca, rab, eanart, carteclient) AS (
	SELECT siege, date, caisse, ticket, qt, ca, rab, eanart, carteclient
	FROM tb2012_old
	WHERE qt < 0
	GROUP BY siege, date, caisse, ticket, qt, ca, rab, eanart, carteclient
),
 
oublis(siege, date, caisse, ticket, qt, ca, rab, eanart, carteclient) AS (
	SELECT a.siege, a.date, a.caisse, a.ticket, a.qt, a.ca, a.rab, a.eanart, a.carteclient
	FROM retours AS a
	WHERE EXISTS(SELECT * FROM tb2012_old AS b
			WHERE	a.siege = b.siege
				AND a.date = b.date
				AND a.caisse = b.caisse
				AND cast(a.ticket AS int)+1 = cast(b.ticket AS int)
				AND a.ca = -b.ca
				AND a.rab = -b.rab
				AND a.qt = -b.qt
				AND a.eanart = b.eanart
				AND a.carteclient = '' 
				AND b.carteclient <> '')
 
)
 
SELECT	siege, datepart(mm,cast(date AS datetime)) AS 'mois', sum(ca-rab) AS 'valeur', 'R' AS 'type'
FROM	retours
WHERE	qt < 0
GROUP	BY siege, datepart(mm,cast(date AS datetime))
union
SELECT	siege, datepart(mm,cast(date AS datetime)) AS 'mois', sum(ca-rab) AS 'valeur', 'O' AS 'type'
FROM	oublis
WHERE	qt < 0
GROUP	BY siege, datepart(mm,cast(date AS datetime))
ORDER	BY siege, datepart(mm,cast(date AS datetime))

nouvelle requête

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
27
28
29
30
31
32
33
34
35
36
37
38
WITH 
retours(siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient) AS (
	SELECT siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient
	FROM tblTransactions
	WHERE qt < 0
	AND year = 2012
	GROUP BY siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient
),
 
 
oublis(siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient) AS (
	SELECT a.siege, a.date_dt, a.caisse, a.ticket, a.qt, a.ca, a.rab, a.eanart, a.carteclient
	FROM retours AS a
	WHERE EXISTS(SELECT * FROM tbltransactions AS b
			WHERE	a.siege = b.siege
				AND year = 2012
				AND a.date_dt = b.date_dt
				AND a.caisse = b.caisse
				AND cast(a.ticket AS int)+1 = cast(b.ticket AS int)
				AND a.ca = -b.ca
				AND a.rab = -b.rab
				AND a.qt = -b.qt
				AND a.eanart = b.eanart
				AND a.carteclient = '' 
				AND b.carteclient <> '')
 
)
 
SELECT	siege, datepart(mm,date_dt) AS 'mois', sum(ca-rab) AS 'valeur', 'R' AS 'type'
FROM	retours
WHERE	qt < 0
GROUP	BY siege, datepart(mm,date_dt)
union
SELECT	siege, datepart(mm,date_dt) AS 'mois', sum(ca-rab) AS 'valeur', 'O' AS 'type'
FROM	oublis
WHERE	qt < 0
GROUP	BY siege, datepart(mm,date_dt)
ORDER	BY siege, datepart(mm,date_dt)

Le fait d'utiliser des CTE peut-il poser problème ?

Bref, je suis un peu perdu... (je précise que je ne suis pas du tout DBA mais que je dois bien m'y coller)

[Rei Ichido]

Je vous invite à :

- éliminer les GROUP BY pour les remplacer par des DISTINCT quand vous ne faites pas d’agrégation.

- remplacer votre UNION par un UNION ALL, étant donné que de toute façon chaque select concerné renvoie des valeurs uniques et que les deux ne peuvent renvoyer une même valeur à cause de la colonne type.

- changer le type de la colonne ticket pour y mettre directement un int

- supprimer la condition "WHERE qt < 0" dans les deux selects finaux, vu que de toute façon elle est comprise dans les conditions initiales

[Kropernic]

Merci beaucoup pour vos remarques.

Cela semble effectivement avoir améliorer certaines choses.

La requête ancien format prend maintenant entre 1 et 9 secondes (au lieu de 2 minutes avant).

Par contre pour le nouveau format, cela fait 2 minutes que ça tourne et j'attends toujours.
Pour info, elle a pris une trentaine de minute cet après-midi. C'est suite à cela que j'ai décidé d'ouvrir cette discussion.

Je ne comprends toujours pas pourquoi une table qui, à priori, est sensée est mieux conçue est plus lente.

Certes il y a beaucoup plus de lignes dedans mais je pensais que les index étaient sensés contrebalancer cela.

Griftou.

[Rei Ichido]

Accessoirement, est-il nécessaire d'opérer le DISTINCT ?

À savoir, y a-t-il dans la table tblTransactions des doublons de valeur du n-uplet (siege, date, caisse, ticket, qt, ca, rab, eanart, carteclient) ?
Quelle est la clé primaire de cette table ?

Et quels index avez-vous ajoutés ?

[Kropernic]

Bonjour,

La clef primaire de cette table est un int auto incrémenté. (vous pourrez trouver la définition complète de la table en suivant le lien de mon premier message).

Il est en effet possible que chaque ligne soit un doublon complet.
Cela se produit lorsqu'un client achète 2 articles identiques et que la caissière les scanne tous les deux plutôt que d'en scanner un et de mettre la quantité à 2.

Et là, c'est le drame... La requête donne des résultats légèrement erroné puisque en cas de doublons, je ne vais avoir qu'une seule fois l'article...
Je vais retirer le disctinct pour voir.

Sinon pour les index de la nouvelle table, je vous renvoie également au lien de mon premier message ou vous y trouverez leur définition complète.

Griftou.


EDIT : Pour info, après avoir apporté les modifications proposées, la requête sur l'ancienne table prends environ 3s et la requête sur la nouvelle table prend environ 25 minutes.

[Rei Ichido]

Question : y a-t-il une relation entre caisse et siege ? Si oui (à tout hasard si l'info portée par siege est comprise dans celle portée par caisse), vous pouvez gagner un peu de temps en supprimant une information. Idem pour ticket et caisse (la valeur de ticket est-elle attribuée par caisse/ par magasin / globalement, remise à zéro par date / pas du tout, etc.) ?

En supposant que non pour ces deux questions, je poserai un index sur (eanart, date_dt, caisse, ticket).

[Waldar]

Votre ancienne modélisation était conceptuellement erronée mais rapide en pratique. En réalité, vous avez créé un partitionning "à la main" sans le savoir.

Votre modélisation est maintenant conceptuellement correcte, mais en pratique lente. Votre index sur l'année n'est probablement pas utilisé : ça dépend de l'historique que vous avez chargé dans votre table :

Code :

1
2
3
4
  SELECT year, count(*)
    FROM dbo.tblTransactions
GROUP BY year
ORDER BY year ASC

Pour avoir la bonne modélisation ET les bonnes performances, il faut utiliser le partitionning horizontal. Je ne suis pas très au fait de son implémentation dans SQL-Server, mais le SGBD devrait être capable d'éliminer les partitions non utilisées. Enfin ça fonctionne ainsi chez la concurrence.

[SQLpro]

Le partitionnement n'est réellement intéressant qu'à partir du moment ou la table dépasse la capacité d'un disque...

Postez le DDL de vos tables avec les index.

Postez les plans de requête.

Avec cela nous pourrons vous aider.

A +

[Kropernic]

Envoyé par SQLpro

Le partitionnement n'est réellement intéressant qu'à partir du moment ou la table dépasse la capacité d'un disque...

Postez le DDL de vos tables avec les index.

Postez les plans de requête.

Avec cela nous pourrons vous aider.

A +

Ils figurent déjà sur un autre sujet dont le lien figure en message d'ouverture mais pour plus de facilité, les voicis :

La table :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
USE [Tickets]
GO
/****** Object:  Table [dbo].[tblTransactions]    Script Date: 02/02/2012 16:46:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTransactions](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[date_dt] [datetime] NULL,
	[year]  AS (LEFT([date],(4))) PERSISTED,
	[date] [char](8) COLLATE Latin1_General_CI_AS NOT NULL,
	[siege] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
	[ray] [char](4) COLLATE Latin1_General_CI_AS NULL,
	[type] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[eanart] [char](13) COLLATE Latin1_General_CI_AS NOT NULL,
	[pu] [money] NOT NULL,
	[qt] [money] NULL,
	[ca] [money] NULL,
	[rab] [money] NULL,
	[escp] [money] NULL,
	[lm] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[nocre] [char](4) COLLATE Latin1_General_CI_AS NULL,
	[caisse] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
	[ticket] [char](4) COLLATE Latin1_General_CI_AS NOT NULL,
	[cartebanque] [char](16) COLLATE Latin1_General_CI_AS NULL,
	[carteclient] [char](16) COLLATE Latin1_General_CI_AS NULL,
	[gib] [char](3) COLLATE Latin1_General_CI_AS NULL,
	[time] [char](4) COLLATE Latin1_General_CI_AS NULL,
	[typecart] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[typefim] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[typerab] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[typeray] [char](1) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK_tblTransactions] PRIMARY KEY NONCLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [INDEX]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Les indexes :

Code :

1
2
3
4
5
6
7
USE [Tickets]
GO
/****** Object:  Index [IX_Caisse]    Script Date: 02/02/2012 16:47:20 ******/
CREATE NONCLUSTERED INDEX [IX_Caisse] ON [dbo].[tblTransactions] 
(
	[caisse] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [INDEX]

Code :

1
2
3
4
5
6
7
8
USE [Tickets]
GO
/****** Object:  Index [IX_DateDT_Id]    Script Date: 02/02/2012 16:47:37 ******/
CREATE CLUSTERED INDEX [IX_DateDT_Id] ON [dbo].[tblTransactions] 
(
	[date_dt] DESC,
	[id] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Code :

1
2
3
4
5
6
7
USE [Tickets]
GO
/****** Object:  Index [IX_Ray]    Script Date: 02/02/2012 16:47:50 ******/
CREATE NONCLUSTERED INDEX [IX_Ray] ON [dbo].[tblTransactions] 
(
	[ray] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [INDEX]

Code :

1
2
3
4
5
6
7
USE [Tickets]
GO
/****** Object:  Index [IX_Siege]    Script Date: 02/02/2012 16:48:01 ******/
CREATE NONCLUSTERED INDEX [IX_Siege] ON [dbo].[tblTransactions] 
(
	[siege] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [INDEX]

Code :

1
2
3
4
5
6
7
USE [Tickets]
GO
/****** Object:  Index [IX_Ticket]    Script Date: 02/02/2012 16:49:57 ******/
CREATE NONCLUSTERED INDEX [IX_Ticket] ON [dbo].[tblTransactions] 
(
	[ticket] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [INDEX]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
USE [Tickets]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object:  Index [IX_Year]    Script Date: 02/02/2012 16:50:11 ******/
CREATE NONCLUSTERED INDEX [IX_Year] ON [dbo].[tblTransactions] 
(
	[year] DESC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [INDEX]

Code :

1
2
3
4
5
6
7
USE [Tickets]
GO
/****** Object:  Index [PK_tblTransactions]    Script Date: 02/02/2012 16:50:26 ******/
ALTER TABLE [dbo].[tblTransactions] ADD  CONSTRAINT [PK_tblTransactions] PRIMARY KEY NONCLUSTERED 
(
	[id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [INDEX]

Les vues (le nom et l'année dans la clause change change suivant l'année de la vue évidemment):

Code :

1
2
3
4
5
6
7
8
9
10
11
USE [Tickets]
GO
/****** Object:  View [dbo].[tb2012]    Script Date: 02/02/2012 16:52:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[tb2012] AS
SELECT date, siege, ray, type, eanart, pu, qt, ca, rab, escp, lm, nocre, caisse, ticket, cartebanque, carteclient, gib, time, typecart, typefim, typerab, typeray
FROM tbltransactions
WHERE year = 2012

La requête :

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
27
28
29
30
31
32
33
34
35
WITH 
retours(siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient) AS (
	SELECT siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient
	FROM tblTransactions
	WHERE qt < 0
	AND year = 2012
),
 
 
oublis(siege, date_dt, caisse, ticket, qt, ca, rab, eanart, carteclient) AS (
	SELECT a.siege, a.date_dt, a.caisse, a.ticket, a.qt, a.ca, a.rab, a.eanart, a.carteclient
	FROM retours AS a
	WHERE EXISTS(SELECT * FROM tbltransactions AS b
			WHERE	a.siege = b.siege
				AND year = 2012
				AND a.date_dt = b.date_dt
				AND a.caisse = b.caisse
				AND cast(a.ticket AS int)+1 = cast(b.ticket AS int)
				AND a.ca = -b.ca
				AND a.rab = -b.rab
				AND a.qt = -b.qt
				AND a.eanart = b.eanart
				AND a.carteclient = '' 
				AND b.carteclient <> '')
 
)
 
SELECT	siege, datepart(mm,date_dt) AS 'mois', sum(ca-rab) AS 'valeur', 'R' AS 'type'
FROM	retours
GROUP	BY siege, datepart(mm,date_dt)
union ALL
SELECT	siege, datepart(mm,date_dt) AS 'mois', sum(ca-rab) AS 'valeur', 'O' AS 'type'
FROM	oublis
GROUP	BY siege, datepart(mm,date_dt)
ORDER	BY siege, datepart(mm,date_dt)

Pour le plan d'exécution estimé, je suis un peu perdu. Je vois parfaitement comment l'obtenir mais je bloque sur la manière de vous le transmettre de manière exploitable...

J'ignore si cela peut avoir son importance mais voici le nombre de lignes actuel de la table tblTransactions :
Row count = 64397130

Griftou.

[Kropernic]

Voici les plans d'exécution (estimé et réel).

Griftou.

[SQLpro]

Commencez par créer l'index qu'il souhaite :

Code :

1
2
3
CREATE NONCLUSTERED INDEX X001
ON [dbo].[tblTransactions] ([carteclient])
INCLUDE ([date_dt],[year],[siege],[eanart],[qt],[ca],[rab],[caisse],[ticket])

Parce que votre requête ne fait que du scan pour chercher les données.

A +

[Kropernic]

Je fais cela de suite !!

J'imagine que vous avez pu détecter cela via les plans d'exécution.

Cependant, ils restent assez obscures pour moi dans le sens où bon nombre des opérations qui y sont décrites ne me parlent pas.

Auriez-vous écrit un article à ce sujet ?

Griftou.

[Kropernic]

Y a-t-il un moyen d'estimé le temps nécessaire à la création de cet index ?

Cela fait 10 minutes que cela tourne je suis sensé quitter le bureau dans quelques temps (en prenant le portable avec ^^).

Est-ce que cela vaut le coup que je laisse le processus tourner ou bien serai-je de toute manière obliger de l'annuler ? Dans ce cas, je crois que le plus tôt sera le mieux...

Griftou.

EDIT : Ouf, il a finit !!! Je m'en vais tester la requête avec ce nouvel index !

[Kropernic]

Ah bin tout de suite, 2min36s au lieu de plus de 25 minutes, c'est appréciable !!!

Un tout grand merci !!!

Je passe le sujet en résolu. Il va falloir que je me pense sur comment savoir s'il faut créer tel ou tel index...

[SQLpro]

Entre nous, venez un jour à mon cours d'optimisation sous MS SQL Server que je donne à Orsys !
http://www.orsys.fr/pdf-auto/pdfCours/SQS.pdf

A +

[Kropernic]

Je réside en Belgique mais j'en toucherai un mot à chef à l'occasion. Peut-être m'autorisera-t-il le "voyage" en note de frais