Performance tuning - sql2016

Rohit Kochar 6 Reputation points
2020-09-22T00:55:40.087+00:00

I have opportunity to tune below monster query. Is there anything right off the bat you guys think I should change?

DECLARE @pDate date

DECLARE @dt6M date = (SELECT DATEADD(MONTH, -6, @pDate))

DECLARE @dt3M date = (SELECT DATEADD(month, -3, @pDate))

IF OBJECT_ID('tempdb..#tmpMasters0') IS NOT NULL DROP TABLE #tmpMasters0

CREATE TABLE #tmpMasters0

(

SourceSecurityId VARCHAR(18) NOT NULL,

CountryId INT NULL,

CountryCode VARCHAR(10) NULL,

ExchangeId INT NULL,

ExchangeCode VARCHAR(10) NULL,

UseCountryCloCal BIT NULL,

UseExchangeCloCal BIT NULL,

)

IF OBJECT_ID('tempdb..#tmpNoClosureCalendarWorkingDays') IS NOT NULL DROP TABLE #tmpNoClosureCalendarWorkingDays

CREATE TABLE #tmpNoClosureCalendarWorkingDays

(

WorkingDay DATE NOT NULL,

OrderNo INT NOT NULL

)

IF OBJECT_ID('tempdb..#tmpCountryWorkingDays') IS NOT NULL DROP TABLE #tmpCountryWorkingDays

CREATE TABLE #tmpCountryWorkingDays

(

CID INT NOT NULL,

WorkingDay DATE NOT NULL,

OrderNo INT NOT NULL

)

IF OBJECT_ID('tempdb..#tmpExchangeWorkingDays') IS NOT NULL DROP TABLE #tmpExchangeWorkingDays

CREATE TABLE #tmpExchangeWorkingDays

(

CID INT NOT NULL,

WorkingDay DATE NOT NULL,

OrderNo INT NOT NULL

)

IF OBJECT_ID('tempdb..#tmpMasters') IS NOT NULL DROP TABLE #tmpMasters

CREATE TABLE #tmpMasters

(

SourceSecurityId VARCHAR(18) NOT NULL,

CountryId INT NULL,

CountryCode VARCHAR(10) NULL,

ExchangeId INT NULL,

ExchangeCode VARCHAR(10) NULL,

UseCountryCloCal BIT NULL,

UseExchangeCloCal BIT NULL,

Date5 DATE NULL,

Date10 DATE NULL,

Date30 DATE NULL,

Date90 DATE NULL,

Date3M DATE NULL,

SecurityPriceId INT NULL

)

IF OBJECT_ID('tempdb..#tmpPrices') IS NOT NULL DROP TABLE #tmpPrices

CREATE TABLE #tmpPrices

(

SourceSecurityId VARCHAR(18) NOT NULL,

MarketDate DATE NULL,

PublishDate DATE NULL,

PriceVol numeric (30,6) NULL,

CurrencyCode VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS,

SecurityPriceId INT NULL,

MasterSecurityId INT NULL,

PriceLast numeric(30,6) NULL

)

IF OBJECT_ID('tempdb..#tmpCFExchangeIds') IS NOT NULL DROP TABLE #tmpCFExchangeIds

CREATE TABLE #tmpCFExchangeIds

(

MarketDate DATE,

CurrencyCode CHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS, --because it has to be case sensitive when comparing

CurrencyForeignExchangeId INT,

IsInverseQuoted BIT,

ExchangeRate NUMERIC(30,6)

)

-- pt 1

INSERT INTO #tmpMasters0

SELECT bb.SourceSecurityId, ss.CDRCountryId, cc.ISOCode2, ss.CDRExchangeId, ee.Code, NULL, NULL

FROM

(

SELECT a1.SourceSecurityId, b1.SecurityId FROM  

(SELECT distinct(sp.SourceSecurityId) as SourceSecurityId FROM  dbo.SecurityPrice sp WITH (NOLOCK))a1  

join  

(   

 SELECT sec.SourceSecurityId, max(sec.SecurityId) AS SecurityId   

 FROM  dbo.Security sec WITH (NOLOCK)  

 WHERE @pDate BETWEEN sec.ValidFromDate AND sec.ValidToDate    

 GROUP BY sec.SourceSecurityId  

) b1 ON a1.SourceSecurityId = b1.SourceSecurityId  

) bb join dbo.Security ss WITH (NOLOCK) on bb.SecurityId = ss.SecurityId

left join dbo.Country cc WITH (NOLOCK) on ss.CDRCountryId = cc.CountryId

left join dbo.Exchange ee WITH (NOLOCK) on ee.ExchangeId = ss.CDRExchangeId

CREATE NONCLUSTERED INDEX [Idx_tmpMasters00] ON #tmpMasters0 (SourceSecurityId ASC);
CREATE NONCLUSTERED INDEX [Idx_tmpMasters01] ON #tmpMasters0 (CountryId ASC, CountryCode ASC);
CREATE NONCLUSTERED INDEX [Idx_tmpMasters02] ON #tmpMasters0 (ExchangeId ASC, ExchangeCode ASC);

