June 6, 2020 at 9:01 pm
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?
June 6, 2020 at 9:17 pm
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.
June 6, 2020 at 9:22 pm
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
June 6, 2020 at 9:28 pm
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.
June 6, 2020 at 10:01 pm
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
June 6, 2020 at 10:02 pm
That worked perfectly. Thanks!
June 6, 2020 at 10:32 pm
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
June 6, 2020 at 11:07 pm
Those are some amazing suggestions. Thanks!
I will change the script and hopefully see the benefit. Do you recommend any non-clustered indexes?
June 7, 2020 at 12:27 am
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
)
)
June 7, 2020 at 10:00 am
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
June 7, 2020 at 4:54 pm
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
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply