Discussion :

[SQLpro]

Et oui.
On notera qu'à ce jeux là c'est Oracle qui gagne avec le partitionnement par référence (https://docs.oracle.com/cd/B28359_01...n.htm#CACIHDII)
On attendra que M$ comprenne le problème

Quand oracle aura compris l'intérêt d'un partitionnement générique… Essaye de partitionner 70 tables et les index alignés avec Oracle et dis m'en des nouvelles lorsque tu veut rajouter des partitions… Dans SQL Server, 2 commandes. Dans Oracle 70 + autant que d'index !!!

A +

[Michel.Priori]

Bonsoir,

Essaye de partitionner 70 tables et les index alignés avec Oracle et dis m'en des nouvelles

Oui.
Mais entre une fonctionnalité pénible à mettre en place et une absence, quel est le moindre mal ?

[SQLpro]

Et oui.
On notera qu'à ce jeux là c'est Oracle qui gagne avec le partitionnement par référence (https://docs.oracle.com/cd/B28359_01...n.htm#CACIHDII)
On attendra que M$ comprenne le problème

C'est tout à fait possible dans SQL Server. Il suffit d'incorporer la colonne de partitionnement dans une contrainte d'unicité et se servir ce cette référence en mode ON UPDATE/DELETE CASCADE…

A +

[Michel.Priori]

Bonsoir,

C'est tout à fait possible dans SQL Server. Il suffit d'incorporer la colonne de partitionnement dans une contrainte d'unicité et se servir ce cette référence en mode ON UPDATE/DELETE CASCADE…

Ok
D'un coté, la syntaxe est complexe
De l'autre, faut repenser le modèle des données

Le bilan n'est pas null.
Désolé.

[SQLpro]

Oui mais cela permet de meilleures optimisations des plans de requête... Donc oui le bilan n'est pas nul, mais il est plutôt positif pour sql server...

[elsuket]

Bonjour,

Avant même de parler de (re)partitionner, il faudrait regarder en premier lieu la requête :

Code :

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

1
2
3
UPDATE gtm_crb_consolide_pt10
      SET puissance_arenh = NULL
    WHERE year (dateadd (minute, -10, crb.datepoint)) = @iYear

Comme Aieuuuuuuuu le disait, elle ne peut pas utiliser d'index : dès lors qu'on pose une fonction sur une colonne, c'est mort (cf ici)

Donc ici on écrira quelque chose comme :

Code :

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

1
2
3
UPDATE	dbo.gtm_crb_consolide_pt10
SET		puissance_arenh = NULL
WHERE	crb.datepoint BETWEEN CAST(@iYear AS char(4)) + '0101' AND CAST(@iYear + 1 AS char(4)) + '0101'

Ensuite il faut lire le plan d'exécution pour vérifier si le filtre permettra d'accéder aux bonnes partitions.
Si ce n'est pas le cas, remplacez les calculs dans le BETWEEN ci-dessus par des variables du même type que la colonne datepoint, que vous affecterez avec la bonne valeur. Un petit OPTION (RECOMPILE) juste après l'UPDATE pour que la valeur des variables locales soit prise en compte à la compilation du plan de l'UPDATE

Ensuite vu le volume, l'idéal est de stocker la valeur de la clé primaire dans une table utilitaire, puis de "batcher", comme vous le conseille SQLPro.
L'intérêt de la table utilitaire est de linéariser l'accès à la table cible, c'est à dire à minimiser le parcours de l'index cluster. Voici un 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
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
44
45
46
47
48
49
SELECT	colonne_de_la_cle_primaire
INTO	dbo.table_utilitaire
FROM	dbo.gtm_crb_consolide_pt10
WHERE	crb.datepoint BETWEEN CAST(@iYear AS char(4)) + '0101' AND CAST(@iYear + 1 AS char(4)) + '0101'
 
-- Pour bien supporter les ORDER BY des expressions de tables communes du WHILE qui suit
ALTER TABLE dbo.table_utilitaire
ADD CONSTRAINT PK_table_utilitaire PRIMARY KEY(colonne_de_la_cle_primaire)
 
DECLARE @dt datetime2(3)
	, @i int = 0
	, @msg varchar(1024)
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.table_utilitaire
)
BEGIN
	SELECT @dt = SYSDATETIME()
		, @i += 1
 
	;WITH
		cte AS
		(
			SELECT TOP (10000) colonne_de_la_cle_primaire
			FROM dbo.table_utilitaire
			ORDER BY colonne_de_la_cle_primaire
		)
	DELETE FROM	dbo.gtm_crb_consolide_pt10
	FROM		dbo.gtm_crb_consolide_pt10 AS tgt
	INNER JOIN	cte AS src
				ON src.colonne_de_la_cle_primaire = tgt.colonne_de_la_cle_primaire;
 
	WITH
		cte AS
		(
			SELECT TOP (10000) colonne_de_la_cle_primaire
			FROM dbo.table_utilitaire
			ORDER BY colonne_de_la_cle_primaire
		)
	DELETE FROM	dbo.table_utilitaire
	FROM		dbo.table_utilitaire AS tgt
	INNER JOIN	cte AS src
				ON src.colonne_de_la_cle_primaire = tgt.colonne_de_la_cle_primaire;
 
	SELECT @msg = 'Batch #' + CAST(@i AS varchar(10) + '	-	Duration (ms) : ' + CAST(DATEDIFF(millisecond, @dt, SYSDATETIME() AS varchar(10)));
	RAISERROR(@msg, 0, 1) WITH NOWAIT;
END

Cela permet de circonscrire le verrouillage aux seules 10000 lignes touchées par chaque tour de boucle.
On peut arrêter le batch à tout moment, éventuellement changer la taille du batch si l'on constate que son exécution génère trop de blocages, et relancer sans perte du travail déjà effectué. Et bien sûr l'impact sur la taille du fichier du journal des transactions est faible. Le RAISERROR vous permet de suivre l'exécution du batch dans la console (onglet Messages du panneau des résultats de requête dans SQL Server Management Studio)

Le fait d'ajouter un index va pénaliser les écritures et ajouter du volume à backuper.

Les écritures ont besoin dans une première phase de trouver les lignes à affecter, ce qui peut justement être facilité par les index.
Donc il est préférable de bien concevoir ses index, et de bien faire attention à ce qu'il n'y ait pas de doublons et de "sur-clés", de façon effectivement à minimiser le travail du moteur de stockage.

@++