Click here to Skip to main content
Click here to Skip to main content

SQL Performance: Good Practices for Beginners

, 23 May 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
This article gives simple practical guidelines which will help a developer to design good performance, high quality SQL databases.

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
    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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Praveen Nair (NinethSense)
Architect ORION INDIA SYSTEMS
India India
Praveen.V.Nair - aka NinethSense - PMP, Microsoft MVP - is working as a Head of Technology and Architecture at Orion India Systems, Kochi, India. He has been playing with electronics from the age of 10 and with computers from the age of 14. He usually blogs at http://blog.ninethsense.com/.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
Generalremove this crap article from Cp. Thanks. Pinmembershaseena26-Nov-07 23:50 
GeneralRe: remove this crap article from Cp. Thanks. PinmemberNinethSense27-Nov-07 0:03 
GeneralRe: remove this crap article from Cp. Thanks. PinmemberMike Ozzie27-Nov-07 5:53 
GeneralTransact-SQL Optimization Tips PinmemberNinethSense13-Aug-07 3:15 
GeneralRe: Transact-SQL Optimization Tips PinmemberNinethSense13-Aug-07 3:17 
GeneralDo not use database to store your images.... Pinmembertuca.ssa28-May-07 8:22 
GeneralRe: Do not use database to store your images.... PinmemberNinethSense28-May-07 20:03 
Generalnot 'bad' but.. Pinmemberwk63325-May-07 9:54 
GeneralRe: not 'bad' but.. PinmemberNinethSense26-May-07 4:07 
GeneralRe: not 'bad' but.. Pinmemberwk63326-May-07 13:44 
GeneralRe: not 'bad' but.. PinmemberBen Daniel26-May-07 23:11 
GeneralCasing Pinmemberravimama23-May-07 1:15 
GeneralRe: Casing PinmemberNinethSense23-May-07 3:00 
GeneralRe: Casing PinmemberNinethSense23-May-07 3:14 
GeneralRe: Casing Pinmemberravimama24-May-07 0:05 
GeneralGreat Stuff!!! PinmemberBertus Kruger22-May-07 17:05 
General22- Use shortest columns in a index Pinmemberfederico0721-May-07 10:55 
GeneralRe: 22- Use shortest columns in a index PinmemberNinethSense21-May-07 20:03 
GeneralNever use stored procedures PinmemberPIEBALDconsult18-May-07 6:28 
GeneralRe: Never use stored procedures [modified] PinmemberNinethSense18-May-07 7:14 
JokeRe: Never use stored procedures PinmemberNinethSense18-May-07 7:43 
GeneralRe: Never use stored procedures PinmemberPIEBALDconsult18-May-07 8:12 
GeneralRe: Never use stored procedures [modified] PinmemberUser of Users Group18-May-07 13:35 
GeneralRe: Never use stored procedures Pinmemberwk63325-May-07 9:48 
GeneralRe: Never use stored procedures PinmemberNinethSense26-May-07 3:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411022.1 | Last Updated 23 May 2007
Article Copyright 2007 by Praveen Nair (NinethSense)
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid