Bonjour,

J'utilise un linked server Oracle pour exécuter la requête suivante








----------------------------------------------------------------------------------
-- GROUPE HSBC - Projet Référentiel Valeurs
----------------------------------------------------------------------------------
-- AUTEUR : D. KIERAN
-- DATE CREATION : 23/11/2007
-- DESCRIPTION : Procédure de transfert du contenu des vues RefVal vers
des tables Horizon

-- VERSION : 1.0
----------------------------------------------------------------------------------
-- MODIFICATIONS : Le 27/11/2007 par D. KIERAN - Faciliter la connexion
avec le serveur Horizon
----------------------------------------------------------------------------------

CREATE PROCEDURE REFVAL.SP_HORIZON_TABLES_VIEW_TRANSFER
AS


SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_STOCK() FROM
DUAL');


INSERT OPENQUERY (Horizon, 'SELECT MNEMO_GL_SLC,
ISIN_CODE,
MIC_CODE,
RIC_REUTER,
CURRENCY,
OBMS_ID,
TICK_SIZE_OBMS,
SOPHIS_CODE,
ISO_MARKET_CODE FROM HORIZON_STOCK')
Select MNEMO_GL_SLC,
ISIN_CODE,
MIC_CODE,
RIC_REUTER,
CURRENCY,
OBMS_ID,
TICK_SIZE,
CODE_SOPHIS,
MARKET_ISO
FROM
V_HORIZON_ACTIONS;


SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_INDEX() FROM
DUAL');

INSERT OPENQUERY (Horizon, 'SELECT MNEMO_GL_SLC,
ISIN_CODE,
MIC_CODE,
RIC_REUTER,
CURRENCY,
OBMS_ID,
TICK_SIZE_OBMS,
SOPHIS_CODE,
ISO_MARKET_CODE FROM HORIZON_INDEX')
SELECT
MNEMO_GL_SLC,
ISIN_CODE,
MIC_CODE,
RIC_REUTER,
CURRENCY,
OBMS_ID,
TICK_SIZE,
CODE_SOPHIS,
MARKET_ISO
FROM
V_HORIZON_INDICES

SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_OPTION() FROM
DUAL');

INSERT OPENQUERY (Horizon, 'SELECT
MNEMO_GL_SLC,
MATURITY,
STRIKE,
CALL_PUT,
STYLE,
MIC_CODE,
RIC_SJ,
CURRENCY,
OBMS_ID,
TICK_SIZE_OBMS FROM HORIZON_OPTION')
SELECT
MNEMO_SLC,
DATE_ECHEANCE,
STRIKE,
CALL_PUT,
STYLE,
MIC_CODE,
RIC_SJ,
CURRENCY,
OBMS_ID,
TICK_SIZE
FROM
V_HORIZON_OPTIONS_GL

SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_FUTURE() FROM
DUAL');

INSERT OPENQUERY(Horizon,'SELECT MNEMO_GL_SLC,
MATURITY,
MIC_CODE,
RIC_REUTER,
QUOTITE,
CURRENCY,
OBMS_ID,
TICK_SIZE_OBMS,
ISO_MARKET_CODE FROM HORIZON_FUTURE')
SELECT
MNEMO_GL_SLC,
MATURITY,
MIC_CODE,
RIC_REUTER,
QUOTITE,
CURRENCY,
OBMS_ID,
TICK_SIZE,
MARKET_ISO
FROM
V_HORIZON_FUTURS
select 1
SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_POPULATE_TABLES() FROM DUAL');
GO
dont la durée d'exécution = 16mn.

Auriez-vous une idée?

Merci

Bon week-end

Brigitte

On 30 nov, 15:37, Brigitte <Brigi...@discussions.microsoft.com> wrote:
> Bonjour,
>
> J'utilise un linked server Oracle pour exécuter la requête suivante
>
> ----------------------------------------------------------------------------------
> -- GROUPE HSBC - Projet Référentiel Valeurs
> ----------------------------------------------------------------------------------
> -- AUTEUR : D. KIERAN
> -- DATE CREATION : 23/11/2007
> -- DESCRIPTION : Procédure de transfert du contenu des vues RefVal vers
> des tables Horizon
>
> -- VERSION : 1.0
> ----------------------------------------------------------------------------------
> -- MODIFICATIONS : Le 27/11/2007 par D. KIERAN - Faciliter la connexion
> avec le serveur Horizon
> ----------------------------------------------------------------------------------
>
> CREATE PROCEDURE REFVAL.SP_HORIZON_TABLES_VIEW_TRANSFER
> AS
>
> SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_STOCK() FROM
> DUAL');
>
> INSERT OPENQUERY (Horizon, 'SELECT MNEMO_GL_SLC,
> ISIN_CODE,
> MIC_CODE,
> RIC_REUTER,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE_OBMS,
> SOPHIS_CODE,
> ISO_MARKET_CODE FROM HORIZON_STOCK')
> Select MNEMO_GL_SLC,
> ISIN_CODE,
> MIC_CODE,
> RIC_REUTER,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE,
> CODE_SOPHIS,
> MARKET_ISO
> FROM
> V_HORIZON_ACTIONS;
>
> SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_INDEX() FROM
> DUAL');
>
> INSERT OPENQUERY (Horizon, 'SELECT MNEMO_GL_SLC,
> ISIN_CODE,
> MIC_CODE,
> RIC_REUTER,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE_OBMS,
> SOPHIS_CODE,
> ISO_MARKET_CODE FROM HORIZON_INDEX')
> SELECT
> MNEMO_GL_SLC,
> ISIN_CODE,
> MIC_CODE,
> RIC_REUTER,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE,
> CODE_SOPHIS,
> MARKET_ISO
> FROM
> V_HORIZON_INDICES
>
> SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_OPTION() FROM
> DUAL');
>
> INSERT OPENQUERY (Horizon, 'SELECT
> MNEMO_GL_SLC,
> MATURITY,
> STRIKE,
> CALL_PUT,
> STYLE,
> MIC_CODE,
> RIC_SJ,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE_OBMS FROM HORIZON_OPTION')
> SELECT
> MNEMO_SLC,
> DATE_ECHEANCE,
> STRIKE,
> CALL_PUT,
> STYLE,
> MIC_CODE,
> RIC_SJ,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE
> FROM
> V_HORIZON_OPTIONS_GL
>
> SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_TRUNCATE_FUNCTION_FUTURE() FROM
> DUAL');
>
> INSERT OPENQUERY(Horizon,'SELECT MNEMO_GL_SLC,
> MATURITY,
> MIC_CODE,
> RIC_REUTER,
> QUOTITE,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE_OBMS,
> ISO_MARKET_CODE FROM HORIZON_FUTURE')
> SELECT
> MNEMO_GL_SLC,
> MATURITY,
> MIC_CODE,
> RIC_REUTER,
> QUOTITE,
> CURRENCY,
> OBMS_ID,
> TICK_SIZE,
> MARKET_ISO
> FROM
> V_HORIZON_FUTURS
> select 1
> SELECT * FROM OPENQUERY(Horizon, 'SELECT DK_POPULATE_TABLES() FROM DUAL');
> GO
> dont la durée d'exécution = 16mn.
>
> Auriez-vous une idée?
>
> Merci
>
> Bon week-end
>
> Brigitte

1) utilisez plutot des requêtes de serveur lié que OPENQUERY...
2) voyez si vous pouvez utiliser des REMOTE JOIN.

A +