-- As tu essayé :

DECLARE @DAT_PRO DATETIME
SELECT @DAT_PRO = '20070806'

SELECT DISTINCT PROFIL_ID, POSBT.SICOVAM,
POSBT.FAIRVALUE, PRIX.THEO,
POS.FAIRVALUE, POSBT.QUANTITY, POSBT.COD_PORT,
POSBT.COD_DEVISE, @DAT_PRO, GETDATE()

FROM IMP_VAR_DETAIL_POS_BT_CORRIGE POSBT
INNER JOIN REF_PORT_SOURCE_HISTORIQUE RPS
ON POSBT.COD_PORT = RPS.COD_PORT
LEFT OUTER JOIN IMP_SOPH_PRIX PRIX
ON POSBT.SICOVAM = SIC_INSTR
INNER JOIN IMP_VAR_DETAIL_POS_GEN_CORRIGE POS
ON POSBT.COD_PORT = POS.COD_PORT
AND POSBT.SICOVAM = POS.SICOVAM
AND POSBT.QUANTITY = POS.QUANTITY

WHERE RPS.DAT_PRO_UTIL = @DAT_PRO
AND POSBT.DAT_PRO = @DAT_PRO
AND PRIX.DAT_PRO = @DAT_PRO
AND POS.DAT_PRO = @DAT_PRO
AND SOURCE = 'SOPHIS'
AND POSBT.QUANTITY <> 0

-- voire :

DECLARE @DAT_PRO DATETIME
SELECT @DAT_PRO = '20070806'

SELECT *
FROM
(
SELECT DISTINCT PROFIL_ID, POSBT.SICOVAM,
POSBT.FAIRVALUE, PRIX.THEO,
POS.FAIRVALUE, POSBT.QUANTITY, POSBT.COD_PORT,
POSBT.COD_DEVISE, @DAT_PRO, GETDATE()

FROM IMP_VAR_DETAIL_POS_BT_CORRIGE POSBT
INNER JOIN REF_PORT_SOURCE_HISTORIQUE RPS
ON POSBT.COD_PORT = RPS.COD_PORT
LEFT OUTER JOIN IMP_SOPH_PRIX PRIX
ON POSBT.SICOVAM = SIC_INSTR
INNER JOIN IMP_VAR_DETAIL_POS_GEN_CORRIGE POS
ON POSBT.COD_PORT = POS.COD_PORT
AND POSBT.SICOVAM = POS.SICOVAM
AND POSBT.QUANTITY = POS.QUANTITY

WHERE RPS.DAT_PRO_UTIL = @DAT_PRO
AND POSBT.DAT_PRO = @DAT_PRO
AND PRIX.DAT_PRO = @DAT_PRO
AND POS.DAT_PRO = @DAT_PRO
AND SOURCE = 'SOPHIS'
)
WHERE NOT POSBT.QUANTITY = 0 -- ou encore POSBT.QUANTITY <> 0



En outre, y a t-il des index sur :
Table colonnes
------------------------------- ----------------------
IMP_VAR_DETAIL_POS_BT_CORRIGE COD_PORT, SICOVAM, QUANTITY
IMP_VAR_DETAIL_POS_BT_CORRIGE DAT_PRO
IMP_VAR_DETAIL_POS_BT_CORRIGE SICOVAM

IMP_SOPH_PRIX DAT_PRO
IMP_SOPH_PRIX SIC_INSTR

IMP_VAR_DETAIL_POS_GEN_CORRIGE COD_PORT, SICOVAM, QUANTITY
IMP_VAR_DETAIL_POS_GEN_CORRIGE DAT_PRO

REF_PORT_SOURCE_HISTORIQUE DAT_PRO_UTIL
REF_PORT_SOURCE_HISTORIQUE COD_PORT


?

A +



Brigitte a écrit :
> Bonjour,
>
> Comment peut-on expliquer que ces deux requêtes ont un temps d'exécution
> totalement différent :
>
> SELECT DISTINCT PROFIL_ID, POSBT.SICOVAM, POSBT.FAIRVALUE, PRIX.THEO,
> POS.FAIRVALUE, POSBT.QUANTITY, POSBT.COD_PORT, POSBT.COD_DEVISE, '20070806',
> GETDATE()
> FROM IMP_VAR_DETAIL_POS_BT_CORRIGE POSBT, REF_PORT_SOURCE_HISTORIQUE RPS,
> IMP_SOPH_PRIX PRIX, IMP_VAR_DETAIL_POS_GEN_CORRIGE POS
> WHERE RPS.DAT_PRO_UTIL = '20070806'
> AND POSBT.DAT_PRO = '20070806'
> AND PRIX.DAT_PRO = '20070806'
> AND POS.DAT_PRO = '20070806'
> AND SOURCE = 'SOPHIS'
> AND POSBT.COD_PORT = RPS.COD_PORT
> AND POSBT.SICOVAM *= SIC_INSTR
> AND POSBT.QUANTITY <> 0
> AND POSBT.COD_PORT = POS.COD_PORT
> AND POSBT.SICOVAM = POS.SICOVAM
> AND POSBT.QUANTITY = POS.QUANTITY
>
> DECLARE @DAT_PRO varchar(8)
> SELECT @DAT_PRO = '20070806'
>
> SELECT DISTINCT PROFIL_ID, POSBT.SICOVAM, POSBT.FAIRVALUE, PRIX.THEO,
> POS.FAIRVALUE, POSBT.QUANTITY, POSBT.COD_PORT, POSBT.COD_DEVISE, '20070806',
> GETDATE()
> FROM IMP_VAR_DETAIL_POS_BT_CORRIGE POSBT, REF_PORT_SOURCE_HISTORIQUE RPS,
> IMP_SOPH_PRIX PRIX, IMP_VAR_DETAIL_POS_GEN_CORRIGE POS
> WHERE RPS.DAT_PRO_UTIL = @DAT_PRO
> AND POSBT.DAT_PRO = @DAT_PRO
> AND PRIX.DAT_PRO = @DAT_PRO
> AND POS.DAT_PRO = @DAT_PRO
> AND SOURCE = 'SOPHIS'
> AND POSBT.COD_PORT = RPS.COD_PORT
> AND POSBT.SICOVAM *= SIC_INSTR
> AND POSBT.QUANTITY <> 0
> AND POSBT.COD_PORT = POS.COD_PORT
> AND POSBT.SICOVAM = POS.SICOVAM
> AND POSBT.QUANTITY = POS.QUANTITY
>
> merci
>
> Bonne journée
>
> Brigitte


--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************