Best Practices For Better SQL Database Performance

  1. Store relevant and necessary information in the database instead of an application structure or an array.
  2. Use normalized tables in the database. Small multiple tables are usually better than one large table.
  3. If you use any enumerated field to create look up for it in the database itself then maintain the database.
  4. Keep primary key of lesser chars or integers. It is easier to process small width keys.
  5. Store image paths or URLs in the database instead of the images. It has less overhead.
  6. Use proper database types for the fields. If StartDate is the database, use date time as datatypes instead of VARCHAR (20).
  7. Specify the column names instead of using * in SELECT statement.
  8. Use LIKE clause properly. If you are looking for an exact match, use “=” instead.
  9. Write SQL keyword in the capital letters for readability purposes.
  10. Using JOIN is better for performance than using sub queries or the nested queries.
  11. Use the stored procedures. They are faster and help in maintainability as well as security of the database.
  12. The user comments for readability as well as guidelines for the next developer, who comes to modify the same code. Proper documentation of an Application will also help.
  13. Proper indexing will improve the speed of operations in the database.
  14. Make sure to test any of the database programming as well administrative changes.


Next Recommended Reading SQL Database Performance Tuning