Programming and Performance Tips for ADO .NET applications using Sql Server

The following practices and guidelines are very importent from the performance perspective of your SQL Server database. The best way to achieve optimum performance benefits is to experiment with the following guidelines.

Database Programming Tips

1. Naming conventions and coding styles need to be standardized in an organization. It improves readability.
2. Write Comments to communicate the implementation logic inside the stored procedures. This greatly helps in understanding the logic in future to programmer himself.
3. Use Return statement in the end to return status of the stored procedure execution.
4. Constant values should be avoided and variables should be used instead.
5. SET NOCOUNT ON in top of all batch T-SQL statements and stored procedures. This turns off the message indicating the number of rows affected as part of the result.
6. Indent the TSQLs by keeping each clause (SELECT, FROM, INTO, WHERE, ORDER BY, GROUP BY etc.,) in start of the new line.

SELECT <<FIELD NAME>>

FROM     <<TABLE NAME>>

WHERE CONDN 1 AND CONDN 2 …

7. Use OUTPUT parameters to return single values from stored procedures.
8. Column names should be used in ORDER BY Clause and not the column numbers. It improves readability.
9. ANSI standard JOIN clauses instead of using old style WHERE clauses for joining tables.
10. @@ERROR global variable should be checked for errors after executing any DML statements.
11. Usage of PRINT Statements should be avoided. Instead of removing the PRINT statements, a bit (@DEBUG) parameter can be used to print debug information while troubleshooting any production problems. By default this should be turned off.
12. Data validations are recommended in the client applications to avoid unnecessary network calls.
13. BLOB data types should be avoided for storing binary files, images etc., Instead these files can be stored in the file system and the file path can be stored in the database column
14. DONOT add same index keys more than ones (with different names of course)
15. Always specify column names to be retrieved in the SELECT query instead of using SELECT *.
16. Size of the index keys matters for the performance. So it is advised to use keys with lesser character values. Index on INT data type column will reduce too many I/O reads.
17.  Derived tables (INTO clause) are recommended instead of creating temporary tables.
18. FILLFACTOR should be kept in 100% while creating indexes. This will result in good performance as the Sql Server will utilize maximum spaces in each page.

ADO .Net Tips

1. Prefer stored procedures over inline queries. Stored procedures are precompiled by the sql server and execution plans are stored. This improves the performance. Also during batch executions we can avoid network round-trips.
2. Keep the connections open for a shorter duration. Avoid transporting DataReader to the client applications as it requires connection to be open.
3. Explicitly close connections after usage. Use “Using” statement in C# to close connections automatically.
4. Fill() or Update() methods in DataAdapter does not require a open connection. DataAdapter automatically opens a connection.
5. Use ExecuteNonQuery if the query does not return values. Use ExecuteScalar or OUTPUT parameter to return single values.
6. Correct Transaction Types should be used. Keep the transaction length as minimal as possible.
7. Paging data over large result sets should be done at the database. This avoids bringing over large result set over the network.

This is list is not complete yet. I welcome all kinds of suggestions and discussions on this front.