Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
SQL
 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
Updated 28-Dec-12 19:22pm
v4
Comments
Zoltán Zörgő 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

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.
 
Share this answer
 
Comments
FranklinRemo 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 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 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 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 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900