Discussion :

[elsuket]

Bonjour,

On peut effectivement placer l'activation et la désactivation des déclencheurs au sein d'une transaction explicite.
Mais comme l'a exposé aieeeuuuuu, vous allez rencontrer des problèmes d'accès.

Comme évoqué auparavant, la revue du plan de requête permettra de voir ce que l'on peut faire pour optimiser l'exécution de la requête.
Vous pouvez pour ce faire vous en remettre à SQL Sentry Plan Explorer, qui permet en plus d'anonymiser le plan de requête : vous serez donc en mesure de le partager ici.

Pourriez-vous aussi n'exécuter que le SELECT que aieeeuuuuu a repris de votre code, en le précédant de SET STATISTICS IO, TIME ON ?
Ceci nous donnera des informations sur les IOs pour chaque table participant à la requête, le temps CPU consommé, et la durée d'exécution de la requête.
Le plus dur sera de mapper le nom de ces tables à ceux obtenus par anonymisation du plan de requête avec SQL Sentry Plan Explorer.

Enfin, pour l'avoir fait un grand nombre de fois : vous pouvez stocker la valeur de la clé primaire de la table cible de la mise à jour dans une table que l'on qualifier d'utilitaire ou d'annexe.
Avec celle-ci, vous pouvez vous contenter de ne stocker que les colonnes nécéssaires au calcul / à la récupération des caractéristiques du prix.
Vous pouvez ensuite réaliser les calculs et stocker dans des colonnes additionnelles à cette table d'annexe les caractéristiques des prix à stocker dans la table cible.

Une fois tout cela réalisé, il suffit de batcher l'UPDATE, comme suggéré par aieeeuuuuu : c'est alors un UPDATE sur une équi-jointure, par exemple de 10000 lignes.
Comme la transaction est petite, on n'a pas besoin de se soucier de l'effet sur le fichier du journal de transactions, si ce n'est peut-être augmenter la fréquence des sauvegardes du fichier du journal des transactions. Par ailleurs, si l'on souhaite arrêter le travail en cours de route à cause des situations de blocage qu'il génère, le travail déjà effectué n'est pas perdu.

Le squelette de cette approche est :

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
50
51
52
53
54
55
56
57
58
59
60
61
SET NOCOUNT ON
 
DECLARE @i int = 0
	, @dt datetime
 
WHILE EXISTS
(
	SELECT	*
	FROM	dbo.maTableAnnexe
)
BEGIN
	BEGIN TRY
		SELECT	@dt = GETDATE()
			, @i += 1
 
		BEGIN TRANSACTION
			;WITH
				CTE AS
				(
					SELECT	TOP 10000 colonnes_PK
						, autresColonnes
					FROM	dbo.maTableAnnexe
					ORDER	BY colonnes_PK
				)
			UPDATE		dbo.maTableCible
			FROM		dbo.maTableCible AS C
			INNER JOIN	CTE AS T
						ON T.colonnes_PK = C.colonnes_PK
 
			;WITH
				CTE AS
				(
					SELECT	TOP 10000 colonnes_PK
						, autresColonnes
					FROM	dbo.maTableAnnexe
					ORDER	BY colonnes_PK
				)
			DELETE FROM	dbo.maTableAnnexe
			FROM		dbo.maTableAnnexe AS A
			INNER JOIN	CTE AS C
						ON A.colonnes_PK = C.colonnes_PK
		COMMIT TRANSACTION
 
		PRINT 'Batch #' + CAST(@i AS varchar(10)) + '	| Duration (ms) : ' + CAST(DATEDIFF(ms, @dt, GETDATE()) AS varchar(10))
	END TRY
	BEGIN CATCH
		DECLARE @err_msg nvarchar(4000) = 'Line ' + CAST(ERROR_LINE() AS varchar(10))
			+ ' - Error Number : ' + CAST(ERROR_NUMBER() AS varchar(10))
			+ ' - ' + ERROR_MESSAGE()
			, @err_svt int = ERROR_SEVERITY()
			, @err_stt int = ERROR_STATE()
 
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;
		END
 
		RAISERROR(@err_msg, @err_svt, @err_stt);
		RETURN;
	END CATCH
END

Veillez bien à ce que la PK de la table d'annexe soit précisément la même que la table cible de la mise à jour.
Si vous devez arrêter le lot en cours de route, veillez bien à exécuter un ROLLBACK TRANSACTION, de façon à libérer les verrous acquis.

@++

[Donpi]

tu as déjà ajouté la foerign key ou tu vas le faire?

La clé est déclarée mais les valeurs sont à NULL et je dois les remplir.
C'est ça qui prend du temps.

... il serait plus que temps d'envisager de faire une auscultation complète de la base de données, ne serait-ce que pour respecter les principes de base...
...mais il est possible de réaliser une couche de compatibilité à base de vues...

Avant que je commance il y avait eu un audit pat ce qu'il ne comprenaient pas pourquoi il fallait utilisé des WITH (NOLOCK) partout.
L'audit (qui a couter plusieurs millier de balles) à révéler des évidences, manques de clé primaires, manque de clé étrangères, manques d'indexes mais pas pourquoi il falait utiliser les Nolock.

En gros, l'audit n'as servi a rien, car d'un côté l'auditeur n'avait pas assez de bouteille et de l'autre mes anciens collègues étaient assez borné.
Mais nous avons fait beaucoup de chemin depuis.

...
pourquoi une jointure externe sur la table des prix ?
1/ s'il n'y a pas de correspondance, alors l'UPDATE ne fera que remplacer NULL par... NULL, ce qui manque certainement d’intérêt
2/ pire, cela signifie que certaines lignes resteront a NULL, et seront donc traitée à chacune de vos boucles. En clair, si vous avez lancé 50 fois votre boucle pour votre test sur 50000 lignes, il se peut que ayez mis à jour 50 fois les mêmes lignes...

C'est par là que tout a commencé en fait.
A la base, la jointure n'existait pas.
Le but est de créer une jointure fiable.
Pour ce faire j'ai créer les prix manquant rétroactivement depuis les ventes.
J'ai chercher les doublons et traiter les cas particulier.
En l'état je n'ai qu'un seul prix possible par vente et toutes mes ventes ont un prix.
Cette situation risque de changer, raison pour laquel je veux créer cette jointure pour que sql server retourne une erreur si il y a une violation d'intégité référentielle.

C'est vrai que cette question pourtant intéressante était passée à l'as

sous SQL Server, il est possible de placer du code DDL dans une transaction. (tant que la cible reste au sein de la base de données)

Pour répondre donc à la question, il est en effet possible de désactiver le déclencheur le temps de la transaction. Cela aura pour effet de poser un verrou de stabilité de schéma sur la table, empêchant par ailleurs toute opération sur celle-ci (y compris un simple SELECT même en READ UNCOMMITTED, puisqu'il sera alors impossible d'acquérir un verrou de modification de schéma).
De fait, l’intégrité sera donc assurée.

Merci, c'est ce dont je voulais être sûr.

[SQLpro]

Je ne connais pas vraiment sqlserver, je ne peux donc pas être catégorique sur l'approche de désactivation au sein de la transaction.
Mais je ne pense pas que ce soit possible (sur Oracle ça ne fonctionnerait pas)

ça n'est pas parce que Oracle est mauvais et ne sait pas faire cela que SQL Server ne transactionne pas le DDL !

Depuis toujours, SQL Server transactionne absolument tous les ordres SQL que ce soit le DDL (CREATE, ALTER, DROP...), le DML (INSERT, UPDATE, DELETE...) et même le DCL (GRANT, REVOKE...)

Donc, non seulement il peut, mais je dirais même qu'il doit et son saucissonage en tranche de n milliers est bon.

Il faut simplement en sus désactiver avant la transactions tous les index qui ne servent pas la requête et les reconstruire en fin de batch !

A +

[SQLpro]

L'audit (qui a couter plusieurs millier de balles) à révéler des évidences, manques de clé primaires, manque de clé étrangères, manques d'indexes mais pas pourquoi il falait utiliser les Nolock.

L'utilisation des NOLOCK systématique est juste une vaste connerie et celui qui vous a audité est très certainement un con...
En effet, à de très rares exceptions près, l'utilisation de ce mode de verrouillage conduit tôt ou tard à des résultats faux, mais vous nje pourrez jamais vous en apercevoir à moins de recontrôler toutes vos données...
En fait le NOLOCK est trompeur car cela ne veut pas dire "NE VERROUILLE PAS", mais plus exactement "NE TIENT PAS COMPTE DES VERROUS". Le traitement est donc amené à ignorer certaines lignes qui aurait dû êtres lues et parfois compter plusieurs fois la même ligne car elle s'est "déplacée" entre temps par une mise à jour concurrente.
À moins de vouloir spécifiquement des résultats approximatif (cas des tableaux statistiques grossiers), ce mode est dangereux !
Il est néanmoins possible de lire les données sans être bloqué en activant le niveau d'isolation SNAPSHOT... votre auditeur aurait du vous en informer... S'il ne l'a pas fait c'est qu'il l'ignore et personnellement je l'attaquerais en responsabilité sur son audit, histoire de lui faire rembourser les milliers d'euros !

En gros, l'audit n'as servi a rien, car d'un côté l'auditeur n'avait pas assez de bouteille et de l'autre mes anciens collègues étaient assez borné.
Mais nous avons fait beaucoup de chemin depuis.

C'est par là que tout a commencé en fait.
A la base, la jointure n'existait pas.
Le but est de créer une jointure fiable.

Vous confondez jointure et intégrité référentielle. On peut faire une jointure sur n'importe quoi... Mais l'intégrité référentielle est une contrainte qui se pose sur une référence qui doit être unique (clef primaire ou contrainte d'unicité). Rien à voir avec une jointure !

Pour ce faire j'ai créer les prix manquant rétroactivement depuis les ventes.
J'ai chercher les doublons et traiter les cas particulier.
En l'état je n'ai qu'un seul prix possible par vente et toutes mes ventes ont un prix.
Cette situation risque de changer, raison pour laquel je veux créer cette jointure pour que sql server retourne une erreur si il y a une violation d'intégité référentielle.

Merci, c'est ce dont je voulais être sûr.

A +