Discussion :

[guigui69]

Bonjour à tous,

Depuis un fichier excel j'interroge une base sql server (2017) , mais je rencontre une erreur "la transaction a été bloquée sur les ressources verrou - MS ..."

voici la requete:

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
SELECT 
societe,
magasin, 
marque,
Case Muerto WHEN 1 then '2-Stock Mort' else '1-Stock Vivant' End As Muerto ,
Case Rango 
WHEN 1 then '1-Mois 6 mois'
WHEN 2 then '2-Mois 6-12 mois'
WHEN 3 then '3-Mois 12-24 mois'
WHEN 4 then '4-Mois + 24 mois' End as Rango ,
SUM(CantidadStock) as qte,
SUM((PCPMAlmacen)* CantidadStock) as "Pamp",
SUM((taHistorico_PrecioVenta)* CantidadStock) as "PVP",
SUM((PrecioCompra)* CantidadStock) as "PA",
SUM((PrecioCosteMedio )* CantidadStock) as "Pamp2"
 
FROM (
SELECT tgMarca.Descrip as "marque",
       tgEmpresa.Razon as "societe",
       tgAlma.Descrip as "magasin",
 
                taArticuloAlma.PrecioCosteMedio AS PCPMAlmacen,
 
                taHistorico.PrecioCosteMedio AS PrecioCosteMedio,
 
                CASE
                    WHEN COALESCE(taHistorico.PrecioVentaTarifa, 0.00) = 0.00 THEN CASE
                                                                                       WHEN COALESCE(taHistorico.PrecioVenta, 0.00) = 0.00 THEN CASE
                                                                                                                                                    WHEN taArticuloPrecio.Precioventa > 0 THEN taArticuloPrecio.Precioventa
                                                                                                                                                    ELSE taArticulo.PrecioVenta
                                                                                                                                                END
                                                                                       ELSE taHistorico.PrecioVenta
                                                                                   END
                    ELSE taHistorico.PrecioVentaTarifa
                END AS taHistorico_PrecioVenta,
                CASE
                    WHEN COALESCE(taHistorico.PrecioCompraTarifa, 0.00) = 0.00 THEN CASE
                                                                                        WHEN COALESCE(taHistorico.PrecioCompra, 0.00) = 0.00 THEN COALESCE(
                                                                                                                                                             (SELECT PrecioCoste
                                                                                                                                                              FROM taArticuloProv
                                                                                                                                                              WHERE taArticuloProv.NumInterno = taArticulo.NumInterno
                                                                                                                                                                AND taArticuloProv.Proveedor = taArticulo.Proveedor),0.00)
                                                                                        ELSE taHistorico.PrecioCompra
                                                                                    END
                    ELSE taHistorico.PrecioCompraTarifa
                END AS PrecioCompra,
                taHistorico.CategoriaStock AS CategoriaStock,
                taHistorico.StockFinal AS CantidadStock,
                CASE
                    WHEN taHistorico.StockFinal <= 0 THEN 0.00
                    ELSE COALESCE(taHistorico.StockFinal, 0.00)
                END AS CdadStock,
 
                CASE
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 6 THEN 1
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 12 THEN 2
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 24 THEN 3
                    ELSE 4
                END AS Rango,
                CASE
                    WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 12 THEN 0
                    ELSE 1
                END AS Muerto
 
 
FROM taHistorico
LEFT OUTER JOIN tgGrupoCont ON taHistorico.GrupoCont = tgGrupoCont.GrupoCont
LEFT OUTER JOIN taFamProveedor ON taHistorico.Marca = taFamProveedor.Marca
AND taHistorico.FamiliaProv = taFamProveedor.Codigo
LEFT OUTER JOIN taFamInterna ON taHistorico.Marca = taFamInterna.Marca
AND taHistorico.FamiliaInt = taFamInterna.Codigo
LEFT OUTER JOIN taCategoriaStock ON taHistorico.Emp = taCategoriaStock.Emp
AND taHistorico.Marca = taCategoriaStock.Marca
AND taHistorico.CategoriaStock = taCategoriaStock.Codigo
LEFT OUTER JOIN taCategoriaPieza ON taHistorico.Marca = taCategoriaPieza.Marca
AND taHistorico.CodigoCategoria = taCategoriaPieza.Codigo,
    taArticuloAlma
LEFT OUTER JOIN taArticuloPrecio ON (taArticuloAlma.NumInterno = taArticuloPrecio.NumInterno
                                     AND taArticuloAlma.Emp = taArticuloPrecio.Emp
                                     AND taArticuloAlma.Almacen = taArticuloPrecio.Almacen), taArticulo
LEFT OUTER JOIN taDctoCompra ON taArticulo.Marca = taDctoCompra.Marca
AND taArticulo.Proveedor = taDctoCompra.Proveedor
AND taArticulo.GrupoDctoCompra = taDctoCompra.GrupDctoComp
AND taDctoCompra.TipoPedido IN
  (SELECT tipopedido
   FROM tgalma
   WHERE emp = '001'
     AND almacen = '10' ), tgEmpresa,
                           tgAlma,
                           tgMarca
WHERE taArticuloAlma.Emp = '001'
  AND taArticuloAlma.Almacen = '10'
  AND taHistorico.Emp = taArticuloAlma.Emp
  AND taHistorico.Almacen = taArticuloAlma.Almacen
  AND taHistorico.NumIntArticulo = taArticuloAlma.NumInterno
  AND taHistorico.NumIntHistorico = dbo.fn_ICarDMS_LastHistArt ('001', '10', taArticuloAlma.NumInterno, '27-04-2020 00:00:00.000')
  AND taHistorico.StockFinal <> 0
  AND taArticuloAlma.NumInterno = taArticulo.NumInterno
  AND taArticulo.EsConsigna = 0
  AND taArticulo.NoAlmacenada = 0
  AND taArticuloAlma.Emp = tgEmpresa.Emp
  AND taArticuloAlma.Emp = tgAlma.Emp
  AND taArticuloAlma.Almacen = tgAlma.Almacen
  AND taArticulo.Marca = tgMarca.Marca) as requete1
