SQLTeam.com | Weblogs | Forums

Sql performance tuning

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

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

See the thread here - same question: https://www.sqlservercentral.com/forums/topic/performance-tuning-for-a-subquery