Click here to Skip to main content
15,867,888 members
Articles / Database Development / SQL Server

SQL Performance: Good Practices for Beginners

Rate me:
Please Sign up or sign in to vote.
3.00/5 (16 votes)
23 May 2007CPOL4 min read 102.7K   47   39
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
    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

License

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


Written By
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/.

Comments and Discussions

 
Generalremove this crap article from Cp. Thanks. Pin
shaseena26-Nov-07 22:50
shaseena26-Nov-07 22:50 
GeneralRe: remove this crap article from Cp. Thanks. Pin
Praveen Nair (NinethSense)26-Nov-07 23:03
Praveen Nair (NinethSense)26-Nov-07 23:03 
GeneralRe: remove this crap article from Cp. Thanks. Pin
Mike Ozzie27-Nov-07 4:53
Mike Ozzie27-Nov-07 4:53 
GeneralTransact-SQL Optimization Tips Pin
Praveen Nair (NinethSense)13-Aug-07 2:15
Praveen Nair (NinethSense)13-Aug-07 2:15 
GeneralRe: Transact-SQL Optimization Tips Pin
Praveen Nair (NinethSense)13-Aug-07 2:17
Praveen Nair (NinethSense)13-Aug-07 2:17 
GeneralDo not use database to store your images.... Pin
tuca.ssa28-May-07 7:22
tuca.ssa28-May-07 7:22 
GeneralRe: Do not use database to store your images.... Pin
Praveen Nair (NinethSense)28-May-07 19:03
Praveen Nair (NinethSense)28-May-07 19:03 
Generalnot 'bad' but.. Pin
wk63325-May-07 8:54
wk63325-May-07 8:54 
GeneralRe: not 'bad' but.. Pin
Praveen Nair (NinethSense)26-May-07 3:07
Praveen Nair (NinethSense)26-May-07 3:07 
GeneralRe: not 'bad' but.. Pin
wk63326-May-07 12:44
wk63326-May-07 12:44 
GeneralRe: not 'bad' but.. Pin
Ben Daniel26-May-07 22:11
Ben Daniel26-May-07 22:11 
GeneralCasing Pin
ravimama23-May-07 0:15
ravimama23-May-07 0:15 
GeneralRe: Casing Pin
Praveen Nair (NinethSense)23-May-07 2:00
Praveen Nair (NinethSense)23-May-07 2:00 
GeneralRe: Casing Pin
Praveen Nair (NinethSense)23-May-07 2:14
Praveen Nair (NinethSense)23-May-07 2:14 
GeneralRe: Casing Pin
ravimama23-May-07 23:05
ravimama23-May-07 23:05 
GeneralGreat Stuff!!! Pin
Bertus Kruger22-May-07 16:05
Bertus Kruger22-May-07 16:05 
General22- Use shortest columns in a index Pin
federico0721-May-07 9:55
federico0721-May-07 9:55 
GeneralRe: 22- Use shortest columns in a index Pin
Praveen Nair (NinethSense)21-May-07 19:03
Praveen Nair (NinethSense)21-May-07 19:03 
GeneralNever use stored procedures Pin
PIEBALDconsult18-May-07 5:28
mvePIEBALDconsult18-May-07 5:28 
GeneralRe: Never use stored procedures [modified] Pin
Praveen Nair (NinethSense)18-May-07 6:14
Praveen Nair (NinethSense)18-May-07 6:14 
JokeRe: Never use stored procedures Pin
Praveen Nair (NinethSense)18-May-07 6:43
Praveen Nair (NinethSense)18-May-07 6:43 
This article is for devlopers who use a front-end language (C#, php etc.) to manipulate SQL.

Once I was reviewing c# code of my friend.
I asked him : "Do you change the data of this table anywhere else?".
Without thinking he answered :"Yes! with Enterprise Manager".




PraVeeN
blog.ninethsense.com/

GeneralRe: Never use stored procedures Pin
PIEBALDconsult18-May-07 7:12
mvePIEBALDconsult18-May-07 7:12 
GeneralRe: Never use stored procedures [modified] Pin
User of Users Group18-May-07 12:35
User of Users Group18-May-07 12:35 
GeneralRe: Never use stored procedures Pin
wk63325-May-07 8:48
wk63325-May-07 8:48 
GeneralRe: Never use stored procedures Pin
Praveen Nair (NinethSense)26-May-07 2:57
Praveen Nair (NinethSense)26-May-07 2:57 

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

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