I was working on some code when I came across a behemoth of an inline SQL query nestled in the code behind of a page.
Select a.list_id, a.company_name, a.company_description, a.url,
( case when (Select upper(County_Name) from tbl_County where County_id=a.County_id) is null then
(Select upper(Country_Name) from tbl_description_Country where Country_id= a.Country_id)
else
(Select upper(County_Name) from tbl_County where County_id= a.County_id) end )
as County_name,
b.review_approval,
( case when (Select county_id from tbl_County where County_id = a.County_id)= 0 then
(Select Country_id from tbl_description_Country where Country_id = a.Country_id)
else
(Select county_id from tbl_County where County_id= a.County_id) end )
as CountryID,
dbo.funcGetRatingSum (a.list_id) as ratingSum,
dbo.funcGetRatingCount (a.list_id) as totalCount,
(Select case when (select top 1 banner_path from tbl_banners where list_id = a.list_id and a.subscription_id = 2) is null then
''
else
(select top 1 banner_path from tbl_banners where list_id = a.list_id and a.subscription_id = 2) end)
as bannerpath
from tbl_directorylisting a, tbl_preferences as b ,
tbl_companymaster c where a. status = 1 and(a.company_id =
b.company_id And a.status = 1) and c.category_id= @CategoryID
and a.company_id= c.company_id and a.subscription_id in (2,3) and subscription_date >= (dateadd(year,-1,getdate()))
Order by county_name, a.subscription_id asc, a.date_fee_paid
Actually it was within several lines of concatenated text values, and had no formatting whatsoever!. My first intention was try to make it a stored procedure, but doing so I couldn't help but try to optimize it a little. Here's my attempt....
Select
listing.list_id,
listing.company_name,
listing.company_description,
listing.url,
Coalesce(county.County_name, country.Country_Name) As County_name,
prefs.review_approval,
listing.Country_ID,
dbo.funcGetRatingSum (listing.list_id) as ratingSum,
dbo.funcGetRatingCount (listing.list_id) as totalCount,
Coalesce((select top 1 banner_path from tbl_banners where list_id = listing.list_id and listing.subscription_id = 2), '') as bannerpath
FROM
tbl_directorylisting listing
Inner Join tbl_preferences prefs
On listing.company_id = prefs.company_id
Inner Join tbl_companymaster company
On listing.company_id = company.company_id
Left Outer Join tbl_county county
On listing.county_id = county.county_id
Inner Join tbl_country country
On listing.country_id = country.country_id
where listing.status = 1
And company.category_id= @CategoryID
and listing.subscription_id in (2,3)
and listing.subscription_date >= (dateadd(year,-1,getdate()))
Order by county_name, listing.subscription_id asc, listing.date_fee_paid
Firstly you'll notice the second query is far easier to read (hopefully). I dont stand for the
old skool method of joining tables, join with a join and it's obvious what is going on, plus it prepares isolation of results for the where clause afterwards.
Secondly there are fewer joins in the Column definition part of the select. I have included one of the original selects, because when optimizing the stored procedure it was siginificantly fast enough to use
select top 1 ... than it was to
Left Outer Join. I did however
Coalesce so that the
case statement didn't repeat iteself.
What do you think? Is that better or worse?
