提高数据库性能的两种方式

    数据库访问性能的两种方式：使用存储过程和缓冲池POOLING。

使用存储过程

在ASP.NET中，你可以使用两种方式执行SQL语句直接在页面执行代码，或者封装SQL语句然后再执行。使用存储过程可以提高应用程序的性能和可维护性；封装多个SQL语句，然后成组执行，比如，你可以创建一个存储过程包含多个SQL UPDATE语句，一次执行修改多个记录；TSQL支持参数、条件、循环和函数，使用这些功能，你能在一个存储过程中创建非常复杂的小程序；存储过程能将应用程序和数据库的实现分离，如果数据库表改变了，你可以只改变存储过程而无须改变你的ASP.NET页面。

使用缓冲池

To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool.

Realize that even very small differences in the connection string can thwart connection pooling. Connections are pooled only when they are opened with connection strings that exactly match character by character. For this reason, it is wise to create your connection string in one place and use the same connection string within all your ASP.NET pages. For example, you can place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection. Another option is to place the connection string in Application state within the Global.asax file.

When using SQL connection pooling, you can place additional options in a connection string to modify how connection pooling works. For example, you can specify the minimum and maximum size of the connection pool or even completely disable connection pooling.

Here's a list of the connection pooling options that you can add to the SQL Server connection string:

• Connection Lifetime— Destroys a connection after a certain number of seconds. The default value is 0, which indicates that connections should never be destroyed.

• Connection Reset— Indicates whether connections should be reset when they are returned to the pool. The default value is true.

• Enlist— Indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true.

• Max Pool Size— The maximum number of connections allowed in a single connection pool. The default value is 100.

• Min Pool Size— The minimum number of connections allowed in a single connection pool. The default value is 0.

• Pooling— Determines whether connection pooling is enabled or disabled. The default value is true.