ey 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