Discussion :

[jef531]

Bonjour à tous,

Je suis sur un gros développement, et je n'arrive pas à trouver une solution pour optimiser la récupération de donnée

J'ai une db qui contient entre-autre 6 tables
- Stock
- Lignes_v
- Pieds_v
- Lignes_a
- Pieds_a
- Lignes_d

Sur ses tables, je dois extraire des données :

Voici ma requete

Code :

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

1
2
3
4
5
6
 
declare @depot char(10),@date date
set @depot = '0000000001'
set @date = '20120101'
 
select *, dbo.dispoadate(s_id,@depot,@date) from stock

Voici ma fonction dispoadate

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
CREATE FUNCTION [dbo].[DISPOADATE]
(
	@id_article char(10), @id_depot char(10), @date date
)
RETURNS float
AS
BEGIN
 
	declare @ret float
	declare @dispo float
	declare @tot_v float
	declare @tot_a float
	declare @tot_d float
 
	select @tot_v=sum(q) from lignes_v (NOLOCK) inner join pieds_v (NOLOCK) on ((pieds_v.id=lignes_v.id) and (pieds_v.journal=lignes_v.journal) and (pieds_v.piece=lignes_v.piece)) where id_article > '' and (id_article=@id_article) and  (type<=2) and (pieds_v.date>@date) and (pieds_v.id_depot=@id_depot) group by id_article
	select @tot_a=sum(q) from lignes_a (NOLOCK) inner join pieds_a (NOLOCK) on ((pieds_a.id=lignes_a.id) and (pieds_a.journal=lignes_a.journal) and (pieds_a.piece=lignes_a.piece)) where id_article > '' and (id_article=@id_article) and  (type<=2) and (pieds_a.date>@date) and (pieds_a.id_depot=@id_depot) group by id_article
	select @tot_d=sum(q) from lignes_d (NOLOCK) where id_art > '' and (id_art=@id_article) and  (date>@date) and (id_depot=@id_depot) group by ID_ART
	select @ret = isnull(@dispo,0)+isnull(@tot_v,0)-isnull(@tot_a,0)-isnull(@tot_d,0)
 
	return isnull(@ret,0)
END

Tous les champs contenu dans les where, ou au niveau des jointures sont indexés.

Sur une petit nombre de donnée cela fonctionne parfaitement, mais dès que j'arrive sur une base de donnée plus significative, le temps de réponse est beaucoup trop important.

Exemple => une base de donnée comportant +-160000 record dans la table stock, et +-7 millions de lignes dans lignes_v, après une heure de traitement, il ne me retournait que 30000 lignes.

J'ai essayer de partir sur des vues au niveau de mes tables lignes_v,lignes_a et lignes_d, mais je ne vois pas trop comment faire pour rendre la vue "dynamique" vus que la date est un paramètre.

Avez-vous des idées pour rendre se traitement possible?

Merci d'avance

[rw72000]

Bonjour,

A mon avis, plutôt que de faire une fonction scalaire, qui donc va s'exécuter pour chaque ligne de résultat, tu aurais intérêt à faire de l'ensembliste.
Là, il semblerait qu'avec tes volumes actuels, ta fonction prenne environ 100ms à s'exécuter, ça fait trop si tu l'exécute pour 160000 lignes de résultat.

J'essaierais pour ma part de coder une table temporaire qui contiendrait la valeur de ton stock à date pour tous les produits et tous les id de dépôt prenant simplement la date en paramètre (qui finirait donc par where date>@date Group by id_article, id_depot). Je ferais ensuite une jointure entre cette table temporaire et le stock sur le bon article et le bon dépôt.

L'alimentation de la table temporaire peut-être un peu longue mais une fois terminée, le reste de la procédure doit être rapide (si besoin indexer la table temporaire)

tu pourras ensuite réorganiser ton code pour passer par une CTE, une vue ou une fonction table si la table temporaire ne te plait pas et si tu veux pouvoir le réutiliser ailleurs.

[jef531]

Suite à votre conseil, je suis parti sur une table temporaire et je réussi à récupéré mes données en moins de 10 minutes.
Voici cette fameuse requete, si vous voyez d'autre amélioration, je suis preneur.

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
 
declare @id_depot char(10), @date date
set @id_depot = '0000000001'
set @date = '20120101'
 
select id_article,sum(qTot) as qTotTot into #tableq from (
select id_article,sum(q) as qTot from lignes_v (NOLOCK) inner join pieds_v (NOLOCK) on ((pieds_v.id=lignes_v.id) and (pieds_v.journal=lignes_v.journal) and (pieds_v.piece=lignes_v.piece)) where id_article > '' and  (type<=2) and (pieds_v.date>@date) and (pieds_v.id_depot=@id_depot) group by id_article
union all
select id_article,-sum(q) as qTot from lignes_a (NOLOCK) inner join pieds_a (NOLOCK) on ((pieds_a.id=lignes_a.id) and (pieds_a.journal=lignes_a.journal) and (pieds_a.piece=lignes_a.piece)) where id_article > '' and  (type<=2) and (pieds_a.date>@date) and (pieds_a.id_depot=@id_depot) group by id_article
union all
select id_art as id_article,-sum(q) as qTot from lignes_d (NOLOCK) where id_art > '' and  (date>@date) and (id_depot=@id_depot) group by ID_ART
)tabletmp 
group by id_article
 
select stock.*,isnull(dispo.dispo,0)+isnull(qtottot,0) as dispo from stock (nolock)
left join dispo (nolock) on s_id = id_stock
left join #tableq on s_id = id_article
where id_magasin = @id_depot
 
