This article describes some basic data access performance issues that should be considered when developing database applications using ADO.NET.
Introduction Data Access plays a key role in business applications. Performance is a key factor that should be considered in any data-intensive application. Many factors can have a negative impact on data access performance, like network load, database server load, unopened SQL statements, and so on. In addition, there are some other factors to consider, including various data access operations performed by most applications, such as opening and closing the connection, acquisition set, blob access, and metadata retrieval. In this article, I will analyze some data access operations and propose some suggestions for improving database access performance.
In this article, I will use the Borland Data Provider (BDP) for .NET and Borland® DelphiTM 8 for .NET and Borland® DelphiTM 8 for .NET and Borland® DelphiTM 8 for the Microsoft® .NET Framework included in Borland® C # BuilderTM, and IBM® DB2® data provider to access IBM® DB2® Universal DatabaseTM (UDB).
Connecting pools create new database connections Sometimes it is very expensive because it involves allocating clients and server resources, authorized users, and other validation. By establishing a connection and reuse the same connection in subsequent requests, the performance of the application can be significantly improved. When the client processes data locally, the database connection is not necessarily an active, so a single connection may be accessed by multiple clients. Therefore, the connection pool (that is, the cache of the database connection) can improve the performance and scalability of the application, especially in the multilayer architecture.
In ADO.NET, the connection pool is identified by a unique connection string. When the new connection is opened, if the connection string does not exactly match any existing pool, create a new connection pool. After the new connection pool is created, create a minimum number of connection objects and add it to the pool of the background. If all existing connections in the pool are busy, the new connection is added to the pool until the maximum size of the pool is reached. By default, the default values for the connection pool parameters can be overwritten with the connection string, such as MIN Pool Size and Max Pool Size.
The connection in the pool is divided into connections without transaction and a connection with detailed transaction context. When an ADO.NET connection is turned on, the connection is obtained from the pool according to the transaction context. If the connection is not related to the transaction, it will be obtained from the non-transaction connection pool.
Turning off the connection operation returns the occupied connection to the connection pool for use. The connection in the pool is associated with the lifecycle, and the connection pool manager regularly scans the useless and expired connection and removes from the pool. Once created, the connection pool will remain active throughout the lifecycle.
In order to show the actual performance of the connection pool, I will write a simple .NET remote management application. Some basic knowledge about .NET remote management, you can refer to my previous article to build a distributed database application using BDP and DB2 in .NET.
The remote server discloses two methods, getDataBDP () and getDataDB2 (), through these two methods, can use Borland Data Provider (BORLAND.DATA.PROVIDER) and IBM DB2 DATA.DB2). To fill and return to the data set. For each request from the client, open the connection and turn off the connection after processing the SQL request. GetDATADB2 () uses a flag to decide whether the connection pool is enabled. The current version of BDP does not support the connection pool.
Here are some basic test results showing the time occupied by minutes. These results should not be considered as a baseline. However, you can see that as more requests arrive at the server, if there is no connection pool in the intermediate layer, the performance of the application will deteriorate. Number of requests: 250 request belt connection pool 250 requests without connecting pools 500 request belt connection pool 500 request without connection pool data provider: IBM DB200: 17.546875002: 01.453125000: 32.875000004: 03.5468750BDP - dB2N / A02: 01.1406250 N / A04: 01.6718750
Below is two code for servers and clients. Please refer to the full source code list.
RemoteServer.cs
Public Class RemoteDataProvider: MarshalByrefObject, IremoteDataProvider
{
Public DataSet getDataBDP ()
{
DataSet DS = NULL;
String connString = "Provider = DB2; Assembly = Borland.Data.Db2, Version = 1.5.1.0, Culture = neutral, PublicKeyToken = 91d62ebb5b0d1b1b; Database = toolsdb; UserName = myuser; Password = mypasswd";
Try
{
DS = new dataset ();
BDPConnection conn = new bdpConnection (connString);
Cn.open ();
BDPDataAdapter adapter = new bdpdataadapter (m_commtext, conn);
Console.writeline ("SQL to DB2:" m_commtext);
Adapter.Fill (DS, "Table1");
CONN.CLOSE ();
}
Catch (Exception E)
{
Throw e;
}
Return DS;
}
Public DataSet GetDataDb2 (Bool BPool)
{
DataSet DS = NULL;
String connString = "Database = Toolsdb; UID = myuser; pwd = mypasswd;";
IF (BPOOL)
{
Console.writeline ("Connection Pooling On ...");
Connstring = connString "Pooling = true; min pool size = 100";
}
Else
{
Console.writeline ("Connection Pooling Off ...");
Connstring = connString "Pooling = false";
}
Try
{
DS = new dataset ();
DB2CONNECTION CONN = New DB2CONNECTION (Conntring);
Cn.open ();
DB2DATADAPTER Adapter = New DB2DataAdapter (M_CommText, Conn);
Console.writeline ("SQL to DB2:" m_commtext);
Adapter.Fill (DS, "Table1"); conn.Close ();
}
Catch (Exception E)
{
Throw e;
}
Return DS;
}
}
REMOTECLIENT.CS
Public Class RemotingClient
{
Public static void main ()
{
TestPooling ();
}
Private static void testpooling ()
{
IremoteDataService REMDS = NULL;
ArrayList Stat = New ArrayList ();
Httpchannel channel = new httpchannel ();
ChannelServices.RegisterChannel (Channel);
String clientid = guid.newguid (). TOSTRING ();
Try
{
REMDS = (iRemoteDataService) Activator.getObject (TypeOf (IremoteDataService), "http: // testserver: 8000 / remoteDataService.soap");
IF (REMDS! = NULL)
{
Stat.Add (GetData (REMDS, 250, FALSE, TRUE);
Stat.Add (GetDATA (REMDS, 250, FALSE, FALSE);
Stat.Add (GetData (REMDS, 250, TRUE, FALSE);
}
Console.writeLine ();
For (int i = 0; i { Console.writeline (STRING) Stat [i]); } } Catch (Exception E) { Console.writeLine (E.MESSAGE); } } Private Static String GetData (IremoteDataService Remds, int NOFREQUEST, BOOL BBDP, BOOL BPOOL) { IremoteDataProvider Remdp = NULL; DataSet DS = NULL; String Out = "" DateTime Stime = datetime.now; String clientid = guid.newguid (). TOSTRING (); For (int i = 0; i { REMDP = REMDS.GETDATAPROVIDER (ClientID); REMDP.COMMANDTEXT = "Select * from addressbook"; IF (BBDP) { DS = transdp.getdatabdp (); } Else { IF (BPOOL) DS = Remdp.getdatadb2 (TRUE); Else DS = transdp.getdatadb2 (false); } IF (ds! = null) { Console.writeline ("Data Received from The RemoteServer); Utils.PrintData (DS); } } Timespan ts = datetime.now - stime; IF (BBDP) { OUT = "Time Duration WITHOUT POOLING (BDP) =" Ts.tostring (); Else { IF (BPOOL) OUT = "Time Duration with pooling (db2) =" ts.tostring (); Else OUT = "Time Duration WITHOUT POOLING (DB2) =" Ts.toString (); } Return Out; } The FORWARD ONLY cursor one-way, read-only cursors provide better throughput, but also use fewer clients and server resources. With one-way cursor, there is no need for any cache in the data access layer, and there is no need to maintain the current recording location associated with the server. The data is read as a stream and records a one-way process. The one-way result set is ideal for reports, data processing applications, as these applications perform the same operation when obtaining data. In ADO.NET, DataReader returns a one-way result set. The DataAdapter-played role is a pipe between database and datasets, using DataReader to extract records from the database and fill in the dataset. Data set caches data, and play a role of in-Memory relational database. Therefore, depending on the needs of the application, you can use DataAder to populate a record each time, or use DataAdapter to populate the data set, which can provide a complete collection of records, and analyze the changes on the client later, save Back to the database. Regardless of which case, select SQL statement is very important for better throughput and overall performance. Blob accesses the most up to 4 GB of BLOB data. Because massive data may be transmitted through line, it is best not to extract BLOB data and other scalar data simultaneously. When using BLOB data, it is important to understand the underlying access mechanism in the database client library. Most database clients provide more than one way to access the BLOB data type. Depending on the BLOB data type, the client can bind a huge buffer or use the BLOB locator to get BLOB data. When binding the huge buffer each extracted, the available BLOB data, or BLOB data up to the maximum cache size is transmitted to the client. On the other hand, the BLOB locator is basically the BLOB data referenced by the database server. During the initial extraction data, only the positioner is transmitted to the client. Once the client gets the BLOB locator, it will call the BLOB access method later to read and write to BLOB data. Therefore, to improve the performance of the application to process BLOB data, you must pay attention to extract BLOB data, or use a new SQL request, or use the locator. At the same time, due to not necessarily processing BLOB data, they only extract them if necessary or when the application explicitly requests. Metadata retrieval metadata retrieval is another expensive operation (because it may involve connecting several system tables, retrieving metadata of a specific database object), should be minimized or completely eliminated at runtime. Most database object metadata retrieval can be done when designing, while mode information can be done for XML or any application-specific format. The runtime metadata cannot be completely eliminated. In some complex applications to analyze relational data or object persistence, you may need to discover the characteristics of the runtime database object. In these cases, the SQL statement of the access system table must be adjusted. In the current version of ADO.NET, the metadata retrieval function is still not enough to retrieve information about the database object. DataReader and DataAdapter have GetSchematable and Fillschema methods, respectively, to extract the provider metadata requested by the current SQL request. The BDP extends ADO.NET and provides the function of retrieving various database object metadata. The following test results show that the BDP and IBM DB2 data providers are equally good in most basic data access operations. However, I have noticed that if the CHAR data type is used to replace the varchar data type, the IBM DB2 data provider seems to blank the data, which results in performance degradation. Data Access Use DataReader to extract 10,000 records with GetSchematable extract 10,000 records to extract 100 recorded data providers: BDP - DB200: 51.724376000: 52.724404800: 51.744404800: 51.9246404800: 51.9246401: 38.2012064 The read and write data block database client library allows clients to bind a single buffer and a record each time. Each extraction requires a network round trip, which will affect performance when the application handles massive results. Although it is not recommended to retrieve the massive result set, this is unavoidable, especially in applications such as OLAP or collect historical data statistics. Some database client libraries allow reading record blocks, clients to bind the array of buffers and retrieve record blocks in a single round trip. In any non-connected data access model (such as Borland Datasnap), when ADO.NET changes all clients to the database back to the database, you need to execute a SQL statement for each modified record. For example, if there is an inserted record, it is not the same INSERT statement as the N times, and the client can pass a set of parameter buffers to perform bulk insertion. Block read and write can significantly improve performance, especially in the WAN environment, because records can be received and transmitted in batches in a single network round trip. BDP does not currently support block reading and writing. Asynchronous execution of long-term running queries, such as complex connection or queries involving the entire table scan, a negative impact on the response capability of the application. When the database is processed SQL request, if the SQL request is not blocked, the client can handle the local application internal transaction. If the asynchronous execution is not available, the SQL request can be performed by a separate thread without running in the main thread. Currently, the ADO.NET framework does not support asynchronous execution modes, but future versions may be supported. Endy language If a variety of optimization factors have not been considered, data access may become the main bottleneck. In addition to tuning databases and tuning SQL makes it better, other metrics (such as connecting pools, minimizing metadata retrieval, remove long-term running queries to separate threads, only BLOB can be extracted if necessary, can also optimize data access performance, providing better response capabilities for any data-intensive application. Therefore, according to the needs of the application, selecting a suitable data access operation can improve performance and scalability. About the author Ramesh Theivendran is a member of the Borland Rad Database Connectivity R & D team from 1995. Currently, he is working on database connectivity research in their Win32 and .NET product team and serves as architects for DBEXPRESS and BORLAND DATA Provider (BDP) for .NET.