GROUP BY societe,
magasin, 
marque,
Muerto,Rango
ORDER BY societe,
magasin, 
marque,
Muerto,Rango

Si j’exécute la requête directement dans sql management studio, je n'ai pas erreur la requête prend 1.30 min

Pourtant erreur est bien une reponse du serveur sql,

est-ce qu'il y a probleme dans la requete?

Merci d'avance pour vos conseils et retour expérience


guigui69

[SQLpro]

le message complet est-il bien :
La transaction (ID de processus ???!) a été bloquée sur les ressources %2! par un autre processus et a été choisie comme victime. Réexécutez la transaction.

Si oui vous avez été victime d'un verrou mortel dans le cadre d'une transaction concurrente.

Cette requêtes est assez monstrueuse, développé avec les pieds... et je soupçonne les fonctions

• dbo.fn_ICarDMS_dateDiff
• dbo.fn_ICarDMS_GetLastHistMovAlm
• dbo.fn_ICarDMS_LastHistArt

De faire du spaghetti !

Il faudrait :
1) la récrire et éviter toutes les fonctions
2) évitez le COALESECE imbriqués
3) placez les bons index
4) utiliser le niveau d’isolation SNAPSHOT

A +

[guigui69]

Bonsoir,

la requête est extrait directement de notre outil métier. ce n'ai pas moi qui l'ai écrit donc je ne sais pas

Par contre je comprend pas de devrai rencontrer ce type de message dans notre outil métier ou bien aussi depuis sqlmanagement, non ?

donc en fait je ne peux rien faire pour améliorer ceci

[guigui69]

je voulais savoir si dans sql management il y a moyen de savoir dans une requête quelle partie/éléments fait ralentir l’exécution de celle ci?

[SQLpro]

je voulais savoir si dans sql management il y a moyen de savoir dans une requête quelle partie/éléments fait ralentir l’exécution de celle ci?

Il suffit de regarder le plan d'exécution.

A +

[SQLpro]

Bonsoir,

la requête est extrait directement de notre outil métier. ce n'ai pas moi qui l'ai écrit donc je ne sais pas

Par contre je comprend pas de devrai rencontrer ce type de message dans notre outil métier ou bien aussi depuis sqlmanagement, non ?

donc en fait je ne peux rien faire pour améliorer ceci

Si par exemple utiliser le niveau d’isolation SNAPSHOT pour certaines requêtes comme celle-ci.
Si vous êtes en version Enterprise vous pouvez le faire via des guides de plans

A +