|
|
Once you have built your application, you usually find that you need to optimize it so it can scale better and perform as well as it did in the production environment. In the following sections, you will learn a few additional techniques for improving data access.
Pooling is a way to share data connections in applications without the need and overhead of establishing a connection each time. Pooling connections can significantly enhance the performance and scalability of your applications. Most database vendors provide ways to manipulate or create connection pools. I will concentrate mainly on the data provider available with .NET, specifically the SQL Server .NET Data Provider. The SQL Server .NET Data Provider provides connection pooling automatically for ADO .NET client applications that connect to any SQL Server database. You can also supply several connection string modifiers to control connection pooling behavior.
When you open a connection, a connection pool is automatically created based on an exact string matching algorithm that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. This means that if you already have a connection with the same connection string as you have just specified, that connection will be used instead of having to create a new one. If an exact match to an existing pool is not found, a new connection is created and added to the pool.
In the code snippet below, three new SqlConnection objects are created, but only two connection pools are required to manage them.
SqlConnection conn = new SqlConnection() conn.ConnectionString = _ "Integrated Security=SSPI;Initial Catalog=northwind" connA.Open() ' Pool A is created. SqlConnection conn = new SqlConnection() conn.ConnectionString = _ "Integrated Security=SSPI;Initial Catalog=pubs" conn.Open() ' Pool B is created because the connection strings differ. SqlConnection conn = new SqlConnection(); conn.ConnectionString = _ "Integrated Security=SSPI;Initial Catalog=northwind" conn.Open() ' The connection string matches pool A ' so A is used instead of creating a new one.
Once created, connection pools are not destroyed until the active process ends. Maintenance of inactive or empty pools involves minimal system overhead.
When a pool is created, multiple connection objects are created and added to the pool. The number of connections created are based on the minimum pool size requirements. Connections are then added to the pool as and when required and up to the maximum pool size. Minimum and maximum pool size are specified as part of the connection string.
When an SqlConnection object is requested, it is obtained from the pool if a usable connection is available. To be usable, the connection must currently be unused, have a matching transaction context or not be associated with any transaction context, and have a valid link to the server.
When the maximum pool size is reached and no usable connection is available, any additional request is queued until a connection does become available or the timeout period is over, in which case an error occurs.
| Warning |
Connections that are not explicitly closed are not added or returned to the pool. You must always close the connection yourself when you are finished using it. This can be done by using either the Close or Dispose method. |
Stored procedures are generally faster than their SQL statement equivalents. The main reason is that stored procedures are compiled and an execution plan is worked out at design time. On the other hand, SQL statements have to be interpreted by the database engine, and the execution plan has to be worked out at run time, which adds additional overhead. Generally, you should try to manipulate the database through stored procedures instead of SQL statements.
When designing your database, one of the most important considerations is the use of data types. Data types are not all created equal, even if they are sometimes interchangeable. For example, if you create a varchar(10) data type, you could also create a char(10) data type instead. However, varchar data types, though more efficient in storage, have added overhead for maintenance. If all the data is going to be ten characters long, char(10) is more efficient; on the other hand, if the data varies in length, varchar(10) is more efficient.
The idea here is for you to be familiar with the data type available in your database and choose wisely according to your needs and future anticipated needs.
Data warehousing is a huge topic in itself, but it is mentioned here as a way to optimize your online transaction processing (OLTP) database. As time goes by, most OLTP databases accumulate historical data that are not required for the day-to-day running of the system but are kept because they are required for statistical analysis. When you use data warehousing, you regularly clean the system, keeping it small and optimal while you maintain the data required for analysis in a data warehouse. The data warehouse can then be used as a source for an online analytical processing (OLAP) system, which is more appropriate for larger volume statistical analysis.
Once the system goes live, it is imperative that you continually monitor and tune the application to match your needs. Initially, that might involve redesigning certain parts of the system, but as the system stabilizes, these issues can be solved by tuning the environment itself, such as the server or network, or even getting more powerful hardware. Your aim should be to identify bottlenecks and make the system causing these bottlenecks as efficient as possible.
To help with monitoring and identifying bottlenecks, many servers (Windows 2000, MS SQL 2000, and Internet Information Services) include performance counters and monitors that you can use. Some, like MS SQL Server, even include a self-tuning option. Study the different performance counters that are available on your system and make good use of them.
|
|