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
Hi Pinal,
1. use correct way of table
For Example
use ..
select e.,e.<col2) from .. as e
OR
select e.,e.<col2) from … as e
instead of
select e.,e…… from as e ;
2. Avoid ISNULL(),COALESCE() AND with CONVERT ()
3. In our Procedure avoid cursor,while,CTE (recursive) AND temporary tables
4. Avoid more table valued UserDefinedFunction inside store procedure
HI All,
i do have a column with huge text more then 8000 char and store as image text data types what are the best option to store data in data base. can convert text data into xml.
I believe the 15th principle would be to understand that any RDBMS is a data management system, not a UI layer. Things like data access, data manipulation and processing should be the responsibility of an RDBMS. But one should not ask the database system to do things like formatting and representing the data.
Create a environment of performance (training, training, tests )
Generate and analyze the SQL Execution Plans for almost all SQLs
Use denormalization when needed
Use the best tools and technologies that your database has (MDC for IBM DB2, cluster indexes for SQL Server, Java VM inside database for Oracle..)
Good point.
This one is controversial, but some of the best database architectures i have seen exhibit this one trait, either by design or by chance: 15. Minimize NULLs in your tables, use NOT NULL very sparingly and strategically.
This one trait tends to enforce point 2, as well as data integrity.
Sorry, meant… use NULL very sparingly and strategically…Maximize the use of NOT NULL
Remember to use ‘UPDATE STATISTICS”. This save my applications. The performance is excelente. Create a job and do once on week.
use nolock in tables that dont update to much, or u will get dirty reads,
dont use nolock in tables that dont update to much, selects dont block each other.
Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.
# 15 – Read Codd’s Twelve Rules for Relational Databases at least once a year.
#15 NO HARD CODE
#15 Always Use single column (IE : Primary Key Column) while you need to just count the number of rows,it takes lesser time than count(*).
Thanks pinal for this Idea….I think,I read my given #15 from some of your blog post.
Not true. SELECT COUNT(*) and SELECT COUNT() both cost exactly the same and both execution plans show an index scan the primary key.
The second one should be SELECT COUNT(primary key) but putting it in angle brackets meant it was stripped out…
Thanks Chris.
#15 Can be:
As we know,for ‘Bit’ datatype in SQL,there is only two values – [1 or 0],so to handle this in Select Query,some people often use
Select Id,Name,Address From dbo.Employee where Isnull(deleted,0)1 —Show only record that is not deleted,Instead we can use it as
Select Id,Name,Address From dbo.Employee where deleted=0,It only checks for deleted -0 ,no concern with 1 ,so this is handy in Performance Wise. :)
Thanks
Very well said.
Instead make such column not null or have default on it. Nulls always fail equal conditions.
15th best practice might be
Avoid using Triggers to ignore deadlock or recursive trigger situation that loads server free space and crashes.
Better to use trigger related logic in a function or SP and to be called from application codes at times for data stability and safety.
16th best practice might be
Avoid using cursor for simple / single table update , instead use merge query for faster query execution time and more compact SQL query.
Use CTE table instead of subquery and for join use integer column
All depends on query plan and cost. We can’t generalize the statement.
“10. Using JOIN is better for performance than using sub queries or nested queries.”
Doesn’t this hold for this statement as well? Sometimes subqueries may perform better.
How to retrieve 50 Lacs record at a time? is it possible in sql?
What kind of question is that? Yes, you can. Does it fail with any error? What exactly you would do with those records?
This question is asked in interview. what should be the answer for this question? Please help me.
Yes, its possible but it all depends on destination. If you try this in SSMS grid, you would get failure due to SSMS not SQL Server.
Hi Pinal,
For 15: Join tables with proper column.
Fully qualified name would be preferable.
This one i recently learned from your session.
Very good Chirag.
Use SELECT COUNT(1) instead of COUNT(*)
Use bit type for column like active , iscashbook , isdeleted status.