In this blog post we will see 15 best practices for better Database Performance.
I have written 14 best practices here, read them all and let me know what is as per your opinion should be the 15th best practice.
1. Store relevant and necessary information in the database instead of application structure or 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 to maintain database integrity.
4. Keep primary key of lesser chars or integer. It is easier to process small width keys.
5. Store image paths or URLs in database instead of images. It has less overhead.
6. Use proper database types for the fields. If StartDate is database filed use date time as datatypes instead of VARCHAR (20).
7. Specify column names instead of using * in SELECT statement.
8. Use LIKE clause properly. If you are looking for exact match use “=” instead.
9. Write SQL keyword in capital letters for readability purpose.
10. Using JOIN is better for performance than using sub queries or nested queries.
11. Use stored procedures. They are faster and help in maintainability as well security of the database.
12. User comments for readability as well as guidelines for the next developer who comes to modify the same code. Proper documentation of application will also aid help too.
13. Proper indexing will improve the speed of operations in the database.
14. Make sure to test it any of the database programming as well administrative changes.
15. ______________________________________________________________
Let me know what should be the 15th best practice.
Reference : Pinal Dave (https://blog.sqlauthority.com)
108 Comments. Leave new
15) avoid concatenating multiple fields into a primary key. There are edge case exceptions, but avoid as much as possible.
#15 . follow proper naming standard for better design and readability
I have noticed a significant performance gain using a correlated subquery instead of an OUTER JOIN, such as:
SELECT C.CustomerName,
(SELECT City FROM Address WHERE Address.Entity_ID = C.Customer_ID) AS City
FROM Customer AS C
Rather than:
SELECT C.CustomerName, A.City
FROM Customer AS C
LEFT OUTER JOIN Address AS A
ON C.Customer_ID = A.Entity_ID
If multiple fields are required from Address, perhaps try OUTER APPLY.
Thanks for sharing this.
Leave us to be free to design (after the respective analysis correct) ourselves the database without impositions of chiefs or project leaders.
Keep enough free space in the server, user databases and tempdb. Drop unwanted indexes in the server
!5.1 Create a dedicated database for each application that coexists in the same Server
!5.2 Chose properly the Collation for database
Great thoughts. Thanks.
Reindex and shrink the database periodically for better performance
Shrinking is very bad practice. https://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/
Do not prefix your stored procedure names with “sp_”. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner.
Agree.