September 7, 2009 16:42 by
andyh
- Place all your code in stored procedures where possible
- Move declarations to the start of stored procedures.
- Are transactions required??? If there are no updates or inserts, then you should remove any transactions and rollback commands. These will just fill your transaction log up.
- Repeat getdate() commands. Why not decalra a variable at the start of the stored procedure and re-use it later on in the code.
- Count(*) and count(fieldname). If your deciding if there is data in a table or looping through a numbe rof rows. Use the EXISTS command instead. If a cound is required, just use one field instead of * as is reducess the number of reads.
- Rollback transactions as soon as possible. Sometimes people build error messages before rolling back... these can be done outside the transaction.
- XML use... try upgrading to the latest XML methods.
- Prevent recompiliation
- Avoid temporary table modifications
- Reduce multiple lookups into one.
- Move select statemnets to the start of stored procedures where possible. This reduces record locking times.
- Modify multiple update statements to use CASE.
- Reduce UNION commands
- Get a tool to help you....MS query analyser is pretty valuable.
- Avoid nested loops
- Avoid functions in the WHERE clause as this affects performance.
- Use performance monitors where available.
- Use a fixed load rather than the maximum load when trying to improve performance
- Call stored procedures using their fully qualified name
- Split larger stored procedures into smaller ones and have acontrolling procedure.
- Do not use sp_ as a stored procedure prrefix
- Consider returning the integer value as a RETURN statement instead of returning an integer value as part of a recordset.
- Avoid temporary tables
- SET NOCOUNT ON
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
- Check the order of ields in the WHERE clause
- Consider additional indexes on popular search fileds
- Reduce the data returned (avoid select *)
- Are ORDER BY and GROUP by required. - these are quite expensive operations
- INcrease workerthreads
- Increase CPUs / hard drive speed / Memory
- Consider offloading work.
- Use the select statements with TOP keyword or the SET ROWCOUNT
statement, if you need to return only the first n rows.
- Clustered indexes are more preferable than nonclustered, if you need
to select by a range of values or you need to sort results set with
GROUP BY or ORDER BY.
- Try to avoid using the DISTINCT clause
- Use table variables instead of temporary tables
-
Create indexes on XML columns
-
De-normalization
- Avoid use of NULL
2ca0393c-4027-4c36-b644-e68cfec484b9|0|.0