我在下面写了一个SQL脚本，但运行速度有问题。在“使用销售数据更新购买情况”一节之前，一切都很好。更新@Results表需要很长时间。有什么办法可以加快速度吗？
USE [IV7]
GO
/*------------------------------------------------------------------------------------- -----------------
Declare all variables
--------------------------------------------------------------------------------------- ---------------*/
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
DECLARE @l_Section VARCHAR(100)
DECLARE @Debug CHAR(1)
DECLARE @Worktable TABLE
(
PortfolioID VARCHAR(20)
, SettlementDate DATETIME
, ContractDate DATETIME
, BrokerCode VARCHAR(100)
, [Broker] VARCHAR(100)
, Currency CHAR(3)
, TransCode CHAR(3)
, TransactionType CHAR(3)
, Nominal DECIMAL(22,3)
, ForwardRate DECIMAL(22,8)
, SpotRate DECIMAl(22,8)
, DealID VARCHAR(20) -- ThinkFolio ID
, TranID VARCHAR(20)
, TranType CHAR(3)
, RKSTranID VARCHAR(16) -- This is the RKS transaction number. It is unique per transaction in RKS, but multi-leg transactions will have same value, allowing us to link them together (e.g. FX s, etc)
, USDSPOTRate FLOAT
, BuyNominalUSD DECIMAL(22,3)
)
DECLARE @Results TABLE
(
PortfolioID VARCHAR(20)
, SettlementDate DATETIME
, ContractDate DATETIME
, BrokerCode VARCHAR(100)
, [Broker] VARCHAR(100)
, BuyCurrency CHAR(3)
, BuyNominal DECIMAL(22,3)
, SellCurrency CHAR(3)
, SellNominal DECIMAL(22,3)
, MarketValue DECIMAL(22,3)
, ForwardRate DECIMAL(22,8)
, SpotRate DECIMAL(22,8)
, DealID VARCHAR(20)
, RKSTranID VARCHAR(16) -- Not used for final output
, BuyNominalUSD DECIMAL(22,3)
)
DECLARE @FX TABLE
(
RateDate DATETIME
, BaseCCY CHAR(3)
, RateCCY CHAR(3)
, MDInd CHAR(1)
, Rate FLOAT
, RateType VARCHAR(10)
)
/*--------------------------------------------------------------------------------------------------------------------------------
Set default values
--------------------------------------------------------------------------------------------------------------------------------*/
SET @l_Section = Setting Default Values
SET @StartDateTime = 2011-04-01 00:00:00.000
SET @EndDateTime = 2012-03-31 23:59:59.000
SET @Debug = Y
IF @Debug = Y
BEGIN
SELECT @l_Section
SELECT @StartDateTime AS StartDate, @EndDateTime AS EndDate
END
/*--------------------------------------------------------------------------------------------------------------------------------
Insert into FX
--------------------------------------------------------------------------------------------------------------------------------*/
INSERT INTO @FX
SELECT * FROM fn_Generic_FXRates ( 2011-04-01 , 2012-03-31 , D , SPOT , USD , 999 )
/*--------------------------------------------------------------------------------------------------------------------------------
Insert values into @WorkTable
--------------------------------------------------------------------------------------------------------------------------------*/
SET @l_Section = Insert values into @WorkTable
BEGIN
INSERT INTO @WorkTable
SELECT
t.acct_id AS PortfolioID
, t.cntrct_pay_tms AS SettlementDate
, t.trd_ex_eff_tms AS ContractDate
, t.trd_brkr_mnem AS BrokerCode
, t.trd_brkr_nme AS Broker
, t.local_curr_cde AS Currency
, t.trn_cl_cde AS TransCode
, t.trn_cde AS TransactionType
, t.quantity AS Nominal
, CASE
WHEN t.trn_cde IN ( FFB , FFS ) THEN
t.fld4_rte ELSE NULL
END AS ForwardRate
, CASE
WHEN t.trn_cde IN ( FSB , FSS ) THEN
t.fld4_rte ELSE NULL
END AS Spot
, t.exec_trd_id AS DealID
, t.actg_trn_id AS TranID
, t.trn_cde AS TranType
, t.deal_id AS RKSTranID
, fx.rate AS USDSPOTRate
, CASE
WHEN t.trn_cde IN ( FFB , FSB ) THEN t.quantity * fx.rate
ELSE 0
END AS BuyNominalUSD
FROM tranevent_dg AS t
INNER JOIN issue_dg AS i
ON i.instr_id = t.instr_id
AND i.iss_typ = FFX
INNER JOIN @FX AS fx
ON fx.RateCCY = t.local_curr_cde
AND fx.RateDate = t.trd_ex_eff_tms
WHERE t.exec_trd_id IS NULL
AND t.inq_basis_num = 1
AND t.trd_ex_eff_tms BETWEEN @StartDateTime AND @EndDateTime
ORDER BY t.deal_id
END
IF @Debug = Y
BEGIN
SELECT @l_Section
SELECT * FROM @WorkTable
ORDER BY RKSTranID
END
/*--------------------------------------------------------------------------------------------------------------------------------
Insert Buy values into @Results
--------------------------------------------------------------------------------------------------------------------------------*/
SET @l_Section = Insert Buy values into @Results
BEGIN
INSERT INTO @Results
SELECT
w.PortfolioID AS PortfolioID
, CONVERT(VARCHAR(10),w.SettlementDate,112) AS SettlementDate
, CONVERT(VARCHAR(10),w.ContractDate,112) AS ContractDate
, w.BrokerCode AS BrokerCode
, w.Broker AS Broker
, w.Currency AS BuyCurrency
, w.Nominal AS BuyNominal
, NULL AS SellCurrency
, NULL AS SellNominal
, NULL AS MarketValue
, w.ForwardRate AS ForwardRate
, w.SpotRate AS SpotRate
, w.DealID AS DealID
, w.RKSTranID AS RKSTranID
, w.BuyNominalUSD AS BuyNominalUSD
FROM @WorkTable AS w
WHERE w.TranType IN ( FFB , FSB )
ORDER BY w.RKSTranID
END
IF @Debug = Y
BEGIN
SELECT @l_Section
SELECT * FROM @Results
ORDER BY RKSTranID
END
/*--------------------------------------------------------------------------------------------------------------------------------
Update Buys with sales data
--------------------------------------------------------------------------------------------------------------------------------*/
SET @l_Section = Update Buys with sales data
BEGIN
UPDATE @Results
SET
SellCurrency = wtSell.Currency
, SellNominal = wtSell.Nominal
FROM @Results AS r
LEFT OUTER JOIN @WorkTable AS wtSell
ON wtSell.PortfolioID = r.PortfolioID
AND wtSell.RKSTranID = r.RKSTranID
AND wtSell.TranType IN ( FFS , FSS )
END
IF @Debug = Y
BEGIN
SELECT @l_Section
SELECT * FROM @Results
ORDER BY RKSTranID
END
/*--------------------------------------------------------------------------------------------------------------------------------
Final Select
--------------------------------------------------------------------------------------------------------------------------------*/
SET @l_Section = Final Output Select
SELECT
PortfolioID AS [Portfolio ID]
, SettlementDate AS [Settlement Date]
, ContractDate AS [Contract Date]
, BrokerCode AS [BrokerCode]
, Broker AS [Broker]
, BuyCurrency AS [Buy Ccy]
, BuyNominal AS [Buy Nominal]
, BuyNominalUSD AS [Buy Nominal USD]
, SellCurrency AS [Sell Ccy]
, SellNominal AS [Sell Nominal]
, MarketValue AS [Market Value]
, ForwardRate AS [Forward Rate]
, SpotRate AS [Spot Rate]
, DealID As [Deal ID]
FROM @Results
ORDER BY DealID