performance tuning for a subquery

Question

Post reply

performance tuning for a subquery

Viewing 11 posts - 16 through 25 (of 25 total)

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

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 1

Than you! Creating clustered index is a good idea.

CREATE CLUSTERED INDEX ix_t1 ON dbo.#Entity(EntityId) ON [PRIMARY]

CREATE CLUSTERED INDEX ix_t3 ON dbo.#EntityIndustry([EntityIndustryId]) ON [PRIMARY]

CREATE CLUSTERED INDEX ix_t4 ON dbo.#SecurityEntityIndustry(SecurityEntityIndustryId) ON [PRIMARY]

CREATE CLUSTERED INDEX ix_t1 ON dbo.#Security(SecurityId) ON [PRIMARY]

Now what's your recommendation on creating non clustered index?

This reply was modified 3 years, 10 months ago by rohitkocharda.

frederico_fonseca SSCoach Points: 16005 More actions · Answer 2

you may not need one, even if MS tells you there is a missing index - many times you add those and they either don't improve performance or they aren't used or cause things to be even slower.

and please do put your code within code tags so it is formatted in a readable way.

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 3

is this better?

ALTER PROCEDURE [dbo].[Spgetentities_ro_test]

---1:19:15

@AsOfDate DATE = '9999-12-31',

@PrivateMarketStatus BIT = 0

AS

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

CREATE CLUSTERED INDEX ix_t1

ON dbo.#entity(entityid)

ON [PRIMARY];

CREATE NONCLUSTERED INDEX #ice1

ON dbo.#entity ( masterentityid);

SELECT *

INTO dbo.#entityindustry

FROM [dbo].[entityindustry]

WHERE @AsOfDate BETWEEN validfromdate AND validtodate

CREATE CLUSTERED INDEX ix_t3

ON dbo.#entityindustry([entityindustryid])

ON [PRIMARY]

SELECT *

INTO dbo.#securityentityindustry

FROM [dbo].[securityentityindustry]

WHERE @AsOfDate BETWEEN validfromdate AND validtodate

CREATE CLUSTERED INDEX ix_t4

ON dbo.#securityentityindustry(securityentityindustryid)

ON [PRIMARY]

SELECT securityid,

[masterentityid],

validfromdate,

validtodate,

[mastersecurityid],

countryissuedid,

isprimarysecurity,

hasmultipleshares,

countryofdomicileid,

countryofincorporationid

INTO dbo.#security

FROM [dbo].[security]

WHERE @AsOfDate BETWEEN validfromdate AND validtodate

CREATE CLUSTERED INDEX ix_t1

ON dbo.#security(securityid)

ON [PRIMARY]

CREATE NONCLUSTERED INDEX #icc1

ON dbo.#security ( countryissuedid, masterentityid, isprimarysecurity);

CREATE NONCLUSTERED INDEX #icc2

ON dbo.#security ( countryofincorporationid, masterentityid,

isprimarysecurity);

CREATE NONCLUSTERED INDEX #icc3

ON dbo.#security ( countryofdomicileid, masterentityid, isprimarysecurity)

;

CREATE NONCLUSTERED INDEX #icc4

ON dbo.#security ( mastersecurityid, masterentityid, isprimarysecurity);

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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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

This reply was modified 3 years, 10 months ago by rohitkocharda.
This reply was modified 3 years, 10 months ago by rohitkocharda.

frederico_fonseca SSCoach Points: 16005 More actions · Answer 4

same thing

you can do it 2 ways

1 - when you are posting you can select the option from the menu that says "insert/edit code sample" - this pops up a window where you can paste the code

2 - before your code you type "["code"]" and after it you type "["/code"]" - without the double quotes - just posting like this so you can see what it is.

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 5



alter PROCEDURE [dbo].[spGetEntities_RO_test] 
 ---1:19:15

@AsOfDate date = '9999-12-31',
@PrivateMarketStatus bit = 0

as 

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

CREATE CLUSTERED INDEX ix_t1 ON dbo.#Entity(EntityId) ON [PRIMARY];

CREATE NONCLUSTERED INDEX #ice1 on dbo.#Entity ( MasterEntityId) ;



