[Optimisation] Trop longue exécution

Version imprimable

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

Code:

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

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

Code:

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 +

07/02/2008, 19h44
Friedrick

L'exécution est encore plus longue avec ce code :aie: 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 !!
08/02/2008, 10h29
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 ?)
08/02/2008, 14h32
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 :aie:(40 765 lignes 8O).
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
08/02/2008, 14h47
Friedrick

:king: 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 :aie:.

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

Friedrick

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:

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';

User: 264
Tables: 432
Donc Z_Privileges: 40 765

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

Code:

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