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
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.