Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008 TSQL
hi all,
 
i got good help from this site, thank for all who is helping to others.
 
as part of my job i have to optimize the procedure , this procedure contains dynamic sql inside of it. finally with this procedure we are getting maximum 100 records only.
but it is taking more than 15 minutes to execute so ultimately procedure time out or session timeout error occurred.
all tables having proper indexes and i added with(nolock) for every table still it is taking same time.
 

 
.e nothing but procedure taking more time because of functions used in sqlstatement.
after that i changed the logic as below instead of using function i.e like below
 
 it is taking 10 minutes time to execute.
 
so if any one having idea or facing such problems then  kindly try to change the existing query logic.
 
many thanks to all for spending more time to study it.
kindly provide solution ASAP
Posted 28-Dec-12 6:42am
Edited 28-Dec-12 19:22pm
v4
Comments
Zoltán Zörgő at 28-Dec-12 13:12pm
   
It is hard to optimize something without knowing the purpose of it. I could figure it out, but it would be better if you would tell us... Optimizing a query like this is no automatic task, if it would be, you wouldn't need somebody to do it for you. So I am not surprised that the built-in optimizer is not capable of giving a good plan (or there is no better plan for this - have you checked the plan?) - because even if such a logic can be packed in a single query, it is not always wise to do so. You have several options, but might need to change more than just a query.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Indexes, indexes, indexes.
 
There are a couple of enhancements I would recommend.
 
1) It looks like you use a few scalar functions. Can these be optimized by making them into a table or cte of values that can be referenced instead of executing on each row. Can these values be cached in a variable that can be used later on thus removing all the round trips and selections done on each row.
2) Are your tables and more importantly the values in the where clause in any indexes? If not get those tables indexed.
3) You are using nested queries. Consider moving to temp or cte tables for joining to and not using nested statements.
4) Use
SQL Sentry Plan Explorer
[^] By looking at a more detailed query plan you can ascertain many optimization routes or problems that need to be addressed within the query.
  Permalink  
Comments
FranklinRemo at 28-Dec-12 23:40pm
   
Thank you db7uk , i verified all tables indexes which are used in where clause conditions all are having indexes.now i am not able to change the above query to cte, if possible can you change the above code
FranklinRemo at 28-Dec-12 23:47pm
   
db7uk , because of those scalar function my procedure taking more time to execute.i eliminated function and implemented same functionality with normal select statement but it is not giving proper result for a procedure but procedure is exectuing with in 5 seconds with un-appropriate data
db7uk at 29-Dec-12 10:26am
   
Ok, The problem is that I do not have your database. It will be very hard to "change" the above code when I can not see results etc. Can you provide a query plan? also I think your question has gone a bit funny. There is no code showing. :)
FranklinRemo at 30-Dec-12 6:07am
   
db7uk thank you for your cooperation.
i resolve this issue by storing functions result into physcial table now my procedure is running with in 00 seconds only
db7uk at 30-Dec-12 14:22pm
   
Excellent. Hope the rest goes well!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 499
1 Nirav Prabtani 338
2 OriginalGriff 175
3 _Amy 170
4 PIEBALDconsult 160
0 OriginalGriff 7,640
1 Sergey Alexandrovich Kryukov 6,836
2 Maciej Los 3,919
3 Peter Leow 3,698
4 CHill60 2,742


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100