How do you increase stored procedure execution time?

Answer 1

1. Use SET NOCOUNT ON

By default, every time a stored procedure is executed, a message is sent from the server to the client indicating the number of rows that were affected by the stored procedure. By turning off this default behaviour, you can reduce network traffic between the server and the client.

2. Use fully qualified name while invoking stored procedures from application and inside SPs.

This allows SQL Server to access the stored procedures execution plan more directly, and in turn, speeding up the performance of the stored procedure.

Use this,

EXEC dbo.yourProcedure

Instead of

EXEC yourProcedure

4. Select only the required columns

Usage of SELECT * can be the cause of NOT using the indexes available on the table. Also, selecting more data means more IO resources.

5. Minimize the use of not equal operations, <> or !=.

SQL Server has to scan a table or index to find all values to see if they are not equal to the value given in the expression.

Use this,

SELECT Name, Description, OrderDate FROM Orders WHERE OrderDate < '2005-01-01' OR OrderDate > '2011-01-01'

instead of this

SELECT Name, Description, OrderDate FROM Orders WHERE OrderDate <> '2011-01-11'

6. Avoid Mixing-up DML and DDL statement on a temp table inside sp

When you create a temp table (#table) and ALTER the same temptable in the same stored procedure later, it causes the stored procedure to get recompiled.

7. Avoid using DISTINCT when not required.

Sort operation is performed for a distinct calculation, which is costly and hits performance.

8. Ensure columns with similar datatype are used when using JOINS

For best performance, the columns used in joins should be of the same data types. And if possible, they should be numeric data types rather than character types.

9. Use EXISTS clause instead of Count(*) for checking existence

Use EXISTS instead of COUNT(*) when looking for the existence of one or more rows in a sub query. EXISTS cancels the sub query once the first existence of a record is found, while COUNT(*) forces processing of the entire query.

10.Remove PRINT statements from stored procedures.

Normally print statements are used for debugging the SPs. Print statements adds an additional overhead to performance because SQL server sends the output to client.

11.Do not use Dynamic SQL inside stored procedures.

Every query you run in SQL Server requires a query plan. When you run a query the first time, SQL Server builds a query plan for it . SQL Server saves the plan in cache, and next time you run the query, the plan is reused. While using dynamic SQL, query execution plan is not cached for the dynamic SQL and it will affect the performance badly.

12.Keep all Transactions short as possible.

This helps to reduce the number of locks (of all types), helping to speed up the overall performance of SQL Server.

13.Use SP_EXECUTESQL rather than EXEC(), it has better performance and improved security

Sp_executesql offers two major advantages over EXECUTE,

1. First, it supports parameter substitution, parameterised statements gives no risk to SQL injection and increases readability.

2. Second, it creates query execution plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance.

14.Always put the DECLARE statements in the starting of the stored procedure.

This enables query optimizer to reuse query plans and also increases readability.

15.Use UNION and UNION ALL properly based on requirement.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

16.Do not use Cursors when they are not required.

Cursors of any kind slow SQL Server's performance. While is some cases they cannot be avoided, in many cases they can. If you need to perform row-by-row operations, consider using one or more of these options instead of using a cursor:

a) Use temp tables

b) Use WHILE loops

c) Use derived tables

d) Use correlated sub-queries

e) Use CASE statements

f) Use multiple queries

17.Don't do SELECT MAX(ID) from MasterTable when inserting in a details table.

This will fail when concurrent users are inserting data at the same

instance. Use one of SCOPE_IDENTITY or IDENT_CURRENT. SCOPE_IDENTITY would give you the identity value from the current context in perspective.

18.All stored procedures should contain the prescribed header.

19.Code that has been "commented out" should be explained or removed.

Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them. Unused code just adds unnecessary bloat to your stored procedures.

20.Write SQL keyword in capital letters for better readability and ensure the code is formatted properly.

a) SQL Keywords should start in a new line and all the keywords should be aligned together.

b) Is the code readable?

UPDATE dbo.Orders SET OrderStatus = @ORDER_PENDING WHERE OrdDate < '2001/10/25'

21.Do not use NON ANSI joins

The use of the JOIN clause separates the relationship logic from the filter logic (the WHERE) and is thus cleaner and easier to understand.

ANSI

SELECT * FROM a JOIN b ON a.id = b.id

Non ANSI

SELECT * FROM a, b WHERE a.id = b.id