Performance TIPS - 1

Guys,

Today I would like to share some performance tips, which I got while we ran an analysis tool on our code. I will share this in parts, so here is part - 1.

Stored Procedure naming convention - prefix control: Names of Procedures should start with X. The prefix must not start with sp because sp carries an overhead. If we call a procedure with sp_, it will first search the MASTER database before searching the current database. By default, only members of the dbo_owner role and db_ddladmin role can create stored procedures. Members of the dbo_owner role can give other users the ability to create procedures using a GRANT statement.

Avoid Functions and Procedures doing an Insert, Update or Delete without including error management: Avoid stored procedures or functions to Insert, Update or Delete without error management. You should use @@error for SQL 2000 and BEGIN TRY and BEGIN CATCH for SQL 2005. You will have full control and can handle all exceptions (both application and system) easily and update user accordingly.


Avoid Stored Procedures not returning a status value: We must avoid using a stored procedure which does not return anything. There should be a RETURN status.


Avoid Functions / Procedures with a complex SELECT clause: Avoid Functions / Procedures with a SELECT clause returning more than 9 columns or with a ´SELECT *´ query. Such queries are considered complex. Changing threshold value requires a Metric Assistant configuration update. In practical scenario its generally not possible to restrict the result to 9 columns but we must try to follow wherever we can.

Avoid Functions / Procedures with SQL statement including Subqueries: Functions / Procedures should not use SQL statement including Subqueries (at least subqueries should be avoided). We generally use subqueries, we have to use subqueries, we can take output into a table variable then we can use join to avoid subquery.

Avoid using temporary Objects:Triggers, Views, Functions and Procedures should not use temporary Objects (except temporary Tables). We can use temp tables / table variables.

Watch for more tips later on. Do write your comments and let me know what else you want to know.

Regards,

Sudhir