Discussion :

[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 :

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