[ASE]optimisation d'une proc stock trés lente

[461219]

Bonjour

Je viens solliciter votre avis d'expert. J'ai une proc stock qui calcule des retro et elle met plus de 4h. Je voudrai l'optimiser et donc j'aurai besoin de votre avis .
la voici

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
 
CREATE PROCEDURE dbo.test
(                 
--@IdFond numeric(7, 0),                 
@IdSA numeric(7, 0),                 
@date smalldatetime                 
)                 
AS                  
 
declare @usdeur numeric(20,10)                
 
BEGIN                  
 
--drop table ##temp           
 
SELECT H.IdHierarchy, H.IdUdl, H.SharesNb  INTO ##temp           
FROM TRetroHierarchy H, TRetroSubAccountCompo C           
WHERE C.IdSubAccount=@IdSA            
AND C.date= (SELECT MAX(date) FROM TRetroSubAccountCompo WHERE IdSubAccount=@IdSA AND date<=@date)                   
AND H.IdCompo=C.IdCompo            
AND H.SharesNb<>0            
 
SELECT @usdeur=InstrBid                 
FROM TInstrValHisto                 
WHERE IdInstr=517 -- USD_EUR                
AND InstrValDate=(SELECT MAX(InstrValDate) FROM TInstrValHisto WHERE IdInstr=517 AND InstrValDate<=@date)                
 
SELECT @IdSA AS IdSubAccount, H.IdUdl, H.SharesNb AS Position, hist.UdlNAV AS NAV, convert(numeric(10,5),@usdeur/usdxxxHisto.InstrBid) AS forex,f.RetroFees, r.IdFundConfig    
FROM ##temp H, TUdlNAVHisto hist, TRetroFundInSubAccountConfig conf, TRetroRate r, TRetroRateFloors f, TUdl u, TInstrument usdxxx  , TInstrValHisto usdxxxHisto                  
WHERE hist.IdUdl=H.IdUdl            
AND hist.IdTpNAV=3 
AND hist.UdlNAVDate=(SELECT MAX(UdlNAVDate) FROM TUdlNAVHisto WHERE IdUdl=H.IdUdl AND IdTpNAV=3  AND UdlNAVDate<=@date)                   
AND conf.IdSubAccount=@IdSA            
AND H.IdUdl=conf.IdUdl                   
AND conf.IdFundConfig=r.IdFundConfig                   
AND r.ValidityStartDate=(SELECT MAX(ValidityStartDate) FROM TRetroRate WHERE IdFundConfig=r.IdFundConfig AND ValidityStartDate<=@date)                   
AND r.IdRetroRate=f.IdRetroRate                   
AND f.FloorAmount=(SELECT MAX(FloorAmount) FROM TRetroRateFloors WHERE IdRetroRate=f.IdRetroRate AND FloorAmount<=H.SharesNb*hist.UdlNAV)                   
AND u.IdUdl=H.IdUdl      
AND usdxxx.IdTpVal=5      
AND SUBSTRING(usdxxx.InstrMDSMnemo,5,CHAR_LENGTH(usdxxx.InstrMDSMnemo))=u.UdlNAVCurr      
AND usdxxx.IdInstr=usdxxxHisto.IdInstr      
AND usdxxxHisto.InstrValDate=(SELECT MAX(InstrValDate) FROM TInstrValHisto WHERE IdInstr=usdxxxHisto.IdInstr AND InstrValDate<=@date)      
 
END
GO

je fais appel à cette même proc stock sur 2 dates par ex 01/01/2006 et 01/05/2006 sur les 3 SA(comptes) un nombre incalculable de fois dans mon code puisuqe je traite 1 SA à la fois et une date à la fois en incrementant dans le code la date.

Ce que je voudrai faire :
supposons j'ai mes

Code :

1
2
3
 
mes 3 SA :(3,4,5) 
mes dates :02/05/2006 et 03/09/2006

comment éviter les appels multiples et faire un seul appel qui va boucler sur les 3 SA,les dates etc et sortir le résultat.

merci

[orafrance]

c'est pas de l'oracle ça

sinon, tu remplace :

Code :

=@IdSA

par :

Code :

IN (@listeidSA)

[461219]

En effet c'est pas d'loracle c'est du sybase mais je pense que la maniére d'ecrire une proc est la même à quelques nuances prés.

[pinocchio]

Envoyé par orafrance

Code :

IN (@listeidSA)

Dans ces cas là, le PL/SQL ne traduit-il pas le @ListeidSA en chaine de caractères?
cdt
Pinocchio

[orafrance]

Envoyé par 461219

En effet c'est pas d'loracle c'est du sybase mais je pense que la maniére d'ecrire une proc est la même à quelques nuances prés.

bah non justement

je déplace le sujet

Envoyé par pinocchio

Dans ces cas là, le PL/SQL ne traduit-il pas le @ListeidSA en chaine de caractères?
cdt
Pinocchio

SYBASE propose peut-être un type de données plus intéressant...

[461219]

il ya ça non seulement mais je pense que jdevrai aussi faire une boucle for pour recuperer la liste des forex avant de boucler à nouveau sur les résultats obtenus à partir de la table temporaire ##temp

[mpeppler]

La technique classique consiste à créer une table temporaire en dehors de la proc stockée, et d'y mettre les valeurs sur lesquels on veut "boucler".

Par exemple:

Code :

1
2
3
4
5
 
CREATE TABLE #IdSA(IdSA numeric(7,0)
INSERT #IdSA values(1)
etc.
exec ma_proc...

et dans ma_proc on fait une jointure de plus:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT H.IdHierarchy
     , H.IdUdl
     , H.SharesNb 
  INTO #temp           
  FROM TRetroHierarchy H
     , TRetroSubAccountCompo C
     , #IdSA d
 WHERE C.IdSubAccount=d.IdSA -- @IdSA
   AND C.date= (SELECT MAX(date) FROM TRetroSubAccountCompo 
                 WHERE IdSubAccount=d.IdSA AND date<=@date)                   
   AND H.IdCompo=C.IdCompo            
   AND H.SharesNb<>0

En principe il doit être possible de combiner les deux paramètres (@date et @IdSA) dans une même table tempo, mais cela va dépendre de la structure des tables, des données, etc.

Michael

[461219]

et pour le @date au lieu de faire x fois les aller retours pour chaque date est ce que je peux faire and date beetween d1 et d2 et aprés boucler sur les résultats obtenus de la premiere jointure

[mpeppler]

C'est possible qu'un BETWEEN soit utilisable - mais cela dépend des données, de la structure des tables, et du résultat désiré. A priori (et vu de très loins) cela ne me semble pas être le cas ici parce qu'on ne cherche pas des données sur une période mais pour des dates précises.

Michael