performance tuning for a subquery

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Hey Guys

    I have a quick questions regarding a better way to write a query:

    e2 is a bigger table . I have two columns here CNTRY_ISSUE_NAME and CNTRY_ISSUE_ISO  here. I would like to know if there is a better way to write that can give a performance boost to this query. These are just two columns and I have 20 more. Since Entity table was used alot I replaced Entity table with a temp table. I am populating #Entity tand #security able with Entity and Security in the beginning and then using it everywhere. So to avoid more calls to direct able instead. Should I use  a table variable instead?

    CNTRY_ISSUE_NAME =

    case

    when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    WHERE e2.MasterEntityId = e.MasterEntityId

    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    )

    then (SELECT top 1 ciss.Name FROM dbo.#Entity e2 with(nolock)

    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    WHERE e2.MasterEntityId = e.MasterEntityId ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    )

    else

    (SELECT TOP 1 ciss.Name FROM dbo.#Security s with(nolock)

    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = s.CountryIssuedId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --and @AsOfDate between s.ValidFromDate and s.ValidToDate

    )

    end

    ,

     

    ,CNTRY_ISSUE_ISO =

    case

    when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    WHERE e2.MasterEntityId = e.MasterEntityId

    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    )

    then (SELECT top 1 ciss.ISOCode2 FROM dbo.#Entity e2 with(nolock)

    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    WHERE e2.MasterEntityId = e.MasterEntityId

    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    )

    else

    (SELECT TOP 1 ciss.ISOCode2 FROM dbo.#Security s with(nolock)

    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = s.CountryIssuedId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --and @AsOfDate between s.ValidFromDate and s.ValidToDate

    )

    end

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Here are the counts on table. I do not want to hit the table again and again so I put the records in a #temp table. I do not know if table variable will perform better.

     

    select count(*) from [dbo].[Country] ---357

    select count(*) from [dbo].[EntityIndustry] --22,434,897

    select count(*) from [dbo].[SecurityEntityIndustry] --3,028,692

    select count(*) from [dbo].[Security] --20,889,944

  • frederico_fonseca

    SSChampion

    Points: 14686

    when posting code can you please try and format it better and use the "insert/edit code sample" option so it is easy to follow it.

    as all that code is repetitive it can easily be replaced with outer apply making it a lot easier to understand

    original code

    CNTRY_ISSUE_NAME = case
    when exists (SELECT 1
    FROM dbo.#Entity e2 with(nolock)
    left join [dbo].[Country] ciss with(nolock)
    ON ciss.CountryId = e2.CountryOfIssueId
    and @AsOfDate between ciss.ValidFromDate
    and ciss.ValidToDate
    WHERE e2.MasterEntityId = e.MasterEntityId
    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
    )
    then (SELECT top 1 ciss.Name
    FROM dbo.#Entity e2 with(nolock)
    left join [dbo].[Country] ciss with(nolock)
    ON ciss.CountryId = e2.CountryOfIssueId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    WHERE e2.MasterEntityId = e.MasterEntityId
    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
    )
    else
    (SELECT TOP 1 ciss.Name
    FROM dbo.#Security s with(nolock)
    left join [dbo].[Country] ciss with(nolock)
    ON ciss.CountryId = s.CountryIssuedId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    WHERE s.MasterEntityId = e.MasterEntityId
    and s.IsPrimarySecurity = 1
    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
    )
    end
    ,CNTRY_ISSUE_ISO = case
    when exists (SELECT 1
    FROM dbo.#Entity e2 with(nolock)
    left join [dbo].[Country] ciss with(nolock)
    ON ciss.CountryId = e2.CountryOfIssueId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    WHERE e2.MasterEntityId = e.MasterEntityId
    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
    )
    then (SELECT top 1 ciss.ISOCode2
    FROM dbo.#Entity e2 with(nolock)
    left join [dbo].[Country] ciss with(nolock)
    ON ciss.CountryId = e2.CountryOfIssueId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    WHERE e2.MasterEntityId = e.MasterEntityId
    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
    )
    else
    (SELECT TOP 1 ciss.ISOCode2
    FROM dbo.#Security s with(nolock)
    left join [dbo].[Country] ciss with(nolock)
    ON ciss.CountryId = s.CountryIssuedId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    WHERE s.MasterEntityId = e.MasterEntityId
    and s.IsPrimarySecurity = 1
    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
    )
    end

    I think it can be rewritten as follows - and will have a lot less access to the temp tables.

    and keep them as temp tables  - going table variable for these volumes is not advisable

    make sure you add required indexes to temp tables - for example #security should have a clustered index on MasterEntityId

    select CNTRY_ISSUE_NAME = case
    when entity.MasterEntityId is not null
    then entity.Name
    else PrimarySec.Name
    end
    , CNTRY_ISSUE_ISO = case
    when entity.MasterEntityId is not null
    then entity.ISOCode2
    else PrimarySec.ISOCode2
    end
    from table_name e
    outer apply (select top 1 ciss.*
    from #entity e2
    left outer join dbo.country ciss
    on ciss.CountryId = e2.CountryOfIssueId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    where e2.MasterEntityId = e.MasterEntityId
    -- order by pick_field may be required so result is always the same
    ) Entity
    outer apply (select top 1 ciss.*
    from #Security s
    left outer join dbo.country ciss
    on ciss.CountryId = s.CountryOfIssueId
    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
    where s.MasterEntityId = e.MasterEntityId
    and s.IsPrimarySecurity = 1
    -- order by pick_field may be required so result is always the same
    ) PrimarySec

     

     

  • Jeffrey Williams

    SSC Guru

    Points: 88557

    One other optimization that *may* be possible is to remove the CASE expression.  I have to assume that if a match is found in #entity that the columns from that table are not null - but if they can be null this works better because it pulls the first non-null value found, so if the Entity value is null - and the PrimarySec value is not null you get the PrimarySec value...

    select CNTRY_ISSUE_NAME = coalesce(entity.Name, PrimarySec.Name)
    , CNTRY_ISSUE_ISO = coalesce(entity.ISOCode2, PrimarySec.ISOCode2)

    If you have appropriate indexes on your Entity and PrimarySec tables - that is, an index that supports the outer apply queries then you wouldn't even need the temp tables.  In the OUTER APPLY - specify the actual columns to be returned and build a non-clustered covering index to support this query...and definitely include an order by in the query so SQL Server can perform an index seek on the matching value(s).

    Without seeing the rest of the code - and how those temp tables are actually used...no way to tell if that can be optimized further.  There are several possibilities on how to build the temp table...one way would be to build a single row per MasterEntityId where you have 4 separate columns populated...then COALESCE to get the first non-null value...or you add a row from Entity with a sorting value of 1 and add a rows from PrimarySec with a sorting value of 2 - and OUTER APPLY with TOP 1 and ORDER BY to get the appropriate value...

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeff Moden

    SSC Guru

    Points: 996676

    Just to confirm, COALESCE won't provide an improvement in performance over CASE, although it will make the code a whole lot easier.  Behind the scenes, the optimizer converts a COALESCE to a CASE statement.  You can see that in the properties window of the Compute Scalar block in the execution plan.

    ISNULL, on the other hand, will speed things up a bit because it's actually an intrinsic function to T-SQL instead of just a convenient way to write a CASE statement, although it'll take a whole lot of rows to see it.  You can also see that in the properties window of the Compute Scalar block in the execution plan.

    And, just in case someone brings it up, no... I don't believe in the myth of truly portable code. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    It was written by someone else and I am just trying to make it better. There were multiple calls to the table so I stored all data in temp tables with filters. So my temp tables has low rows then actual tables. I am only getting data in temp table which is needed. I am trying to figure out what index I just used on temp table to make this faster.

    Here is the a better version with just one variable CNTRY_ISSUE_NAME and this whole code will go inside a sproc.

     

    DECLARE @AsOfDate            DATE = '20200529',

    @PrivateMarketStatus BIT = 0

    SELECT countryofissueid,

    masterentityid,

    validfromdate,

    validtodate,

    countryofincorporationid,

    countryofdomicileid,

    marketstatusid,

    primarycurrencyid,

    primaryexchangeid,

    sourceentityid,

    NAME,

    primaryexchangeticker,

    tickerexchangecode,

    recentperiodenddate,

    entityid,

    recentsemiend,

    recentquarterend,

    recentquarterenddate,

    recentbsperiod,

    iscurrent

    INTO   dbo.#entity

    FROM   [dbo].[entity]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT *

    INTO   dbo.#entityindustry

    FROM   [dbo].[entityindustry]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    ---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,

    SELECT *

    INTO   dbo.#securityentityindustry

    FROM   [dbo].[securityentityindustry]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT masterentityid,

    validfromdate,

    validtodate,

    [mastersecurityid],

    countryissuedid,

    isprimarysecurity,

    hasmultipleshares,

    countryofdomicileid,

    countryofincorporationid

    INTO   dbo.#security

    FROM   [dbo].[security]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT *

    INTO   dbo.#vwbics

    FROM   vwbics

    SELECT e.[masterentityid],

    [entityid],

    e.[name]                AS NAME,

    e.[sourceentityid]      AS ID_BB_COMPANY,

    ID_BB_UNIQUE=map.sourcesecurityid,

    [primaryexchangeticker] AS TICKER,

    ex.code                 AS EXCH_CODE,

    cr.code                 AS EQY_FUND_CRNCY,

    [tickerexchangecode]    AS TICKER_AND_EXCH_CODE,

    ms.code                 AS MARKET_STATUS,

    (SELECT Max(Cast(hasmultipleshares AS TINYINT))

    FROM   [#security] s

    WHERE  s.masterentityid = e.masterentityid

    --AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate

    )                       AS MULTIPLE_SHARE,

    CNTRY_ISSUE_NAME = CASE

    WHEN EXISTS (SELECT 1

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] ciss

    ON ciss.countryid =

    e2.countryofissueid

    AND @AsOfDate BETWEEN

    ciss.validfromdate

    AND

    ciss.validtodate

    WHERE  e2.masterentityid =

    e.masterentityid

    --and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    ) THEN (SELECT TOP 1 ciss.NAME

    FROM

    dbo.#entity e2

    LEFT JOIN [dbo].[country] ciss

    ON ciss.countryid =

    e2.countryofissueid

    AND @AsOfDate BETWEEN

    ciss.validfromdate

    AND

    ciss.validtodate

    WHERE

    e2.masterentityid = e.masterentityid

    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    )

    ELSE (SELECT TOP 1 ciss.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] ciss

    ON ciss.countryid =

    s.countryissuedid

    AND @AsOfDate BETWEEN

    ciss.validfromdate AND

    ciss.validtodate

    WHERE  s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    )

    END,

    e.[validfromdate]       AS ValidFromDate,

    e.[validtodate]         AS ValidToDate,

    [iscurrent]

    FROM   [dbo].[#entity] e

    JOIN [dbo].[marketstatus] ms

    ON ms.marketstatusid = e.marketstatusid

    JOIN [dbo].currency cr

    ON cr.currencyid = e.primarycurrencyid

    JOIN [dbo].exchange ex

    ON ex.exchangeid = e.primaryexchangeid

    LEFT JOIN [dbo].entityidmapping map

    ON map.sourceentityid = e.sourceentityid

    LEFT JOIN (SELECT masterentityid,

    [6]  AS BICS_1_CODE,

    [7]  AS BICS_2_CODE,

    [8]  AS BICS_3_CODE,

    [9]  AS BICS_4_CODE,

    [10] AS BICS_5_CODE,

    [11] AS BICS_6_CODE,

    [12] AS BICS_7_CODE

    FROM   (SELECT e2.masterentityid,

    i.industryclassificationid,

    Code1=Cast(i.code1 AS BIGINT)

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    AND '99991231' BETWEEN

    ei.validfromdate AND

    ei.validtodate

    LEFT JOIN [dbo].[industry] i

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e2.masterentityid

    AND i.industryclassificationid BETWEEN 6 AND 12

    -- BICS level 1 to 7

    AND '99991231' BETWEEN e2.validfromdate AND

    e2.validtodate

    --    and e2.MasterEntityId=926860 -- Abbvie Inc.

    ) up

    PIVOT ( Avg(code1)

    FOR industryclassificationid IN ( [6],

    [7],

    [8],

    [9],

    [10],

    [11],

    [12] ) ) AS x)

    bics

    ON e.masterentityid = bics.masterentityid

    LEFT JOIN dbo.#vwbics bics_1

    ON bics.bics_1_code = bics_1.bics_code

    LEFT JOIN dbo.#vwbics bics_2

    ON bics.bics_2_code = bics_2.bics_code

    LEFT JOIN dbo.#vwbics bics_3

    ON bics.bics_3_code = bics_3.bics_code

    LEFT JOIN dbo.#vwbics bics_4

    ON bics.bics_4_code = bics_4.bics_code

    LEFT JOIN dbo.#vwbics bics_5

    ON bics.bics_5_code = bics_5.bics_code

    LEFT JOIN dbo.#vwbics bics_6

    ON bics.bics_6_code = bics_6.bics_code

    LEFT JOIN dbo.vwbics bics_7

    ON bics.bics_7_code = bics_7.bics_code

    WHERE

    --@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate

    ---AND

    ( ( ms.code = 'PRIV'

    AND @PrivateMarketStatus = 1 )

    OR ( ms.code <> 'PRIV'

    AND @PrivateMarketStatus = 0 ) )

    AND e.sourceentityid = 28046509

    --- 1:20 seconds

    DROP TABLE #entity

    DROP TABLE #entityindustry

    DROP TABLE #vwbics

    DROP TABLE #securityentityindustry

    DROP TABLE #security

    It was written by someone else and I am just trying to make it better. There were multiple calls to the table so I stored all data in temp tables with filters. So my temp tables has low rows then actual tables. I am only getting data in temp table which is needed. I am trying to figure out what index I just used on temp table to make this faster.

    Here is the a better version with just one variable CNTRY_ISSUE_NAME and this whole code will go inside a sproc.

    DECLARE @AsOfDate DATE = '20200529',
    @PrivateMarketStatus BIT = 0

    SELECT countryofissueid,
    masterentityid,
    validfromdate,
    validtodate,
    countryofincorporationid,
    countryofdomicileid,
    marketstatusid,
    primarycurrencyid,
    primaryexchangeid,
    sourceentityid,
    NAME,
    primaryexchangeticker,
    tickerexchangecode,
    recentperiodenddate,
    entityid,
    recentsemiend,
    recentquarterend,
    recentquarterenddate,
    recentbsperiod,
    iscurrent
    INTO dbo.#entity
    FROM [dbo].[entity]
    WHERE @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT *
    INTO dbo.#entityindustry
    FROM [dbo].[entityindustry]
    WHERE @AsOfDate BETWEEN validfromdate AND validtodate

    ---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,
    SELECT *
    INTO dbo.#securityentityindustry
    FROM [dbo].[securityentityindustry]
    WHERE @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT masterentityid,
    validfromdate,
    validtodate,
    [mastersecurityid],
    countryissuedid,
    isprimarysecurity,
    hasmultipleshares,
    countryofdomicileid,
    countryofincorporationid
    INTO dbo.#security
    FROM [dbo].[security]
    WHERE @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT *
    INTO dbo.#vwbics
    FROM vwbics

    SELECT e.[masterentityid],
    [entityid],
    e.[name] AS NAME,
    e.[sourceentityid] AS ID_BB_COMPANY,
    ID_BB_UNIQUE=map.sourcesecurityid,
    [primaryexchangeticker] AS TICKER,
    ex.code AS EXCH_CODE,
    cr.code AS EQY_FUND_CRNCY,
    [tickerexchangecode] AS TICKER_AND_EXCH_CODE,
    ms.code AS MARKET_STATUS,
    (SELECT Max(Cast(hasmultipleshares AS TINYINT))
    FROM [#security] s
    WHERE s.masterentityid = e.masterentityid
    --AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate
    ) AS MULTIPLE_SHARE,
    CNTRY_ISSUE_NAME = CASE
    WHEN EXISTS (SELECT 1
    FROM dbo.#entity e2
    LEFT JOIN [dbo].[country] ciss
    ON ciss.countryid =
    e2.countryofissueid
    AND @AsOfDate BETWEEN
    ciss.validfromdate
    AND
    ciss.validtodate
    WHERE e2.masterentityid =
    e.masterentityid
    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
    ) THEN (SELECT TOP 1 ciss.NAME
    FROM
    dbo.#entity e2
    LEFT JOIN [dbo].[country] ciss

    ON ciss.countryid =
    e2.countryofissueid
    AND @AsOfDate BETWEEN
    ciss.validfromdate
    AND
    ciss.validtodate
    WHERE
    e2.masterentityid = e.masterentityid
    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
    )
    ELSE (SELECT TOP 1 ciss.NAME
    FROM dbo.#security s
    LEFT JOIN [dbo].[country] ciss
    ON ciss.countryid =
    s.countryissuedid
    AND @AsOfDate BETWEEN
    ciss.validfromdate AND
    ciss.validtodate
    WHERE s.masterentityid = e.masterentityid
    AND s.isprimarysecurity = 1
    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
    )
    END,
    e.[validfromdate] AS ValidFromDate,
    e.[validtodate] AS ValidToDate,
    [iscurrent]
    FROM [dbo].[#entity] e
    JOIN [dbo].[marketstatus] ms
    ON ms.marketstatusid = e.marketstatusid
    JOIN [dbo].currency cr
    ON cr.currencyid = e.primarycurrencyid
    JOIN [dbo].exchange ex
    ON ex.exchangeid = e.primaryexchangeid
    LEFT JOIN [dbo].entityidmapping map
    ON map.sourceentityid = e.sourceentityid
    LEFT JOIN (SELECT masterentityid,
    [6] AS BICS_1_CODE,
    [7] AS BICS_2_CODE,
    [8] AS BICS_3_CODE,
    [9] AS BICS_4_CODE,
    [10] AS BICS_5_CODE,
    [11] AS BICS_6_CODE,
    [12] AS BICS_7_CODE
    FROM (SELECT e2.masterentityid,
    i.industryclassificationid,
    Code1=Cast(i.code1 AS BIGINT)
    FROM dbo.#entity e2
    LEFT JOIN [dbo].[#entityindustry] ei
    ON ei.masterentityid = e2.masterentityid
    AND '99991231' BETWEEN
    ei.validfromdate AND
    ei.validtodate
    LEFT JOIN [dbo].[industry] i
    ON ei.industryid = i.industryid
    WHERE e2.masterentityid = e2.masterentityid
    AND i.industryclassificationid BETWEEN 6 AND 12
    -- BICS level 1 to 7
    AND '99991231' BETWEEN e2.validfromdate AND
    e2.validtodate
    -- and e2.MasterEntityId=926860 -- Abbvie Inc.
    ) up
    PIVOT ( Avg(code1)
    FOR industryclassificationid IN ( [6],
    [7],
    [8],
    [9],
    [10],
    [11],
    [12] ) ) AS x)
    bics
    ON e.masterentityid = bics.masterentityid
    LEFT JOIN dbo.#vwbics bics_1
    ON bics.bics_1_code = bics_1.bics_code
    LEFT JOIN dbo.#vwbics bics_2
    ON bics.bics_2_code = bics_2.bics_code
    LEFT JOIN dbo.#vwbics bics_3
    ON bics.bics_3_code = bics_3.bics_code
    LEFT JOIN dbo.#vwbics bics_4
    ON bics.bics_4_code = bics_4.bics_code
    LEFT JOIN dbo.#vwbics bics_5
    ON bics.bics_5_code = bics_5.bics_code
    LEFT JOIN dbo.#vwbics bics_6
    ON bics.bics_6_code = bics_6.bics_code
    LEFT JOIN dbo.vwbics bics_7
    ON bics.bics_7_code = bics_7.bics_code
    WHERE
    --@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate
    ---AND
    ( ( ms.code = 'PRIV'
    AND @PrivateMarketStatus = 1 )
    OR ( ms.code <> 'PRIV'
    AND @PrivateMarketStatus = 0 ) )
    AND e.sourceentityid = 28046509

    --- 1:20 seconds
    DROP TABLE #entity

    DROP TABLE #entityindustry

    DROP TABLE #vwbics

    DROP TABLE #securityentityindustry

    DROP TABLE #security
  • Jeffrey Williams

    SSC Guru

    Points: 88557

    Jeff Moden wrote:

    Just to confirm, COALESCE won't provide an improvement in performance over CASE, although it will make the code a whole lot easier.  Behind the scenes, the optimizer converts a COALESCE to a CASE statement.  You can see that in the properties window of the Compute Scalar block in the execution plan.

    ISNULL, on the other hand, will speed things up a bit because it's actually an intrinsic function to T-SQL instead of just a convenient way to write a CASE statement, although it'll take a whole lot of rows to see it.  You can also see that in the properties window of the Compute Scalar block in the execution plan.

    And, just in case someone brings it up, no... I don't believe in the myth of truly portable code. 😀

    Correct - but in this case it will have a *slight* improvement because it isn't checking the existence of the MasterEntityID and then returning another column value.  It also has the benefit that if there is a MasterEntityID match and the column value IS NULL - it will return the value from the other table.

    Either ISNULL or COALESCE...I prefer COALESCE as it allows for more columns and sets the data type based on precedence...not on the first column used.  But that is just a preference and either will work in this scenario.

    Regardless of this issue - I believe the best way to improve the performance here is to either restructure how the temp table(s) are created and used or make sure appropriate non-clustered covering indexes exist.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Also based on the execution plan hints to create indexes..I created three NONCLUSTERED index. Do you think there should be a clustered index first?

     

    CREATE NONCLUSTERED INDEX ix1_security

    ON [dbo].[#Security] ([ValidFromDate],[ValidToDate])

    INCLUDE ([MasterSecurityId],[MasterEntityId],[CountryIssuedId],[CountryOfIncorporationId],[CountryOfDomicileId],[IsPrimarySecurity],[HasMultipleShares])

    CREATE NONCLUSTERED INDEX [ix1_EntityIndustry]

    ON [dbo].[#EntityIndustry] ([ValidFromDate],[ValidToDate])

    INCLUDE ([EntityIndustryId],[IndustryId],[MasterEntityId],[LoadDate],[LoadedBy],[UpdateDate],[UpdatedBy])

    CREATE NONCLUSTERED INDEX [ix1_secentInd]

    ON [dbo].[#SecurityEntityIndustry] ([ValidFromDate],[ValidToDate])

    INCLUDE ([SecurityEntityIndustryId],[IndustryId],[MasterSecurityId],[LoadDate],[LoadedBy],[UpdateDate],[UpdatedBy])

  • Jeffrey Williams

    SSC Guru

    Points: 88557

    Is there more to this code than you have shown?  I see several temp tables that are created - and never used.  I also see an outer join to a PIVOT that is utilized (this can be rewritten using a CROSSTAB if it is actually used, but it doesn't seem to be used at all) - and I would rewrite that to perform the join to the 'view' one time and 'pivot' the actual value(s) needed instead of multiple joins to the results of the pivot (if possible).

    For the #security table - you are only utilizing 2 of the columns (as far as I can tell).  Eliminate the other columns since they are not needed or used.

    If you are using those other temp tables - then make sure you are selecting only the columns needed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Here is the complete code:

    I a now using coalesce so that extra query inside when exists is removed. I do not see a huge performance gain though.

     

     

    DECLARE @AsOfDate            DATE = '20200529',

    @PrivateMarketStatus BIT = 0

    SET nocount ON;

    SELECT countryofissueid,

    masterentityid,

    validfromdate,

    validtodate,

    countryofincorporationid,

    countryofdomicileid,

    marketstatusid,

    primarycurrencyid,

    primaryexchangeid,

    sourceentityid,

    NAME,

    primaryexchangeticker,

    tickerexchangecode,

    recentperiodenddate,

    entityid,

    recentsemiend,

    recentquarterend,

    recentquarterenddate,

    recentbsperiod,

    iscurrent

    INTO   dbo.#entity

    FROM   [dbo].[entity]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT *

    INTO   dbo.#entityindustry

    FROM   [dbo].[entityindustry]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    ---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,

    SELECT *

    INTO   dbo.#securityentityindustry

    FROM   [dbo].[securityentityindustry]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT masterentityid,

    validfromdate,

    validtodate,

    [mastersecurityid],

    countryissuedid,

    isprimarysecurity,

    hasmultipleshares,

    countryofdomicileid,

    countryofincorporationid

    INTO   dbo.#security

    FROM   [dbo].[security]

    WHERE  @AsOfDate BETWEEN validfromdate AND validtodate

    SELECT *

    INTO   dbo.#vwbics

    FROM   vwbics

    CREATE NONCLUSTERED INDEX ix1_security

    ON [dbo].[#Security] ([validfromdate], [validtodate])

    include ([MasterSecurityId], [MasterEntityId], [CountryIssuedId],

    [CountryOfIncorporationId], [CountryOfDomicileId], [IsPrimarySecurity],

    [HasMultipleShares])

    CREATE NONCLUSTERED INDEX [ix1_EntityIndustry]

    ON [dbo].[#EntityIndustry] ([validfromdate], [validtodate])

    include ([EntityIndustryId], [IndustryId], [MasterEntityId], [LoadDate],

    [LoadedBy], [UpdateDate], [UpdatedBy])

    CREATE NONCLUSTERED INDEX [ix1_secentInd]

    ON [dbo].[#SecurityEntityIndustry] ([validfromdate], [validtodate])

    include ([SecurityEntityIndustryId], [IndustryId], [MasterSecurityId],

    [LoadDate], [LoadedBy], [UpdateDate], [UpdatedBy])

    SELECT e.[masterentityid],

    [entityid],

    e.[name]                AS NAME,

    e.[sourceentityid]      AS ID_BB_COMPANY,

    ID_BB_UNIQUE=map.sourcesecurityid,

    [primaryexchangeticker] AS TICKER,

    ex.code                 AS EXCH_CODE,

    cr.code                 AS EQY_FUND_CRNCY,

    [tickerexchangecode]    AS TICKER_AND_EXCH_CODE,

    ms.code                 AS MARKET_STATUS,

    (SELECT Max(Cast(hasmultipleshares AS TINYINT))

    FROM   [#security] s

    WHERE  s.masterentityid = e.masterentityid

    --AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate

    )                       AS MULTIPLE_SHARE,

    CNTRY_ISSUE_NAME =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --                              --and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --        )

    --  then (SELECT top 1 ciss.Name FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId                          ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 ciss.Name     FROM dbo.#Security s with(nolock)

    --    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = s.CountryIssuedId      and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 ciss.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] ciss WITH(nolock)

    ON ciss.countryid = e2.countryofissueid

    AND @AsOfDate BETWEEN ciss.validfromdate AND

    ciss.validtodate

    WHERE  e2.masterentityid = e.masterentityid),

    (SELECT TOP 1 ciss.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] ciss WITH(nolock)

    ON ciss.countryid = s.countryissuedid

    AND @AsOfDate BETWEEN ciss.validfromdate AND ciss.validtodate

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1)),

    CNTRY_ISSUE_ISO =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  then (SELECT top 1 ciss.ISOCode2 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 ciss.ISOCode2     FROM dbo.#Security s with(nolock)

    --    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = s.CountryIssuedId      and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    --    )

    --end

    COALESCE((SELECT TOP 1 ciss.isocode2

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] ciss WITH(nolock)

    ON ciss.countryid = e2.countryofissueid

    AND @AsOfDate BETWEEN ciss.validfromdate AND

    ciss.validtodate

    WHERE  e2.masterentityid = e.masterentityid),

    (SELECT TOP 1 ciss.isocode2

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] ciss WITH(nolock)

    ON ciss.countryid = s.countryissuedid

    AND @AsOfDate BETWEEN ciss.validfromdate AND ciss.validtodate

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1)),

    CNTRY_OF_INCORPORATION_NAME =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  then (SELECT top 1 cinc.Name FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 cinc.Name     FROM dbo.#Security s with(nolock)

    --    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = s.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --    --  and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    --    )

    --end

    COALESCE((SELECT TOP 1 cinc.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] cinc WITH(nolock)

    ON cinc.countryid = e2.countryofincorporationid

    AND @AsOfDate BETWEEN cinc.validfromdate AND

    cinc.validtodate

    WHERE  e2.masterentityid = e.masterentityid),

    (SELECT TOP 1 cinc.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] cinc WITH(nolock)

    ON cinc.countryid = s.countryofincorporationid

    AND @AsOfDate BETWEEN cinc.validfromdate AND cinc.validtodate

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1)),

    CNTRY_OF_INCORPORATION =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  then (SELECT top 1 cinc.ISOCode2 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 cinc.ISOCode2     FROM dbo.#Security s with(nolock)

    --    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = s.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    --    )

    --end

    COALESCE((SELECT TOP 1 cinc.isocode2

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] cinc WITH(nolock)

    ON cinc.countryid = e2.countryofincorporationid

    AND @AsOfDate BETWEEN cinc.validfromdate AND

    cinc.validtodate

    WHERE  e2.masterentityid = e.masterentityid),

    (SELECT TOP 1 cinc.isocode2

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] cinc WITH(nolock)

    ON cinc.countryid = s.countryofincorporationid

    AND @AsOfDate BETWEEN cinc.validfromdate AND cinc.validtodate

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1)),

    CNTRY_OF_DOMICILE_NAME =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  then (SELECT top 1 cdom.Name FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 cdom.Name     FROM dbo.#Security s with(nolock)

    --    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = s.CountryOfDomicileId      and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --    --  and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    --    )

    --end

    COALESCE((SELECT TOP 1 cdom.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] cdom WITH(nolock)

    ON cdom.countryid = e2.countryofdomicileid

    AND @AsOfDate BETWEEN cdom.validfromdate AND

    cdom.validtodate

    WHERE  e2.masterentityid = e.masterentityid),

    (SELECT TOP 1 cdom.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] cdom WITH(nolock)

    ON cdom.countryid = s.countryofdomicileid

    AND @AsOfDate BETWEEN cdom.validfromdate AND cdom.validtodate

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1)),

    CNTRY_OF_DOMICILE =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  then (SELECT top 1 cdom.ISOCode2 FROM dbo.#Entity e2 with(nolock)

    --    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate

    --    WHERE e2.MasterEntityId = e.MasterEntityId

    --    --and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 cdom.ISOCode2     FROM dbo.#Security s with(nolock)

    --    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = s.CountryOfDomicileId      and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1

    --    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate

    --    )

    --end

    COALESCE((SELECT TOP 1 cdom.isocode2

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[country] cdom WITH(nolock)

    ON cdom.countryid = e2.countryofdomicileid

    AND @AsOfDate BETWEEN cdom.validfromdate AND

    cdom.validtodate

    WHERE  e2.masterentityid = e.masterentityid),

    (SELECT TOP 1 cdom.isocode2

    FROM   dbo.#security s

    LEFT JOIN [dbo].[country] cdom WITH(nolock)

    ON cdom.countryid = s.countryofdomicileid

    AND @AsOfDate BETWEEN cdom.validfromdate AND cdom.validtodate

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1)),

    [recentperiodenddate]   AS MOST_RECENT_PERIOD_END_DT,

    [recentsemiend]         AS EQY_RECENT_SEMI_END,

    [recentquarterend]      AS EQY_RECENT_QT_END_QT,

    [recentquarterenddate]  AS EQY_RECENT_QT_END_DT,

    [recentbsperiod]        AS EQY_RECENT_BS_QT,

    EQY_SIC_NAME =

    ---case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1

    --    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1

    --    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)

    --    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId

    --    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=1

    --    --and @AsOfDate between s.ValidFromDate and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 i.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e.masterentityid

    AND i.industryclassificationid = 1),

    (SELECT TOP 1 i.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[#securityentityindustry] sei WITH(nolock)

    ON sei.mastersecurityid = s.mastersecurityid

    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON sei.industryid = i.industryid

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    AND i.industryclassificationid = 1)),

    EQY_SIC_CODE =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1

    --    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  then (SELECT TOP 1 i.Code1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 i.Code1 FROM dbo.#Security s with(nolock)

    --    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId

    --    ---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=1

    --    --and @AsOfDate between s.ValidFromDate and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 i.code1

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e.masterentityid

    AND i.industryclassificationid = 1),

    (SELECT TOP 1 i.code1

    FROM   dbo.#security s

    LEFT JOIN [dbo].[#securityentityindustry] sei

    ON sei.mastersecurityid = s.mastersecurityid

    ---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON sei.industryid = i.industryid

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    AND i.industryclassificationid = 1)),

    INDUSTRY_SECTOR =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=2

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=2

    --    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)

    --    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId

    --    ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=2

    --    --and @AsOfDate between s.ValidFromDate and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 i.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e.masterentityid

    AND i.industryclassificationid = 2),

    (SELECT TOP 1 i.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[#securityentityindustry] sei

    ON sei.mastersecurityid = s.mastersecurityid

    AND @AsOfDate BETWEEN sei.validfromdate AND sei.validtodate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON sei.industryid = i.industryid

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    AND i.industryclassificationid = 2)),

    INDUSTRY_GROUP =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=3

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=3

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)

    --    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId

    --    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=3

    --    ---and @AsOfDate between s.ValidFromDate and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 i.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    AND @AsOfDate BETWEEN ei.validfromdate AND

    ei.validtodate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e.masterentityid

    AND i.industryclassificationid = 3),

    (SELECT TOP 1 i.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[#securityentityindustry] sei

    ON sei.mastersecurityid = s.mastersecurityid

    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON sei.industryid = i.industryid

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    AND i.industryclassificationid = 3)),

    INDUSTRY_SUBGROUP =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    --- AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=4

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=4

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)

    --    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId

    --    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=4

    --    --and @AsOfDate between s.ValidFromDate and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 i.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e.masterentityid

    AND i.industryclassificationid = 4),

    (SELECT TOP 1 i.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[#securityentityindustry] sei

    ON sei.mastersecurityid = s.mastersecurityid

    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON sei.industryid = i.industryid

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    AND i.industryclassificationid = 4)),

    EQY_FUND_IND =

    --case

    --  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=5

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)

    --    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId

    --    ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId

    --    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=5

    --    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate

    --    )

    --  else

    --    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)

    --    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId

    --    ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    --    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId

    --    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=5

    --    --- and @AsOfDate between s.ValidFromDate and s.ValidToDate

    --    )

    --end

    COALESCE ((SELECT TOP 1 i.NAME

    FROM   dbo.#entity e2

    LEFT JOIN [dbo].[#entityindustry] ei

    ON ei.masterentityid = e2.masterentityid

    ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e.masterentityid

    AND i.industryclassificationid = 5),

    (SELECT TOP 1 i.NAME

    FROM   dbo.#security s

    LEFT JOIN [dbo].[#securityentityindustry] sei

    ON sei.mastersecurityid = s.mastersecurityid

    ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON sei.industryid = i.industryid

    WHERE

    s.masterentityid = e.masterentityid

    AND s.isprimarysecurity = 1

    AND i.industryclassificationid = 5)),

    BICS_1=bics_1.bics_name,

    BICS_1_CODE=bics_1.bics_code,

    BICS_2=bics_2.bics_name,

    BICS_2_CODE=bics_2.bics_code,

    BICS_3=bics_3.bics_name,

    BICS_3_CODE=bics_3.bics_code,

    BICS_4=bics_4.bics_name,

    BICS_4_CODE=bics_4.bics_code,

    BICS_5=bics_5.bics_name,

    BICS_5_CODE=bics_5.bics_code,

    BICS_6=bics_6.bics_name,

    BICS_6_CODE=bics_6.bics_code,

    BICS_7=bics_7.bics_name,

    BICS_7_CODE=bics_7.bics_code,

    e.[validfromdate]       AS ValidFromDate,

    e.[validtodate]         AS ValidToDate,

    [iscurrent]

    FROM   [dbo].[#entity] e WITH(nolock)

    JOIN [dbo].[marketstatus] ms WITH(nolock)

    ON ms.marketstatusid = e.marketstatusid

    JOIN [dbo].currency cr WITH(nolock)

    ON cr.currencyid = e.primarycurrencyid

    JOIN [dbo].exchange ex WITH(nolock)

    ON ex.exchangeid = e.primaryexchangeid

    LEFT JOIN [dbo].entityidmapping map WITH(nolock)

    ON map.sourceentityid = e.sourceentityid

    LEFT JOIN (SELECT masterentityid,

    [6]  AS BICS_1_CODE,

    [7]  AS BICS_2_CODE,

    [8]  AS BICS_3_CODE,

    [9]  AS BICS_4_CODE,

    [10] AS BICS_5_CODE,

    [11] AS BICS_6_CODE,

    [12] AS BICS_7_CODE

    FROM   (SELECT e2.masterentityid,

    i.industryclassificationid,

    Code1=Cast(i.code1 AS BIGINT)

    FROM   dbo.#entity e2 WITH(nolock)

    LEFT JOIN [dbo].[#entityindustry] ei WITH(

    nolock)

    ON ei.masterentityid = e2.masterentityid

    AND '99991231' BETWEEN

    ei.validfromdate AND

    ei.validtodate

    LEFT JOIN [dbo].[industry] i WITH(nolock)

    ON ei.industryid = i.industryid

    WHERE  e2.masterentityid = e2.masterentityid

    AND i.industryclassificationid BETWEEN 6 AND 12

    -- BICS level 1 to 7

    AND '99991231' BETWEEN e2.validfromdate AND

    e2.validtodate

    --    and e2.MasterEntityId=926860 -- Abbvie Inc.

    ) up

    PIVOT ( Avg(code1)

    FOR industryclassificationid IN ( [6],

    [7],

    [8],

    [9],

    [10],

    [11],

    [12] ) ) AS x)

    bics

    ON e.masterentityid = bics.masterentityid

    LEFT JOIN dbo.#vwbics bics_1

    ON bics.bics_1_code = bics_1.bics_code

    LEFT JOIN dbo.#vwbics bics_2

    ON bics.bics_2_code = bics_2.bics_code

    LEFT JOIN dbo.#vwbics bics_3

    ON bics.bics_3_code = bics_3.bics_code

    LEFT JOIN dbo.#vwbics bics_4

    ON bics.bics_4_code = bics_4.bics_code

    LEFT JOIN dbo.#vwbics bics_5

    ON bics.bics_5_code = bics_5.bics_code

    LEFT JOIN dbo.#vwbics bics_6

    ON bics.bics_6_code = bics_6.bics_code

    LEFT JOIN dbo.vwbics bics_7

    ON bics.bics_7_code = bics_7.bics_code

    WHERE

    --@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate

    ---AND

    ( ( ms.code = 'PRIV'

    AND @PrivateMarketStatus = 1 )

    OR ( ms.code <> 'PRIV'

    AND @PrivateMarketStatus = 0 ) )

    AND e.sourceentityid = 28046509

    --- 1:20 seconds

    DROP TABLE #entity

    DROP TABLE #entityindustry

    DROP TABLE #vwbics

    DROP TABLE #securityentityindustry

    DROP TABLE #security

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    This is deleted

    • This reply was modified 1 month, 1 week ago by  rohitkocharda.
  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    can moderator please delete my post with all html above? I apologize for that.

  • Jeff Moden

    SSC Guru

    Points: 996676

    rohitkocharda wrote:

    can moderator please delete my post with all html above? I apologize for that.

    It would be quicker if you just edited the post and deleted the code block with a short explanation.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • rohitkocharda

    Mr or Mrs. 500

    Points: 522

    Thank you I just deleted

  • frederico_fonseca

    SSChampion

    Points: 14686

    rohitkocharda wrote:

    Here is the complete code:

    I a now using coalesce so that extra query inside when exists is removed. I do not see a huge performance gain though.

    code deleted as not required

    Using the coalesce as you did without changing the sql's to be based on outer apply as I gave an example is not likely to be better.

    Not only that but using coalesce may not give you the same results as before - and you may be populating variables with values that should not be there - it all depends on how your data is structured but it is a possibility.

    regarding indexes

    As a rule of thumb avoid creating the indexes that are being suggested unless you know they are likely to be the correct ones.

    always look at how the table is accessed and create the indexes based on that.

    and try out multiple combinations if one does not help performance as expected.

    And as a primary rule always create a clustered index - and add more nonclustered if required.

    on the case of #EntityIndustry I would suggest you try this one

    create clustered index ix1_EntityIndustry on #EntityIndustry

    (EntityIndustryId

    )

     

Viewing 15 posts - 1 through 15 (of 26 total)

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