Click here to Skip to main content
13,503,505 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 28-Dec-12 19:22pm
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

Rate this: bad
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.
FranklinRemo 28-Dec-12 23:40pm
Thank you db7uk , i verified all tables indexes which are used in where clause conditions all are having 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web03 | 2.8.180417.1 | Last Updated 29 Dec 2012
Copyright © CodeProject, 1999-2018
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