Discussion :

[Negaton]

Bonjour,

J'ai une requête assez simple

Code :

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

1
2
3
UPDATE Ventes
SET Vente.Dim_Items_dkey=Dim_Items.Dim_Items_dkey
FROM Dim_Items INNER JOIN Ventes ON (Dim_Items.Company=Ventes.Company AND Dim_Items.Item_Number=Ventes.Item_Number)

Cette requête est très longue, et pour cause : 12 millions de lignes dans la table "Ventes" et 26608 items.

Quelqu'un aurait une piste d'optimisation ? (les champs concernés par la jointure sont indexés)

Merci

[Rei Ichido]

Et les valeurs changent à chaque fois ?

En considérant que la valeur ne peut pas être NULL, je tenterais déjà ceci pour éviter de faire inutilement de fausses mises à jour :

Code :

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

1
2
3
4
5
 
UPDATE Ventes
SET Vente.Dim_Items_dkey=Dim_Items.Dim_Items_dkey
FROM Dim_Items INNER JOIN Ventes ON (Dim_Items.Company=Ventes.Company AND Dim_Items.Item_Number=Ventes.Item_Number)
WHERE Vente.Dim_Items_dkey <> Dim_Items.Dim_Items_dkey

Ensuite il faudrait effectivement voir quels sont les index posés : peut-on avoir le DDL des tables / index ?

[Negaton]

Merci de ton aide.
Effectivement, le champ concerné par l'update change a chaque fois (il est même vidé, donc null, avant le passage de l'update)

Le where n'est donc ici d'aucune utilité

Voici la structure abrégée des tables (y'a plus de champs, mais qui n'entrent pas en jeu pour l'optimisation (du moins je crois !))

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE [dbo].[Dim_Items](
	[dim_items_dKey] [bigint] NOT NULL,
	[company] [smallint] NULL,
	[item_number] [nvarchar](15) NULL,
	[item_name] [nvarchar](30) NULL,
...)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE [dbo].[Ventes](
	[Dim_Sales_Budget_Analysis_dkey] [bigint] IDENTITY(1,1) NOT NULL,
	[dim_divisions_dkey] [bigint] NULL,
	[dim_invoiced_customer_dkey] [bigint] NULL,
	[dim_customers_dkey] [bigint] NULL,
	[dim_salesperson_lastknown_dkey] [bigint] NULL,
	[dim_periods_dkey] [bigint] NULL,
	[dim_items_dkey] [bigint] NULL,
	[company] [smallint] NOT NULL,
	[division] [nvarchar](3) NOT NULL,
	[item_number] [nvarchar](15) NOT NULL,
...

Et les champs concernés par l'update sont indexés

[SQLpro]

Il est logique que cela rame. Vous avez une clause WHERE qui est non sargeable ce qui se traduit pas un SCAN.
La seule chose qui pourrait accélérer un peu est de réduire la clef de jointure à une seule colonne de type BIGINT.

A +

[Negaton]

Merci pour votre aide.

noir c'est noir... !

Une seule jointure sur un champ de type big int pourrait améliorer un peu. Et si ce champ était de type nvarchar, d'après vous, ça améliorerait aussi ?

[Rei Ichido]

Et les champs concernés par l'update sont indexés

Juste pour être sûrs : à quoi ressemblent les index ?

Idéalement il faudrait avoir :

Code :

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

1
2
3
4
 
CREATE INDEX IDX_DIM_ITEMS_TEST ON Dim_Items  (Item_number,Company) INCLUDE (dim_items_dKey)
-- le include n'est pas nécessaire si la table est clustered avec une PK sur dim_items_dKey
CREATE INDEX IDX_Ventes_TEST ON (Item_number,Company)

[Negaton]

Mes deux champs sont sur deux index séparés sur les deux tables.
J'ai un espace disque limité par le dbo pour ma table vente et je ne pouvais pas, il y a quelque temps encore, créer de nouveaux index sans en supprimer
Mais ca me fait penser du coup à une question : celà pourrait-il avoir un intérêt de faire un index sur les deux champs uniquement sur la table dim_items (sans vente) ? Cette table étant largement plus petite, je dois pouvoir créer l'index composite

Code :

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

1
2
3
4
5
 
CREATE NONCLUSTERED INDEX [IX_Dim_Items] ON [dbo].[Dim_Items] 
(
	[company] 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, FILLFACTOR = 100) ON [PRIMARY]

Code :

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

1
2
3
4
5
 
CREATE NONCLUSTERED INDEX [IX_Dim_Items_1] ON [dbo].[Dim_Items] 
(
	[item_number] 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, FILLFACTOR = 100) ON [PRIMARY]

Index similaires (non groupés) sur la table des ventes.

[Rei Ichido]

Oui il y aura toujours au moins un petit gain à pouvoir ne considérer que l'index, et dans le cas présent ça peut être vraiment important car il n'y a plus besoin d'aller voir la table (en utilisant l'include). Le mieux étant vraiment d'avoir le même sur les deux tables pour faire une jointure directement.

Ici il faut bien voir qu'avoir 2 index séparés a un intérêt très faible (*) pour cette requête ; et potentiellement sur beaucoup d'autres, avez-vous beaucoup de requêtes qui accèdent aux ventes uniquement par item_number (sans aucune restriction ou lien sur company) par exemple ? Si ce n'est pas le cas, autant avoir un index directement sur (item_number,company), et non pas simplement sur (item_number).


(*) De la même façon qu'un annuaire qui serait trié d'une part par nom de famille (tous les prénoms dans le désordre) et d'autre part par prénom (tous les noms dans le désordre) aurait peu d'intérêt

[Negaton]

Merci beaucoup pour ces explications.
Je vais donc mettre les clés composites en place sur les tables de moindre volume.

Pour ce qui est des index sur la table "vente", je vais effectivement faire un petit tour des utilisateurs (tout du moins des personnes ayant réalisé les interfaces de consultation via Business Object) et valider avec eux les requêtes qu'ils ne font pas (et qu'ils n'envisagent pas de faire) de type "code article seul".
Je sais qu'un certain nombre de ces requêtes sont déjà faite, mais typiquement, celle sur code article n'est peut être pas réalisée.

Merci pour votre aide