SQL Performance: Good Practices for Beginners

Introduction

Most of the developers are like 'accidental developers' and they identify the database performance problems only when the client reports the issues. If you give importance to these guidelines, you can standardise the performance to a stable level. I wrote this article from my own experience and knowledge. Since the internet is a wide resource, like most of the professionals I also got such information from the contributions which I read from browsing.

The statements provided here may not be applicable to all the databases since all the features (Eg: Stored Procedures) are not available on all the database systems.

SQL Performance: Good Practices for Beginners

1. Divide your large table to small multiple tables. (In SQL terminology, we call it as Normalization.)
2. Use the lookup tables. This will help you reduce the overload of tables. E.g.: If you have a product-sales table and want to store 3 pictures of the same product, rather than adding three columns to the table, use one lookup table. This way, you have the freedom to add any number of photos.
3. Also, use only necessary columns. E.g.: If you have columns A & B and you have the sum in another column C. Simply, you don't need that C since we can reproduce the same table effect with statement
   SQL

   SELECT A, B, (A+B) AS C FROM TABLE1

4. The performance of your database increases if the primary key is numeric or small in data width.
5. Do not use database to store your images. A good approach is store only URL in the table and store the image file in desk itself.
6. But sometimes the Normalization may fail if you have a large database, much calculations and frequent calls if the output table is almost static in result. (E.g.: salary details of employees for years old data). In this case, you can improve the performance by using a De-normalized table. But that too has many demerits like very large database, etc.
7. Use the right data types and widths when defining columns. E.g.: If you want to store the 'age', then you don't need to use the VARCHAR field since a TINYINT can do the job. (TINYINT can store integer data from 0 to 255. You know no 'age' value exceeds 255.)
8. Start! If you don't have the practice of using Primary Key, Foreign Key, etc.
9. Use stored procedures and functions instead of writing all the messy code in the program itself. It not only gives you the performance but also a matter of security.
10. Always try to keep one error table to log all the errors which comes from the stored procedures or functions. In T-SQL (version < 2000), you have the global variable @@ERROR to detect errors. From 2005 or greater versions, you have the freedom to use TRY/CATCH which is available in today's highlevel languages. It helps you save time and avoid tension for finding 'Where is the bug?'
11. Use transactions to avoid loss of data while execution of a stored procedure. The possibility of failure is high. It can be a data truncation problem, network problem, etc. In MSSQL, we have BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION available. Use this along with the error handling methods. Regarding performance, TRANSACTION is one step down when comparing with ordinary statements but when considering quality of the product it is very high.
12. Avoid using CURSORs. Use only when no other way exists. In most cases, CURSOR consumes much time since it is a record-by-record process.
13. Avoid using GOTOs. This is not just for SQL but for all the programming languages. GOTO statement is considered unstructured and it has the possibility of jumping out of our hands. But there are circumstances in which we cannot avoid GOTO.
14. Avoid IF and start using CASE.
15. Write your code readable.
    1. User proper indents.
    2. Keep all the statements in upper case.
    3. Use lowercase for data-types.
    4. Use Upper Camel notations (also known as Pascal Casing) for all user created objects. E.g.: @EmployeeCode.
    5. Use meaningful user-defined identifiers. Use only names which contain A-Z, a-Z, 0-9 and underscore character. Do not use regional special characters.
    6. Specify operation performed also in the name of stored procedure. E.g.: spEmployeeUpdate.
    7. Always use the schema also while calling SQL objects. Eg: EXEC dbo.spEmployeeUpdat.
    8. Use BEGIN and END to specify block of statements.
    9. User alias where we need operations including multiple tables.
    10. Even though the AS keyword is optional, use always.
16. Use proper commenting. Also add the purpose, author, date, version, etc. details on top of all the procedures, functions, etc.
17. Add test data with statements inside stored procedures and comment it. This helps you in future debugging. Also this helps another developer to get a quick start.
18. Do proper INDEX-ing. There will be a noticeable difference when applying INDEX on very large tables.
19. Always use column names in SELECT, INSERT, UPDATE statements. Avoid using asterisks –(*). 
20. Avoid using dynamic SQL statements inside stored procedures, i.e., Do not use SQL statements to create SQL statements.
21. Be careful while SELECT-ing strings with LIKE clause. If it is not used wisely, it will give rise to performance problems.

History

• 18 May 2007 Version 1.0
• 23 May 2007 Version 1.1