January 14, 2011 at 1:25 am
Hi
The sql statement below runs much quicker than the exec of the stored proc shown underneath. I know this is because of the way I am filtering for the optional parameters (which is causing sql to ignore the index I have set up - also shown at bottom). Does anyone know a solution to this? I need to keep the optional params in the stored proc because a fairly large amount of code would need to be changed and redeployed to do it any other way.
Many Thanks
SELECT t.*, f.Name AS FundName, s.Name AS SecurityName, s.Type AS SecurityType,
CASE stt.Reference
WHEN 2
THEN 0
ELSE 1
END AS IsAllocated
FROM trade t
INNER JOIN Source s ON t.securityRef = s.Reference
INNER JOIN Book b ON t.BookRef = b.Reference
INNER JOIN Strategy st ON b.StrategyRef = st.Reference
INNER JOIN StrategyType stt ON st.StrategyTypeRef = stt.Reference
INNER JOIN vwFund f ON t.FundRef = f.Reference
WHERE
TradeDate >= '2010-06-07 00:00:00'
AND TradeDate <= '2011-01-14 00:00:00'
AND tradeTypeId <> 5
AND t.deleted = 0
and ArcOnly = 0
and strategyRef <> 2
exec spGetTrade @tradeDateMax='2011-01-14 00:00:00',@tradeDateMin='2010-06-07 00:00:00',@deleted=0,@isSubmitted=1,@isAllocated=1
/****** Object: StoredProcedure [dbo].[spGetTrade] Script Date: 01/13/2011 14:26:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetTrade]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
ALTER PROCEDURE [dbo].[spGetTrade]
@tradeId INT = NULL,
@dat AS DATETIME= NULL,
@datMax AS DATETIME = NULL,
@datMin AS DATETIME = NULL,
@fundRef AS VARCHAR(15) = NULL,
@securityRef AS VARCHAR(15) = NULL,
@quantity AS MONEY = NULL,
@dealer AS VARCHAR(10) = NULL,
@issuerId AS UNIQUEIDENTIFIER = NULL,
@price AS FLOAT = NULL,
@tradeDate AS DATETIME = NULL,
@tradeDateMax AS DATETIME = NULL,
@tradeDateMin AS DATETIME = NULL,
@settlementDate AS DATETIME = NULL,
@settlementDateMax AS DATETIME = NULL,
@settlementDateMin AS DATETIME = NULL,
@deleted AS BIT = NULL,
@counter AS CHAR(10) = NULL,
@basketCounter AS INT = NULL,
@comment AS CHAR (255) = NULL,
@commission AS FLOAT = NULL,
@upfrontUnwindFee AS DECIMAL(18,2) = NULL,
@dealSpread AS DECIMAL(18,8) = NULL,
@Collateral AS DECIMAL(18,2) = NULL,
@custodianFlag AS CHAR(10) = NULL,
@ifs_trade_id AS CHAR(10) = NULL,
@longview_trade_id AS CHAR(10) = NULL,
@cleanPrice AS FLOAT = NULL,
@clearerType AS VARCHAR (20) = NULL,
@clearer AS CHAR (20) = NULL,
@fxToBaseCurr AS DECIMAL(18,8) = NULL,
@extracted AS DATETIME = NULL,
@cancelled AS DATETIME = NULL,
@bookRef AS INT = NULL,
@tradeTypeId AS INT = NULL,
@tradeSourceId AS INT = NULL,
@isSubmitted AS BIT = NULL,
@lastUpdated AS DATETIME = NULL,
@lastUpdatedMin AS DATETIME = NULL,
@lastUpdatedMax AS DATETIME = NULL,
@relatedTradeId AS INTEGER = NULL,
@isAllocated AS BIT = NULL,
@cashJournalTradeTypeId AS INTEGER = NULL
AS
SELECT t.*, f.Name AS FundName, s.Name AS SecurityName, s.Type AS SecurityType,
CASE stt.Reference
WHEN 2
THEN 0
ELSE 1
END AS IsAllocated
FROM trade t
INNER JOIN Source s ON t.securityRef = s.Reference
INNER JOIN Book b ON t.BookRef = b.Reference
INNER JOIN Strategy st ON b.StrategyRef = st.Reference
INNER JOIN StrategyType stt ON st.StrategyTypeRef = stt.Reference
INNER JOIN vwFund f ON t.FundRef = f.Reference
WHERE
(@TradeID IS NULL OR TradeID = @TradeID)
AND (@dat IS NULL OR dat = @dat)
AND (@datMax IS NULL OR dat <= @datMax)
AND (@datMin IS NULL OR dat >= @datMin)
AND (@fundRef IS NULL OR t.fundref = @fundRef)
AND (@securityRef IS NULL OR securityRef = @securityRef)
AND (@quantity IS NULL OR quantity = @quantity)
AND (@dealer IS NULL OR dealer = @dealer)
AND (@issuerId IS NULL OR t.IssuerId = @issuerId)
AND (@price IS NULL OR price = @price)
AND (@tradeDate IS NULL OR tradeDate = @tradeDate)
AND (@tradeDateMax IS NULL OR tradeDate <= @tradeDateMax)
AND (@tradeDateMin IS NULL OR tradeDate >= @tradeDateMin)
AND (@settlementDate IS NULL OR settlementDate = @settlementDate)
AND (@settlementDateMax IS NULL OR settlementDate <= @settlementDateMax)
AND (@settlementDateMin IS NULL OR settlementDate >= @settlementDateMin)
AND (@deleted IS NULL OR t.deleted = @deleted)
AND (@counter IS NULL OR counter = @counter)
AND (@basketCounter IS NULL OR basketCounter = @basketCounter)
AND (@comment IS NULL OR t.comment = @comment)
AND (@commission IS NULL OR commission = @commission)
AND (@UpfrontUnwindFee IS NULL OR UpfrontUnwindFee = @UpfrontUnwindFee)
AND (@DealSpread IS NULL OR DealSpread = @DealSpread)
AND (@Collateral IS NULL OR Collateral = @Collateral)
AND (@custodianFlag IS NULL OR custodianFlag = @custodianFlag)
AND (@IFS_trade_ID IS NULL OR IFS_trade_ID = @IFS_trade_ID)
AND (@longview_trade_ID IS NULL OR Longview_trade_ID = @longview_trade_ID)
AND (@cleanPrice IS NULL OR cleanPrice = @cleanPrice)
AND (@clearerType IS NULL OR clearerType = @clearerType)
AND (@clearer IS NULL OR clearer = @clearer)
AND (@fxToBaseCurr IS NULL OR fxToBaseCurr = @fxToBaseCurr)
AND (@extracted IS NULL OR extracted = @extracted)
AND (@cancelled IS NULL OR cancelled = @cancelled)
AND (@bookRef IS NULL OR bookRef = @bookRef)
-- nb we exclude cash journal trades by default
AND ((@tradeTypeId IS NULL AND tradeTypeId <> 5) OR tradeTypeId = @tradeTypeId)
AND (@tradeSourceId IS NULL OR tradeSourceId = @tradeSourceId)
AND (@isSubmitted IS NULL OR (@isSubmitted = 0 AND ArcOnly = 1) OR (@isSubmitted =1 AND ArcOnly = 0))
AND (@LastUpdated IS NULL OR LastUpdated = @LastUpdated)
AND (@LastUpdatedMax IS NULL OR LastUpdated <= @LastUpdatedMax)
AND (@LastUpdatedMin IS NULL OR LastUpdated >= @LastUpdatedMin)
AND (@RelatedTradeID IS NULL OR RelatedTradeID = @RelatedTradeID)
AND (@cashJournalTradeTypeId IS NULL OR CashJournalTradeTypeId = @cashJournalTradeTypeId)
AND CASE
WHEN @isAllocated IS NULL
THEN 1
WHEN @isAllocated = 0 AND StrategyTypeRef = 2
THEN 1
WHEN @isAllocated = 1 AND StrategyTypeRef <> 2
THEN 1
ELSE 0
END = 1
ORDER BY Dat ASC
'
END
January 14, 2011 at 2:00 am
January 14, 2011 at 2:39 am
Perfect!! Thanks Dave
Sacha
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply