June 6, 2020 at 3:35 am
Hey Guys
I have a quick questions regarding a better way to write a query:
e2 is a bigger table . I have two columns here CNTRY_ISSUE_NAME and CNTRY_ISSUE_ISO here. I would like to know if there is a better way to write that can give a performance boost to this query. These are just two columns and I have 20 more. Since Entity table was used alot I replaced Entity table with a temp table. I am populating #Entity tand #security able with Entity and Security in the beginning and then using it everywhere. So to avoid more calls to direct able instead. Should I use a table variable instead?
CNTRY_ISSUE_NAME =
case
when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
--and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
then (SELECT top 1 ciss.Name FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
else
(SELECT TOP 1 ciss.Name FROM dbo.#Security s with(nolock)
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = s.CountryIssuedId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--and @AsOfDate between s.ValidFromDate and s.ValidToDate
)
end
,
,CNTRY_ISSUE_ISO =
case
when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
then (SELECT top 1 ciss.ISOCode2 FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
else
(SELECT TOP 1 ciss.ISOCode2 FROM dbo.#Security s with(nolock)
left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = s.CountryIssuedId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--and @AsOfDate between s.ValidFromDate and s.ValidToDate
)
end
June 6, 2020 at 3:50 am
Here are the counts on table. I do not want to hit the table again and again so I put the records in a #temp table. I do not know if table variable will perform better.
select count(*) from [dbo].[Country] ---357
select count(*) from [dbo].[EntityIndustry] --22,434,897
select count(*) from [dbo].[SecurityEntityIndustry] --3,028,692
select count(*) from [dbo].[Security] --20,889,944
June 6, 2020 at 8:12 am
when posting code can you please try and format it better and use the "insert/edit code sample" option so it is easy to follow it.
as all that code is repetitive it can easily be replaced with outer apply making it a lot easier to understand
original code
CNTRY_ISSUE_NAME = case
when exists (SELECT 1
FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock)
ON ciss.CountryId = e2.CountryOfIssueId
and @AsOfDate between ciss.ValidFromDate
and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
--and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
then (SELECT top 1 ciss.Name
FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock)
ON ciss.CountryId = e2.CountryOfIssueId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
else
(SELECT TOP 1 ciss.Name
FROM dbo.#Security s with(nolock)
left join [dbo].[Country] ciss with(nolock)
ON ciss.CountryId = s.CountryIssuedId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId
and s.IsPrimarySecurity = 1
--and @AsOfDate between s.ValidFromDate and s.ValidToDate
)
end
,CNTRY_ISSUE_ISO = case
when exists (SELECT 1
FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock)
ON ciss.CountryId = e2.CountryOfIssueId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
then (SELECT top 1 ciss.ISOCode2
FROM dbo.#Entity e2 with(nolock)
left join [dbo].[Country] ciss with(nolock)
ON ciss.CountryId = e2.CountryOfIssueId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE e2.MasterEntityId = e.MasterEntityId
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
else
(SELECT TOP 1 ciss.ISOCode2
FROM dbo.#Security s with(nolock)
left join [dbo].[Country] ciss with(nolock)
ON ciss.CountryId = s.CountryIssuedId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
WHERE s.MasterEntityId = e.MasterEntityId
and s.IsPrimarySecurity = 1
--and @AsOfDate between s.ValidFromDate and s.ValidToDate
)
end
I think it can be rewritten as follows - and will have a lot less access to the temp tables.
and keep them as temp tables - going table variable for these volumes is not advisable
make sure you add required indexes to temp tables - for example #security should have a clustered index on MasterEntityId
select CNTRY_ISSUE_NAME = case
when entity.MasterEntityId is not null
then entity.Name
else PrimarySec.Name
end
, CNTRY_ISSUE_ISO = case
when entity.MasterEntityId is not null
then entity.ISOCode2
else PrimarySec.ISOCode2
end
from table_name e
outer apply (select top 1 ciss.*
from #entity e2
left outer join dbo.country ciss
on ciss.CountryId = e2.CountryOfIssueId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
where e2.MasterEntityId = e.MasterEntityId
-- order by pick_field may be required so result is always the same
) Entity
outer apply (select top 1 ciss.*
from #Security s
left outer join dbo.country ciss
on ciss.CountryId = s.CountryOfIssueId
and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
where s.MasterEntityId = e.MasterEntityId
and s.IsPrimarySecurity = 1
-- order by pick_field may be required so result is always the same
) PrimarySec
June 6, 2020 at 3:07 pm
One other optimization that *may* be possible is to remove the CASE expression. I have to assume that if a match is found in #entity that the columns from that table are not null - but if they can be null this works better because it pulls the first non-null value found, so if the Entity value is null - and the PrimarySec value is not null you get the PrimarySec value...
select CNTRY_ISSUE_NAME = coalesce(entity.Name, PrimarySec.Name)
, CNTRY_ISSUE_ISO = coalesce(entity.ISOCode2, PrimarySec.ISOCode2)
If you have appropriate indexes on your Entity and PrimarySec tables - that is, an index that supports the outer apply queries then you wouldn't even need the temp tables. In the OUTER APPLY - specify the actual columns to be returned and build a non-clustered covering index to support this query...and definitely include an order by in the query so SQL Server can perform an index seek on the matching value(s).
Without seeing the rest of the code - and how those temp tables are actually used...no way to tell if that can be optimized further. There are several possibilities on how to build the temp table...one way would be to build a single row per MasterEntityId where you have 4 separate columns populated...then COALESCE to get the first non-null value...or you add a row from Entity with a sorting value of 1 and add a rows from PrimarySec with a sorting value of 2 - and OUTER APPLY with TOP 1 and ORDER BY to get the appropriate value...
Jeffrey Williams
“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 3:20 pm
Just to confirm, COALESCE won't provide an improvement in performance over CASE, although it will make the code a whole lot easier. Behind the scenes, the optimizer converts a COALESCE to a CASE statement. You can see that in the properties window of the Compute Scalar block in the execution plan.
ISNULL, on the other hand, will speed things up a bit because it's actually an intrinsic function to T-SQL instead of just a convenient way to write a CASE statement, although it'll take a whole lot of rows to see it. You can also see that in the properties window of the Compute Scalar block in the execution plan.
And, just in case someone brings it up, no... I don't believe in the myth of truly portable code. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2020 at 3:39 pm
It was written by someone else and I am just trying to make it better. There were multiple calls to the table so I stored all data in temp tables with filters. So my temp tables has low rows then actual tables. I am only getting data in temp table which is needed. I am trying to figure out what index I just used on temp table to make this faster.
Here is the a better version with just one variable CNTRY_ISSUE_NAME and this whole code will go inside a sproc.
DECLARE @AsOfDate DATE = '20200529',
@PrivateMarketStatus BIT = 0
SELECT countryofissueid,
masterentityid,
validfromdate,
validtodate,
countryofincorporationid,
countryofdomicileid,
marketstatusid,
primarycurrencyid,
primaryexchangeid,
sourceentityid,
NAME,
primaryexchangeticker,
tickerexchangecode,
recentperiodenddate,
entityid,
recentsemiend,
recentquarterend,
recentquarterenddate,
recentbsperiod,
iscurrent
INTO dbo.#entity
FROM [dbo].[entity]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO dbo.#entityindustry
FROM [dbo].[entityindustry]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,
SELECT *
INTO dbo.#securityentityindustry
FROM [dbo].[securityentityindustry]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT masterentityid,
validfromdate,
validtodate,
[mastersecurityid],
countryissuedid,
isprimarysecurity,
hasmultipleshares,
countryofdomicileid,
countryofincorporationid
INTO dbo.#security
FROM [dbo].[security]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO dbo.#vwbics
FROM vwbics
SELECT e.[masterentityid],
[entityid],
e.[name] AS NAME,
e.[sourceentityid] AS ID_BB_COMPANY,
ID_BB_UNIQUE=map.sourcesecurityid,
[primaryexchangeticker] AS TICKER,
ex.code AS EXCH_CODE,
cr.code AS EQY_FUND_CRNCY,
[tickerexchangecode] AS TICKER_AND_EXCH_CODE,
ms.code AS MARKET_STATUS,
(SELECT Max(Cast(hasmultipleshares AS TINYINT))
FROM [#security] s
WHERE s.masterentityid = e.masterentityid
--AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate
) AS MULTIPLE_SHARE,
CNTRY_ISSUE_NAME = CASE
WHEN EXISTS (SELECT 1
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] ciss
ON ciss.countryid =
e2.countryofissueid
AND @AsOfDate BETWEEN
ciss.validfromdate
AND
ciss.validtodate
WHERE e2.masterentityid =
e.masterentityid
--and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
) THEN (SELECT TOP 1 ciss.NAME
FROM
dbo.#entity e2
LEFT JOIN [dbo].[country] ciss
ON ciss.countryid =
e2.countryofissueid
AND @AsOfDate BETWEEN
ciss.validfromdate
AND
ciss.validtodate
WHERE
e2.masterentityid = e.masterentityid
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
ELSE (SELECT TOP 1 ciss.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[country] ciss
ON ciss.countryid =
s.countryissuedid
AND @AsOfDate BETWEEN
ciss.validfromdate AND
ciss.validtodate
WHERE s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
--and @AsOfDate between s.ValidFromDate and s.ValidToDate
)
END,
e.[validfromdate] AS ValidFromDate,
e.[validtodate] AS ValidToDate,
[iscurrent]
FROM [dbo].[#entity] e
JOIN [dbo].[marketstatus] ms
ON ms.marketstatusid = e.marketstatusid
JOIN [dbo].currency cr
ON cr.currencyid = e.primarycurrencyid
JOIN [dbo].exchange ex
ON ex.exchangeid = e.primaryexchangeid
LEFT JOIN [dbo].entityidmapping map
ON map.sourceentityid = e.sourceentityid
LEFT JOIN (SELECT masterentityid,
[6] AS BICS_1_CODE,
[7] AS BICS_2_CODE,
[8] AS BICS_3_CODE,
[9] AS BICS_4_CODE,
[10] AS BICS_5_CODE,
[11] AS BICS_6_CODE,
[12] AS BICS_7_CODE
FROM (SELECT e2.masterentityid,
i.industryclassificationid,
Code1=Cast(i.code1 AS BIGINT)
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
AND '99991231' BETWEEN
ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e2.masterentityid
AND i.industryclassificationid BETWEEN 6 AND 12
-- BICS level 1 to 7
AND '99991231' BETWEEN e2.validfromdate AND
e2.validtodate
-- and e2.MasterEntityId=926860 -- Abbvie Inc.
) up
PIVOT ( Avg(code1)
FOR industryclassificationid IN ( [6],
[7],
[8],
[9],
[10],
[11],
[12] ) ) AS x)
bics
ON e.masterentityid = bics.masterentityid
LEFT JOIN dbo.#vwbics bics_1
ON bics.bics_1_code = bics_1.bics_code
LEFT JOIN dbo.#vwbics bics_2
ON bics.bics_2_code = bics_2.bics_code
LEFT JOIN dbo.#vwbics bics_3
ON bics.bics_3_code = bics_3.bics_code
LEFT JOIN dbo.#vwbics bics_4
ON bics.bics_4_code = bics_4.bics_code
LEFT JOIN dbo.#vwbics bics_5
ON bics.bics_5_code = bics_5.bics_code
LEFT JOIN dbo.#vwbics bics_6
ON bics.bics_6_code = bics_6.bics_code
LEFT JOIN dbo.vwbics bics_7
ON bics.bics_7_code = bics_7.bics_code
WHERE
--@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate
---AND
( ( ms.code = 'PRIV'
AND @PrivateMarketStatus = 1 )
OR ( ms.code <> 'PRIV'
AND @PrivateMarketStatus = 0 ) )
AND e.sourceentityid = 28046509
--- 1:20 seconds
DROP TABLE #entity
DROP TABLE #entityindustry
DROP TABLE #vwbics
DROP TABLE #securityentityindustry
DROP TABLE #security
It was written by someone else and I am just trying to make it better. There were multiple calls to the table so I stored all data in temp tables with filters. So my temp tables has low rows then actual tables. I am only getting data in temp table which is needed. I am trying to figure out what index I just used on temp table to make this faster.
Here is the a better version with just one variable CNTRY_ISSUE_NAME and this whole code will go inside a sproc.
DECLARE @AsOfDate DATE = '20200529',
@PrivateMarketStatus BIT = 0
SELECT countryofissueid,
masterentityid,
validfromdate,
validtodate,
countryofincorporationid,
countryofdomicileid,
marketstatusid,
primarycurrencyid,
primaryexchangeid,
sourceentityid,
NAME,
primaryexchangeticker,
tickerexchangecode,
recentperiodenddate,
entityid,
recentsemiend,
recentquarterend,
recentquarterenddate,
recentbsperiod,
iscurrent
INTO dbo.#entity
FROM [dbo].[entity]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO dbo.#entityindustry
FROM [dbo].[entityindustry]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,
SELECT *
INTO dbo.#securityentityindustry
FROM [dbo].[securityentityindustry]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT masterentityid,
validfromdate,
validtodate,
[mastersecurityid],
countryissuedid,
isprimarysecurity,
hasmultipleshares,
countryofdomicileid,
countryofincorporationid
INTO dbo.#security
FROM [dbo].[security]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO dbo.#vwbics
FROM vwbics
SELECT e.[masterentityid],
[entityid],
e.[name] AS NAME,
e.[sourceentityid] AS ID_BB_COMPANY,
ID_BB_UNIQUE=map.sourcesecurityid,
[primaryexchangeticker] AS TICKER,
ex.code AS EXCH_CODE,
cr.code AS EQY_FUND_CRNCY,
[tickerexchangecode] AS TICKER_AND_EXCH_CODE,
ms.code AS MARKET_STATUS,
(SELECT Max(Cast(hasmultipleshares AS TINYINT))
FROM [#security] s
WHERE s.masterentityid = e.masterentityid
--AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate
) AS MULTIPLE_SHARE,
CNTRY_ISSUE_NAME = CASE
WHEN EXISTS (SELECT 1
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] ciss
ON ciss.countryid =
e2.countryofissueid
AND @AsOfDate BETWEEN
ciss.validfromdate
AND
ciss.validtodate
WHERE e2.masterentityid =
e.masterentityid
--and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
) THEN (SELECT TOP 1 ciss.NAME
FROM
dbo.#entity e2
LEFT JOIN [dbo].[country] ciss
ON ciss.countryid =
e2.countryofissueid
AND @AsOfDate BETWEEN
ciss.validfromdate
AND
ciss.validtodate
WHERE
e2.masterentityid = e.masterentityid
---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
)
ELSE (SELECT TOP 1 ciss.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[country] ciss
ON ciss.countryid =
s.countryissuedid
AND @AsOfDate BETWEEN
ciss.validfromdate AND
ciss.validtodate
WHERE s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
--and @AsOfDate between s.ValidFromDate and s.ValidToDate
)
END,
e.[validfromdate] AS ValidFromDate,
e.[validtodate] AS ValidToDate,
[iscurrent]
FROM [dbo].[#entity] e
JOIN [dbo].[marketstatus] ms
ON ms.marketstatusid = e.marketstatusid
JOIN [dbo].currency cr
ON cr.currencyid = e.primarycurrencyid
JOIN [dbo].exchange ex
ON ex.exchangeid = e.primaryexchangeid
LEFT JOIN [dbo].entityidmapping map
ON map.sourceentityid = e.sourceentityid
LEFT JOIN (SELECT masterentityid,
[6] AS BICS_1_CODE,
[7] AS BICS_2_CODE,
[8] AS BICS_3_CODE,
[9] AS BICS_4_CODE,
[10] AS BICS_5_CODE,
[11] AS BICS_6_CODE,
[12] AS BICS_7_CODE
FROM (SELECT e2.masterentityid,
i.industryclassificationid,
Code1=Cast(i.code1 AS BIGINT)
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
AND '99991231' BETWEEN
ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e2.masterentityid
AND i.industryclassificationid BETWEEN 6 AND 12
-- BICS level 1 to 7
AND '99991231' BETWEEN e2.validfromdate AND
e2.validtodate
-- and e2.MasterEntityId=926860 -- Abbvie Inc.
) up
PIVOT ( Avg(code1)
FOR industryclassificationid IN ( [6],
[7],
[8],
[9],
[10],
[11],
[12] ) ) AS x)
bics
ON e.masterentityid = bics.masterentityid
LEFT JOIN dbo.#vwbics bics_1
ON bics.bics_1_code = bics_1.bics_code
LEFT JOIN dbo.#vwbics bics_2
ON bics.bics_2_code = bics_2.bics_code
LEFT JOIN dbo.#vwbics bics_3
ON bics.bics_3_code = bics_3.bics_code
LEFT JOIN dbo.#vwbics bics_4
ON bics.bics_4_code = bics_4.bics_code
LEFT JOIN dbo.#vwbics bics_5
ON bics.bics_5_code = bics_5.bics_code
LEFT JOIN dbo.#vwbics bics_6
ON bics.bics_6_code = bics_6.bics_code
LEFT JOIN dbo.vwbics bics_7
ON bics.bics_7_code = bics_7.bics_code
WHERE
--@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate
---AND
( ( ms.code = 'PRIV'
AND @PrivateMarketStatus = 1 )
OR ( ms.code <> 'PRIV'
AND @PrivateMarketStatus = 0 ) )
AND e.sourceentityid = 28046509
--- 1:20 seconds
DROP TABLE #entity
DROP TABLE #entityindustry
DROP TABLE #vwbics
DROP TABLE #securityentityindustry
DROP TABLE #security
June 6, 2020 at 3:40 pm
Just to confirm, COALESCE won't provide an improvement in performance over CASE, although it will make the code a whole lot easier. Behind the scenes, the optimizer converts a COALESCE to a CASE statement. You can see that in the properties window of the Compute Scalar block in the execution plan.
ISNULL, on the other hand, will speed things up a bit because it's actually an intrinsic function to T-SQL instead of just a convenient way to write a CASE statement, although it'll take a whole lot of rows to see it. You can also see that in the properties window of the Compute Scalar block in the execution plan.
And, just in case someone brings it up, no... I don't believe in the myth of truly portable code. 😀
Correct - but in this case it will have a *slight* improvement because it isn't checking the existence of the MasterEntityID and then returning another column value. It also has the benefit that if there is a MasterEntityID match and the column value IS NULL - it will return the value from the other table.
Either ISNULL or COALESCE...I prefer COALESCE as it allows for more columns and sets the data type based on precedence...not on the first column used. But that is just a preference and either will work in this scenario.
Regardless of this issue - I believe the best way to improve the performance here is to either restructure how the temp table(s) are created and used or make sure appropriate non-clustered covering indexes exist.
Jeffrey Williams
“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 4:31 pm
Also based on the execution plan hints to create indexes..I created three NONCLUSTERED index. Do you think there should be a clustered index first?
CREATE NONCLUSTERED INDEX ix1_security
ON [dbo].[#Security] ([ValidFromDate],[ValidToDate])
INCLUDE ([MasterSecurityId],[MasterEntityId],[CountryIssuedId],[CountryOfIncorporationId],[CountryOfDomicileId],[IsPrimarySecurity],[HasMultipleShares])
CREATE NONCLUSTERED INDEX [ix1_EntityIndustry]
ON [dbo].[#EntityIndustry] ([ValidFromDate],[ValidToDate])
INCLUDE ([EntityIndustryId],[IndustryId],[MasterEntityId],[LoadDate],[LoadedBy],[UpdateDate],[UpdatedBy])
CREATE NONCLUSTERED INDEX [ix1_secentInd]
ON [dbo].[#SecurityEntityIndustry] ([ValidFromDate],[ValidToDate])
INCLUDE ([SecurityEntityIndustryId],[IndustryId],[MasterSecurityId],[LoadDate],[LoadedBy],[UpdateDate],[UpdatedBy])
June 6, 2020 at 4:33 pm
Is there more to this code than you have shown? I see several temp tables that are created - and never used. I also see an outer join to a PIVOT that is utilized (this can be rewritten using a CROSSTAB if it is actually used, but it doesn't seem to be used at all) - and I would rewrite that to perform the join to the 'view' one time and 'pivot' the actual value(s) needed instead of multiple joins to the results of the pivot (if possible).
For the #security table - you are only utilizing 2 of the columns (as far as I can tell). Eliminate the other columns since they are not needed or used.
If you are using those other temp tables - then make sure you are selecting only the columns needed.
Jeffrey Williams
“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 5:50 pm
Here is the complete code:
I a now using coalesce so that extra query inside when exists is removed. I do not see a huge performance gain though.
DECLARE @AsOfDate DATE = '20200529',
@PrivateMarketStatus BIT = 0
SET nocount ON;
SELECT countryofissueid,
masterentityid,
validfromdate,
validtodate,
countryofincorporationid,
countryofdomicileid,
marketstatusid,
primarycurrencyid,
primaryexchangeid,
sourceentityid,
NAME,
primaryexchangeticker,
tickerexchangecode,
recentperiodenddate,
entityid,
recentsemiend,
recentquarterend,
recentquarterenddate,
recentbsperiod,
iscurrent
INTO dbo.#entity
FROM [dbo].[entity]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO dbo.#entityindustry
FROM [dbo].[entityindustry]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,
SELECT *
INTO dbo.#securityentityindustry
FROM [dbo].[securityentityindustry]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT masterentityid,
validfromdate,
validtodate,
[mastersecurityid],
countryissuedid,
isprimarysecurity,
hasmultipleshares,
countryofdomicileid,
countryofincorporationid
INTO dbo.#security
FROM [dbo].[security]
WHERE @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO dbo.#vwbics
FROM vwbics
CREATE NONCLUSTERED INDEX ix1_security
ON [dbo].[#Security] ([validfromdate], [validtodate])
include ([MasterSecurityId], [MasterEntityId], [CountryIssuedId],
[CountryOfIncorporationId], [CountryOfDomicileId], [IsPrimarySecurity],
[HasMultipleShares])
CREATE NONCLUSTERED INDEX [ix1_EntityIndustry]
ON [dbo].[#EntityIndustry] ([validfromdate], [validtodate])
include ([EntityIndustryId], [IndustryId], [MasterEntityId], [LoadDate],
[LoadedBy], [UpdateDate], [UpdatedBy])
CREATE NONCLUSTERED INDEX [ix1_secentInd]
ON [dbo].[#SecurityEntityIndustry] ([validfromdate], [validtodate])
include ([SecurityEntityIndustryId], [IndustryId], [MasterSecurityId],
[LoadDate], [LoadedBy], [UpdateDate], [UpdatedBy])
SELECT e.[masterentityid],
[entityid],
e.[name] AS NAME,
e.[sourceentityid] AS ID_BB_COMPANY,
ID_BB_UNIQUE=map.sourcesecurityid,
[primaryexchangeticker] AS TICKER,
ex.code AS EXCH_CODE,
cr.code AS EQY_FUND_CRNCY,
[tickerexchangecode] AS TICKER_AND_EXCH_CODE,
ms.code AS MARKET_STATUS,
(SELECT Max(Cast(hasmultipleshares AS TINYINT))
FROM [#security] s
WHERE s.masterentityid = e.masterentityid
--AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate
) AS MULTIPLE_SHARE,
CNTRY_ISSUE_NAME =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT top 1 ciss.Name FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 ciss.Name FROM dbo.#Security s with(nolock)
-- left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = s.CountryIssuedId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 ciss.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = e2.countryofissueid
AND @AsOfDate BETWEEN ciss.validfromdate AND
ciss.validtodate
WHERE e2.masterentityid = e.masterentityid),
(SELECT TOP 1 ciss.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = s.countryissuedid
AND @AsOfDate BETWEEN ciss.validfromdate AND ciss.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_ISSUE_ISO =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT top 1 ciss.ISOCode2 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = e2.CountryOfIssueId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 ciss.ISOCode2 FROM dbo.#Security s with(nolock)
-- left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId = s.CountryIssuedId and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE((SELECT TOP 1 ciss.isocode2
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = e2.countryofissueid
AND @AsOfDate BETWEEN ciss.validfromdate AND
ciss.validtodate
WHERE e2.masterentityid = e.masterentityid),
(SELECT TOP 1 ciss.isocode2
FROM dbo.#security s
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = s.countryissuedid
AND @AsOfDate BETWEEN ciss.validfromdate AND ciss.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_INCORPORATION_NAME =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId = e2.CountryOfIncorporationId and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT top 1 cinc.Name FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId = e2.CountryOfIncorporationId and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 cinc.Name FROM dbo.#Security s with(nolock)
-- left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId = s.CountryOfIncorporationId and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
-- -- and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE((SELECT TOP 1 cinc.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = e2.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND
cinc.validtodate
WHERE e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cinc.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = s.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND cinc.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_INCORPORATION =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId = e2.CountryOfIncorporationId and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- --- and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT top 1 cinc.ISOCode2 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId = e2.CountryOfIncorporationId and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- --- and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 cinc.ISOCode2 FROM dbo.#Security s with(nolock)
-- left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId = s.CountryOfIncorporationId and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE((SELECT TOP 1 cinc.isocode2
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = e2.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND
cinc.validtodate
WHERE e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cinc.isocode2
FROM dbo.#security s
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = s.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND cinc.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_DOMICILE_NAME =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId = e2.CountryOfDomicileId and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- --- and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT top 1 cdom.Name FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId = e2.CountryOfDomicileId and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 cdom.Name FROM dbo.#Security s with(nolock)
-- left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId = s.CountryOfDomicileId and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
-- -- and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE((SELECT TOP 1 cdom.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = e2.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND
cdom.validtodate
WHERE e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cdom.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = s.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND cdom.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_DOMICILE =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId = e2.CountryOfDomicileId and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- --- and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT top 1 cdom.ISOCode2 FROM dbo.#Entity e2 with(nolock)
-- left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId = e2.CountryOfDomicileId and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
-- WHERE e2.MasterEntityId = e.MasterEntityId
-- --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 cdom.ISOCode2 FROM dbo.#Security s with(nolock)
-- left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId = s.CountryOfDomicileId and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE((SELECT TOP 1 cdom.isocode2
FROM dbo.#entity e2
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = e2.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND
cdom.validtodate
WHERE e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cdom.isocode2
FROM dbo.#security s
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = s.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND cdom.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
[recentperiodenddate] AS MOST_RECENT_PERIOD_END_DT,
[recentsemiend] AS EQY_RECENT_SEMI_END,
[recentquarterend] AS EQY_RECENT_QT_END_QT,
[recentquarterenddate] AS EQY_RECENT_QT_END_DT,
[recentbsperiod] AS EQY_RECENT_BS_QT,
EQY_SIC_NAME =
---case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
-- --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
-- --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
-- LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
-- ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=1
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 1),
(SELECT TOP 1 i.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei WITH(nolock)
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 1)),
EQY_SIC_CODE =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
-- --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT TOP 1 i.Code1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 i.Code1 FROM dbo.#Security s with(nolock)
-- LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
-- ---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=1
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 i.code1
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 1),
(SELECT TOP 1 i.code1
FROM dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 1)),
INDUSTRY_SECTOR =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=2
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=2
-- --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
-- LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
-- ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=2
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 2),
(SELECT TOP 1 i.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
AND @AsOfDate BETWEEN sei.validfromdate AND sei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 2)),
INDUSTRY_GROUP =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=3
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=3
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
-- LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
-- ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=3
-- ---and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
AND @AsOfDate BETWEEN ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 3),
(SELECT TOP 1 i.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 3)),
INDUSTRY_SUBGROUP =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- --- AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=4
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=4
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
-- LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
-- ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=4
-- --and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 4),
(SELECT TOP 1 i.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 4)),
EQY_FUND_IND =
--case
-- when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=5
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
-- LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
-- ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
-- WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=5
-- ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
-- )
-- else
-- (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
-- LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
-- ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
-- left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
-- WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=5
-- --- and @AsOfDate between s.ValidFromDate and s.ValidToDate
-- )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 5),
(SELECT TOP 1 i.NAME
FROM dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 5)),
BICS_1=bics_1.bics_name,
BICS_1_CODE=bics_1.bics_code,
BICS_2=bics_2.bics_name,
BICS_2_CODE=bics_2.bics_code,
BICS_3=bics_3.bics_name,
BICS_3_CODE=bics_3.bics_code,
BICS_4=bics_4.bics_name,
BICS_4_CODE=bics_4.bics_code,
BICS_5=bics_5.bics_name,
BICS_5_CODE=bics_5.bics_code,
BICS_6=bics_6.bics_name,
BICS_6_CODE=bics_6.bics_code,
BICS_7=bics_7.bics_name,
BICS_7_CODE=bics_7.bics_code,
e.[validfromdate] AS ValidFromDate,
e.[validtodate] AS ValidToDate,
[iscurrent]
FROM [dbo].[#entity] e WITH(nolock)
JOIN [dbo].[marketstatus] ms WITH(nolock)
ON ms.marketstatusid = e.marketstatusid
JOIN [dbo].currency cr WITH(nolock)
ON cr.currencyid = e.primarycurrencyid
JOIN [dbo].exchange ex WITH(nolock)
ON ex.exchangeid = e.primaryexchangeid
LEFT JOIN [dbo].entityidmapping map WITH(nolock)
ON map.sourceentityid = e.sourceentityid
LEFT JOIN (SELECT masterentityid,
[6] AS BICS_1_CODE,
[7] AS BICS_2_CODE,
[8] AS BICS_3_CODE,
[9] AS BICS_4_CODE,
[10] AS BICS_5_CODE,
[11] AS BICS_6_CODE,
[12] AS BICS_7_CODE
FROM (SELECT e2.masterentityid,
i.industryclassificationid,
Code1=Cast(i.code1 AS BIGINT)
FROM dbo.#entity e2 WITH(nolock)
LEFT JOIN [dbo].[#entityindustry] ei WITH(
nolock)
ON ei.masterentityid = e2.masterentityid
AND '99991231' BETWEEN
ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE e2.masterentityid = e2.masterentityid
AND i.industryclassificationid BETWEEN 6 AND 12
-- BICS level 1 to 7
AND '99991231' BETWEEN e2.validfromdate AND
e2.validtodate
-- and e2.MasterEntityId=926860 -- Abbvie Inc.
) up
PIVOT ( Avg(code1)
FOR industryclassificationid IN ( [6],
[7],
[8],
[9],
[10],
[11],
[12] ) ) AS x)
bics
ON e.masterentityid = bics.masterentityid
LEFT JOIN dbo.#vwbics bics_1
ON bics.bics_1_code = bics_1.bics_code
LEFT JOIN dbo.#vwbics bics_2
ON bics.bics_2_code = bics_2.bics_code
LEFT JOIN dbo.#vwbics bics_3
ON bics.bics_3_code = bics_3.bics_code
LEFT JOIN dbo.#vwbics bics_4
ON bics.bics_4_code = bics_4.bics_code
LEFT JOIN dbo.#vwbics bics_5
ON bics.bics_5_code = bics_5.bics_code
LEFT JOIN dbo.#vwbics bics_6
ON bics.bics_6_code = bics_6.bics_code
LEFT JOIN dbo.vwbics bics_7
ON bics.bics_7_code = bics_7.bics_code
WHERE
--@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate
---AND
( ( ms.code = 'PRIV'
AND @PrivateMarketStatus = 1 )
OR ( ms.code <> 'PRIV'
AND @PrivateMarketStatus = 0 ) )
AND e.sourceentityid = 28046509
--- 1:20 seconds
DROP TABLE #entity
DROP TABLE #entityindustry
DROP TABLE #vwbics
DROP TABLE #securityentityindustry
DROP TABLE #security
June 6, 2020 at 5:57 pm
This is deleted
June 6, 2020 at 6:28 pm
can moderator please delete my post with all html above? I apologize for that.
June 6, 2020 at 7:14 pm
can moderator please delete my post with all html above? I apologize for that.
It would be quicker if you just edited the post and deleted the code block with a short explanation.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2020 at 7:53 pm
Thank you I just deleted
June 6, 2020 at 8:51 pm
Here is the complete code:
I a now using coalesce so that extra query inside when exists is removed. I do not see a huge performance gain though.
code deleted as not required
Using the coalesce as you did without changing the sql's to be based on outer apply as I gave an example is not likely to be better.
Not only that but using coalesce may not give you the same results as before - and you may be populating variables with values that should not be there - it all depends on how your data is structured but it is a possibility.
regarding indexes
As a rule of thumb avoid creating the indexes that are being suggested unless you know they are likely to be the correct ones.
always look at how the table is accessed and create the indexes based on that.
and try out multiple combinations if one does not help performance as expected.
And as a primary rule always create a clustered index - and add more nonclustered if required.
on the case of #EntityIndustry I would suggest you try this one
create clustered index ix1_EntityIndustry on #EntityIndustry
(EntityIndustryId
)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply