This blog post will discuss 15 best practices for improving database performance. I have listed 14 best practices below. Read through them and let me know what you think should be the 15th best practice.
- Store only relevant and necessary data in the database
Avoid storing data in application structures or arrays when it belongs in the database. - Use normalized tables
Normalize your database to eliminate redundancy and maintain data integrity. Smaller, related tables are often better than one large table. - Create lookup tables for enumerated fields
For enumerated fields, use lookup tables to enforce consistency and maintain database integrity. - Use small and efficient primary keys
Choose short primary keys, such as integers or small character fields, to optimize performance. - Store image paths or URLs instead of images
Save image paths or URLs in the database and store the images in file storage to reduce overhead. - Select appropriate data types
Use precise data types for fields, such asDATETIMEfor date fields instead ofVARCHAR(20). - **Avoid SELECT ***
Specify column names inSELECTstatements to improve performance and readability. - Use LIKE clauses sparingly
When an exact match is required, use the=operator instead ofLIKEfor better efficiency. - Write SQL keywords in uppercase
Using uppercase for keywords such asSELECT,WHERE, andJOINenhances code readability. - Prefer JOINs over subqueries
UseJOINinstead of subqueries or nested queries for improved performance and clarity. - Utilize stored procedures
Stored procedures improve execution speed, simplify maintenance, and enhance security. - Comment and document your code
Add comments to your SQL scripts for clarity and as a guide for future developers. Proper application documentation is equally important. - Use proper indexing
Create and maintain indexes to improve query performance, keeping in mind the trade-off with write operations. - Test all changes thoroughly
Test any database programming or administrative changes in a non-production environment before deployment. - Monitor and optimize query performance
Use tools like SQL Server Profiler or Query Store to identify and optimize slow-performing queries. Regularly review execution plans to ensure efficient operations.
These practices will help you design, maintain, and optimize databases and for database performance and long-term reliability. Let me know your thoughts or if you have additional tips to share!
Reference: Pinal Dave (https://blog.sqlauthority.com)
108 Comments. Leave new
This is for Mr.Prashant:
Take image field name as n/varchar(max) named mainpic. Insert image name in a that column. Suppose one of the field value is default.jpg. No need to have image datatype.
Make a folder in Application same directory. And store Image with same name as in database in that folder say D:\\project\images\default.jpg
Now in datalist data fetch coding, in image tag.
if at design :
<asp:Image ID=”imgperson” runat=”server” imageurl=”images/” />
This is for Mr.Prashant:
Take image field name as n/varchar(max) named mainpic. Insert image name in a that column. Suppose one of the field value is default.jpg. No need to have image datatype.
Make a folder in Application same directory. And store Image with same name as in database in that folder say D:\\project\images\default.jpg
Now in datalist data fetch coding, in image tag.
if at design :
<asp:Image ID=”imgperson” runat=”server”
imageurl=”images/” />
The 15th best practice would be to follow the above mentioned 14 best practices.
There are a lot of best practices foir better DB Performance.
In additional:
1.) In finiding the existence of the record: Avoid using the count() instead, just simply write “select a from tb where a=param”
2.) In optimizing the performance, avoid using the “JOIN”,
instead you can use “SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)”
why specify column names instead of using * in SELECT statement?
Selecting just used column names is a good one. Especially if one column is an image. If you use SELECT * the image will be sent too even if you do not need it. A real show stopper if you are just trying to populate a grid with names and it takes an entire Snickers bar to load due to have image in the recordset.
Hello Sarika,
When * is used in SELECT clause then all the columns are fetched as resultset. But most of time we do not need all columns data and unnecessary data is transferred from server to client. Using column names in SELECT clause only fetch data of required columns. Processing and transferring of lesser data will turn into improved response time.
Regards,
Pinal Dave
sir
right now i doing a documentation management project in asp.net using vb for that i need upload and doc in database i dont know how to the save the doc file in a database sql server so kindly guid me sir ……..
Hello,
Thank you for these “Best practices”,
I think that the 15 BP is one of :
1. Use data base diagrams for big table to focus on table-relationships.
or
2. Use constraints like : CHECK and/or ‘ idX IS NULL AND idY IS NOT NULL OR idX IS NOT NULL AND idY iIS NULL’
15. Use with nolock with every select statement (by default), only remove it if required.
16. Avoid use of Trigger
In today’s government environment where the owners of the database have no rights on the server, then “5. Store image paths or URLs in database instead of images. It has less overhead.” is not possible. You can’t have an app store a file on the server when it is not allowed any access to the server.
Because of the same issues regarding the lack of access that developers have to the web servers and the need to jump through 50 million hoops to deploy a recompiled web app on the server, then placing all your queries in stored procedures in a 100% better idea.
Perfect real life example; when there is the need to change the spelling of a field from contarct to contract, this change can be made in the database in a the stored procedure with no changes being made in the application merely by running a SQL script. When the queries are in the web app (and they were in this case) you have to have the developers chase all through the web app’s code to find every reference to it, recompile the web app, submit a help desk ticket to deploy it to the test server, carry out testing, submit a help desk ticket to get it deployed to the staging server, have the SMEs complete testing on the web app, then submit a help desk ticket to get it deployeyed to the production server. All this to change the spelling of one word.
That deserves a “Seriously!?!”
When we are using a join on tables which are huge, we need to ensure in real tme that the columns in ON condition are indexed, this helps in fatser retrieval of data..
use nolock in select statements
Please share some Inputs that how to monitor the performance of SQL 2008 and what are the parameter used in same Thanks in advance
Sir,
What is the way to insert videos,images,audio,pdf,doc files into a database….
My webapp is using SQL2008 and we’ve used most of the tips suggested here. But we are still facing performance issues. Can compression help improve the performance. If so, what are the methods or tools available.
The 15’th best practice is to follow all the 14 steps above.
don’t use cursor
Hi All,
I have a DB [‘Student_DB’] with bulk data. Now, i have took backup and restored it another server to optimize the DB which is called ‘Optimized_Student_DB’. Then, i have optimized the ‘Optimized_Student_DB’ DB. Also, i have dropped all data from the ‘Optimized_Student_DB’ DB. Now, it is a complete optimized DB with data.
Now, i have took script [schema only] from the ‘Optimized_Student_DB’ DB. By using this script, i have created another new DB which is called ‘Final_Student_DB’.
My question is, which is give best performance? Optimized_Student_DB or Final_Student_DB?
Please let us know for any questions.
Thanks,
Sathish
15 Point is It is good to write code in procedure, but if no of line in procedure exceed then degrade the performance, so break up your procedure if no of line exceed.
And how many lines you think is optimal. I have not heard of any such recommendation.
15. Update the database statistics on regular basis for better performance