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
> 1. Store relevant and necessary information in the
> database instead of application structure or array.
I knew a fellow who prefered to keep his enums in code, thus requiring a recompile before adding/changing values to his applications. He didn’t trust his clients’ DBAs, I suppose. In general, I prefer to store things in the db too.
> 3. If you use any enumerated field create look up for it in
> the database itself to maintain database integrity.
Same as 1, no?
> 5. Store image paths or URLs in database instead of
> images. It has less overhead.
How do you handle security? I once did this for .DOC and .XLS files. Each file had a user-specified universe of authorised users, so I renamed all files to prevent someone from downloading documents based simply on filename. I couldn’t use an int, to prevent users from simply iterating upwards until they found what they wanted. Next time, I vowed I would store such documents as blob/image.
> 9. Write SQL keyword in capital letters for readability
> purpose.
Words written in caps are harder to read than words written in lower-case. Now that I have a UI which highlights keywords using colour, I avoid caps.
I think we got into the habit from the days when our UI was a text-editor and caps were the only way to highlight.
> 10. Using JOIN is better for performance then using sub
> queries or nested queries.
It can also make queries more difficult to maintain. Depending upon complexity I may use subqueries or “pre-queries”.
> 12. Use comments for readability …
> 14. Make sure to test any of the database programming
I have also learned to comment my *tests*. Too often I have returned to a test and had to “invest” time figuring out exactly what I was testing — I was not pleased with myself.
what are the data types used for inserting the image paths and urls in a table? Is this applicable for sql2005 onwards or only from 2008?
#3 is not the same as number one. #1 is stating don’t use the application to store data definitions. Whereas #3 is saying build lookup tables. We have here too many so-called db designers that use the code a code like C, P, or D in the table because it is supposedly understood that they stand for “complete”, “pending” and “denied”, yet these definitions are not specified anywhere in either the application or the database. .
As for a number 15, my personal Bete noire, Never never, never, make the primary key, user entered data. If it is so important that user entered data be unique, then use a unique index. I watched once for three hours while an alleged DBA chased through three databases to fix a problem that arose when the user typed in “F14-E” instead of “F-14E”. Since they were using this aircraft type as a primary key in that table and foreign key in about a dozen other tables in three databases, This erroneous data entry caused a major breakdown in several applications.
Hi ,
I want to add images in my database and show them in datagrid view using asp.net. I used image field in sql server 2005 express , but images were not shown in asp.net.
please suggest a solution.
15. Use Fill Factor to 70 percent, when the table’s data modified very often.
If you are importing large volumes of data from other sources, update statistics often, this will help the DB engine to make use of the indexes that were defined.
Recompile stored procedures after updating the stats.
15. If you are handling large volumes of data in tables.
Use Table Partitioning.
Don’t put many queries with different execution plans in stored procedures (e.g. using IF’s or CASE). Sql Server caches only one execution plan per sproc.
However, if you have to do this, consider using WITH RECOMPILE option to get optimum results. Often it is better to recompile each time than use poor plans.
CREATE PROCEDURE dbo.Products_GetAll WITH RECOMPILE AS
Re: #11…Seriously? When will these myths die??
15. Make use of standard naming conversions for your Table, Fields, View, Store procedures and triggers.
Example :
Table : Employee, employee, tbl_employee
: GeneralLedger, general_ledger, tbl_general_ledger
Fields – Primary Key : EmployeeId, employee_id, fld_employee_id
Fields – Others : Surname, surname, fld_surname
Views – vwEmployee, VWEmployee, vw_employee
You might not really need to implement a rule for Store procedures and triggers because the are actions(method)
Avoid using cursors should be the 15th.
BTW, what does #9 has to do with db performance?
I agree with 3, but the enumerations should be kept in the code as well. Use code generation if you want to keep it DRY.
I do not agree with 11. Stored procs are much harder to develop, debug, maintain, etc… They offer absolutely no performance benefit in Sql server. Now sure there are uses for stored procs, but for most apps they are few and far between. I would always use parameterized client side sql before I created a stored proc.
Hmmm, I don’t count that many items related to “better database performance”. Maybe the title isn’t quite right :)
Not that I entirely disagree with the items stated, but I’d make the following observations as food for thought…
1. Information stored in the database verses the app means extra database hits. It’s a good idea of course, especially if cached, but does make it less efficient.
2. While a good idea, you’ll get better _performance_ in certain circumstances with database denormalisation. Of course a normalised database will be more maintainable and keeping tables small certainly makes sense. Keeping table sizes under the max row size makes even more sense.
9. & 12. While a good practice, casing and comments won’t effect performance. Maintenance yes. So still a good idea.
13. “Proper indexing” is a fairly general statement. Providing indexes for oft queried columns will help as does keeping the more common columns earlier in index definition. Also, choosing the most suitable index to have as the primary key (as a clustered index) will help performance.
15 (non-performance related). No Hungarian notation on objects – it’s quite absurd. You’ve probably (i hope) named the tables properly (as in suitable for the purpose), views should be named descriptively as well.
15 (performance related). Don’t hit the database repetitively for the same data within the same context (e.g. a single page request) and cache redundant data where appropriate. This eases load from the server and increases the response time of your app.
Re: #11…Seriously? When will these myths die??
The same day DBA’s will start programming and/or maintaining the whole application instead of just the database.
But you are correct. I think you need a new #11….
15. Don’t use triggers. Ever. On pain of death.
15. Cursors and temp tables are the product of a feeble SQL mind.
15. Be sure, that all data-rules is stored in the database. Use PK, UNIQUE, FK, CHECK or Triggers.
If not, you must write more complicated DML-statement. Use triggers when you cant’t use PK, FK, … Triggers is just programming .
Great points Pinal! They may not all be performance related, but they are important points nonetheless.
Someone had mentioned that their IDE changes the colors of keywords so capital casing isn’t required. If the IDE isn’t at hand: Notepad, email, or just making notes on a scratch pad, its easier for the next guy to distinguish objects from keywords (lets not forget about colorblind people).
#11 – I concur.
I think #15 should be avoid cursors or batch together multiple concurrent sql queries when possible to avoid numerous round trips when 1 will suffice.
Re: #11… I know that ad-hoc queries might be faster (or the same speed), but with 100s of applications running on 10s of SQL servers, tracking down that ad-hoc query that is running (in some application or from someone’s query analyzer) for 10+ hours is more difficult than finding and fixing a stored procedure.
In some cases, the source code has been lost for this critical application (or never provided) and there is no way to optimize a query written back when the back-end database was MSSQL 6.5… Telling the underpaid developers to reverse engineer an application just to let the DBAs sleep at night… good luck…
15. Avoid writing code that is susceptible to SQL injection.
Concatenated SQL statements transfer the SQL injection risk from the inline code directly to the stored procedure.
This commonly occurs in reports or in search queries, where the assumption is that a SQL statement in a Stored Procedure needs to be generated dynamically.
Frequently a WHERE clause may get generated on the client web application, and then passed to the stored procedure. Here is an example:
CREATE PROCEDURE pMyQuery (
@Where varchar(8000)
)
AS
DECLARE @sql varchar(8000)
SET @sql = ‘SELECT * FROM MyTable ‘ + @Where + ‘ AND MyField = 4 ORDER BY This, That‘
EXEC(@sql)
Pass this to the WHERE clause and a table could get dropped.
‘1 = 1 DROP TABLE MyTable –‘
Note that the comment dashes at the end of the line will prevent any subsequent code in the concatenated SQL statement from being executed. In the above case the additional filter and ORDER BY clause would not be executed.
Rather than concatenating SQL statements, use conditionals and case statements within stored procedures to create dynamic queries. One approach is to employ CASE statements in WHERE clauses to handle filters and searches.
For example, let’s say we have a user table with 6 fields: UserID, FirstName, LastName, NTUserName, Email, and BusinessID. And we want to create one stored procedure capable of returning both single and multiple records, and capable of applying filters or search criteria.
Presented with that scenario many developers would program a concatenated SQL statement, either on the client or in the stored procedure (bad, bad, bad). Some may write a long stored procedure with a series of IF conditionals (better).
A very efficient way to address this need is to employ CASE statements in the WHERE clause of the procedure. For example:
CREATE PROCEDURE [dbo].[p_UserGet] (
@UserID int,
@BusinessID int,
@SearchField varchar(20),
@SearchText varchar(100)
)
AS
SELECT * FROM tbl_User
WHERE CASE
— Return all records.
WHEN (@BusinessID IS NULL) AND (@UserID IS NULL) AND (@SearchField IS NULL) THEN 1
— Return all records for this BusinessID.
WHEN (BusinessID = @BusinessID) AND (@UserID IS NULL) AND (@SearchField IS NULL) THEN 1
— Search by FirstName.
WHEN (BusinessID = @BusinessID) AND (@UserID IS NULL)
AND ((@SearchField = ‘FirstName’) AND (FirstName LIKE ‘%’ + @SearchText + ‘%’)) THEN 1
— Search by LastName
WHEN (BusinessID = @BusinessID) AND (@UserID IS NULL)
AND ((@SearchField = ‘LastName’) AND (LastName LIKE ‘%’ + @SearchText + ‘%’)) THEN 1
— Return a single record for this UserID.
WHEN (UserID = @UserID) THEN 1
ELSE 0
END = 1
GO
Comments reveal the logic. The first case statement returns all records, and the last case statement returns one record. Statements in between apply filters. The procedure could be extended to search by the other fields using statements similar to those that search on FirstName and LastName.
This approach consolidates 3 potential stored procedures into one. It avoids SQL injection problems that can occur within search procedures that concatenate filters into WHERE clauses. And, it allows SQL to compile an execution plan, which improves performance.
15. Do not use functions in WHERE clauses.
Maybe it’s just that I’m a database newbie, but I don’t quite get #10 – can’t you join to a subquery, or is this referring to linking to subqueries with the IN keyword?