Discussion :

[Friedrick]

Bonjour,
Lorsque j'exécute cette store proc, ma page web ne répond plus. J'aimerais savoir s'il existe un moyen d'optimiser cette requête.

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
CREATE TABLE #T_User_Temp(
    [UserCode] [varchar](18) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [NomTable] [char](30) DEFAULT(''),
    [NomColonne] [char](30) DEFAULT(''),
    [RestrictionCreer] [bit] DEFAULT(''),
    [RestrictionMaj] [bit] DEFAULT(''),
    [RestrictionSupp] [bit] DEFAULT(''))
 
INSERT INTO #T_User_Temp 
([UserCode], [UserName], [NomTable]) 
SELECT T_User.UserCode, T_User.UserName, Z_Privilege.NomTable FROM T_User, Z_Privilege WHERE T_User.UserCode<>'JADE'
 
UPDATE #T_User_Temp
SET #T_User_Temp.RestrictionCreer = Z_Privilege.RestrictionCreer,
#T_User_Temp.RestrictionMaj = Z_Privilege.RestrictionMaj,
#T_User_Temp.RestrictionSupp = Z_Privilege.RestrictionSupp
FROM #T_User_Temp INNER JOIN Z_Privilege ON #T_User_Temp.usercode = Z_Privilege.usercode

Merci, Friedrick

[SQLpro]

je ne sais pas si c'est volontaire, mais vous faites un produit cartésiens : (CROSS JOIN)

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
CREATE TABLE #T_User_Temp(
    [UserCode] [varchar](18) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [NomTable] [char](30) DEFAULT(''),
    [NomColonne] [char](30) DEFAULT(''),
    [RestrictionCreer] [bit] DEFAULT(''),
    [RestrictionMaj] [bit] DEFAULT(''),
    [RestrictionSupp] [bit] DEFAULT(''));
 
INSERT INTO #T_User_Temp 
([UserCode], [UserName], [NomTable]) 
SELECT T_User.UserCode, T_User.UserName, Z_Privilege.NomTable 
FROM   T_User
       CROSS JOIN Z_Privilege 
WHERE  T_User.UserCode <>'JADE';
 
CREATE INDEX X ON #T_User_Temp (usercode);
 
UPDATE #T_User_Temp
SET    RestrictionCreer = p.RestrictionCreer,
       RestrictionMaj   = p.RestrictionMaj,
       RestrictionSupp  = p.RestrictionSupp
FROM   #T_User_Temp t
       INNER JOIN Z_Privilege p
             ON t.usercode = p.usercode;

Avec l'index, cela devrait déjà aller plus vite !

A +

[Friedrick]

L'exécution est encore plus longue avec ce code Il faut aussi dire que la base fait 2go de données alors ce n'est pas une petite base de données mais quand même! Une requête ne devrait pas prendre autant de temps que ce la !!

[rberthou]

Le problème est surement sur le produit cartésien. Cela serait étonnant qu'il soit volontaire.

Peux tu nous préciser si ce produit cartésien est volontaire ? (Y a t'il seulement 1 enregistrement dans Z_Privilege ?)

[Friedrick]

Je vais expliquer mais je ne peux pas vraiment montrer d'exemple (données confidentielle).
- T_User contient les données de tout les utilisateurs (264 lignes).
- Dans la table Z_Privilege se retrouve les droits sur les tables. Chaque user à une ligne par table sois : 432 lignes donc au total ça fait beaucoup de ligne (40 765 lignes ).
Voila! Donc dans la table Z_Privilege j'ai 432 lignes pour chaque utilisateur. Le problème est si l'utilisateur n'a jamais eu d'accès il n'est pas dans cette table mais je veux voir tout les users donc je me crée une table temporaire pour faire le lien entre toutes les tables, les privilèges et les users. Si vous avez d'autre question gêné vous pas !

Friedrick

[Friedrick]

Je viens d'avoir une idée qui va peut-être régler mon problème! Au lieu de faire cette table au complet, je vais le faire pour l'utilisateur sélectionné! Je crois que la requête va s'exécuter beaucoup plus rapidement .

Si vous avez d'autres idées ou même une syntaxe possible pour cette requête dites moi le!

Friedrick

[rberthou]

Donc si j'ai bien compris les volumes sont :.
- T_User : 264 lignes
- Z_Privilege : 40 765

Donc la table temporaire créé
=> Produit cartesien => 264 * 40765 = 10 761 960 ( presque 11 millions de lignes d'ou la lenteur)

Je pense que cela est donc preferable je ne connais pas la clef commune au deux tables (j'ai decide que cela et Usercode)

Code :

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

1
2
3
4
5
 
SELECT T_User.UserCode, T_User.UserName, Z_Privilege.NomTable 
FROM   T_User LEFT JOIN Z_Privilege 
                  ON T_User.UserCode = Z_Privilege.UserCode 
WHERE  T_User.UserCode <>'JADE';

[Friedrick]

User: 264
Tables: 432
Donc Z_Privileges: 40 765

Mais j'ai réglé mon problème avec une belle store proc

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
ALTER PROCEDURE [dbo].[WSP_Securite_Fred] 
    @usercode nvarchar(20)
AS
 
CREATE TABLE #T_User_Temp(
    [UserCode] [varchar](18) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [NomTable] [char](30) DEFAULT(''),
    [NomColonne] [char](30) DEFAULT(''),
    [RestrictionCreer] [bit] DEFAULT(''),
    [RestrictionMaj] [bit] DEFAULT(''),
    [RestrictionSupp] [bit] DEFAULT(''));
 
INSERT INTO #T_User_Temp 
([UserCode], [UserName], [NomTable]) 
SELECT T_User.UserCode, T_User.UserName, Z_Privilege.NomTable 
FROM   T_User
       CROSS JOIN Z_Privilege 
WHERE  T_User.UserCode <>'JADE' 
AND T_User.UserCode=@usercode;
 
CREATE INDEX X ON #T_User_Temp (usercode);
 
UPDATE #T_User_Temp
SET    RestrictionCreer = p.RestrictionCreer,
       RestrictionMaj   = p.RestrictionMaj,
       RestrictionSupp  = p.RestrictionSupp
FROM   #T_User_Temp t
       INNER JOIN Z_Privilege p
             ON t.usercode = p.usercode
WHERE t.UserCode=@usercode;
 
select distinct * from #T_User_Temp ORDER BY NomTable

Merci Developpez!

Friedrick