Stored Procedure and Performance Issues

[aax123]

Can anyone suggest a better way of writing this stored procedure? I noticed big performace issues and think it could be written better but don't know how I could do it. Any help would be greatly appreciated.

----------------------------

ALTER PROCEDURE spGetLinerAdDetail
@I_HeaderID int,
@I_SortColumnint,
@I_SortDir varchar(3),
@O_ErrStatusvarchar(3)OUTPUT,
@O_ErrMessagevarchar(500) OUTPUT

AS
Set @O_ErrStatus = 'OK'

-- liner_year asc
if @I_SortColumn = 1 and @I_SortDir = 'A'
select
a.po,
a.created_on,
b.classification_id,
b.vehicle_id,
b.original_text,
b.liner_year,
b.liner_make,
b.liner_model,
b.liner_series,
b.liner_bodystyle,
b.liner_vinfull,
b.liner_vinlast8,
b.liner_miles,
b.liner_retailprice,
b.liner_internetprice,
b.liner_stocknum,
b.liner_descriptionadj,
adj.description,
b.liner_text,
a.run_days,
a.run_date,
isnull(b.cost, 0) as dcost,
isnull(a.cost, 0) as hcost,
a.date_sent,
s.description as header_status,
a.status_code,
c.sub_name,
c.rate_code,
d.pub_name,
e.description as Classification,
b.line_count,
a.sub_publication_id,
a.company_id,
(select count(*) from lnr_budget where company_id = a.company_id) as bcount,
z.status_id,
isnull(zz.vehicle_photo_id, 0) as has_photo
from
lnr_ad_header a (nolock) inner join
lnr_ad_detail b (nolock) on a.ad_header_id = b.ad_header_id left outer join
lnr_description_adj adj (nolock) on b.liner_descriptionadj = adj.description inner join
lnr_sub_publication c (nolock) on a.sub_publication_id = c.sub_publication_id inner join
lnr_publication d (nolock) on c.publication_id = d.publication_id inner join
lnr_status s (nolock) on a.status_code = s.status_code inner join
aax_vehicle z (nolock) on b.vehicle_id = z.vehicle_id left join
aax_vehicle_photos zz (nolock) on z.vehicle_id = zz.vehicle_id and zz.prefered = 1 left join
lnr_status q (nolock) on b.status_code = q.status_code left join
lnr_classification e (nolock) on b.classification_id = e.classification_id
where
a.ad_header_id = @I_HeaderID
order by
b.liner_year asc
-- liner_year asc END

-- liner_year desc
else if @I_SortColumn = 1 and @I_SortDir = 'D'
select
a.po,
a.created_on,
b.classification_id,
b.vehicle_id,
b.original_text,
b.liner_year,
b.liner_make,
b.liner_model,
b.liner_series,
b.liner_bodystyle,
b.liner_vinfull,
b.liner_vinlast8,
b.liner_miles,
b.liner_retailprice,
b.liner_internetprice,
b.liner_stocknum,
b.liner_descriptionadj,
adj.description,
b.liner_text,
a.run_days,
a.run_date,
isnull(b.cost, 0) as dcost,
isnull(a.cost, 0) as hcost,
a.date_sent,
s.description as header_status,
a.status_code,
c.sub_name,
c.rate_code,
d.pub_name,
e.description as Classification,
b.line_count,
a.sub_publication_id,
a.company_id,
(select count(*) from lnr_budget where company_id = a.company_id) as bcount,
z.status_id,
isnull(zz.vehicle_photo_id, 0) as has_photo
from
lnr_ad_header a (nolock) inner join
lnr_ad_detail b (nolock) on a.ad_header_id = b.ad_header_id left outer join
lnr_description_adj adj (nolock) on b.liner_descriptionadj = adj.description inner join
lnr_sub_publication c (nolock) on a.sub_publication_id = c.sub_publication_id inner join
lnr_publication d (nolock) on c.publication_id = d.publication_id inner join
lnr_status s (nolock) on a.status_code = s.status_code inner join
aax_vehicle z (nolock) on b.vehicle_id = z.vehicle_id left join
aax_vehicle_photos zz (nolock) on z.vehicle_id = zz.vehicle_id and zz.prefered = 1 left join
lnr_status q (nolock) on b.status_code = q.status_code left join
lnr_classification e (nolock) on b.classification_id = e.classification_id
where
a.ad_header_id = @I_HeaderID
order by
b.liner_year desc
-- liner_year desc END

