May 14, 2008 at 4:23 am
All,
I have one senario. My co worker send me a query and he said that it is taking 13-14 seconds to complete its execution. I also executed it in back end,it took around 14 seconds.
Actually he is calling it from the Front End (.NET). End users wanted to reduce it to 3-5 seconds.
Query:
select b_id,
coalesce(bd.Dis, bd.Desc, 'unknown') as bmarkdesc,
convert(varchar(5), convert(decimal(8,1), bm_pct ))+ "%" as weight
from mgr_acc b
left join BmarkDesc bd
on bd.BCode = b.bm_id
where b.mgr_28B_code+'B' = ? -- Input parameter
and b.bintent = 'S'
order by bmpct desc
TABLE Info:
BmarkDesc - Clustered Index on BCode - 743 Rows
mgr_acc - Non clulstred index on mgr_28B_code,bintent,b_id - 11160 rows
I tried out the following methods:
1) select b_id,
coalesce(bd.Dis, bd.Desc, 'unknown') as bmarkdesc,
convert(varchar(5), convert(decimal(8,1), bm_pct ))+ "%" as weight
from mgr_acc b(index 1)
left join BmarkDesc bd (index 0)
on bd.BCode = b.bm_id
where b.mgr_28B_code+'B' = ? -- Input parameter
and b.bintent = 'S'
order by bmpct desc
2)
select b_id,
coalesce(bd.Dis, bd.Desc, 'unknown') as bmarkdesc,
convert(varchar(5), convert(decimal(8,1), bm_pct ))+ "%" as weight
from mgr_acc b
left join BmarkDesc bd (index 0)
on bd.BCode = b.bm_id
where b.mgr_28B_code+'B' = ? -- Input parameter
and b.bintent = 'S'
order by bmpct desc
3)
select b_id,
coalesce(bd.Dis, bd.Desc, 'unknown') as bmarkdesc,
convert(varchar(5), convert(decimal(8,1), bm_pct ))+ "%" as weight
from mgr_acc b(index 1)
left join BmarkDesc bd
on bd.BCode = b.bm_id
where b.mgr_28B_code+'B' = ? -- Input parameter
and b.bintent = 'S'
order by bmpct desc
4) Here i just removed Order by (To avoid WorkTABLE ),but again query took the same 14 seconds.
select b_id,
coalesce(bd.Dis, bd.Desc, 'unknown') as bmarkdesc,
convert(varchar(5), convert(decimal(8,1), bm_pct ))+ "%" as weight
from mgr_acc b(index 1)
left join BmarkDesc bd
on bd.BCode = b.bm_id
where b.mgr_28B_code+'B' = ? -- Input parameter
and b.bintent = 'S'
--order by bmpct desc
5)
select b_id,
coalesce(bd.Dis, bd.Desc, 'unknown') as bmarkdesc,
bm_pct as weight -- Here i removed Convert and concatenation, but no improvement.
from mgr_acc b(index 1)
left join BmarkDesc bd
on bd.BCode = b.bm_id
where b.mgr_28B_code+'B' = ? -- Input parameter
and b.bintent = 'S'
--order by bmpct desc
How to reduce the elapsed time ? Suggestions are welcome!
Also i have some questions:
1) Will CONVERT function affect the performance ?
I think 'Yes'. Bcoz i remember that
(No character based date conversions) + (No concatenation) = Very high speed short code.
if it is mandatory to use CONVERT function,How to avoid it ?
2) What is the alternate way of ORDER BY ?
3) Will concatenation in WHERE clause affect the performance ?
Again i think 'Yes'.
4) Will index forcing really help in fine tuning ?
Bcoz i haven't seen any improvement.
karthik
May 14, 2008 at 6:40 am
Cross post.
Already answered -
http://www.sqlservercentral.com/Forums/Topic500325-8-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply