How to tune a stored procedure?
Answers were Sorted based on User's Feedback
Answer / arun yadav
There could be 'n' reasons for slow performance. For tuning
the performance of a SP we need to know how much resources
it is using and how much time it takes in execution. We can
check this using the performance monitor (for example, RPC
Counter) and execution plan. Check if we are using
appropriate joins, table defrag, index rebuilt can be the
solutions.
Is This Answer Correct ? | 9 Yes | 1 No |
Answer / gopi a
Stored Procedure is Precompiled set of quries. Stored
Procedure performence fully depended on this queries. First
Look the queries performence using Execution Plan. After
find the Queries, just check the Index(for table used in
query) and look the join conditions between tables in the
query. - This is way we need to approch to tune the SP.
Is This Answer Correct ? | 6 Yes | 2 No |
Answer / virgilio
The statements from above are correct but one main thing
that might speed a proc up is the naming convention.
If you create a user defined Storproc and you name it by
using the "Sp_" prefix SQL thinks is a System Store Proc
and goes to the Master DB to look for the Object instead of
looking locally.
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / arindam choudhury
suppose the query is correct in astored procedure then
1)we need to use tablename with (nolock) in our select
statements so that it does not create delay if other person
is updating the table at the same time it will show last
updated table recordset
2)if we dont need that much the database server to return
the number of records affected after executing any dml
statements which actually reduces the performance ,we can
use set nocount on -set nocount off before and after
writing the sql query,
3)wecan create lesser join statements if we can use
subqueries
these are the few steps to tune stored procedure
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vimal
Store procedure is a pre compiled code
it is doing single task of several task
it is used to reduce the execution time
if we want to call store procedure just we call its name
Is This Answer Correct ? | 1 Yes | 3 No |
Answer / neethu mohanan
stored procedure is used to write queries,if there is an
error in the query it willnot be saved.In this if there is
any parameters first declare it,and then the query is write.
Each stored procedure could have a name.We can create the
stored procedure for any tables in the database.
create database->create table->stored procedure.
format:
(stored procedure name)
(@parameter1 type(size),@parameter2 type(size))
//(query)
insert into tablename values(@parameter1,@parameter2 )
Is This Answer Correct ? | 3 Yes | 11 No |
create table with fields ID, reserved_by,res_date res_date is datefield like 2010-03-09 00:00:00.000 from 2005 to 2006 any date assume based on res_date need to slect table and display based on month (full jan details in database irrespective of date and year
How to include date and time values in sql statements?
WHAT IS UNIQUE IDENTIFIER DATA TYPE?
You are doing log shipping due to some reasons it is failing. How you will proceed from there
How to create logins using windows Authentication mode?
How to write an inner join with the where clause in ms sql server?
How many types of triggers in sql server?
What are the dmvs?
How can you insert null values in a column while inserting the data?
Differnce between sel server 2000 and 2005
What is SQL Profiler and the use of it?
What are the differences between left join and inner join in sql server?