[SQL-Server 2005] Vues et connexion timeout

[Kropernic]

Bonjour,

Je plante le décor :

Soit une DB nommée "Tickets". Elle contient en fait les transactions caisses des 15 magasins de la chaine pour laquelle je travaille. Ces transactions étant séparées dans une table par année.

Voici la structure d'une de ces tables (ici celle de 2012 mais elles sont toutes identiques)

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
 
USE [Tickets]
GO
/****** Object:  Table [dbo].[tb2012_old]    Script Date: 02/02/2012 16:42:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb2012_old](
	[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
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Comme vous pouvez le constater et si vous n'avez pas eu de crise cardiaque après avoir vu cette structure, c'est assez moche. Cela date d'une époque où je n'avais eu aucune formation et il avait bien fallu que je me débrouille pour produire quelque chose de fonctionnel).

Suite à une formation sur sql server et après en avoir disctuer avec le formateur, j'ai décidé de fusionner toutes ces tables en une seule dont voici la structure :

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

J'ai également ajouté une série d'index sur cette nouvelle table. Les voici :

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]

Voilà, avec ça, on a la définition complète de la table (à un trigger près).

Suite à cette fusion des tables par années, j'ai renommé lesdites tables en ajoutant le suffixe '_old' histoire de les garder sous le coude au cas où.

Mais vu qu'il y avait des applications se basant sur ces tables, j'ai créé des vues reprenant les mêmes colonnes. Une vue par année donc et portant chacune le même nom que les anciennes tables.
Voici la définition de la vue pour 2012 :

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

Bon, maintenant que le décor est planté (c'était long!), la question est :
Pourquoi certaines applications reçoivent une erreur de type 'connection timeout' lorsqu'elles essaient d'accéder aux vues ?

A côté de cela, j'ai un service qui récupère les transactions tous les jours par ftp sur les serveurs de chaque magasin. Ces transactions sont contenues dans des fichiers plats et le service injecte les données dans la table dbo.tblTransactions mais en passant par la vue adéquate (vu que je n'ai nullement modifié le code qui existait déjà).
Pourquoi ce service fonctionne-t-il correctement et pas les applications clientes ?

Y a-t-il des choses supplémentaires à paramétrer pour pouvoir faire des requêtes de sélection les vues sans problèmes ?

Bien à vous,

Griftou.

[Rei Ichido]

Si je comprend bien, outre les typages, vous aviez avant plusieurs tables (une par année) et maintenant il n'y en a plus qu'une seule ?
=> vous devriez partitionner la table selon year, ce qui améliorera très significativement les accès par année.

[Kropernic]

Envoyé par Rei Ichido

Si je comprend bien, outre les typages, vous aviez avant plusieurs tables (une par année) et maintenant il n'y en a plus qu'une seule ?
=> vous devriez partitionner la table selon year, ce qui améliorera très significativement les accès par année.

J'ai créé une vue par année. Cela n'est pas bon ?
Et euh... Pour partitionner la table ??? Enfin, je vais aller demander à mon pote google !

edit : ça a l'air trop compliqué pour un jeudi soir...

[Rei Ichido]

Avec une vue par année, il faut encore faire un scan de la table (ou passer par l'index, mais sur peu de valeurs distinctes de year ce n'est pas fortement efficace).

Partitionner la table, c'est à peu près équivalent à avoir des tables séparées mais interrogeables en même temps, et soumises à des contraintes communes. Cela permet notamment de gagner beaucoup de temps quand on veut pouvoir requêter en filtrant sur une année.

[Kropernic]

Et si, à la place de créer les vues en fonction de la colonne year (index non cluster), je créais les vues en fonction de la colonne date_dt qui elle, fait partie de mon index cluster.

Ne serait-ce pas mieux ?

J'avoue que cette histoire de partitionnement me laisse perplexe. J'ai peur que la gestion de ce genre de chose ne soit pas de mon niveau.

[Kropernic]

Envoyé par griftou

Et si, à la place de créer les vues en fonction de la colonne year (index non cluster), je créais les vues en fonction de la colonne date_dt qui elle, fait partie de mon index cluster.

Ne serait-ce pas mieux ?

Je m'auto-cite pour apporter la réponse à ma question :

Après avoir supprimer la vue tb2007 et l'avoir recréer en mettant le filtre sur la colonne de l'index cluster, j'ai comparé les plans d'exécution d'une requête SELECT * sur la vue tb2007 et sur la vue tb2008 et ils sont identiques. Ils font tout deux un scan de l'index cluster.

Le problème est donc ailleurs. Je ne m'explique toujours pas ce problème de timeout...