Problème Vue SQL serveur 2000

**verryvalentino** · 13/05/2012, 14h28

Bonjour à tous,

Je rencontre un problème sur une vue SQL Server 2000. En effet, j'utise une vue définissant différentes colonnes dans lesquelles viennent s'incérer les résulats obtenus à partir d'une fonction. J'ai constaté que le temps d'exécution de la fonction est assez lente. Aussi, je souhaiterai savoir s'il y a un moyen pour accélérer la lecture de la vue ou pour la rendre plus optimale d'une part. Et d'autre part, je souhaiterai savoir si le problème de lenteur ne serait pas lié au code ci - dessous:

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
114
115
116
117
118
119
CREATE VIEW SCORECARD_EURO 
 
AS         
SELECT 
        m1.entry_id AS ENTRY_ID1, 
        m2.entry_id AS ENTRY_ID2, 
        cross_scr.ENTRY_ID AS ENTRY_ID3, 
        m1.title AS TITLE1, 
        m2.title AS TITLE2, 
        m3.title AS TITLE3, 
        cross_scr.ROW_ID AS ROW_ID, 
        scorecard_row.position AS ROW_POSITION, 
        scorecard_rowgroup.position AS ROWGROUP_POSITION, 
        CASE 
                WHEN scorecard_row.reserved = 'EDITABLE' THEN scorecard.col_0003 
                ELSE scorecard_row.label 
        END        AS ROW_LABEL, 
          scorecard_rowgroup.label AS ROWGROUP_LABEL, 
          scorecard_row.reserved AS RESERVED, 
        m3.COL_CURRENCY AS CURRENCY_ID, 
        CURRENCY.MULTIPLIER, 
        CURRENCY.LABEL AS CURRENCY_LABEL, 
        m1.COL_NATURE_L1 AS NATURE_L1, 
        m3.COL_NATURE_L3 AS NATURE_L3, 
        nature_level3.GROUPNAME AS NATURE_L3_CLASS, 
        m3.COL_NATURE_FINE AS NATURE_FINE, 
        m3.COL_MATURITY AS MATURITY, 
        m3.COL_PILOT_ENTITY AS PILOT_ENTITY, 
        m1.COL_PILOT_ENTITY AS PILOT_ENTITY_LEVEL1, 
        m3.COL_LEGAL_ENTITY AS LEGAL_ENTITY, 
        m3.COL_USED_METHODOLOGY AS USED_METHODOLOGY, 
        m3.COL_TRACING AS TRACING, 
        m3.COL_SC_VALIDATED AS SC_VALIDATED, 
        org_departments.groupname AS POL, 
        org_departments_level1.groupname AS POL_LEVEL1, 
        scorecard.history_date AS HISTORY_DATE, 
        m3.COL_SC_COMM AS SC_COMM, 
        m3.COL_SC_COMM2 AS SC_COMM2, 
        m3.COL_RESP_EOG AS RESP_EOG, 
        m3.COL_PLANNING AS PLANNING, 
        m3.COL_STOCK AS STOCK, 
        m3.COL_TERRITORY AS TERRITORY, 
        m3.COL_MINORITY_SHAREHOLDER AS MINORITY_SHAREHOLDER, 
        m3.COL_BUDGET_PROCEDURE AS BUDGET_PROCEDURE, 
        m3.COL_IMPACT, 
        CASE 
                WHEN cross_scr.ROW_ID <= 49 OR cross_scr.ROW_ID=99 OR cross_scr.ROW_ID=100 THEN 0 
                ELSE 49 
        END AS SC_PART,         
        CASE 
                WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id<>64 AND cross_scr.row_id <> 67 
                 AND cross_scr.row_id <> 30 AND cross_scr.row_id <> 39 AND cross_scr.row_id<>79 AND cross_scr.row_id <> 88 
                 THEN 
                        ISNULL(SCORECARD.COL_0036*(ISNULL(currency_management.COL_0036,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                        ISNULL(SCORECARD.COL_0086*(ISNULL(currency_management.COL_0086,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                        ISNULL(SCORECARD.COL_0136*(ISNULL(currency_management.COL_0136,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                        ISNULL(SCORECARD.COL_0186*(ISNULL(currency_management.COL_0186,1)*ISNULL(currency.multiplier,1))/1000000,0) 
 
                WHEN cross_scr.row_id = 30 OR cross_scr.row_id = 39 OR cross_scr.row_id=79 OR cross_scr.row_id = 88 THEN 
                        ISNULL(COL_0001,0) 
                ELSE 0 
        END 
        AS COL_0001, 
        CASE 
                WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id<>64 AND cross_scr.row_id <> 67 
                 AND cross_scr.row_id <> 30 AND cross_scr.row_id <> 39 AND cross_scr.row_id<>79 AND cross_scr.row_id <> 88 
                 THEN 
                        ISNULL( 
                                ISNULL(SCORECARD.COL_0036*(ISNULL(currency_management.COL_0036,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                                ISNULL(SCORECARD.COL_0086*(ISNULL(currency_management.COL_0086,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                                ISNULL(SCORECARD.COL_0136*(ISNULL(currency_management.COL_0136,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                                ISNULL(SCORECARD.COL_0186*(ISNULL(currency_management.COL_0186,1)*ISNULL(currency.multiplier,1))/1000000,0) 
 
                        ,0) 
                        - 
                        ( 
                                SELECT 
                                ISNULL(SUM( 
                                        ISNULL( 
                                                ISNULL(SCORECARD_49.COL_0036*(ISNULL(currency_management.COL_0036,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                                                ISNULL(SCORECARD_49.COL_0086*(ISNULL(currency_management.COL_0086,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                                                ISNULL(SCORECARD_49.COL_0136*(ISNULL(currency_management.COL_0136,1)*ISNULL(currency.multiplier,1))/1000000,0)+ 
                                                ISNULL(SCORECARD_49.COL_0186*(ISNULL(currency_management.COL_0186,1)*ISNULL(currency.multiplier,1))/1000000,0) 
 
 
                                        ,0)        ),0) 
                                FROM 
                                        SCORECARD        as SCORECARD_49 
                                        inner join maindata on maindata.entry_id=scorecard_49.entry_id   
                                        left outer join currency_management on currency_management.currency_id=maindata.col_currency 
                                        inner join currency on currency.id=maindata.col_currency 
                                WHERE SCORECARD_49.entry_id = cross_scr.entry_id AND SCORECARD_49.row_id=cross_scr.row_id+49 AND SCORECARD_49.row_id<>99 AND SCORECARD_49.row_id<>100 
                        ) 
                WHEN cross_scr.row_id = 30 OR cross_scr.row_id = 39 OR cross_scr.row_id=79 OR cross_scr.row_id = 88 THEN 
                        ISNULL(COL_0002,0) 
                ELSE 0 
        END AS COL_0002, 
        SCORECARD.COL_0003 AS COL_0003,         
        SCORECARD.col_0006*CASE WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id <> 64 AND cross_scr.row_id <> 67 THEN (ISNULL(currency_management.col_0006,1)*ISNULL(currency.multiplier,1))/1000000 ELSE 1 END  AS COL_0006, 
        SCORECARD.COL_0016*CASE WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id <> 64 AND cross_scr.row_id <> 67 THEN (ISNULL(currency_management.COL_0016,1)*ISNULL(currency.multiplier,1))/1000000 ELSE 1 END  AS COL_0016, 
        SCORECARD.COL_0026*CASE WHEN cross_scr.row_id <> 15 AND cross_scr.row_id <> 18 AND cross_scr.row_id <> 64 AND cross_scr.row_id <> 67 THEN (ISNULL(currency_management.COL_0026,1)*ISNULL(currency.multiplier,1))/1000000 ELSE 1 END  AS COL_0026 
 
 
FROM 
        (select 
    distinct scorecard.entry_id as entry_id,scorecard_row.row_id as row_id 
        from 
    scorecard_row ,scorecard) cross_scr 
  left outer join scorecard on cross_scr.entry_id=scorecard.entry_id and cross_scr.row_id = scorecard.row_id 
        inner join maindata m3 on m3.entry_id=cross_scr.entry_id  and m3.deleted=0 
        left outer join currency_management on currency_management.currency_id=m3.col_currency 
        inner join currency on currency.id=m3.col_currency 
        left outer join maindata m2 on m2.entry_id = m3.parent_entry_id 
        left outer join maindata m1 on m1.entry_id = m2.parent_entry_id 
        left outer join scorecard_row on scorecard_row.row_id = cross_scr.row_id 
        left outer join scorecard_rowgroup on scorecard_rowgroup.group_id = scorecard_row.group_id 
        left outer join org_departments on m3.col_pilot_entity = org_departments.id 
        left outer join org_departments org_departments_level1 on m1.col_pilot_entity = org_departments_level1.id 
        left outer join nature_level3 on nature_level3.id = m3.col_nature_l3

Je fais appel à la vue dans la fonction qui permet d'effectuer différents calculs dont les résultats sont censés être incérés dans la vue ci - dessus:

Code :

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

1
2
FROM scorecard_euro WHERE row_id = @row_id and entry_id3 = @entry_id

J'ai essayé d'utiliser l'expression "WITH (NOEXPAND)" au niveau de la fonction, mais SQL Server 2000 ne l'accepte pas.

Code :

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

FROM scorecard_euro WITH (NOEXPAND) WHERE row_id = @row_id and entry_id3 = @entry_id

Aussi je sollicite votre aide.

Merci d'avance pour votre aide.

**SQLpro** · 14/05/2012, 10h05

Une fonction est TOUJOURS lente car elle n'est pas optimisable. En effet lorsque vous faite des requêtes vous êtes dans un univers ensembliste et le SGBDR peut traiter les données en volume en profitant des index et du parallélisme afin d'accélérer de façon très importante le traitement.

Pour optimiser une telle requête, il suffit simplement de la supprimer en réintégrant ce quelle est censée faire sous forme ensembliste, c'est à dire des requêtes.

A +

Problème Vue SQL serveur 2000

MS SQL Server

Discussions similaires

Partager

Partager