September 22, 2020 at 1:12 am
I have opportunity to tune below monster query. Is there anything right off the bat you guys think I should change?
DECLARE @pDate date
SELECT @pDate = '20200910'
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
September 22, 2020 at 8:20 am
following will help.
1 - get ride of those nolock
if you really have to use them and your business signs off on the possibility of having bad data being supplied to them then use a single statement at the top of the proc "set transaction isolation level read uncommitted" which does the same.
this way when they complain you have a single line to remove
2 - remove those collate - this means create temp copies of some (not all) of the tables where you are using them and when creating the temp table do the collate at that point so the queries do not need to use it.
3 - make sure your temp tables have a clustered index - the one that covers most of its use.
for further help we would need an actual explain plan to see potential issues
September 22, 2020 at 11:39 am
Let's assume that you need nolock everywhere <sigh>, instead, use READ_UNCOMMITTED at the connection. That way, when you find that all the bad data being returned by nolock is causing a problem, you only have one thing to change, not hundreds.
Anyhoo...
I'd suggest capturing all the statements using extended events. All of 'em. Then, identify the individual statements that are causing the most pain. For those statements only, get the execution plans. Understand why you're seeing poor performance in those queries. Make changes.
Stuff like the SELECT ... FROM (SELECT... FROM (SELECT... FROM))) JOIN... makes my hackles rise. The more of that you do, the more likely the optimizer has a harder time unpicking your queries and making decisions to provide you with an optimal plan.
Pretty much any time I see a query like this, I go back to fundamentals. Can we break this down and rebuild it? Does it have to be done the way we're doing it? We're moving data multiple times and then updating it. Could we modify it as part of the move? Creating temp tables to break down data sets to arrive at easier to understand and implement queries (also, ones the optimizer can actually optimize) is a very valid approach, no question. However, multiple moves on the data as well as secondary updates, that's not helping our cause, so I'd focus there. However, as I stated at the top (feel free to discount the statement about nolock, everyone does anyway), measure and understand where the pain points are. That will allow you to focus in the right areas.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2020 at 5:21 pm
Those are some great suggestions .I started with :
Question: Do I need : 'tempdb..#tmpCountryWorkingDays' or just '#tmpCountryWorkingDays'? I see everywhere temptables are being referred as #tempdb..#" ?
what clustered index you suggest?
September 22, 2020 at 6:13 pm
you need 'tempdb..#xxx' when checking for object existence.
e.g. if object_id('tempdb..#xxx') is not null drop table #xxx;
other than the case above you do not need to reference tempdb.. and you can use just the table name directly e.g. #xxx
for clustered indexes you are the one that needs to figure the correct one .
as a rule if the temp table has only 1 index then that index should be clustered.
if table has more than one index then the one that is most commonly used should be clustered, and the other(s) should be non clustered and include all required columns to satisfy their queries.
for example on your code you create 2 indexes on #tmpExchangeWorkingDays -pick one to be the clustered index according to its usage, and on the other include all required columns
CREATE NONCLUSTERED INDEX [Idx_tmpExchangeWorkingDays1] ON #tmpExchangeWorkingDays (CID ASC, WorkingDay ASC)
CREATE NONCLUSTERED INDEX [Idx_tmpExchangeWorkingDays2] ON #tmpExchangeWorkingDays (CID ASC, OrderNo DESC, WorkingDay ASC)
September 22, 2020 at 7:17 pm
Getting rid of NOLOCK will only hurt performance, never help it. That's not an endorsement of nor objection to NOLOCK in these specific statements, just a general statement of fact. When performance is the stated focus, getting rid of NOLOCK would be the last step, not the first one.
I don't have time to go thru the specifics of the code now, but in general, with temp tables:
(1) Don't create and load the table in same statement: SELECT ... INTO new_table FROM ...
Instead, first create the table: SELECT TOP (0) ... INTO new_table FROM ...
Then create the clustered index (before loading the table),
Finally, load the table.
It's extraordinarily rare that it's worth creating (a) nonclus index(es) on a temp table, so don't create any for now.
(2) Don't use a temp table unless you need to, just read the data from the original table instead, esp. if it's only being read once.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 22, 2020 at 8:27 pm
you need 'tempdb..#xxx' when checking for object existence.
e.g. if object_id('tempdb..#xxx') is not null drop table #xxx;
other than the case above you do not need to reference tempdb.. and you can use just the table name directly e.g. #xxx
If you change to the new option you can remove the reference to tempdb: DROP TABLE IF EXISTS #xxx;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply