Discussion :

[PickEpique]

Bonjour

sur la table 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
 
CREATE TABLE [dbo].[T_ARTICLES_ART](
	[ART_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[ART_STYPE] [smallint] NULL,
	[ART_FAMILLE] [dbo].[D_FAMILLE_ARTICLE] NULL,
	[ART_REF_CONSTR] [dbo].[D_REF_ARTICLE] NULL,
	[ART_LIBELLE] [dbo].[D_LIBELLE_ARTICLE] NULL,
	[ART_PU] [dbo].[D_MT_MONETAIRE] NULL,
	[ART_PU_BASE] [dbo].[D_MT_MONETAIRE] NULL,
	[ART_CODIF_MARCHE] [varchar](48) COLLATE French_CS_AS NULL
 CONSTRAINT [PK_T_ARTICLES_ART] PRIMARY KEY CLUSTERED 
(
	[ART_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Je veux impléménter une recherche exacte ou approchée qui me renvoie
- Une ligne recherchée sur Famille + Ref ou sur Famille + libelle
- x lignes avant
- x lignes après
notez que les x lignes et la ligne centrale ne sont donc pas forcement reliées

J'ai esayé 2 méthodes
Créer des champs "computed" non persistant sur la table soit

Code :

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

1
2
3
 
 CAST(ART_FAMILLE AS CHAR(16)) + ART_REF_CONSTR AS ART_CP_FAMREF
 CAST(ART_FAMILLE AS CHAR(16)) + ART_LIBELLE AS ART_CP_FAMLIB

Avec un index sur chaque
et avec le code suivant partie d'une procedure stockée

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
DECLARE @P_TOP INT
DECLARE @P_FAMILLE VARCHAR(16)
DECLARE @P_REF VARCHAR(48)
 
SET STATISTICS IO ON
SET STATISTICS TIME ON
 
SET @P_TOP = 50;
SET @P_FAMILLE = 'LEGR';
SET @P_REF = '011';
 
-- recherche exacte sur famille , approchée sur ref
 
WITH Ta
AS
(
SELECT TOP 1 ART_ID, ART_CP_FAMREF
FROM   dbo.T_ARTICLES_ART
WHERE  ART_CP_FAMREF LIKE CAST(@P_FAMILLE AS CHAR(16)) + @P_REF + '%'
ORDER  BY ART_CP_FAMREF
),
TTa 
AS
(
SELECT TOP (@P_TOP) A1.ART_ID
FROM	dbo.T_ARTICLES_ART A1
INNER JOIN Ta AS T1 
ON A1.ART_CP_FAMREF < T1.ART_CP_FAMREF
ORDER  BY A1.ART_CP_FAMREF DESC
UNION ALL
SELECT TOP (@P_TOP + 1) A2.ART_ID
FROM	dbo.T_ARTICLES_ART A2
		INNER JOIN Ta AS T2 
             ON A2.ART_CP_FAMREF >= T2.ART_CP_FAMREF
ORDER BY A2.ART_CP_FAMREF
) 
SELECT T.ART_ID, 
T.ART_STYPE, T.ART_FAMILLE, T.ART_REF_CONSTR, T.ART_LIBELLE, T.ART_PU, T.ART_PU_BASE, T.ART_CODIF_MARCHE, T.ART_ATTRIB_E, T.ART_DACRE, T.ART_DAMAJ, T.ART_USERCRE, T.ART_USERMAJ
FROM   TTa INNER JOIN T_ARTICLES_ART AS T ON (TTa.ART_ID = T.ART_ID)
ORDER BY T.ART_FAMILLE, T.ART_REF_CONSTR;

J'obtiens une réponse quasi instantanée (4 ms) sur une table de 1 millions d'articles

Mais si (solution que je trouve plus propre) je crée une vue sur la table article et comportant ART_ID + les 2 champs calculés comme
précédemment, avec un index cluster unique sur ART_ID et avec un index sur chaque autre champ
J'obtiens cette fois une réponse après 1700 ms sur une requête de même type (sauf que les cte portent cette fois sur la vue)
Je voudrais savoir si la méthode par computed column est correcte dans le cas d'une utilisation en production ,et le pourquoi de cette moins bonne vélocité de la vue indexée
Merci

[PickEpique]

J'ai oublié de dire que je suis sous SQLserver 2005, même si les Cte peuvent l'indiquer

[rudib]

Bonjour,

Le meilleur moyen de la savoir, est de comparer les plans d'exécution. CTRL+L pour obtenir le plan d'exécution estimé d'une requête.

Pour avoir les x lignes avant et après, pourquoi ne pas utiliser les fonction de fenêtrage : ROW_NUMBER() OVER (...) ?

[PickEpique]

Bonjour

Oui les plans d'execution c'est encore un peu illisible pour moi !
je ne compare pour l'instant que les temps d'execution et je suis étonné que la méthode que je considère peut être à tort comme la plus "bourrin" donne les meilleurs résultats
Pour les fonctions de fenêtrage le code suivant qui est le cas le plus simple d'une recherche approchée sur famille seule

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
25
26
27
 
-- code fourni par Fred Brouard
WITH T
AS
(
SELECT TOP 1 ART_FAMILLE
FROM   dbo.T_ARTICLES_ART
WHERE  ART_FAMILLE LIKE 'W%'
ORDER  BY ART_FAMILLE
),
TT
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY A1.ART_FAMILLE DESC) * -1 AS N, A1.ART_ID
FROM   dbo.T_ARTICLES_ART A1
       INNER JOIN T AS T1 
             ON A1.ART_FAMILLE < T1.ART_FAMILLE
UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY A2.ART_FAMILLE) - 1 AS N, A2.ART_ID
FROM   dbo.T_ARTICLES_ART A2
       INNER JOIN T AS T2 
             ON A2.ART_FAMILLE >= T2.ART_FAMILLE
) 
SELECT *
FROM   TT
WHERE  N BETWEEN -50 AND 50
ORDER BY N;

me donne au mieux 800 ms, le temps est très important dans mon cas parce que ces requète permettent de mimer une recherche incrémentale, du genre les utilsateurs tapent les premiers caractères des references cherchée et à chaque frappe je leur renvoie un set de donnée avec la ligne centrale qui approche au mieux les réferences tapées, donc comme un semblant de scroll sur une table fichier

[rudib]

Désolé, mais seul le plan d'exécution et les statistiques I/O vont te donner une information fiable, et te permettre de choisir la meilleure requête. Le temps d'exécution de la requête peut varier considérablement selon la charge et l'état du serveur.

Ceci dit, pragmatiquement, reste avec la solution par colonnes calculées. Si ta question est : pourquoi c'est plus rapide, je ne vois pas comment y répondre sans voir le plan d'exécution.

[PickEpique]

Voila les plans de requête dans le zip
1 colonne computed indexee ( colonnes calculéés indexées sur la table même)
2 vue indexee (ART_ID (cluster), ART_CP_FAMREF (index) , ART_CP_FAMLIB (index))
3 rownumber

notez que pour faire la 2 j'ai supprimer les 2 index que j'avais sur mes colonnes computed sinon la requête sur la vue semble en profiter et dans ce cas la vue indexée me semble inutile

[rudib]

Merci. En effet, dans ton cas la fonction de fenêtrage n'est pas intéressante, si tu veux te positionner sur une ligne de ton jeu de résultat, et prendre des lignes avant et après.

La méthode en colonne calculée est bien sûr ok en production. Tu dois simplement faire attention aux options des sessions qui lisent et modifient la table. Elles doivent être consistantes (lire l'aide en ligne pour les détails). Par exemple, SET ANSI_NULLS doit toujours être à ON, etc.

Dans le plan d'exécution, on voit clairement qu'aucun index de ta vue n'est utilisé. Tu peux poster le code DDL de ta vue, avec celui des index ?

Ceci dit, pourquoi fais-tu tout ça ? Tu cherches à avoir ta recherche approchée même sur la famille, c'est ça ? Nous sommes d'accord que si la famille est toujours en recherche exacte, tu n'as pas besoin de concaténer tes colonnes.

[PickEpique]

Merci rudib de t'interesser à mon cas
Voici donc le DDL de la vue et des index

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
25
26
27
28
29
30
31
32
33
 
USE [GPG]
GO
/****** Objet*:  View [dbo].[V_ARTICLE_SEARCH_ASE]    Date de génération du script*: 11/09/2007 11:04:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_ARTICLE_SEARCH_ASE]
WITH SCHEMABINDING
AS
SELECT     ART_ID AS ASE_ID, CAST(ART_FAMILLE AS CHAR(16)) + ART_REF_CONSTR AS ASE_CP_FAMREF, CAST(ART_FAMILLE AS CHAR(16)) 
                      + ART_LIBELLE AS ASE_CP_FAMLIB
FROM         dbo.T_ARTICLES_ART
 
 
/****** Objet*:  Index [IX_ASE]    Date de génération du script*: 11/09/2007 11:06:55 ******/
CREATE UNIQUE CLUSTERED INDEX [IX_ASE] ON [dbo].[V_ARTICLE_SEARCH_ASE] 
(
	[ASE_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
 
/****** Objet*:  Index [IX_ASE_CP_FAMLIB]    Date de génération du script*: 11/09/2007 11:08:05 ******/
CREATE NONCLUSTERED INDEX [IX_ASE_CP_FAMLIB] ON [dbo].[V_ARTICLE_SEARCH_ASE] 
(
	[ASE_CP_FAMLIB] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
 
/****** Objet*:  Index [IX_ASE_CP_FAMREF]    Date de génération du script*: 11/09/2007 11:08:49 ******/
CREATE NONCLUSTERED INDEX [IX_ASE_CP_FAMREF] ON [dbo].[V_ARTICLE_SEARCH_ASE] 
(
	[ASE_CP_FAMREF] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Je vais me documenter sur ce dont tu parles à propos des méthodes d'accès consistantes

C'est vrai tu as raison sur le dernier point je pourrais effectivement extraire la liste des familles uniques ( env 450) à partir d'une requête "GROUP BY" et la proposer ensuite dans une liste de choix et ne traiter que REF ou LIBELLE en l'état, je vais proposer cela à mes utilsateurs, mais j'ai des doutes ... les habitudes prises ...

[PickEpique]

Je pourrais aussi simuler tout cela à l'utilisateur final cad recherche dans une table locale de la famille comme si cela avait lieu sur le serveur , j'essaie de traiter cela de manière générale pour tous les cas ou des utilisateurs ne veulent pas démordre d'un fonctionnement à la mode base de donnée fichier