-- liner_make asc
else if @I_SortColumn = 2 and @I_SortDir = 'A'
select
a.po,
a.created_on,
b.classification_id,
b.vehicle_id,
b.original_text,
b.liner_year,
b.liner_make,
b.liner_model,
b.liner_series,
b.liner_bodystyle,
b.liner_vinfull,
b.liner_vinlast8,
b.liner_miles,
b.liner_retailprice,
b.liner_internetprice,
b.liner_stocknum,
b.liner_descriptionadj,
adj.description,
b.liner_text,
a.run_days,
a.run_date,
isnull(b.cost, 0) as dcost,
isnull(a.cost, 0) as hcost,
a.date_sent,
s.description as header_status,
a.status_code,
c.sub_name,
c.rate_code,
d.pub_name,
e.description as Classification,
b.line_count,
a.sub_publication_id,
a.company_id,
(select count(*) from lnr_budget where company_id = a.company_id) as bcount,
z.status_id,
isnull(zz.vehicle_photo_id, 0) as has_photo
from
lnr_ad_header a (nolock) inner join
lnr_ad_detail b (nolock) on a.ad_header_id = b.ad_header_id left outer join
lnr_description_adj adj (nolock) on b.liner_descriptionadj = adj.description inner join
lnr_sub_publication c (nolock) on a.sub_publication_id = c.sub_publication_id inner join
lnr_publication d (nolock) on c.publication_id = d.publication_id inner join
lnr_status s (nolock) on a.status_code = s.status_code inner join
aax_vehicle z (nolock) on b.vehicle_id = z.vehicle_id left join
aax_vehicle_photos zz (nolock) on z.vehicle_id = zz.vehicle_id and zz.prefered = 1 left join
lnr_status q (nolock) on b.status_code = q.status_code left join
lnr_classification e (nolock) on b.classification_id = e.classification_id
where
a.ad_header_id = @I_HeaderID
order by
b.liner_make asc
-- liner_make asc END

-- liner_make desc
else if @I_SortColumn = 2 and @I_SortDir = 'D'
select
a.po,
a.created_on,
b.classification_id,
b.vehicle_id,
b.original_text,
b.liner_year,
b.liner_make,
b.liner_model,
b.liner_series,
b.liner_bodystyle,
b.liner_vinfull,
b.liner_vinlast8,
b.liner_miles,
b.liner_retailprice,
b.liner_internetprice,
b.liner_stocknum,
b.liner_descriptionadj,
adj.description,
b.liner_text,
a.run_days,
a.run_date,
isnull(b.cost, 0) as dcost,
isnull(a.cost, 0) as hcost,
a.date_sent,
s.description as header_status,
a.status_code,
c.sub_name,
c.rate_code,
d.pub_name,
e.description as Classification,
b.line_count,
a.sub_publication_id,
a.company_id,
(select count(*) from lnr_budget where company_id = a.company_id) as bcount,
z.status_id,
isnull(zz.vehicle_photo_id, 0) as has_photo
from
lnr_ad_header a (nolock) inner join
lnr_ad_detail b (nolock) on a.ad_header_id = b.ad_header_id left outer join
lnr_description_adj adj (nolock) on b.liner_descriptionadj = adj.description inner join
lnr_sub_publication c (nolock) on a.sub_publication_id = c.sub_publication_id inner join
lnr_publication d (nolock) on c.publication_id = d.publication_id inner join
lnr_status s (nolock) on a.status_code = s.status_code inner join
aax_vehicle z (nolock) on b.vehicle_id = z.vehicle_id left join
aax_vehicle_photos zz (nolock) on z.vehicle_id = zz.vehicle_id and zz.prefered = 1 left join
lnr_status q (nolock) on b.status_code = q.status_code left join
lnr_classification e (nolock) on b.classification_id = e.classification_id
where
a.ad_header_id = @I_HeaderID
order by
b.liner_make desc
-- liner_make desc END

