sql server 2016 performance tuning

  • rohitkocharda

    Mr or Mrs. 500

    Points: 529

    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

  • frederico_fonseca

    SSChampion

    Points: 14779

    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

  • Grant Fritchey

    SSC Guru

    Points: 396761

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • rohitkocharda

    Mr or Mrs. 500

    Points: 529

    Those are some great suggestions .I started with :

    • getting rid of nolock()
    • remove * from one of the table only one column was being used

    Question: Do I need  : 'tempdb..#tmpCountryWorkingDays' or just  '#tmpCountryWorkingDays'? I see everywhere temptables are being referred as #tempdb..#" ?

    what clustered index you suggest?

  • frederico_fonseca

    SSChampion

    Points: 14779

    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)

  • ScottPletcher

    SSC Guru

    Points: 98626

     

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeffrey Williams

    SSC Guru

    Points: 88705

    frederico_fonseca wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply