Improving Ado Net Perfo Objective Ado Net Architecture

Improving Ado. Net Perfo Objective Ado. Net Architecture ADO. NET Data Providers Ado. Net Objects Connection Command Data Reader Data Adapter Data Set Connected vs. Disconnected Architecture Data Set vs. Data Reader Design Consideration Cache Data to avoid unnecessary work Acquire Late, Release Early Reduce Round Trips Return Only the data you need Use Stored Procedure Exception Management Transaction Points To Ponder

Improving Ado. Net Perfo Ado. Net – Active. X Data Object. Ado. Net is an object-oriented set of libraries that allows you to interact with data sources. Ado. Net offers Disconnected Architecture of working.

Improving Ado. Net Perfo Ado. Net Architecture

Improving Ado. Net Perfo Ado. Net Architecture Data Access in ADO. NET relies on two components: Data. Set and Data Provider. Data. Set Shared by both the System. Data. ADO and the System. Data. SQL namespaces

Improving Ado. Net Perfo ADO. NET Data Providers We know that ADO. NET allows us to interact with different types of data sources and different types of data bases. However, there isn't a single set of classes that allow you to accomplish this universally. Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol. ADO. NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source.

Improving Ado. Net Perfo ADO. NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols. The library APIs have prefixes that indicate which provider they support. Data Source Description Provider Name API prefix ODBC Data Provider Odbc Data Sources with an ODBC interface. Normally older data bases. Ole. Db Data Provider Ole. Db Data Sources that expose an Ole. Db interface, i. e. Access or Excel. Oracle Data Provider Oracle For Oracle Data Bases. SQL Data Provider Sql For interacting with Microsoft SQL Server. Borland Data Provider Bdp Generic access to many data bases such as Interbase, SQL Server, IBM DB 2, and Oracle.

Improving Ado. Net Perfo Ado. Net Objects Connection To improve database connection performance and scalability, apply the following strategies to your connection management policy: Open and close the connection in the method. Explicitly close connections. When using Data. Readers, specify Command. Behavior. Close. Connection. Do not explicitly open a connection if you use Fill or Update for a single operation.

Improving Ado. Net Perfo Ado. Net Objects Connection Open and close the connection in the method. Opening connections before they are needed reduces the number of connections that are available and increases resource pressure. Close connections quickly to ensure that they can be reused as soon as possible. Do not hold on to connections. Holding on to connections reduces the connections that are available to other code and increases resource pressure

Improving Ado. Net Perfo Ado. Net Objects Connection Explicitly close connections Explicitly call the Close or Dispose methods on Sql. Connection objects as soon as you finish using them to release the resources that they use. This delays the reuse of the connection and negatively affects performance and scalability.

Improving Ado. Net Perfo Ado. Net Objects Connection Explicitly close connections Closing Connections in C# public void Do. Some. Work() { Sql. Connection conn = new Sql. Connection(connection. String); try { conn. Open(); // Do Work } catch (Exception e) { // Handle and log error } finally { if(null!=conn) conn. Close(); } }

Improving Ado. Net Perfo Ado. Net Objects Connection Explicitly close connections Closing Connections with the Using Statement in C# The using statement simplifies code for C# developers by automatically generating a try and finally block when the code is compiled. This ensures that the Dispose method is called even if an exception occurs. The following code fragment shows how to use the using statement. using (Sql. Connection conn = new Sql. Connection(conn. String)) { conn. Open(); . . . } // Dispose is automatically called on the conn variable here

Improving Ado. Net Perfo Ado. Net Objects Connection When Using Data. Readers, Specify Command. Behavior. Close. Connection When you create a Data. Reader object, specify the Command. Behavior. Close. Connection enumeration in your call to Execute. Reader. This ensures that when you close the Data. Reader, the connection is also closed. The following code fragment shows how to use the Command. Behavior enumeration // Create connection and command. Open connection. . Sql. Data. Reader my. Reader= my. Command. Execute. Reader(Command. Behavior. Close. Connection); // read some data. . . my. Reader. Close(); // The connection and reader are closed.

Improving Ado. Net Perfo Ado. Net Objects Connection Do Not Explicitly Open a Connection if You Use Fill or Update for a Single Operation If you perform a single Fill or Update operation, do not open the connection before you call the Fill method, because the Data. Adapter automatically opens and closes the connection for you. The following code fragment shows how to call Fill. Data. Set d. Set = new Data. Set("test"); Sql. Connection conn = new Sql. Connection(conn. String); Sql. Command cmd = new Sql. Command(sql. Query, conn); Sql. Data. Adapter d. Adapter = new Sql. Data. Adapter(cmd); d. Adapter. Fill(d. Set); // The connection was not explicitly opened. // The connection is opened and closed by the Data. Adapter automatically.

Improving Ado. Net Perfo Ado. Net Objects Commands When you run commands against a database, consider the following recommendations Validate SQL input and use Parameter objects. Use Execute. Non. Query for commands that do not return data. Use Execute. Scalar to return single values.

Improving Ado. Net Perfo Ado. Net Objects Commands Validate SQL input and use Parameter objects. Use Parameter objects when you build database commands. When you use Parameter objects, each parameter is automatically type checked. use Parameter objects in conjunction with stored procedures to performance improve

Improving Ado. Net Perfo Ado. Net Objects Commands Validate SQL input and use Parameter objects. if you want to filter a query, you could build the string dynamically, but you wouldn't want to. Here is a bad example of filtering a query. // don't ever do this! Sql. Command cmd = new Sql. Command( "select * from Customers where city = '" + input. City + ‘”)”; Don't ever build a query this way! The input variable, input. City, is typically retrieved from a Text. Box control on either a Windows form or a Web Page. Anything placed into that Text. Box control will be put into input. City and added to your SQL string. This situation invites a hacker to replace that string with something malicious. In the worst case, you could give full control of your computer away.

Improving Ado. Net Perfo Ado. Net Objects Commands Validate SQL input and use Parameter objects. preparing a Sql. Command Object for Parameters // 1. declare command object with parameter Sql. Command cmd = new Sql. Command( "select * from Customers where city = @City", conn); Declaring a Sql. Parameter Object // 2. define parameters used in command object Sql. Parameter param = new Sql. Parameter(); param. Parameter. Name = "@City"; param. Value = input. City; Associate a Sql. Parameter Object with a Sql. Command Object // 3. add new parameter to command object cmd. Parameters. Add(param);

Improving Ado. Net Perfo Ado. Net Objects Commands Use Execute. Non. Query for commands that do not return data. If you want to run commands that do not retrieve data, use the Execute. Non. Query method. For example, you would use Execute. Non. Query for the following types of commands: Data Definition Language commands such as CREATE and ALTER Data Modification Language commands such as INSERT, UPDATE, and DELETE Data Control Language commands such as GRANT and REVOKE. Sql. Connection conn = new Sql. Connection(conn. String); Sql. Command cmd = new Sql. Command( "UPDATE Customer SET Freight = 45. 44 WHERE Customer. ID = 10248", conn); cmd. Execute. Non. Query();

Improving Ado. Net Perfo Ado. Net Objects Commands Use Execute. Scalar to Return Single Values If you want to retrieve a single value from your query by using a function such as COUNT(*) or SUM(Price), static int Get. Customer. Count. With. Scalar() { int customer. Count = 0; Sql. Connection conn = new Sql. Connection( "server=(local); Integrated Security=SSPI; database=northwind"); Sql. Command cmd = new Sql. Command("Get. Customer. Count. With. Scalar", conn ); cmd. Command. Type = Command. Type. Stored. Procedure; try { conn. Open(); customer. Count = (int)cmd. Execute. Scalar(); } finally { if(null!=conn) conn. Close(); } return customer. Count; }

Improving Ado. Net Perfo Ado. Net Objects Data Reader A Sql. Data. Reader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. Sql. Data. Readers are often described as fast-forward firehose-like streams of data. You can read from Sql. Data. Reader objects in a forward-only sequential manner. Once you've read some data, you must save it because you will not be able to go back and read it again.

Improving Ado. Net Perfo Ado. Net Objects Data Reader Creating a Sql. Data. Reader Object Sql. Data. Reader rdr = cmd. Execute. Reader(); Reading Data while (rdr. Read()) { // get the results of each column string contact = rdr["Contact. Name"]; string company = rdr["Company. Name"]; string city = rdr["City"]; } Finishing Up if (rdr != null) rdr. Close();

Improving Ado. Net Perfo Ado. Net Objects Data Adapter The Data. Adapter is the class at the core of ADO. NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a Data. Set The Data. Adapter is used either to fill a Data. Table or Data. Set with data from the database with it's Fill method. After the memory-resident data has been manipulated, the Data. Adapter can commit the changes to the database by calling the Update method. The Data. Adapter provides four properties that represent database commands: Select. Command, Insert. Command, Delete. Command, Update. Command

Improving Ado. Net Perfo Ado. Net Objects Data Set The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The Data. Set is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this Data. Set is finished, changes can be made back to the central database for updating. The data in Data. Set can be loaded from any valid data source like Microsoft Sql Server database, an Oracle database or from a Microsoft Access database.

Improving Ado. Net Perfo Ado. Net Objects Data Set To help optimize the performance of Data. Set objects, consider the following recommendations: Reduce serialization. Use primary keys and Rows. Find for indexed searching. Use a Data. View for repetitive non-primary key searches. Use the Optimistic concurrency model for datasets.

Improving Ado. Net Perfo Ado. Net Objects Data Set Reduce serialization. Use column name aliasing. The serialized data contains column names so that you can use column name aliasing to reduce the size of the serialized data. Avoid serializing multiple versions of the same data. The Data. Set maintains the original data along with the changed values. If you do not need to serialize new and old values, call Accept. Changes before you serialize a Data. Set to reset the internal buffers. Reduce the number of Data. Table objects that are serialized. If you do not need to send all the Data. Table objects contained in a Data. Set, consider copying the Data. Table objects you need to send into a separate Data. Set.

Improving Ado. Net Perfo Ado. Net Objects Data Set Use Primary Keys and Rows. Find for Indexed Searching If you need to search a Data. Set by using a primary key, create the primary key on the Data. Table. This creates an index that the Rows. Find method can use to quickly find the records that you want. Do not use Data. Table. Select because Data. Table. Select does not use indices.

Improving Ado. Net Perfo Ado. Net Objects Data Set Use a Data. View for Repetitive Non-Primary Key Searches If you need to repetitively search by using non-primary key data, create a Data. View that has a sort order. This creates an index that can be used to perform the search. This is best suited to repetitive searches because there is some cost to creating the index. The Data. View object exposes the Find and Find. Rows methods so that you can query the data in the underlying Data. Table.

Improving Ado. Net Perfo Ado. Net Objects Data Set Use the Optimistic Concurrency Model for Datasets There are two concurrency models that you can use when working with datasets in an environment that has multiple users. These two models are the pessimistic and optimistic models. When you read data and use the pessimistic model, locks are established and held until updates are made and the locks are released. Holding locks on server resources, in this case database tables, leads to contention issues.

Improving Ado. Net Perfo Ado. Net Objects Data Set Use the Optimistic Concurrency Model for Datasets The optimistic model does not lock the data when the data is read. The optimistic model locks the data just before the data is updated and releases the lock afterwards. There is less contention for data with the optimistic model, which is good for shared server scenarios; however, you should take into account the scenarios for managing the concurrency violations. A common technique you can use to manage concurrency violations is to implement a timestamp column or to verify against the original copy of data.

Improving Ado. Net Perfo Connected vs. Disconnected Architecture Connected Architecture in Ado. Net is, connection state should be established in open state until a data manipulation processs to be completed, this is said to be connected Architecture. Ex: Data Reader Disconnected Architecture in Ado. Net is, data manipulation process is done without the connection state is open this is achieved by caching the data in cache memory and manipulating from the cache. Ex: Data Set

Improving Ado. Net Perfo Data Set vs. Data Reader Use a Data. Reader when the following conditions are true: You need forward-only, read-only access to data (the fire hose scenario), and you want to access the data as quickly as possible, and you do not need to cache it. You have a data container such as a business component that you can put the data in. Note: It’s forward only

Improving Ado. Net Perfo Data Set vs. Data Reader Use a Data. Set when the following conditions are true: You have to cache or pass the data between layers. You require an in-memory relational view of the data for XML or non. XML manipulation. You want to update some or all the retrieved rows, and you want to use the batch update facilities of the Sql. Data. Adapter class. You have to bind data to a control type that the Data. Reader cannot be bound to. Many Windows Forms controls capable of data binding require a data source that implements the IList interface. You have to access multiple sets of data at the same time, and you do not want to hold open server resources.

Improving Ado. Net Perfo Design Consideration Cache Data to Avoid Unnecessary Work Caching data can substantially reduce the load on your database server. By caching data, you avoid the overhead of connecting to your database, searching, processing, and transmitting data from your database server. By caching data, you directly improve performance and scalability in your application. When you define your caching strategy, consider the following: Is the data used application-wide and shared by all users, or is the data specific to each user? Data that is used across the application, such as a list of products, is a better caching candidate than data that is specific to each user.

Improving Ado. Net Perfo Design Consideration How frequently do you need to update the cache? Even though the source data may change frequently, your application may not need to update the cache as often. If your data changes too frequently, it may not be a good caching candidate. You need to evaluate the expense of updating the cache compared to the cost of fetching the data as needed. Where should you cache data? You can cache data throughout the application layers. By caching data as close as possible to the consumer of the data, you can reduce the impact of network latency. What form of the data should you cache? The best form of data to cache is usually determined by the form that your clients require the data to be in. Try to reduce the number of times that you need to transform data. How do you expire items in the cache? Consider the mechanism that you will use to expire old items in the cache and the best expiration time for your application.

Improving Ado. Net Perfo Acquire Late, Release Early Your application should share expensive resources efficiently by acquiring the resources late, and then releasing them as early as possible. To do so: Open database connections right when you need them. Close the database connections as soon as you are finished. Do not open them early, and do not hold them open across calls. Acquire locks late, and release them early.

Improving Ado. Net Perfo Reduce Round Trips Network round trips are expensive and affect performance. Minimize round trips by using the following techniques: If possible, batch SQL statements together. Failure to batch work creates additional and often unnecessary trips to the database. You can batch text SQL statements by separating them with a semicolon or by using a stored procedure. If you need to read multiple result sets, use the Next. Result method of the Data. Reader object to access subsequent result sets.

Improving Ado. Net Perfo Reduce Round Trips Use connection pooling to help avoid extra round trips. By reusing connections from a connection pool, you avoid the round trips that are associated with connection establishment and authentication. Do not return results if you do not need them. If you only need to retrieve a single value, use the Execute. Scalar method to avoid the operations that are required to create a result set. You can also use the Execute. Non. Query method when you perform data definition language (DDL) operations such as the create table operation. This also avoids the expense of creating a result set. Use caching to bring nearly static data close to the consumer instead of performing round trips for each request.

Improving Ado. Net Perfo Return Only Data You Need Evaluate the data that your application actually requires. Minimize the data that is sent over the network to minimize bandwidth consumption. The following approaches help reduce data over the network: Return only the columns and rows that you need. Cache data where possible. Provide data paging for large results. For more information about paging, see "Paging Records" later in this chapter.

Improving Ado. Net Perfo Use Stored Procedures Avoid embedded SQL statements. Generally, welldesigned stored procedures outperform embedded SQL statements. However, performance is not the only consideration. When you choose whether to store your SQL commands on the server by using stored procedures or to embed commands in your application by using embedded SQL statements, consider the following issues:

Improving Ado. Net Perfo Exception Management ADO. NET errors that are propagated through Sql. Exception or Ole. Db. Exception objects use custom error handling for specific data access code. Consider the following guidelines for exception handling in ADO. NET data access code: Use the Connection. State property. Avoid relying on an error handler to detect connection state availability. When you can, use the Connection. State. Open or Connection. State. Close method to check the state before use. specific exceptions precede generic handlers, as shown in the following code fragment. try {. . . } catch (Sql. Exception sqlex) // specific handler {. . . } catch (Exception ex) // Generic handler {. . . }

Improving Ado. Net Perfo Exception Management Use try/finally to clean up resources. Use try/finally more often than try/catch/finally. Using finally gives you the option to close the connection, even if an exception occurs. If you develop in C#, the using statement provides this functionality with code that is easy to maintain, as shown in the following code fragment. using( Sql. Connection conn = new Sql. Connection( "server=(local); Integrated Security=SSPI; database=Northwind") ) {. . . }

Improving Ado. Net Perfo Exception Management Use specific handlers to catch specific exceptions. If you know that there are scenarios where specific errors could possibly occur, use specific handlers. For example, if you want to know if a concurrency violation occurs when multiple updates are occurring, look for exceptions of type DBConcurrency. Exception. The specific exception classes Sql. Exception and Ole. Db. Exception provide a detailed message when errors occur. Use this message to log the details. Ensure that specific exceptions precede generic handlers, as shown in the following code fragment.

Improving Ado. Net Perfo Transactions are important for ensuring data integrity but come at an operational cost. Selecting the right transaction management mechanism for your application can significantly improve scalability and performance. Key considerations include the type and quantity of resources involved and the isolation level required for the transactions. When you determine how you should manage transactions in your system, consider the following recommendations: Use SQL transactions for server-controlled transactions on a single data store. Use ADO. NET transactions for client-controlled transactions on a single data store. Keep transactions as short as possible.

Improving Ado. Net Perfo Transactions Use SQL transactions for server-controlled transactions on a single data store. If you need to write to a single data store, and if you can complete the operation in a single call to the database, use the transaction control provided by the SQL language on your database transaction runs close to the data and reduces the cost of the transaction. Running the transaction close to the data also permits database administrators to tune the operation without changing the deployment of your application code. The following code fragment shows a simple T-SQL transaction performed in a stored procedure. BEGIN TRAN UPDATE Orders SET Freight=@Freight Where Order. ID=@Order. ID UPDATE [Order Details] SET Quantity=@Quantity Where Order. ID=@Order. ID IF (@@ERROR > 0) ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION

Improving Ado. Net Perfo Transactions Use ADO. NET transactions for client-controlled transactions on a single data store. Use ADO. NET Transactions for Client-Controlled Transactions on a Single Data Store If you need to make multiple calls to a single data store participate in a transaction, use ADO. NET manual transactions. The. NET Data Provider for SQL Server and the. NET Data Provider for Oracle use the appropriate transaction language to enforce transactions on all subsequent SQL commands.

Improving Ado. Net Perfo Transactions Use ADO. NET transactions for client-controlled transactions on a single data store. If you use SQL Profiler to monitor your use of ADO. NET manual transactions, you see that BEGIN TRAN, COMMIT TRAN, or ROLLBACK TRAN is run against the data store on your behalf by the provider. This enables you to control the transaction from your. NET Framework code and to maintain performance at a level that is similar to SQL transactions. The following code fragment shows how to use ADO. NET transactions.

Improving Ado. Net Perfo Transactions Use ADO. NET transactions for client-controlled transactions on a single data store. Sql. Connection conn = new Sql. Connection(conn. String); Sql. Transaction trans = conn. Begin. Transaction(); try { Sql. Command cmd = new Sql. Command("My. Write. Proc", conn, trans); cmd. Command. Type = Command. Type. Stored. Procedure; cmd. Parameters. Add(. . …) // additional transactioned writes to database trans. Commit(); } catch { trans. Rollback(); }

Improving Ado. Net Perfo Transactions Keep Transactions as Short as Possible Design your code to keep transactions as short as possible to help minimize lock contention and to increase throughput. Avoid selecting data or performing long operations in the middle of a transaction.