-- liner_model asc
else if @I_SortColumn = 3 and @I_SortDir = 'A'
select
a.po,
a.created_on,
b.classification_id,
b.vehicle_id,
b.original_text,
b.liner_year,
b.liner_make,
b.liner_model,
b.liner_series,
b.liner_bodystyle,
b.liner_vinfull,
b.liner_vinlast8,
b.liner_miles,
b.liner_retailprice,
b.liner_internetprice,
b.liner_stocknum,
b.liner_descriptionadj,
adj.description,
b.liner_text,
a.run_days,
a.run_date,
isnull(b.cost, 0) as dcost,
isnull(a.cost, 0) as hcost,
a.date_sent,
s.description as header_status,
a.status_code,
c.sub_name,
c.rate_code,
d.pub_name,
e.description as Classification,
b.line_count,
a.sub_publication_id,
a.company_id,
(select count(*) from lnr_budget where company_id = a.company_id) as bcount,
z.status_id,
isnull(zz.vehicle_photo_id, 0) as has_photo
from
lnr_ad_header a (nolock) inner join
lnr_ad_detail b (nolock) on a.ad_header_id = b.ad_header_id left outer join
lnr_description_adj adj (nolock) on b.liner_descriptionadj = adj.description inner join
lnr_sub_publication c (nolock) on a.sub_publication_id = c.sub_publication_id inner join
lnr_publication d (nolock) on c.publication_id = d.publication_id inner join
lnr_status s (nolock) on a.status_code = s.status_code inner join
aax_vehicle z (nolock) on b.vehicle_id = z.vehicle_id left join
aax_vehicle_photos zz (nolock) on z.vehicle_id = zz.vehicle_id and zz.prefered = 1 left join
lnr_status q (nolock) on b.status_code = q.status_code left join
lnr_classification e (nolock) on b.classification_id = e.classification_id
where
a.ad_header_id = @I_HeaderID
order by
b.liner_model asc
-- liner_model asc END

-- liner_model desc
else if @I_SortColumn = 3 and @I_SortDir = 'D'
select
a.po,
a.created_on,
b.classification_id,
b.vehicle_id,
b.original_text,
b.liner_year,
b.liner_make,
b.liner_model,
b.liner_series,
b.liner_bodystyle,
b.liner_vinfull,
b.liner_vinlast8,
b.liner_miles,
b.liner_retailprice,
b.liner_internetprice,
b.liner_stocknum,
b.liner_descriptionadj,
adj.description,
b.liner_text,
a.run_days,
a.run_date,
isnull(b.cost, 0) as dcost,
isnull(a.cost, 0) as hcost,
a.date_sent,
s.description as header_status,
a.status_code,
c.sub_name,
c.rate_code,
d.pub_name,
e.description as Classification,
b.line_count,
a.sub_publication_id,
a.company_id,
(select count(*) from lnr_budget where company_id = a.company_id) as bcount,
z.status_id,
isnull(zz.vehicle_photo_id, 0) as has_photo
from
lnr_ad_header a (nolock) inner join
lnr_ad_detail b (nolock) on a.ad_header_id = b.ad_header_id left outer join
lnr_description_adj adj (nolock) on b.liner_descriptionadj = adj.description inner join
lnr_sub_publication c (nolock) on a.sub_publication_id = c.sub_publication_id inner join
lnr_publication d (nolock) on c.publication_id = d.publication_id inner join
lnr_status s (nolock) on a.status_code = s.status_code inner join
aax_vehicle z (nolock) on b.vehicle_id = z.vehicle_id left join
aax_vehicle_photos zz (nolock) on z.vehicle_id = zz.vehicle_id and zz.prefered = 1 left join
lnr_status q (nolock) on b.status_code = q.status_code left join
lnr_classification e (nolock) on b.classification_id = e.classification_id
where
a.ad_header_id = @I_HeaderID
order by
b.liner_model desc
-- liner_model desc END

[FalseDawn]

Run the individual queries in the SP through query analyzer and see what indexes it recommends adding and also check for bottlenecks.

You haven't really provided anough information to enable anyone to offer definitive help - eg table sizes, existing indexes, and most importantly - what the SP is supposed to accomplish.

[aspdaddy]

Couple of quick observations that may or may not help -

1) Dont start your procedure names with sp, it thinks its a system proc so looks first in the system procs for the definition.

2) Fully qualify all your tbles and fields e.g dbo.Tablename.FieldName

3)Isnull. I have found CASE WHEN Field is Null Field ELSE 0 END AS Alias to be much,much faster.

4) I havent had aproper look at the proc but it seems to switch to a few different queries based on inputs this will make it hard to compile efficiently. Could this be handled in teh application and have 3 or so different procs without the IF statements?

Some more tips here
[mssqlcity.com...]