Following ten points helps to improve database related operations:
1)Use Sequential Access as Often as Possible
With a data reader, use CommandBehavior.SequentialAccess. This is essential for dealing with blob data types since it allows data to be read off of the wire in small chunks. While you can only work with one piece of the data at a time, the latency for loading a large data type disappears. If you don't need to work the whole object at once, using
Sequential Access will give you much better performance.
2) Use Stored Procedures Whenever Possible
3) Use SqlDataReader Instead of Dataset wherever it is possible
4) Keep Your Datasets Lean
Remember that the dataset stores all of its data in memory, and that the more data you request, the longer it will take to transmit across the wire. Therefore Only put the records you need into the dataset.
5) Avoid Inefficient queries
6) Unnecessary round trips
7) Too many open connections
Connections are an expensive and scarce resource, which should be shared between callers by using connection pooling. Opening a connection for each caller limits scalability.
8) Avoid Transaction misuse
How it affects performance:
If you select the wrong type of transaction management, you may add latency to each operation. Additionally, if you keep transactions active for long periods of time, the active transactions may cause resource pressure.
9) Avoid Over Normalized tables
Over Normalized tables may require excessive joins for simple operations. These additional steps may significantly affect the performance and scalability of your application, especially as the number of users and requests increases.
10) Reduce Serialization
Dataset serialization is more efficiently implemented in .NET Framework version 1.1 than in version 1.0. However, Dataset serialization often introduces performance bottlenecks.
|
| Author: Bunty 21 Jun 2008 | Member Level: Diamond Points : 2 |
Hi,
Nice tips on SQL Server.
If you elaborate such as Use Stored Procedure whenever possible.If you include what are the benifit of using SQL Server then it is well and good.
Keep posting.
|