Optimisation du SQL stored procedure [Résolu]

[ttttnht]

Bonjour,

J'ai une procedure stockée suivante, le seul problème ceci prend du temps à exécuter (Sybase 12.5) en moyen 10 secondes.

je souhaite savoir si j'aurai le même résultat si je mets en commentaires ces lignes limitées par les tags :
--BEGIN TAG

--END TAG


Merci d'avance

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
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
120
121
122
123
124
CREATE procedure GetCorrelationFXIR
(
    @Alias         varchar(10),
    @AsOfDate   datetime,
    @ListName   varchar(5000)
)
AS
Begin
--declare the delimeter between each element
    DECLARE @Delimeter char(1)
    SET @Delimeter = ','
--Parse the string and insert each name into the @tblName table
    CREATE TABLE #tempInstr (Name varchar(50))
    DECLARE @Name varchar(50)
    DECLARE @StartPos int
 
    WHILE @ListName NOT IN ('', NULL)
    BEGIN
        SET @StartPos = CHARINDEX(@Delimeter, @ListName)
        IF @StartPos < 0 SET @StartPos = 0
        IF @StartPos > 0
        BEGIN
            SET @Name = SUBSTRING(@ListName, 1, @StartPos - 1)
            SET @ListName = SUBSTRING(@ListName, @StartPos + 1,   LEN(@ListName) - @StartPos)
        END
        ELSE
        BEGIN
            SET @Name = @ListName
            SET @ListName = ''
        END
        SET @Name = LTRIM(RTRIM(@Name))
        INSERT #tempInstr (Name) VALUES(@Name)
    END
 
------------------------
    SELECT DISTINCT
        my.M_CCBID    AS Correlation,
        mx.M_INSTR1   AS LabelOne,
        mx.M_INSTR2   AS LabelTwo
 
    FROM
        COR_TM_DBF ct JOIN COR_TS_DBF ci   ON ct.M_INT_NB  = ci.M_INT_NB
                 JOIN COR_TS_DBF ci2  ON ci.M_PATHI   = ci2.M_PATHI AND ct.M_INT_NB = ci2.M_INT_NB
                 JOIN MPX_CORR_DBF mx ON ci2.M_MLABEL = mx.M_INSTR1
                 JOIN MPY_CORR_DBF my ON mx.M__INDEX_ = my.M__INDEX_
                 JOIN OM_MAT_DBF   m  ON my.M_MATCOD  = m.M_CODE
    WHERE
        ct.M_LABEL    = 'IR vs FX' 
        AND ci.M_MLABEL   = 'FX' 
        AND NOT (ci2.M_MLABEL = 'FX') 
 
        AND mx.M_TYP1     = 'Contract' 
        AND mx.M_TYP2     = 'Index' 
        AND lower(m.M_LABEL) = 'o/n' 
 
 
   -- ensure second instrument in correlation is in the right group
--BEGIN TAG
        AND mx.M_INSTR2 IN
        (
            SELECT
                ci2.M_MLABEL
            FROM
                COR_TM_DBF ct JOIN COR_TS_DBF ci  ON ct.M_INT_NB = ci.M_INT_NB
                     JOIN COR_TS_DBF ci2 ON ci.M_PATHI  = ci2.M_PATHI AND ct.M_INT_NB = ci2.M_INT_NB
            WHERE
                 ct.M_LABEL  = 'IR vs FX' 
                 AND ci.M_MLABEL = 'Rates' 
                 AND NOT (ci2.M_MLABEL IN ('Rates', 'Indexes') )
        ) 
--END TAG
 
        AND mx.M__ALIAS_ = @Alias 
        AND mx.M__DATE_  = @AsOfDate
        AND mx.M_INSTR1  IN (SELECT Name FROM #tempInstr) 
        AND mx.M_INSTR2  IN (SELECT Name FROM #tempInstr) 
 
union
 
    SELECT DISTINCT
        my.M_CCBID    AS Correlation,
        mx.M_INSTR1   AS LabelOne,
        mx.M_INSTR2   AS LabelTwo
 
    FROM
        COR_TM_DBF ct JOIN COR_TS_DBF ci   ON ct.M_INT_NB  = ci.M_INT_NB
                 JOIN COR_TS_DBF ci2  ON ci.M_PATHI   = ci2.M_PATHI AND ct.M_INT_NB = ci2.M_INT_NB
                 JOIN MPX_CORR_DBF mx ON ci2.M_MLABEL = mx.M_INSTR1
                 JOIN MPY_CORR_DBF my ON mx.M__INDEX_ = my.M__INDEX_
                 JOIN OM_MAT_DBF   m  ON my.M_MATCOD  = m.M_CODE
    WHERE
        ct.M_LABEL    = 'IR vs FX' 
        AND ci.M_MLABEL   = 'Rates' 
        AND NOT (ci2.M_MLABEL IN ('Rates', 'Indexes') ) 
 
        AND mx.M_TYP1     = 'Index' 
        AND mx.M_TYP2     = 'Contract' 
        AND lower(m.M_LABEL) = 'o/n' 
 
   -- ensure second instrument in correlation is in the right group
--BEGIN TAG
        AND mx.M_INSTR2 IN
        (
            SELECT
                ci2.M_MLABEL
            FROM
                COR_TM_DBF ct JOIN COR_TS_DBF ci  ON ct.M_INT_NB = ci.M_INT_NB
                     JOIN COR_TS_DBF ci2 ON ci.M_PATHI  = ci2.M_PATHI AND ct.M_INT_NB = ci2.M_INT_NB
            WHERE
                ct.M_LABEL = 'IR vs FX' 
                AND ci.M_MLABEL   = 'FX' 
                AND NOT (ci2.M_MLABEL = 'FX')
        ) 
--END TAG
 
        AND mx.M__ALIAS_ = @Alias 
        AND mx.M__DATE_  = @AsOfDate
        AND mx.M_INSTR1  IN (SELECT Name FROM #tempInstr) 
        AND mx.M_INSTR2  IN (SELECT Name FROM #tempInstr) 
 
    ORDER BY LabelOne, LabelTwo
------------------------
    DROP TABLE #tempInstr
End

[mpeppler]

Il est à priori difficile de dire si les sections entre BEGIN/END TAG sont superflues sans mieux connaitre la structure des tables, et surtout les indexes.

Pour la perf - il faudrait voir un showplan et statistics IO pour éventuellement avoir une idée - mais là aussi il faudrait connaitre la structure des indexes sur chacune des tables, et la volumétries des tables pour pouvoir donner un avis.

Michael