SQL Server Performance Forum – Threads Archive

sp tuning ideas

code removed.

Use index tuning wizard to look for any recommendation on indexes. Also look at the execution plan of the query to look for any visible bottlenecks. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Execution plan is good.
Selectivity of each non-clustered index is good.
Clustered indexes are good choices. Running now at around 750 ms. Still need to improve this.

i suspect the bulk of your cpu is going into the parse and compile for the the dynamic sql,
try writing a stored proc for an explicit case without the case handling capability and compare it with the full dynamic sql another potential cpu load is recompile caused by inserts into temp tables, the recompile set points are 6 rows and 500 rows + 20% or something like that (MS SQL Server article on Query Recompilation if some could lookup the url) OPTION (KEEP PLAN) inhibits the 6 row recompile. (KEEP FIXED PLAN) inhibits the second set point, but i do not recommend that setting

There are couple of good discusiions on this forum on dynamic search that you need in this procedure. Try looking at them. Search for "Dynamic" keyword. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

any coding ideas?

See http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=708
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=624
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=999
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=953 HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

cool. Thanks!!!

Forum Locked to prevent further posts as original code was removed. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

]]>