ASP.NET, Coding

Best Practices: Database Operations

Use Native Dirvers
When ever connecting to the database, make sure that you use the Native drivers for the respective databases.

Enable Connection Pooling
Always enable connection pooling in your application. This will save time to establish a fresh connection to the database. If any open unused connection present in the pool, by enabling Connection Pooling, application will take one open connection and use it. Ms Access database could be the only exception as it wont support this.

USING Statements
.Net is following OOPS concepts and hence all object must be deals in the similar and correct fashion. If you are using any object, make sure that you close/dispose them after use. Most time, developer forgets this and there will be many open objects present in the memory which is a bad thing. Hence make use of USING statement so that the object disposal will automatically taken care by the GAC.

Proper Table Indexes
Try to use indexes on your database tables. This will speed up the data retrieval operation.

Use of Stored Procedures
In old times, it was a common trend to have embedded queries (ie, having SQL code inside your application code). These days, most database supports stored procedures and hence would recommend to use Stored Procedures. If you have any database related functionalities that can be performed at backend, please do it so inside the Stored Proc itself.

An additional benefit to using stored procedures is that the execution plans are typically updated as part of scheduled maintenance on the DBMS. This means that every time data is moved around on the physical disk for optimization by the server maintenance jobs, the stored procedure execution plans are automatically updated as well. The result is that users experience continued improvement to performance rather than executed some plan that was stored years ago when they first ran the dynamic SQL statement in the application.

Set NoCount On at the beginning of stored procedures can reduce overhead dramatically as well if the rowcount is not required as part of processing the transaction.

Query Optimization
Another way to ensure good performance is to view the execution plan of any SQL statements (either dynamic or stored procedures) in SQL Server Management Studio or equivalent, to determine lock levels and possible efficiencies. Methods like dirty reads (uncommitted or with no lock) should be used whenever possible. Other things like making sure you are using the indexes which are set up are critical to performance. For example, if you cast or convert a value to be different from the type that the index is set on, you lose the value of the index entirely.

If you are facing issues with temporary data, check out this link for options – <http://support.microsoft.com/default.aspx/kb/305977

You Might Also Like