1. Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).
2. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.
3. Don’t index anything else (yet).
4. Unless you need a different behaviour, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.
5. Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.
6. Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
7. I know you’ve heard it a million times, but only return the columns and the rows you need.
8. Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.
9. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.
10. Avoid NOT IN, instead use a left outer join - even though it’s often easier to visualize the NOT IN.
11. If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.
12. Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.
13. Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.
14. Avoid index and join hints.
15. When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.
16. Do not use user defined table types in reporting queries. There generally is only a need for use of these types when moving large amounts of data from .net into sql – for inner sql communication / data transference we should be use temp tables.
17. Do not use table variables – in almost every case where you are using a table variable, speed will be increased by using an appropriate temp table.
18. Do not have extremely large multi-table joins. SQL Server is most efficient when joining 4 tables at a time as that is the maximum number that the query optimizer can consider during the optimization cycle of query compilation. If you are using 5-6-7 tables in a join this is probably not the end of the world but if you find yourself using more (or joining against views) it is probably a good idea to create a temp table and start filling it with data through multiple updates/inserts.
19. Create appropriate indexes (covering, if needed) over tables or clustered indexes on views (only if absolutely necessary).
20. In most case we avoid create temp table, but with complex business a temp with index will improve performance. In some case may be more than 10 times.
21. With a large amount of record, please avoid self JOIN statement.
No comments:
Post a Comment