drop table #tableq

[SQLpro]

A mon avis votre modèle est déjà mauvais... Pourquoi 3 tables lignes_v, lignes_a, lignes_d ?

L'utilisation du NOLOCK est aussi une horreur qui ne peut que conduire à des données erronées un jour ou l'autre... Préférez une niveau d'isolation SNAPSHOT.

Il est aussi idiot d'avoir utilisé des mots clé réservé de SQL comme DATE ou type pour nommer des colonnes. Ceci peut conduire à des erreurs d'exécution de requêtes...

Donnez nous les index pour savoir s'ils sont couvrants.

Sinon, le mieux serait de créer des vues indexées, par exemple :

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
CREATE VIEW pied_ligne_v
WITH SCHEMABINDING
AS
SELECT id_article, "type", pieds_v."date", pieds_v.id_depot,
       sum(q) AS SUM_g, COUNT_BIG(*)  AS NOMBRE
FROM   dbo.lignes_v 
       INNER JOIN dbo.pieds_v 
             ON ((pieds_v.id=lignes_v.id) 
			 AND (pieds_v.journal=lignes_v.journal) 
			 AND (pieds_v.piece=lignes_v.piece)) 
WHERE id_article > '' 
  AND (id_article=@id_article) 
  AND  (type<=2) 
  AND (pieds_v.date>@date)
  AND (pieds_v.id_depot=@id_depot) 
GROUP BY id_article, "type", pieds_v."date", pieds_v.id_depot;

Code :

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

1
2
CREATE UNIQUE CLUSTERED INDEX XV 
   ON pied_ligne_v (id_article, "type", "date", id_depot);

A +

[jef531]

A mon avis votre modèle est déjà mauvais... Pourquoi 3 tables lignes_v, lignes_a, lignes_d ?

Les trois tables sont là car trois traitements différents au niveau du logiciel et aussi à cause de l'historique du logiciel.

L'utilisation du NOLOCK est aussi une horreur qui ne peut que conduire à des données erronées un jour ou l'autre... Préférez une niveau d'isolation SNAPSHOT.

J'y regarde, mais il me semble que le niveau d'isolation SNAPSHOT peut réduire les performances. Fin quoi qu'il en soit je vais faire le test.

Il est aussi idiot d'avoir utilisé des mots clé réservé de SQL comme DATE ou type pour nommer des colonnes. Ceci peut conduire à des erreurs d'exécution de requêtes...

Je ne sais rien y faire, c'est dû à l'historique du logiciel, qui au départ travaillait sur des tables DBF, et la migration vers SQL c'est fait en douceur et donc on a du garder ce genre de chose ..., on fait avec.

Donnez nous les index pour savoir s'ils sont couvrants.

Voici les index de la table lignes_v, pieds_v et stock par exemple.

Code :

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

1
2
3
4
CREATE NONCLUSTERED INDEX [ID_ART_2] ON [dbo].[LIGNES_V] 
(
	[ID_ARTICLE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Code :

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

1
2
3
4
CREATE NONCLUSTERED INDEX [JOURNAL] ON [dbo].[LIGNES_V] 
(
	[JOURNAL] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Code :

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

1
2
3
4
CREATE NONCLUSTERED INDEX [ID] ON [dbo].[LIGNES_V] 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Code :

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

1
2
3
4
CREATE NONCLUSTERED INDEX [PIECE] ON [dbo].[LIGNES_V] 
(
	[PIECE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Code :

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

1
2
3
4
5
6
REATE NONCLUSTERED INDEX [IDENT] ON [dbo].[LIGNES_V] 
(
	[ID] ASC,
	[JOURNAL] ASC,
	[PIECE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Code :

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

1
2
3
4
CREATE NONCLUSTERED INDEX [DATE] ON [dbo].[PIEDS_V] 
(
	[DATE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

le reste des index créer sur les tables est construit de la même manière.

Sinon, le mieux serait de créer des vues indexées

J'ai bien compris le concept, mais comment créer une vue avec des variable qui sont passé en paramètre ?
Si j'essaie telquel, il me dit que je dois déclarer les variables @date ... mais comment faire?

Merci en tout cas pour vos réponses.

[OokieDookie91]

comment créer une vue avec des variable qui sont passé en paramètre ?

Bonjour,

En parallèle de ce que vous pourrez faire pour améliorer les perfs, ce sujet pourra peut-être vous aider, vous avez l'air de vous heurter aux même problématiques que moi

Bonne journée

[SQLpro]

J'y regarde, mais il me semble que le niveau d'isolation SNAPSHOT peut réduire les performances. Fin quoi qu'il en soit je vais faire le test.

le problème n'est pas sur la performance. NOLOCK entraine des données fausses. La question est donc acceptez vous des résultats potentiellement faux ou voulez vous des données justes. Par exemple, pour des statistiques qui se traduisent sous forme de camembert on peut se contenter de données approximatives et donc du NOLOCK... mais pour une comptabilité c'est suicidaire !

...
Voici les index de la table lignes_v, pieds_v et stock par exemple.

C'est bien ce que je pensais... Aucun index n'est couvrant. À me lire : http://sqlpro.developpez.com/cours/quoi-indexer/#LVIII

J'ai bien compris le concept, mais comment créer une vue avec des variable qui sont passé en paramètre ?
Si j'essaie telquel, il me dit que je dois déclarer les variables @date ... mais comment faire?

je vous ais donné un exemple; Lancez le et voyez ce qui se passe !

A +