UPDATE #tmpMasters0 SET

UseCountryCloCal = IIF((SELECT count(*) FROM dbo.[ClosureCalendar] WITH (NOLOCK)

     WHERE CalendarType = 'Country' and @pDate between ValidFromDate and ValidToDate and  

     CalendarCode COLLATE SQL_Latin1_General_CP1_CS_AS = #tmpMasters0.CountryCode COLLATE SQL_Latin1_General_CP1_CS_AS) > 0 , 1, 0),  

UseExchangeCloCal = IIF((SELECT count(*) FROM dbo.[ClosureCalendar] WITH (NOLOCK)

      WHERE CalendarType = 'Exchange' and @pDate between ValidFromDate and ValidToDate and  

      CalendarCode COLLATE SQL_Latin1_General_CP1_CS_AS = #tmpMasters0.ExchangeCode COLLATE SQL_Latin1_General_CP1_CS_AS) > 0 , 1, 0)  

--pt 2

INSERT INTO #tmpNoClosureCalendarWorkingDays

SELECT CalDate, ROW_NUMBER() OVER(ORDER BY kkk.CalDate DESC)

FROM

(

SELECT DATEADD(DAY, number, @dt6M) AS CalDate  

FROM (SELECT DISTINCT number FROM master.dbo.spt_values WHERE name IS NULL) n  

WHERE DATEADD(DAY, number, @dt6M) <=  @pDate  

) kkk

CREATE NONCLUSTERED INDEX [Idx_tmpNoClosureCalendarWorkingDays] ON #tmpNoClosureCalendarWorkingDays (WorkingDay DESC, OrderNo DESC)

INSERT INTO #tmpCountryWorkingDays

SELECT vv.CountryId, kkk.CalDate, ROW_NUMBER() OVER(PARTITION BY vv.CountryId ORDER BY kkk.CalDate DESC)

FROM

(

SELECT DISTINCT (CountryCode) AS CountryCode FROM #tmpMasters0   

) cc1

JOIN dbo.Country vv ON cc1.CountryCode COLLATE SQL_Latin1_General_CP1_CS_AS = vv.ISOCode2 COLLATE SQL_Latin1_General_CP1_CS_AS AND @pDate BETWEEN vv.ValidFromDate AND vv.ValidToDate

CROSS APPLY

(

SELECT DatesList.CalDate --these are working days   

FROM (  

 SELECT * FROM  dbo.[ClosureCalendar]  WITH (NOLOCK) WHERE CalendarType = 'Country' and @pDate between ValidFromDate and ValidToDate and  

 CalendarCode COLLATE SQL_Latin1_General_CP1_CS_AS = cc1.CountryCode COLLATE SQL_Latin1_General_CP1_CS_AS  

 ) AS CloCal  

FULL OUTER JOIN   

(SELECT WorkingDay AS CalDate FROM #tmpNoClosureCalendarWorkingDays) AS DatesList  

ON CloCal.ClosureDate = DatesList.CalDate WHERE CloCal.ClosureDate IS NULL  

) kkk

CREATE NONCLUSTERED INDEX [Idx_tmpCountryWorkingDays1] ON #tmpCountryWorkingDays (CID ASC, WorkingDay ASC)

CREATE NONCLUSTERED INDEX [Idx_tmpCountryWorkingDays2] ON #tmpCountryWorkingDays (CID ASC, OrderNo DESC, WorkingDay ASC)

INSERT INTO #tmpExchangeWorkingDays

SELECT vv.ExchangeId, kkk.CalDate, ROW_NUMBER() OVER(PARTITION BY vv.ExchangeId ORDER BY kkk.CalDate DESC)

FROM

(

SELECT DISTINCT (ExchangeCode) AS ExchangeCode FROM #tmpMasters0  

) cc1

JOIN dbo.Exchange vv ON cc1.ExchangeCode COLLATE SQL_Latin1_General_CP1_CS_AS = vv.Code COLLATE SQL_Latin1_General_CP1_CS_AS AND @pDate BETWEEN vv.ValidFromDate AND vv.ValidToDate

CROSS APPLY

(

SELECT DatesList.CalDate --these are working days   

FROM (  

 SELECT * FROM  dbo.[ClosureCalendar]  WITH (NOLOCK) WHERE CalendarType = 'Exchange' and @pDate between ValidFromDate and ValidToDate and   

 CalendarCode COLLATE SQL_Latin1_General_CP1_CS_AS = cc1.ExchangeCode COLLATE SQL_Latin1_General_CP1_CS_AS  

 ) AS CloCal  

FULL OUTER JOIN   

(SELECT WorkingDay AS CalDate FROM #tmpNoClosureCalendarWorkingDays) AS DatesList  

ON CloCal.ClosureDate = DatesList.CalDate  

WHERE CloCal.ClosureDate IS NULL  

) kkk

CREATE NONCLUSTERED INDEX [Idx_tmpExchangeWorkingDays1] ON #tmpExchangeWorkingDays (CID ASC, WorkingDay ASC)

CREATE NONCLUSTERED INDEX [Idx_tmpExchangeWorkingDays2] ON #tmpExchangeWorkingDays (CID ASC, OrderNo DESC, WorkingDay ASC)

--pt 3

DECLARE @dt5date date = (SELECT WorkingDay FROM #tmpNoClosureCalendarWorkingDays with (nolock) WHERE OrderNo = 5)

DECLARE @dt10date date = (SELECT WorkingDay FROM #tmpNoClosureCalendarWorkingDays with (nolock) WHERE OrderNo = 10)

DECLARE @dt30date date = (SELECT WorkingDay FROM #tmpNoClosureCalendarWorkingDays with (nolock) WHERE OrderNo = 30)

DECLARE @dt90date date = (SELECT WorkingDay FROM #tmpNoClosureCalendarWorkingDays with (nolock) WHERE OrderNo = 90)

INSERT INTO #tmpMasters

SELECT cc1.SourceSecurityId, cc1.CountryId, cc1.CountryCode, cc1.ExchangeId, cc1.ExchangeCode,

 cc1.UseCountryCloCal, cc1.UseExchangeCloCal, dd1.WorkingDay, dd2.WorkingDay, dd3.WorkingDay, dd4.WorkingDay,   

 (  

  SELECT TOP 1 WorkingDay FROM #tmpExchangeWorkingDays  

  WHERE CID = cc1.ExchangeId AND WorkingDay <= @dt3M  

  ORDER BY WorkingDay DESC  

 ), NULL  

FROM #tmpMasters0 cc1 with (nolock)

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpExchangeWorkingDays with (nolock) WHERE OrderNo = 5) dd1 ON cc1.ExchangeId = dd1.CID

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpExchangeWorkingDays with (nolock) WHERE OrderNo = 10) dd2 ON cc1.ExchangeId = dd2.CID

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpExchangeWorkingDays with (nolock) WHERE OrderNo = 30) dd3 ON cc1.ExchangeId = dd3.CID

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpExchangeWorkingDays with (nolock) WHERE OrderNo = 90) dd4 ON cc1.ExchangeId = dd4.CID

WHERE cc1.UseExchangeCloCal = 1

INSERT INTO #tmpMasters

SELECT cc1.SourceSecurityId, cc1.CountryId, cc1.CountryCode, cc1.ExchangeId, cc1.ExchangeCode,

 cc1.UseCountryCloCal, cc1.UseExchangeCloCal, dd1.WorkingDay, dd2.WorkingDay, dd3.WorkingDay, dd4.WorkingDay,   

 (   

  SELECT TOP 1 WorkingDay   

  FROM #tmpCountryWorkingDays  

  WHERE CID = cc1.CountryId AND WorkingDay <= @dt3M  

  ORDER BY WorkingDay DESC  

 ), NULL  

FROM #tmpMasters0 cc1 with (nolock)

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpCountryWorkingDays with (nolock) WHERE OrderNo = 5) dd1 ON cc1.CountryId = dd1.CID

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpCountryWorkingDays with (nolock) WHERE OrderNo = 10) dd2 ON cc1.CountryId = dd2.CID

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpCountryWorkingDays with (nolock) WHERE OrderNo = 30) dd3 ON cc1.CountryId = dd3.CID

LEFT JOIN (SELECT CID, WorkingDay FROM #tmpCountryWorkingDays with (nolock) WHERE OrderNo = 90) dd4 ON cc1.CountryId = dd4.CID

WHERE cc1.UseCountryCloCal = 1 and cc1.UseExchangeCloCal = 0

INSERT INTO #tmpMasters

SELECT cc1.SourceSecurityId, cc1.CountryId, cc1.CountryCode, cc1.ExchangeId, cc1.ExchangeCode,

 cc1.UseCountryCloCal, cc1.UseExchangeCloCal,   

 @dt5date, @dt10date,@dt30date,@dt90date, @dt3M, NULL  

FROM #tmpMasters0 cc1 with (nolock)

WHERE cc1.UseCountryCloCal = 0 and cc1.UseExchangeCloCal = 0

CREATE NONCLUSTERED INDEX [Idx_tmpMasters] ON #tmpMasters (SourceSecurityId ASC)

CREATE NONCLUSTERED INDEX [Idx_tmpMasters1] ON #tmpMasters (ExchangeId ASC, SourceSecurityId ASC)

CREATE NONCLUSTERED INDEX [Idx_tmpMasters2] ON #tmpMasters (CountryId ASC, SourceSecurityId ASC)

--pt 4

--ones that use exchange id for working days

INSERT INTO #tmpPrices

SELECT a1.SourceSecurityId, a1.WorkingDay As MarketDate, ISNULL(mmm.PublishDate, @pDate) AS PublishDate,

ISNULL(mmm.PriceVolume, 0) AS PriceVolume, UPPER(cic.Code), b1.SecurityPriceId, NULL, mmm.PriceLast  

FROM   

(  

 SELECT DISTINCT(io2.WorkingDay) AS WorkingDay, io1.SourceSecurityId  

 FROM #tmpMasters io1 WITH (NOLOCK) JOIN #tmpExchangeWorkingDays io2 ON io1.ExchangeId = io2.CID  

 WHERE io1.UseExchangeCloCal = 1   

) a1   

left join   

(  

 SELECT yy1.SourceSecurityId, yy1.MarketDate, MAX(yy2.SecurityPriceId) AS SecurityPriceId  

 FROM  

 (  

  SELECT sp.SourceSecurityId, sp.MarketDate, max(sp.PublishDate) as PublishDate   

  FROM  dbo.[SecurityPrice] sp WITH (NOLOCK)   

  JOIN #tmpMasters io1 WITH (NOLOCK) ON sp.SourceSecurityId = io1.SourceSecurityId  

  WHERE io1.UseExchangeCloCal = 1 AND sp.MarketDate >= @dt6M AND sp.PublishDate <= @pDate  

  GROUP BY sp.SourceSecurityId, sp.MarketDate  

 ) yy1  

 LEFT JOIN  dbo.SecurityPrice yy2 WITH (NOLOCK) ON yy1.SourceSecurityId = yy2.SourceSecurityId and   

 yy1.MarketDate = yy2.MarketDate and yy1.PublishDate = yy2.PublishDate  

 group by yy1.SourceSecurityId, yy1.MarketDate  

) b1 on a1.SourceSecurityId = b1.SourceSecurityId and a1.WorkingDay = b1.MarketDate  

left join  dbo.[SecurityPrice] mmm WITH (NOLOCK) on b1.SecurityPriceId = mmm.SecurityPriceId  

left join  dbo.Currency cic with (nolock) on mmm.CurrencyId = cic.CurrencyId  

--ones that use country id for working days

INSERT INTO #tmpPrices

SELECT a1.SourceSecurityId, a1.WorkingDay As MarketDate, ISNULL(mmm.PublishDate, @pDate) AS PublishDate,

ISNULL(mmm.PriceVolume, 0) AS PriceVolume, UPPER(cic.Code), b1.SecurityPriceId, NULL, mmm.PriceLast  

FROM   

(  

 SELECT DISTINCT(io2.WorkingDay) AS WorkingDay, io1.SourceSecurityId  

 FROM #tmpMasters io1 WITH (NOLOCK) JOIN #tmpCountryWorkingDays io2 ON io1.CountryId = io2.CID  

 WHERE io1.UseExchangeCloCal = 0 and io1.UseCountryCloCal = 1   

) a1   

left join   

(  

 SELECT yy1.SourceSecurityId, yy1.MarketDate, MAX(yy2.SecurityPriceId) AS SecurityPriceId  

 FROM  

 (  

  SELECT sp.SourceSecurityId, sp.MarketDate, max(sp.PublishDate) as PublishDate   

  FROM  dbo.[SecurityPrice] sp WITH (NOLOCK)   

  JOIN #tmpMasters io1 WITH (NOLOCK) ON sp.SourceSecurityId = io1.SourceSecurityId  

  WHERE io1.UseExchangeCloCal = 0 AND io1.UseCountryCloCal = 1 AND sp.MarketDate >= @dt6M AND sp.PublishDate <= @pDate  

  GROUP BY sp.SourceSecurityId, sp.MarketDate  

 ) yy1  

 LEFT JOIN  dbo.SecurityPrice yy2 WITH (NOLOCK) ON yy1.SourceSecurityId = yy2.SourceSecurityId and   

 yy1.MarketDate = yy2.MarketDate and yy1.PublishDate = yy2.PublishDate  

 group by yy1.SourceSecurityId, yy1.MarketDate  

) b1 on a1.SourceSecurityId = b1.SourceSecurityId and a1.WorkingDay = b1.MarketDate  

left join  dbo.[SecurityPrice] mmm WITH (NOLOCK) on b1.SecurityPriceId = mmm.SecurityPriceId  

left join  dbo.Currency cic with (nolock) on mmm.CurrencyId = cic.CurrencyId  

--ones that have no closure calendar days defined

INSERT INTO #tmpPrices

SELECT a1.SourceSecurityId, a1.WorkingDay As MarketDate, ISNULL(mmm.PublishDate, @pDate) AS PublishDate,

ISNULL(mmm.PriceVolume, 0) AS PriceVolume, UPPER(cic.Code), b1.SecurityPriceId, NULL, mmm.PriceLast  

FROM   

(  

 SELECT DISTINCT(io2.WorkingDay) AS WorkingDay, io1.SourceSecurityId  

 FROM #tmpMasters io1 WITH (NOLOCK) CROSS JOIN #tmpNoClosureCalendarWorkingDays io2  

 WHERE io1.UseExchangeCloCal = 0 and io1.UseCountryCloCal = 0   

) a1   

left join   

(  

 SELECT yy1.SourceSecurityId, yy1.MarketDate, MAX(yy2.SecurityPriceId) AS SecurityPriceId  

 FROM  

 (  

  SELECT sp.SourceSecurityId, sp.MarketDate, max(sp.PublishDate) as PublishDate   

  FROM  dbo.[SecurityPrice] sp WITH (NOLOCK)   

  JOIN #tmpMasters io1 WITH (NOLOCK) ON sp.SourceSecurityId = io1.SourceSecurityId  

  WHERE io1.UseExchangeCloCal = 0 AND io1.UseCountryCloCal = 0 AND sp.MarketDate >= @dt6M AND sp.PublishDate <= @pDate  

  GROUP BY sp.SourceSecurityId, sp.MarketDate  

 ) yy1  

 LEFT JOIN  dbo.SecurityPrice yy2 WITH (NOLOCK) ON yy1.SourceSecurityId = yy2.SourceSecurityId and   

 yy1.MarketDate = yy2.MarketDate and yy1.PublishDate = yy2.PublishDate  

 group by yy1.SourceSecurityId, yy1.MarketDate  

) b1 on a1.SourceSecurityId = b1.SourceSecurityId and a1.WorkingDay = b1.MarketDate  

left join  dbo.[SecurityPrice] mmm WITH (NOLOCK) on b1.SecurityPriceId = mmm.SecurityPriceId  

left join  dbo.Currency cic with (nolock) on mmm.CurrencyId = cic.CurrencyId  

UPDATE #tmpMasters SET SecurityPriceId =

        (SELECT TOP 1 sp1.SecurityPriceId FROM #tmpPrices sp1 WITH (NOLOCK)     

         WHERE sp1.SourceSecurityId = #tmpMasters.SourceSecurityId and sp1.SecurityPriceId IS NOT NULL  

         ORDER BY sp1.MarketDate DESC, sp1.PublishDate DESC)  

UPDATE #tmpPrices SET MasterSecurityId =

(SELECT TOP 1 ss.MasterSecurityId FROM dbo.Security ss WHERE ss.SourceSecurityId = #tmpPrices.SourceSecurityId)

    CREATE NONCLUSTERED INDEX [Idx_tmpMasters3] ON #tmpMasters (UseExchangeCloCal ASC, UseCountryCloCal ASC, SecurityPriceId ASC)  

CREATE NONCLUSTERED INDEX [Idx_tmpMasters4] ON #tmpMasters (UseExchangeCloCal ASC, SecurityPriceId ASC)

CREATE NONCLUSTERED INDEX [Idx_tmpPrices] ON #tmpPrices (MasterSecurityId ASC, MarketDate ASC, PublishDate ASC)

--CREATE NONCLUSTERED INDEX [Idx_tmpPrices2] ON #tmpPrices (CurrencyCode ASC, MarketDate ASC)

CREATE NONCLUSTERED INDEX tmpPrices_CurrencyCode
ON #tmpPrices ([CurrencyCode])
INCLUDE ([MarketDate],[SecurityPriceId])

CREATE NONCLUSTERED INDEX tmpPrices_CurrencyCode_MarketDate
ON #tmpPrices([CurrencyCode],[MarketDate])
INCLUDE ([SecurityPriceId])

IF OBJECT_ID('tempdb..#tmpAdjFactor') IS NOT NULL DROP TABLE #tmpAdjFactor

CREATE TABLE #tmpAdjFactor
(
BloombergUniqueId VARCHAR(18) NULL,
ValidFrom DATE NULL,
ValidTo DATE NULL,
Factor NUMERIC(30,12) NULL,
FactorCumulative NUMERIC(30,16) NULL,
MasterSecurityId INT NULL
)

INSERT INTO #tmpAdjFactor EXEC [dbo].[spCalcAdjFactor] @LoadDate = @pDate

UPDATE #tmpPrices SET PriceVol =

(

SELECT t1.PriceVol * ISNULL(t2.FactorCumulative, 1.0)

FROM #tmpPrices t1 LEFT JOIN

(

SELECT DISTINCT(tpc.MasterSecurityId) AS MasterSecurityId, tpc.MarketDate, ISNULL(MAX(af.FactorCumulative),1.0) as FactorCumulative  

FROM #tmpPrices tpc LEFT JOIN  #tmpAdjFactor af ON  

tpc.MasterSecurityId = af.MasterSecurityId and   

af.ValidFrom <= tpc.MarketDate AND tpc.MarketDate <= af.ValidTo  

GROUP BY tpc.MasterSecurityId,tpc.MarketDate  

) t2 ON t1.MasterSecurityId = t2.MasterSecurityId and t1.MarketDate = t2.MarketDate

WHERE t1.MasterSecurityId = #tmpPrices.MasterSecurityId and t1.MarketDate = #tmpPrices.MarketDate

)

UPDATE #tmpPrices SET PriceLast =

(

SELECT t1.PriceLast / ISNULL(t2.FactorCumulative, 1.0)

FROM #tmpPrices t1 LEFT JOIN

(

SELECT DISTINCT(tpc.MasterSecurityId) AS MasterSecurityId, tpc.MarketDate, ISNULL(MAX(af.FactorCumulative),1.0) as FactorCumulative  

FROM #tmpPrices tpc LEFT JOIN #tmpAdjFactor af ON  

tpc.MasterSecurityId = af.MasterSecurityId and  

af.ValidFrom <= tpc.MarketDate AND tpc.MarketDate <= af.ValidTo  

GROUP BY tpc.MasterSecurityId,tpc.MarketDate  

) t2 ON t1.MasterSecurityId = t2.MasterSecurityId and t1.MarketDate = t2.MarketDate

WHERE t1.MasterSecurityId = #tmpPrices.MasterSecurityId and t1.MarketDate = #tmpPrices.MarketDate

)

--pt 5

---------------------------------------

INSERT INTO #tmpCFExchangeIds

SELECT tbll.MarketDate, tbll.CurrencyCode, tbll.CurrencyForeignExchangeId, cfee.IsInverseQuoted, cfee.ExchangeRate

FROM (

SELECT tbl.CurrencyCode, tbl.MarketDate, tbl.ExchangeRateDate, max(cfe.CurrencyForeignExchangeId) AS CurrencyForeignExchangeId  

FROM (  

 SELECT DISTINCT (pp.CurrencyCode) AS CurrencyCode, pp.MarketDate, kk.CurrencyId, MAX(cfe1.ExchangeRateDate) as ExchangeRateDate  

 FROM #tmpPrices pp WITH (NOLOCK)   

 JOIN #tmpMasters mm WITH (NOLOCK)  ON pp.SecurityPriceId = mm.SecurityPriceId  

 JOIN  dbo.Currency kk WITH (NOLOCK) ON kk.Code = pp.CurrencyCode AND @pDate BETWEEN kk.ValidFromDate AND kk.ValidToDate --COLLATE SQL_Latin1_General_CP1_CS_AS   

 JOIN  dbo.[CurrencyForeignExchange] cfe1 WITH (NOLOCK) ON kk.CurrencyId = cfe1.CurrencyId AND  

 cfe1.ExchangeRateDate <= pp.MarketDate AND cfe1.ExchangeRateDate >= DATEADD(d, -31, pp.MarketDate) AND @pDate BETWEEN cfe1.ValidFromDate AND cfe1.ValidToDate  

 WHERE pp.CurrencyCode IS NOT NULL  

 GROUP BY pp.CurrencyCode, pp.MarketDate, kk.CurrencyId  

) tbl  

JOIN  dbo.CurrencyForeignExchange cfe WITH(NOLOCK)  

ON (tbl.CurrencyId = cfe.CurrencyId AND tbl.ExchangeRateDate = cfe.ExchangeRateDate AND @pDate BETWEEN cfe.ValidFromDate AND cfe.ValidToDate)  

GROUP BY tbl.CurrencyCode, tbl.MarketDate, tbl.ExchangeRateDate  

) tbll

JOIN dbo.CurrencyForeignExchange cfee with (nolock) ON tbll.CurrencyForeignExchangeId = cfee.CurrencyForeignExchangeId
OPTION(RECOMPILE)

CREATE NONCLUSTERED INDEX [Idx_tmpCFExchangeIds ] ON #tmpCFExchangeIds (MarketDate ASC, CurrencyCode ASC)

INSERT INTO [dbo].[SecurityMarketStatistics]

([MasterSecurityId] ,[SecurityPriceId] ,[AdjustmentFactorCumulative] ,[CurrencyForeignExchangeId] ,[MarketDate] ,[PublishDate] ,
[CurrentMarketCapLocal] ,[ExchangeRate] ,[CurrentMarketCapUSD] ,[DividendYield12M] ,[QuoteLotSize] ,[RoundLotSize] ,[TradeLotSize] ,
[AdjustedPriceLast] ,[AdjustedPriceBid] ,[AdjustedPriceAsk] ,[AdjustedPriceVolume] ,[PriceLast3M] ,[PriceLast3MDate],
---[PriceVolume5DMean] ,
--[PriceVolume5DMedian] ,
--[PriceVolume10DMean] ,
---[PriceVolume10DMedian] ,
----[PriceVolume30DMean] ,
[PriceVolume30DMedian] ,
--[PriceVolume90DMean] ,
[PriceVolume90DMedian] ,
[LoadDate] ,[LoadedBy])

SELECT   

tt1.MasterSecurityId  

,tt1.SecurityPriceId  

,tt1.AdjustmentFactorCumulative  

,tt1.ExchCurrencyForeignExchangeId  AS CurrencyForeignExchangeId  

,tt1.MarketDate  

,@pDate AS PublishDate  

,tt1.CurrentMarketCap AS CurrentMarketCapLocal  

,  

IIF((ISNULL(tt1.CurrentMarketCap,-12345) = -12345),  

NULL,  

(IIF(tt1.ExchIsInverseQuoted = 0, tt1.ExchExchangeRate,   

IIF(tt1.ExchExchangeRate != 0, (1.0 / tt1.ExchExchangeRate), NULL))))  

 AS ExchangeRate  

,(tt1.CurrentMarketCap / (IIF(tt1.ExchIsInverseQuoted = 0,   

tt1.ExchExchangeRate, IIF(tt1.ExchExchangeRate != 0, (1.0 / tt1.ExchExchangeRate), NULL)))) AS CurrentMarketCapUSD  

,tt1.DividendYield12m  

,tt1.QuoteLotSize  

,tt1.RoundLotSize  

,tt1.TradeLotSize  

,(tt1.PriceLast / ISNULL(tt1.AdjustmentFactorCumulative, 1.0)) AS AdjustedPriceLast  

,(tt1.PriceBid / ISNULL(tt1.AdjustmentFactorCumulative, 1.0)) AS AdjustedPriceBid  

,(tt1.PriceAsk / ISNULL(tt1.AdjustmentFactorCumulative, 1.0)) AS AdjustedPriceAsk  

,(tt1.PriceVolume * ISNULL(tt1.AdjustmentFactorCumulative, 1.0)) AS AdjustedPriceVolume  

,(  

 SELECT TOP 1 sp1.PriceLast   

 FROM #tmpPrices sp1    

 WHERE sp1.MasterSecurityId = tt1.MasterSecurityId and sp1.MarketDate = tt1.PriceLast3MDate   

) AS PriceLast3M  

,tt1.PriceLast3MDate  

--,(  

-- SELECT AVG(PriceVol) FROM #tmpPrices gg  

-- WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate and   

--  gg.MarketDate >= tt1.Date5 and gg.PublishDate <= @pDate  

-- ) AS PriceVolume5DMean  

---,  
--(  

-- SELECT AVG(PriceVol) AS Median  

-- FROM  

-- (  

--  SELECT sp.PriceVol,  

--   ROW_NUMBER() OVER (ORDER BY sp.OrderNum ASC) AS RowNumAsc,  

--   ROW_NUMBER() OVER (ORDER BY sp.OrderNum DESC) AS RowNumDesc  

--  FROM   

--   (SELECT gg.PriceVol, ROW_NUMBER() OVER (ORDER BY gg.PriceVol ASC) AS OrderNum  

--   FROM #tmpPrices gg  

--   WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate   

--   and gg.PublishDate <= @pDate and gg.MarketDate >= tt1.Date5) sp  

-- ) x  

-- WHERE RowNumAsc BETWEEN (RowNumDesc - 1) AND (RowNumDesc + 1)  

--) AS PriceVolume5DMedian  

--,(  

-- SELECT AVG(PriceVol) FROM #tmpPrices gg  

-- WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate and   

--  gg.MarketDate >= tt1.Date10 and PublishDate <= @pDate  

-- ) AS PriceVolume10DMean  

--,(  

-- SELECT AVG(PriceVol) AS Median  

-- FROM  

-- (  

--  SELECT sp.PriceVol,  

--   ROW_NUMBER() OVER (ORDER BY sp.OrderNum ASC) AS RowNumAsc,  

--   ROW_NUMBER() OVER (ORDER BY sp.OrderNum DESC) AS RowNumDesc  

--  FROM   

--   (SELECT gg.PriceVol, ROW_NUMBER() OVER (ORDER BY gg.PriceVol ASC) AS OrderNum  

--   FROM #tmpPrices gg  

--   WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate   

--   and gg.PublishDate <= @pDate and gg.MarketDate >= tt1.Date10) sp  

-- ) x  

-- WHERE RowNumAsc BETWEEN (RowNumDesc - 1) AND (RowNumDesc + 1)  

--) AS PriceVolume10DMedian  

--,(  

-- SELECT AVG(PriceVol) FROM #tmpPrices gg  

-- WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate and   

--  gg.MarketDate >= tt1.Date30 and gg.PublishDate <= @pDate  

-- ) AS PriceVolume30DMean  

,(  

 SELECT AVG(PriceVol) AS Median  

 FROM  

 (  

  SELECT sp.PriceVol,  

   ROW_NUMBER() OVER (ORDER BY sp.OrderNum ASC) AS RowNumAsc,  

   ROW_NUMBER() OVER (ORDER BY sp.OrderNum DESC) AS RowNumDesc  

  FROM   

   (SELECT gg.PriceVol, ROW_NUMBER() OVER (ORDER BY gg.PriceVol ASC) AS OrderNum  

   FROM #tmpPrices gg  

   WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate   

   and gg.PublishDate <= @pDate and gg.MarketDate >= tt1.Date30) sp  

 ) x  

 WHERE RowNumAsc BETWEEN (RowNumDesc - 1) AND (RowNumDesc + 1)  

) AS PriceVolume30DMedian  

--,(  

-- SELECT AVG(PriceVol) FROM #tmpPrices gg  

-- WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate and   

-- gg.MarketDate >= tt1.Date90 and PublishDate <= @pDate  

-- ) AS PriceVolume90DMean  

,(  

 SELECT AVG(PriceVol) AS Median  

 FROM  

 (  

  SELECT sp.PriceVol,  

   ROW_NUMBER() OVER (ORDER BY sp.OrderNum ASC) AS RowNumAsc,  

   ROW_NUMBER() OVER (ORDER BY sp.OrderNum DESC) AS RowNumDesc  

  FROM   

   (SELECT gg.PriceVol, ROW_NUMBER() OVER (ORDER BY gg.PriceVol ASC) AS OrderNum  

   FROM #tmpPrices gg  

   WHERE gg.MasterSecurityId = tt1.MasterSecurityId and gg.MarketDate <= @pDate   

   and gg.PublishDate <= @pDate and gg.MarketDate >= tt1.Date90) sp  

 ) x  

 WHERE RowNumAsc BETWEEN (RowNumDesc - 1) AND (RowNumDesc + 1)  

) AS PriceVolume90DMedian  

,GETDATE() AS LoadDate  

,2 as LoadedBy  

FROM  

(  

 SELECT tt.MasterSecurityId, tt.SecurityPriceId, tt.SourceSecurityId  

   ,tt.Date5, tt.Date10, tt.Date30, tt.Date90  

   ,cfe.CurrencyCode AS ExchCurrencyCode  

   ,cfe.CurrencyForeignExchangeId AS ExchCurrencyForeignExchangeId  

   ,cfe.IsInverseQuoted AS ExchIsInverseQuoted  

   ,cfe.ExchangeRate AS ExchExchangeRate  

   ,ISNULL((  

    --Get AdjustmentFactorId for each SecurityPriceId or NULL if it doesn't exists  

    SELECT ISNULL(MAX(af.FactorCumulative),1.0) --MAX applied to get a single row  

    FROM  #tmpAdjFactor af  

    WHERE af.MasterSecurityId = tt.MasterSecurityId   

    AND  af.ValidFrom <= spX.MarketDate AND spX.MarketDate <= af.ValidTo  

   ), 1.0) AS AdjustmentFactorCumulative   

   ,spX.MarketDate AS MarketDate, spX.PublishDate AS PublishDate  

   ,spX.CurrentMarketCap AS CurrentMarketCap,spX.DividendYield12m AS DividendYield12m  

   ,spX.QuoteLotSize AS QuoteLotSize,spX.RoundLotSize AS RoundLotSize  

   ,spX.TradeLotSize AS TradeLotSize,spX.PriceLast AS PriceLast  

   ,spX.PriceBid AS PriceBid,spX.PriceAsk AS PriceAsk,spX.PriceVolume AS PriceVolume  

   ,tt.Date3M AS PriceLast3MDate  

   ,spX.SecurityId AS SecurityId  

 FROM  

 (   

  SELECT ggg3.*, ss3.MasterSecurityId   

  from #tmpMasters ggg3 join  dbo.Security ss3 on ggg3.SourceSecurityId = ss3.SourceSecurityId  

  where @pDate BETWEEN ss3.ValidFromDate AND ss3.ValidToDate  

 )tt   

 JOIN  dbo.SecurityPrice spX WITH (NOLOCK) ON spX.SecurityPriceId = tt.SecurityPriceId  

 LEFT JOIN  dbo.Currency gg  WITH (NOLOCK) ON gg.CurrencyId = spX.CurrencyId  

 LEFT JOIN #tmpCFExchangeIds cfe  WITH (NOLOCK)   

 ON cfe.CurrencyCode = UPPER(gg.Code) --COLLATE SQL_Latin1_General_CP1_CS_AS   
               
 AND cfe.MarketDate = spx.MarketDate  

 WHERE spX.MarketDate >= tt.Date90  

) tt1  

--END

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,234 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

2 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-09-22T02:03:42.103+00:00

    Hi@Rohit Kochar

    Is there anything wrong with your query? The query is too slow ? If the query is too slow, you need to publish the execution plan so that the experts can solve the problem.

    In addition, you posted a very long code. Have you debugged which piece of code has performance problems? I see that your third part uses a lot of left joins, which will slow down the query.
    After reading your code, I found that the five parts are very similar. You can perform the test separately to find out which part of the query is slower

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Best Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-09-22T10:41:36.46+00:00

    Your code is pretty much unreadable Plus you post sooo much code. Your task is to determine which parts takes time and show us something that we can actually work with. I.e., those queries, execution plans, what indexes you have etc. Imagine it is you sitting on this end, expected to give free advice. Help us help you. :-)

    One thing (which isn't performance related) that scares me are all these NOLOCK hints!

    0 comments No comments