May 14, 2008 at 5:19 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:38 am
Switch the order of this index, or create a new one
mgr_acc - Non clulstred index on mgr_28B_code,bintent,b_id - 11160 rows
Change to Bintent, b_id, mgr_28B_code
or
b_id, Bintent, mgr_28B_code
Try both, see which is beter
The convert in the select clause won't hurt.
The concatenation in the where will. It prevents index seeks on that column. Since that's the leading column of your index, you have a problem.
4) Will index forcing really help in fine tuning ?
No. Don't tell the optimiser what to do, unless you are 100% sure that you know better than it does.
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
May 15, 2008 at 5:12 am
1) have you checked for blocking? These two tables are EXTREMELY small and even if they are a massive cross product and/or have to do a hash join they should come back very quickly. you can use sp_who2 active to see blocks
2) how many rows come back to the client? if it is a cross product, perhaps you are simply waiting for the results to be displayed on the client?
3) check for waits while this is going on too. maybe the network is clogged or the cpus are overloaded or something.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 15, 2008 at 5:26 am
1) have you checked for blocking? These two tables are EXTREMELY small and even if they are a massive cross product and/or have to do a hash join they should come back very quickly. you can use sp_who2 active to see blocks
What do you mean by massive cross product ?
How to do HashJoin ?
What i have to check in Blocks ?
2) how many rows come back to the client? if it is a cross product, perhaps you are simply waiting for the results to be displayed on the client?
200-300 Rows,Again what do you mean by cross product ?
3) check for waits while this is going on too. maybe the network is clogged or the cpus are overloaded or something.
There is no network colg also there is no overload on the CPU.
karthik
May 15, 2008 at 5:35 am
I told you how to check for blocks: sp_who2 active
you don't do hash joins - the optimizer can. forget I mentioned it
cross product is when joins are many-to-many in nature. you can get an explosion of data. search online for cartesian product for a technical definition
this query should be coming back in a flash given the small amount of data. something is definitely going on to prevent that. what is the query plan and the cost of the query plan? what are the number of reads performed (set statistics io on)?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 15, 2008 at 6:50 am
cross product is when joins are many-to-many in nature. you can get an explosion of data. search online for cartesian product for a technical definition
Oh...Sorry...I though you talked about some non technical related term.
what are the number of reads performed (set statistics io on)?
Table: mgr_acc scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: BmarkDesc scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
Execution Time 4.
SQL Server cpu time: 400 ms. SQL Server elapsed time: 14256 ms.
karthik
May 15, 2008 at 6:52 am
Table: mgr_acc scan count 0, logical reads: (regular=0 apf=0 total=0), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: BmarkDesc scan count 1, logical reads: (regular=3 apf=0 total=3), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Table: Worktable1 scan count 0, logical reads: (regular=9 apf=0 total=9), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0
Execution Time 6.
SQL Server cpu time: 600 ms. SQL Server elapsed time: 14336 ms.
karthik
May 15, 2008 at 7:22 am
blocking is definitely the first thing to look for here. 12 logical reads returning 200-300 rows is essentially instantaneous yet the query took 14 sec.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 15, 2008 at 9:30 am
Guru,
As you suggested,I have executed sp_who2.
i got the below result
310 Sleeping Swqr\Karthik SWETT-234Q . GCM AWAITING COMMAND 11652 1678 05/13 21:28:14 Microsoft SQL Server Management Studio - Query 310 0
karthik
May 15, 2008 at 11:59 pm
karthikeyan (5/15/2008)
AWAITING COMMAND
That's an idle conection. TRy runing sp_who2 while the slow query is executing, see what waits/blocks you have
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
May 16, 2008 at 5:24 am
karthikeyan (5/14/2008)
3) Will concatenation in WHERE clause affect the performance ?Again i think 'Yes'.
The way you use it - Yes, it can't use the index
This is OK
where col = @f+'g'
But this is not
where col+'g' = 'abcdefg'
If you use a function on a column in the where clause then the index on that column can't be used
So instead of
where b.mgr_28B_code+'B' = ? -- Input parameter
Rather remove the trailing B from your input parameter
someting like
where b.mgr_28B_code = left(?,datalength(?)-1) -- Input parameter
May 19, 2008 at 5:00 am
The way you use it - Yes, it can't use the index
This is OK
where col = @f+'g'
But this is not
where col+'g' = 'abcdefg'
If you use a function on a column in the where clause then the index on that column can't be used
So instead of
where b.mgr_28B_code+'B' = ? -- Input parameter
Rather remove the trailing B from your input parameter
someting like
where b.mgr_28B_code = left(?,datalength(?)-1) -- Input parameter
Thanks.
karthik
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply