SQL SERVER - 15 Best Practices for Better Database Performance
April 6, 2008 by pinaldave
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 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 datetime 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 then using sub queries or nested queries.
11. Use stored procedures. They are faster and help in maintainability as well security of the database.
12. Use comments for readability as well guidelines for 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 (http://www.SQLAuthority.com)
pinaldave
> 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.
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?
#15 should be “…and remember, ‘IT DEPENDS’ will be an often-used answer”.
Particularly for #s 5,9 and 10!
> 5. Store image paths or URLs in database instead of
> images. It has less overhead.
I hope in SQL 2008, they have the provision for storing the images in File System and the Security, Permission … will all handled by SQL Server.
Please let me know if this is correct.
Much of this is about database design, and not “SQL Server” best practice, which is about the DBA function and is technology-specific, for example
2. Use normalized tables in the database. Small multiple tables are usually better than one large table.
Assume’s you’re operating an OLTP implementation. Exactly the opposite applies in a multi-TB data warehouse.
Some of your other comments such as
9. Write SQL keyword in capital letters for readability purpose.
this is too specific, the best practice is to create and operate coding standards, and adhere to them for consistency. The specific standards themselves are less important.
Agree with most of these guidelines.
But there is a great discussion on 11) in Frans Boumas Blog
Stored procedures are bad ‘kay
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
15. Set up an appropriate backup / restore procedure. Test this procedure. Be ready to recover whatever happens.
16. Document the database. For example, add extended properties to tables, columns, etc.
…and read http://www.sqlservercentral.com/articles/Database+Administration/62480/
Re: #11 and comments about it.
People are correct in noting that as far as query performance there is not a performance gain over ad-hoc SQL (particularly parameterized), but there are gains in maintainability and security. I have always worked in small shops where I have done both SQL DBA/Development and application development and I have always found it easier to make a change in a stored procedure than changing compiled code and re-releasing.
I also think that all of these DO refer to performance because the time it takes to develop and maintain the database and application need to be taken into account which is why commenting, naming conventions, and coding standards are important.
IMO #15 Use set-based solutions whenever possible.
15. Surely it must be - if it is important (it must be you just spent time on it right?) - back it up.
#15 Write queries with all operators eg:
INSERT INTO TABLE () VALUES instead of INSERT TABLE
This makes the code more readable.
No. 15 could perhaps be to run MSSQL and SQLServerAgent service under a different account than the default
For Mark Brackett:
As I’ve been learning SQL over the past 8-9 months with virtually no mentor, I’ve been writing feeble queries using mostly temp tables. What would you recommend as a good resource for us self-teaching types to get away from our feeblness?
I want to give answer of prashant. If he wants to show images(stored in database) in datagrid. He has to write a class that will save the image temporarily in a folder and get the URL of that, because datagrid takes the image url to show the images
The following reference can help you to store images in database and retrieve them in GridView or DataGrid control
http://www.beansoftware.com/ASP.NET-Tutorials/Images-Database.aspx
#5 See http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45 for another viewpoint.
Even though the paper says that access to docs over 1MB in size performs better when they are stored on the filesystem, this ignores issues of backup and referential integrity that could make it advantageous to keep larger docs in the database.
An important message from the paper is that databases are able to process larger objects than they could do in the past. IMO this trend will continue, and the DB designer needs to know works best with current software and hardware rather than always confining large objects to the filestore.
On 11) and LINQ to SQL (if I may)
“… I have always found it easier to make a change in a stored procedure than changing compiled code and re-releasing.”
Any change to the number or type of input output parameters will require a change to the stored procedure and the application code.
Incidentally you really should be generating most of the DAL layer in any case and it is purely a matter of whether the code resides inside or outside the database. The actual code required (or generated from metadata) is pretty well identical in either case.
I’m not suggesting there are never situations where it makes sense to use stored procedures but the Performance/Security/Maintainability mantra is a falsehood that many appear to believe purely because it has been repeated so often.
I think also relevant here is the LINQ to SQL debate. Microsoft themselves are endorsing dynamic SQL vs Stored procedure code.
Below taken from Simon Evans Blog
http://blogs.conchango.com/simonevans/archive/2007/11/11/LINQ-to-SQL_3A00_-Let-the-debate-begin.aspx
So with LINQ to SQL (and LINQ to Entities) the most hotly debated point was around the auto generation of LINQ’s dynamic SQL vs. using stored procedures. In the old days, one point favoring of using stored procedures was the performance gain from a cached execution plan. But since SQL Server 2005, dynamic SQL is also cached, so this reason for using stored procedures has largely gone away. One benefit dynamic SQL has over stored procedures is that you only select what you need in all cases. In a typical scenario with stored procedures and a DAL populating an object model, stored procedures will tend to be reused even when all of data is not used by the calling DAL method. There ways around this, but it largely involves an ever increasing list of stored procedures (GetObjectByX), make the solution less manageable.
15 . Use Computed columns for columns which are prefix by functions and frequently used in where clause.
16. Use Index hint if necessary.
Below is my thought about 15th best practise.
Use multiple data files for tempdb and if possible place them in seperate disk drives. This will reduce the bottleneck of tempdb.
The tempdb of SQL Server will be used in many occations, e.g: sorting, use of temp tables and variable tables, worktables, etc.
15. Plan use of nulls