Interview question stored procedure

Can anyone answer ths question please.

If your page is running slow what sould u check specifically with the store procedure. What is it tht you might think is giving a problem. or how would u go about see what making your stored procdure run slow.??

Give these articles a try:

http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx

http://articles.techrepublic.com.com/5100-22-1045447.html

http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

|||

Absolute basics are check the execution plan, look for things like table scans which could be turned into index seeks by adding indexes, perhaps use the database engine tuning advisor.

Check the SQL for subselects where joins could be used instead, multiple predicates where an IN or BETWEEN clause would be better, unnecessary ORDER BY or DISTINCT instructions, possibly split up stored procedures which have the format

if inputvar = true

do this select

else

do that select

into separate sub-stored procedures for each select with the top level sp just doing the logic and then calling the appropriate sub proc (having multiple optional queries in the same proc means that the query plan may have to be re-generated each time, which costs performance)

Lots of other things you can do too, which I'm sure are mentioned in the above answer's links.