select * into dbo.#EntityIndustry
from [dbo].[EntityIndustry]
where  @AsOfDate between ValidFromDate    and ValidToDate

CREATE CLUSTERED INDEX ix_t3 ON dbo.#EntityIndustry([EntityIndustryId]) ON [PRIMARY]


select * into dbo.#SecurityEntityIndustry
from [dbo].[SecurityEntityIndustry]
where  @AsOfDate between ValidFromDate    and ValidToDate

CREATE CLUSTERED INDEX ix_t4 ON dbo.#SecurityEntityIndustry(SecurityEntityIndustryId) ON [PRIMARY]


select 
SecurityId,
[MasterEntityId],
ValidFromDate,
ValidToDate,
[MasterSecurityId],
CountryIssuedId,
IsPrimarySecurity,
HasMultipleShares,
CountryOfDomicileId,
CountryOfIncorporationId into dbo.#Security
from [dbo].[Security]
where  @AsOfDate between ValidFromDate and ValidToDate

CREATE CLUSTERED INDEX ix_t1 ON dbo.#Security(SecurityId) ON [PRIMARY]

CREATE NONCLUSTERED INDEX #icc1 on dbo.#Security ( CountryIssuedId, MasterEntityId,IsPrimarySecurity) ;

CREATE NONCLUSTERED INDEX #icc2 on dbo.#Security ( CountryOfIncorporationId,MasterEntityId,IsPrimarySecurity) ;

CREATE NONCLUSTERED INDEX #icc3 on dbo.#Security ( CountryOfDomicileId,MasterEntityId,IsPrimarySecurity) ;

CREATE NONCLUSTERED INDEX #icc4 on dbo.#Security ( MasterSecurityId,MasterEntityId,IsPrimarySecurity) ;



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 = 

coalesce ( (SELECT top 1 ciss.Name FROM dbo.#Entity e2  
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId= e2.CountryOfIssueIdand @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.CountryIssuedIdand @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1)
)


,CNTRY_ISSUE_ISO = 


coalesce( ( SELECT top 1 ciss.ISOCode2 FROM dbo.#Entity e2 
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId= e2.CountryOfIssueIdand @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.CountryIssuedIdand @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
)
)

,CNTRY_OF_INCORPORATION_NAME = 


coalesce( (SELECT top 1 cinc.Name FROM dbo.#Entity e2  
left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId= e2.CountryOfIncorporationIdand @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.CountryOfIncorporationIdand @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1)
)


,CNTRY_OF_INCORPORATION = 

coalesce(
 (SELECT top 1 cinc.ISOCode2 FROM dbo.#Entity e2 
left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId= e2.CountryOfIncorporationIdand @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.CountryOfIncorporationIdand @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
)
)

,CNTRY_OF_DOMICILE_NAME = 


coalesce(
(SELECT top 1 cdom.Name FROM dbo.#Entity e2
left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId= e2.CountryOfDomicileIdand @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.CountryOfDomicileIdand @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1)
)

,CNTRY_OF_DOMICILE = 


coalesce( (SELECT top 1 cdom.ISOCode2 FROM dbo.#Entity e2  
left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId= e2.CountryOfDomicileIdand @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.CountryOfDomicileIdand @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 = 


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 = 

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 =


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 =


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 =

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 =

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 Say Hey Kid Points: 687 More actions · Answer 6

rohitkocharda

Say Hey Kid

Points: 687

June 6, 2020 at 10:02 pm

#3761118

That worked perfectly. Thanks!

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 7

For the creation of the #Entity temp table.

I would move the join to Industry into the build of the #EntityIndustry table - and then move the join to #EntityIndustry into the build of the #Entity table (or move both into the build of #Entity. This will eliminate the need for a sub-query to get those values.

Move the MarketStatus join into the build of the #Entity table and include the where clause for those in the build of that table. It will eliminate more rows from the #Entity table.

Convert the PIVOT derived table to use a CROSSTAB and include both the CODE and NAME. This will eliminate all of the outer joins to the #vwBICS table.

Move the join to #SecurityEntityIndustry into the build of the #Security table.

For both the #Entity table and the #Security tables - you can use the following structure to limit the rows to a single row per MasterEntityID:

SELECT TOP 1 WITH TIES
...
FROM ...
WHERE ...
ORDER BY row_number() over(partition by MasterEntityID order by ValidToDate DESC)

This is only needed if there can be overlapping time frames - if the Entity and Security tables cannot have overlapping rows then it isn't needed and neither will the checks in the outer apply/sub-queries on the date ranges be needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 8

Those are some amazing suggestions. Thanks!

I will change the script and hopefully see the benefit. Do you recommend any non-clustered indexes?

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 9

Als , does it make sense to replace

EQY_FUND_IND =

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

)

EQY_FUND_IND =

isnull( (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

)

frederico_fonseca SSCoach Points: 16005 More actions · Answer 10

did a bit of changes to your code - untested and may have typos so if you get any error that is most likely why

there are a few comments on it - make sure you go through them, mainly on the block around BICS Codes

What I would like you to do now is to get a explain plan for the execution of this version and attach the resulting sqlplan file here (you will need to rename it to be a .txt)

alter PROCEDURE [dbo].[spGetEntities_RO_test] 
 ---1:19:15

@AsOfDate date = '9999-12-31',
@PrivateMarketStatus bit = 0

as 

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 #Entity
from [dbo].[Entity]
where  @AsOfDate between ValidFromDate and ValidToDate

create clustered index ix_t1 ON #Entity (EntityId);

create nonclustered index #ice1 on #Entity (MasterEntityId) ;


select MasterEntityId
     , IndustryId
     , ValidFromDate
     , ValidToDate
into #EntityIndustry
from dbo.EntityIndustry
where  @AsOfDate between ValidFromDate and ValidToDate


create clustered index ix_t3 ON #EntityIndustry (MasterEntityId);

select MasterSecurityId
     , IndustryId
into #SecurityEntityIndustry
from dbo.SecurityEntityIndustry
where @AsOfDate between ValidFromDate and ValidToDate

create clustered index ix_t4 ON #SecurityEntityIndustry (MasterSecurityId);


select SecurityId
     , MasterEntityId
     , ValidFromDate
     , ValidToDate
     , MasterSecurityId
     , CountryIssuedId
     , IsPrimarySecurity
     , HasMultipleShares
     , CountryOfDomicileId
     , CountryOfIncorporationId
into #Security
from dbo.Security
where  @AsOfDate between ValidFromDate and ValidToDate

create clustered index ix_t1 ON #Security(SecurityId);

create nonclustered index #icc1 on #Security (CountryIssuedId, MasterEntityId, IsPrimarySecurity) ;

create nonclustered index #icc2 on #Security (CountryOfIncorporationId, MasterEntityId, IsPrimarySecurity) ;

create nonclustered index #icc3 on #Security (CountryOfDomicileId, MasterEntityId, IsPrimarySecurity) ;

create nonclustered index #icc4 on #Security (MasterSecurityId, MasterEntityId, IsPrimarySecurity) ;

-- new temp table for country - so we can filter the date immediately making code clearer further down
if object_id('tempdb..#country') is not null
    drop table #country;

select ciss.CountryId
     , ciss.Name
     , ciss.ISOCode2
from dbo.Country ciss
where @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate

create clustered index #ix1_country on #country
(CountryId
)



select BICS_CODE
     , BICS_NAME
into #vwBICS 
from vwBICS

create clustered index #ix1_vwBICS on #vwBICS
(BICS_CODE
)


    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
    
         -- block changed to use outer apply
          ,CNTRY_ISSUE_NAME = coalesce(ciss.Name, seciss.Name) 
          ,CNTRY_ISSUE_ISO = coalesce(ciss.ISOCode2, seciss.ISOCode2) 
          ,CNTRY_OF_INCORPORATION_NAME = coalesce(cinc.Name, secinc.Name) 
          ,CNTRY_OF_INCORPORATION =  coalesce(cinc.ISOCode2, secinc.ISOCode2)
          ,CNTRY_OF_DOMICILE_NAME = coalesce(cdom.Name, secdom.Name)
          ,CNTRY_OF_DOMICILE = coalesce(cdom.ISOCode2, secdom.ISOCode2)
         -- block changed to use outer apply

          ,[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 = coalesce (EQY_SIC_1.Name, EQY_SIC_SEC_1.Name)
          ,EQY_SIC_CODE = coalesce (EQY_SIC_1.Code1, EQY_SIC_SEC_1.Code1)

        /*
           following block of code not changed as each block has a different IndustryClassificationId
        */
          ,INDUSTRY_SECTOR  = 
            coalesce (( SELECT TOP 1 i.Name FROM #Entity e2
                        inner join #EntityIndustry ei 
                           ON ei.MasterEntityId = e2.MasterEntityId 
                        ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
                        inner 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 #Security s 
                       inner join #SecurityEntityIndustry sei 
                           ON sei.MasterSecurityId = s.MasterSecurityId 
                       ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
                       inner 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  = 
            coalesce ((SELECT TOP 1 i.Name FROM #Entity e2 
                       inner join join #EntityIndustry ei 
                           ON ei.MasterEntityId = e2.MasterEntityId 
                       ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
                       inner join 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 #Security s 
                       inner join join #SecurityEntityIndustry sei 
                           ON sei.MasterSecurityId = s.MasterSecurityId 
                       ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
                       inner join 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  = 
            coalesce ((SELECT TOP 1 i.Name FROM #Entity e2 
                       inner join join #EntityIndustry ei 
                           ON ei.MasterEntityId = e2.MasterEntityId
                       ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
                       inner join 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 #Security s  
                       inner join join #SecurityEntityIndustry sei 
                           ON sei.MasterSecurityId = s.MasterSecurityId 
                       ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
                       inner join 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  = 
            coalesce ((SELECT TOP 1 i.Name FROM #Entity e2 
                       inner join join #EntityIndustry ei
                           ON ei.MasterEntityId = e2.MasterEntityId 
                       ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
                       inner join 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 #Security s 
                       inner join join #SecurityEntityIndustry sei
                           ON sei.MasterSecurityId = s.MasterSecurityId 
                       ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
                       inner join 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 #Entity e
      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

      -- Issue country
      outer apply (SELECT top 1 ctry.ISOCode2, ctry.Name
                   from #Country ctry
                   where ctry.CountryId = e.CountryOfIssueId
                  ) ciss
      outer apply (SELECT top 1 ctry.ISOCode2, ctry.Name
                   from #Security s
                   inner join #Country ctry
                       ON ctry.CountryId = s.CountryIssuedId
                       and s.IsPrimarySecurity = 1
                   where s.MasterEntityId = e.MasterEntityId
                  ) seciss

      -- Incorporation country
      outer apply (SELECT top 1 ctry.ISOCode2, ctry.Name
                   from #Country ctry
                   where ctry.CountryId = e.CountryOfIncorporationId
                  ) cinc
      outer apply (SELECT top 1 ctry.ISOCode2, ctry.Name
                   from #Security s
                   inner join #Country ctry
                       ON ctry.CountryId = s.CountryOfIncorporationId
                       and s.IsPrimarySecurity = 1
                   where s.MasterEntityId = e.MasterEntityId
                  ) secinc

      -- Domicile country
      outer apply (SELECT top 1 ctry.ISOCode2, ctry.Name
                   from #Country ctry
                   where ctry.CountryId = e.CountryOfDomicileId
                  ) cdom
      outer apply (SELECT top 1 ctry.ISOCode2, ctry.Name
                   from #Security s
                   inner join #Country ctry
                       ON ctry.CountryId = s.CountryOfDomicileId
                       and s.IsPrimarySecurity = 1
                   where s.MasterEntityId = e.MasterEntityId
                  ) secdom

       outer apply (select top 1 i.Name, i.Code1
                    from #Entity e2 
                       inner join join #EntityIndustry ei 
                           ON ei.MasterEntityId = e2.MasterEntityId 
                       ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
                       inner join join [dbo].[Industry] i with(nolock)
                           on ei.IndustryId = i.IndustryId
                       WHERE e2.MasterEntityId = e.MasterEntityId and i.IndustryClassificationId = 1     
                    ) EQY_SIC_1
       outer apply (select top 1 i.Name, i.Code1
                    from #Security s 
                       inner join join #SecurityEntityIndustry  sei
                           ON sei.MasterSecurityId = s.MasterSecurityId
                        ---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
                       inner join 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_Sec_1

      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 #Entity e2  
        /*

             SHOULD THIS be using temp table? not same criteria as the other uses of the table so maybe this should be going directly to base table
             or maybe create a new temp table just for this query - taking in consideration that 99992131 is highest date possible
             that temp table could be set to retrieve only where validtodate = 99991231 
        */
        inner join #EntityIndustry ei 
                           ON ei.MasterEntityId = e2.MasterEntityId and '99991231' between ei.ValidFromDate and ei.ValidToDate
        inner join [dbo].[Industry] i with(nolock)
                           on ei.IndustryId = i.IndustryId


        WHERE e2.MasterEntityId = e2.MasterEntityId  ---- SHOULD THIS BE e2.MasterEntityId = e1.MasterEntityId ? if not then condition can be removed as redundant


        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 #vwBICS bics_1
             on bics.BICS_1_CODE = bics_1.BICS_CODE
left join #vwBICS bics_2
             on bics.BICS_2_CODE = bics_2.BICS_CODE
left join #vwBICS bics_3
             on bics.BICS_3_CODE = bics_3.BICS_CODE
left join #vwBICS bics_4
             on bics.BICS_4_CODE = bics_4.BICS_CODE
left join #vwBICS bics_5
             on bics.BICS_5_CODE = bics_5.BICS_CODE
left join #vwBICS bics_6
             on bics.BICS_6_CODE = bics_6.BICS_CODE
left join #vwBICS bics_7
             on bics.BICS_7_CODE = bics_7.BICS_CODE
--   left join dbo.vwBICS bics_7 on bics.BICS_7_CODE = bics_7.BICS_CODE -- this was still going to the base view instead of temp table
  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: 90244 More actions · Answer 11

Here is what I was thinking - not tested and may need some refinement in the build of the temp tables. This all depends on how many rows can satisfy the @AsOfDate requirement. If more than a single row can qualify then it will depend on how that 'correct' row is identified.

  Alter Procedure dbo.spGetEntities_RO_test
        @AsOfDate date = '9999-12-31'
      , @PrivateMarketStatus bit = 0
     As
        
    Set Nocount On;
    Set Transaction Isolation Level Read Uncommitted;

 --==== Build Entity Industry temp
   Drop Table If Exists #EntityIndustry;

 Select ei.MasterEntityId
      , EQY_SIC_NAME = iif(i.IndustryClassificationId = 1, i.Name, Null)
      , EQY_SIC_CODE = iif(i.IndustryClassificationId = 1, i.Code1, Null)
      , INDUSTRY_SECTOR = iif(i.IndustryClassificationId = 2, i.Name, Null)
      , INDUSTRY_GROUP = iif(i.IndustryClassificationId = 3, i.Name, Null)
      , INDUSTRY_SUBGROUP = iif(i.IndustryClassificationId = 4, i.Name, Null)
      , EQY_FUND_IND = iif(i.IndustryClassificationId = 5, i.Name, Null)
   Into dbo.#EntityIndustry
   From dbo.EntityIndustry                ei 
  Inner Join dbo.Industry                  i On i.IndustryId = ei.IndustryId
  Where i.IndustryClassificationId Between 1 And 5
    And @AsOfDate Between ei.ValidFromDate And ei.ValidToDate;

 --==== Build Security Entity Industry temp
   Drop Table If Exists #SecurityEntityIndustry;

 Select sei.MasterEntityId
      , EQY_SIC_NAME = iif(i.IndustryClassificationId = 1, i.Name, Null)
      , EQY_SIC_CODE = iif(i.IndustryClassificationId = 1, i.Code1, Null)
      , INDUSTRY_SECTOR = iif(i.IndustryClassificationId = 2, i.Name, Null)
      , INDUSTRY_GROUP = iif(i.IndustryClassificationId = 3, i.Name, Null)
      , INDUSTRY_SUBGROUP = iif(i.IndustryClassificationId = 4, i.Name, Null)
      , EQY_FUND_IND = iif(i.IndustryClassificationId = 5, i.Name, Null)
   Into dbo.#SecurityEntityIndustry
   From dbo.SecurityEntityIndustry       sei
  Inner Join dbo.Industry                  i On i.IndustryId = sei.IndustryId
  Where i.IndustryClassificationId Between 1 And 5
    And @AsOfDate Between ei.ValidFromDate And ei.ValidToDate;

 --==== Build Entity temp table
   Drop Table If Exists #Entity;

 Select ent.CountryOfIssueId
      , ent.MasterEntityId
      , ent.ValidFromDate
      , ent.ValidToDate
      , ent.CountryOfIncorporationId
      , ent.CountryOfDomicileId
      , ent.MarketStatusId
      , ent.PrimaryCurrencyId
      , ent.PrimaryExchangeId
      , ID_BB_COMPANY = ent.SourceEntityId
      , ID_BB_UNIQUE = map.SourceSecurityId
      , EntityName = ent.Name
      , TICKET = ent.PrimaryExchangeTicker
      , EXCH_CODE = ex.Code
      , EQY_FUND_CRNCY = cr.Code
      , TICKER_AND_EXCH_CODE = ent.TickerExchangeCode
      , MARKET_STATUS = ms.Code
      , MOST_RECENT_PERIOD_END_DT = ent.RecentPeriodEndDate
      , ent.EntityId
      , EQY_RECENT_SEMI_END = ent.RecentSemiEnd
      , EQY_RECENT_QT_END_QT = ent.RecentQuarterEnd
      , EQY_RECENT_QT_END_DT = ent.RecentQuarterEndDate
      , EQY_RECENT_BS_QT = ent.RecentBSPeriod
      , ent.IsCurrent

      , CNTRY_ISSUE_NAME = ciss.Name
      , CNTRY_ISSUE_ISO = ciss.ISOCode2

      , CNTRY_OF_INCORPORATION_NAME = cinc.Name
      , CNTRY_OF_INCORPORATION = cinc.ISOCode2

      , CNTRY_OF_DOMICILE_NAME = cdom.Name
      , CNTRY_OF_DOMICILE = cdom.ISOCode2

      , ei.EQY_SIC_NAME
      , ei.EQY_SIC_CODE
      , ei.INDUSTRY_SECTOR
      , ei.INDUSTRY_GROUP
      , ei.INDUSTRY_SUBGROUP
      , ei.EQY_FUND_IND

   Into dbo.#Entity                    
   From dbo.Entity                       ent
  Inner Join dbo.MarketStatus             ms On ms.MarketStatusId = ent.MarketStatusId
  Inner Join dbo.Currency                 cr On cr.CurrencyId = ent.PrimaryCurrencyId
  Inner Join dbo.Exchange                 ex On ex.ExchangeId = ent.PrimaryExchangeId

   Left Join dbo.EntityIDMapping         map On map.SourceEntityId = ent.SourceEntityId

   Left Join dbo.Country                ciss On ciss.CountryId = ent.CountryOfIssueId
                                            And @AsOfDate Between ciss.ValidFromDate And ciss.ValidToDate
   Left Join dbo.Country                cinc On cinc.CountryId = ent.CountryOfIncorporationId
                                            And @AsOfDate Between cinc.ValidFromDate And cinc.ValidToDate
   Left Join dbo.Country                cdom On cdom.CountryId = ent.CountryOfDomicileId
                                            And @AsOfDate Between cinc.ValidFromDate And cinc.ValidToDate

   Left Join #EntityIndustry              eiOn ei.MasterEntityId = ent.MasterEntityId
  Where @AsOfDate Between ent.ValidFromDate And ent.ValidToDate
    And (
        (ms.Code = 'PRIV' And @PrivateMarketStatus = 1)
     Or (ms.Code <> 'PRIV' And @PrivateMarketStatus = 0)
        );

 --==== Build the Security temp table
   Drop Table If Exists #Security;

 Select s.MasterEntityId
      , s.SecurityId
      , s.ValidFromDate
      , s.ValidToDate
      , s.MasterSecurityId
      , s.CountryIssuedId
      , s.IsPrimarySecurity
      , s.HasMultipleShares
      , s.CountryOfDomicileId
      , s.CountryOfIncorporationId

      , CNTRY_ISSUE_NAME = ciss.Name
      , CNTRY_ISSUE_ISO = ciss.ISOCode2

      , CNTRY_OF_INCORPORATION_NAME = cinc.Name
      , CNTRY_OF_INCORPORATION = cinc.ISOCode2

      , CNTRY_OF_DOMICILE_NAME = cdom.Name
      , CNTRY_OF_DOMICILE = cdom.ISOCode2

      , sei.EQY_SIC_NAME
      , sei.EQY_SIC_CODE
      , sei.INDUSTRY_SECTOR
      , sei.INDUSTRY_GROUP
      , sei.INDUSTRY_SUBGROUP
      , sei.EQY_FUND_IND

   Into #Security
   From dbo.[Security]                     s
   Left Join dbo.SecurityEntityIndustry  sei On sei.MasterSecurityId = s.MasterSecurityId 
   Left Join dbo.Industry                  i On i.IndustryId = sei.IndustryId

   Left Join dbo.Country                ciss On ciss.CountryId = s.CountryIssueId
                                            And @AsOfDate Between ciss.ValidFromDate And ciss.ValidToDate
   Left Join dbo.Country                cinc On cinc.CountryId = s.CountryOfIncorporationId
                                            And @AsOfDate Between cinc.ValidFromDate And cinc.ValidToDate
   Left Join dbo.Country                cdom On cdom.CountryId = s.CountryOfDomicileId
                                            And @AsOfDate Between cinc.ValidFromDate And cinc.ValidToDate

   Left Join #SecurityEntityIndustry      ei On sei.MasterEntityId = ent.MasterEntityId

  Where s.IsPrimarySecurity = 1
    And @AsOfDate Between s.ValidFromDate And s.ValidToDate;

 --==== Build Cross-tab for BICS CODE
   Drop Table If Exists #bics_codes;

 Select ent.MasterEntityId
      , BICS_1 = max(iif(i.IndustryClassificationId =  6, bic.BIC_CODE, Null)
      , BICS_1_NAME = max(iif(i.IndustryClassificationId =  6, bic.BIC_NAME, Null)
      , BICS_2 = max(iif(i.IndustryClassificationId =  7, bic.BIC_CODE, Null)
      , BICS_2_NAME = max(iif(i.IndustryClassificationId =  7, bic.BIC_NAME, Null)
      , BICS_3 = max(iif(i.IndustryClassificationId =  8, bic.BIC_CODE, Null)
      , BICS_3_NAME = max(iif(i.IndustryClassificationId =  8, bic.BIC_NAME, Null)
      , BICS_4 = max(iif(i.IndustryClassificationId =  9, bic.BIC_CODE, Null)
      , BICS_4_NAME = max(iif(i.IndustryClassificationId =  9, bic.BIC_NAME, Null)
      , BICS_5 = max(iif(i.IndustryClassificationId = 10, bic.BIC_CODE, Null)
      , BICS_5_NAME = max(iif(i.IndustryClassificationId = 10, bic.BIC_NAME, Null)
      , BICS_6 = max(iif(i.IndustryClassificationId = 11, bic.BIC_CODE, Null)
      , BICS_6_NAME = max(iif(i.IndustryClassificationId = 11, bic.BIC_NAME, Null)
      , BICS_7 = max(iif(i.IndustryClassificationId = 12, bic.BIC_CODE, Null)
      , BICS_7_NAME = max(iif(i.IndustryClassificationId = 12, bic.BIC_NAME, Null)
   Into #bics_codes
   From dbo.Entity                       ent 
  Inner Join dbo.EntityIndustry           ei On ei.MasterEntityId = ent.MasterEntityId
                                            And '99991231' Between ei.ValidFromDate And ei.ValidToDate
  Inner Join dbo.Industry                  i On i.IndustryId = ei.IndustryId
  Inner Join dbo.vwBICS                  bic On bic.BICS_CODE = i.Code1
  Where i.IndustryClassificationId Between 6 And 12 -- BICS level 1 to 7
    And '99991231' Between ent.ValidFromDate And ent.ValidToDate
  Group By
        ent.MasterEntityId;

 --==== Put it all together
 Select e.MasterEntityId
      , e.EntityId
      , e.[Name]
      , e.ID_BB_COMPANY
      , ID_BB_UNIQUE = map.SourceSecurityId
      , e.TICKER
      , e.EXCH_CODE
      , e.EQY_FUND_CRNCY
      , e.TICKER_AND_EXCH_CODE
      , e.MARKET_STATUS

      , MULTIPLE_SHARE = ( Select max(cast(HasMultipleShares As tinyint))
                             From #Security             s
                            Where s.MasterEntityId = e.MasterEntityId) 

      , CNTRY_ISSUE_NAME = coalesce(e.CNTRY_ISSUE_NAME, s.CNTRY_ISSUE_NAME)
      , CNTRY_ISSUE_ISO = coalesce(e.CNTRY_ISSUE_ISO, s.CNTRY_ISSUE_ISO)
      , CNTRY_OF_INCORPORATION_NAME = coalesce(e.CNTRY_OF_INCORPORATION_NAME, s.CNTRY_OF_INCORPORATION_NAME)
      , CNTRY_OF_INCORPORATION = coalesce(e.CNTRY_OF_INCORPORATION, s.CNTRY_OF_INCORPORATION)
      , CNTRY_OF_DOMICILE_NAME = coalesce(e.CNTRY_OF_DOMICILE_NAME, s.CNTRY_OF_DOMICILE_NAME)
      , CNTRY_OF_DOMICILE = coalesce(e.CNTRY_OF_DOMICILE, s.CNTRY_OF_DOMICILE)

      , e.MOST_RECENT_PERIOD_END_DT
      , e.EQY_RECENT_SEMI_END
      , e.EQY_RECENT_QT_END_QT
      , e.EQY_RECENT_QT_END_DT
      , e.EQY_RECENT_BS_QT

      , EQY_SIC_NAME = coalesce(e.EQY_SIC_NAME, s.EQY_SIC_NAME)
      , EQY_SIC_CODE = coalesce(e.EQY_SIC_CODE, s.EQY_SIC_CODE)
      , INDUSTRY_SECTOR = coalesce(e.INDUSTRY_SECTOR, s. INDUSTRY_SECTOR)
      , INDUSTRY_GROUP = coalesce(e.INDUSTRY_GROUP, s.INDUSTRY_GROUP)
      , INDUSTRY_SUBGROUP = coalesce(e.INDUSTRY_SUBGROUP, s.INDUSTRY_SUBGROUP)
      , EQY_FUND_IND = coalesce(e.EQY_FUND_IND, s.EQY_FUND_IND)

      , bc.BICS_1
      , bc.BICS_1_NAME
      , bc.BICS_2
      , bc.BICS_2_NAME
      , bc.BICS_3
      , bc.BICS_3_NAME
      , bc.BICS_4
      , bc.BICS_4_NAME
      , bc.BICS_5
      , bc.BICS_5_NAME
      , bc.BICS_6
      , bc.BICS_6_NAME
      , bc.BICS_7
      , bc.BICS_7_NAME

      , e.ValidFromDate
      , e.ValidToDate
      , e.IsCurrent
   From #Entity                            e
   Left Join #Security                     s On s.MasterEntityId = e.MasterEntityId
   Left Join #bics_codes                  bc On bc.MasterEntityId = e.MasterEntityId;
     Go

I did not include any indexes at this point - test for correctness first and adjust as needed. Once the results are validated and correct - then determine if there is a need to improve performance. If there is a need, then add indexes or restructure the code.

You should also validate the joins - for example, if you can change the join from Security to SecurityEntityIndustry and Industry from an outer join to an inner join in the #Security table build - it might improve the performance.

You could also change the #bics_codes - instead of using Between just check for ValidToDate = '99991231'.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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