Improving Ado.Net Performance

1. Improving Ado.Net Performance 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

2. Improving Ado.Net Performance Ado.Net Ado.Net – ActiveX 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.

3. Improving Ado.Net Performance Ado.Net Architecture Ado.Net

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

5. Improving Ado.Net Performance 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.

6. Improving Ado.Net Performance ADO.NET Data Providers 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.

7. Improving Ado.Net Performance 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 DataReaders, specify CommandBehavior.CloseConnection. • Do not explicitly open a connection if you use Fill or Update for a single operation.

8. Improving Ado.Net Performance 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

9. Improving Ado.Net Performance Ado.Net Objects Connection Explicitly close connections Explicitly call the Close or Dispose methods on SqlConnection 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.

10. Improving Ado.Net Performance Ado.Net Objects • Connection • Explicitly close connections • Closing Connections in C# • public void DoSomeWork() • { • SqlConnection conn = new SqlConnection(connectionString); • try • { • conn.Open(); • // Do Work • } • catch (Exception e) • { • // Handle and log error • } • finally • { • if(null!=conn) • conn.Close(); • } • }

11. Improving Ado.Net Performance 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 (SqlConnection conn = new SqlConnection(connString)) { conn.Open(); . . . } // Dispose is automatically called on the conn variable here

12. Improving Ado.Net Performance Ado.Net Objects Connection When Using DataReaders, Specify CommandBehavior.CloseConnection When you create a DataReader object, specify the CommandBehavior .CloseConnection enumeration in your call to ExecuteReader. This ensures that when you close the DataReader, the connection is also closed. The following code fragment shows how to use the CommandBehavior enumeration // Create connection and command. Open connection . . . . SqlDataReader myReader= myCommand.ExecuteReader(CommandBehavior.CloseConnection); // read some data . . . myReader.Close(); // The connection and reader are closed.

13. Improving Ado.Net Performance 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 DataAdapter automatically opens and closes the connection for you. The following code fragment shows how to call Fill. DataSet dSet = new DataSet("test"); SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sqlQuery,conn); SqlDataAdapter dAdapter = new SqlDataAdapter(cmd); dAdapter.Fill(dSet); // The connection was not explicitly opened. // The connection is opened and closed by the DataAdapter automatically.

14. Improving Ado.Net Performance Ado.Net Objects • Commands • When you run commands against a database, consider the following recommendations • Validate SQL input and use Parameter objects. • Use ExecuteNonQuery for commands that do not return data. • Use ExecuteScalar to return single values.

15. Improving Ado.Net Performance 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 improve performance

16. Improving Ado.Net Performance 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! • SqlCommand cmd = new SqlCommand( "select * from Customers where city = '" + inputCity + ‘”)”; • Don't ever build a query this way!  The input variable, inputCity, is typically retrieved from a TextBox control on either a Windows form or a Web Page.  Anything placed into that TextBox control will be put into inputCity 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.

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

18. Improving Ado.Net Performance Ado.Net Objects Commands Use ExecuteNonQuery for commands that do not return data. If you want to run commands that do not retrieve data, use the ExecuteNonQuery method. For example, you would use ExecuteNonQuery 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. SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand( "UPDATE Customer SET Freight = 45.44 WHERE CustomerID = 10248", conn); cmd.ExecuteNonQuery();

19. Improving Ado.Net Performance Ado.Net Objects Commands Use ExecuteScalar 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 GetCustomerCountWithScalar() { int customerCount = 0; SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=northwind"); SqlCommand cmd = new SqlCommand("GetCustomerCountWithScalar", conn ); cmd.CommandType = CommandType.StoredProcedure; try { conn.Open(); customerCount = (int)cmd.ExecuteScalar(); } finally { if(null!=conn) conn.Close(); } return customerCount; }

20. Improving Ado.Net Performance Ado.Net Objects Data Reader A SqlDataReader is a type that is good for reading data in the most efficient manner possible.  You can *not* use it for writing data.  SqlDataReaders are often described as fast-forward firehose-like streams of data. You can read from SqlDataReader 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.

21. Improving Ado.Net Performance Ado.Net Objects Data Reader • Creating a SqlDataReader Object • SqlDataReader rdr = cmd.ExecuteReader(); • Reading Data • while (rdr.Read()) • { • // get the results of each column • string contact = rdr["ContactName"]; • string company = rdr["CompanyName"]; • string city = rdr["City"]; • } • Finishing Up • if (rdr != null) • rdr.Close();

22. Improving Ado.Net Performance Ado.Net Objects Data Adapter • The DataAdapter 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 DataSet • The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. • After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. • The DataAdapter provides four properties that represent database commands: • SelectCommand, InsertCommand, DeleteCommand, UpdateCommand

23. Improving Ado.Net Performance 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 DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft Sql Server database, an Oracle database or from a Microsoft Access database.

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

25. Improving Ado.Net Performance 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 DataSet maintains the original data along with the changed values. If you do not need to serialize new and old values, call AcceptChanges before you serialize a DataSet to reset the internal buffers. • Reduce the number of DataTable objects that are serialized. • If you do not need to send all the DataTable objects contained in a DataSet, consider copying the DataTable objects you need to send into a separate DataSet.

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

27. Improving Ado.Net Performance Ado.Net Objects Data Set Use a DataView for Repetitive Non-Primary Key Searches If you need to repetitively search by using non-primary key data, create a DataView 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 DataView object exposes the Find and FindRows methods so that you can query the data in the underlying DataTable.

28. Improving Ado.Net Performance 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.

29. Improving Ado.Net Performance 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.

30. Improving Ado.Net Performance 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

31. Improving Ado.Net Performance Data Set vs. Data Reader • Use a DataReader 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

32. Improving Ado.Net Performance Data Set vs. Data Reader • Use a DataSet 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 SqlDataAdapter class. • You have to bind data to a control type that the DataReader 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.

33. Improving Ado.Net Performance 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.

34. Improving Ado.Net Performance 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.

35. Improving Ado.Net Performance 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.

36. Improving Ado.Net Performance 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 NextResult method of the DataReader object to access subsequent result sets.

37. Improving Ado.Net Performance 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 ExecuteScalar method to avoid the operations that are required to create a result set. • You can also use the ExecuteNonQuery 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.

38. Improving Ado.Net Performance 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.

39. Improving Ado.Net Performance Use Stored Procedures • Avoid embedded SQL statements. Generally, well-designed 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:

40. Improving Ado.Net Performance Exception Management • ADO.NET errors that are propagated through SqlException or OleDbException 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 ConnectionState property. Avoid relying on an error handler to detect connection state availability. When you can, use the ConnectionState.Open or ConnectionState.Close method to check the state before use. • specific exceptions precede generic handlers, as shown in the following code fragment. • try • { • ... • } • catch (SqlException sqlex) // specific handler • { • ... • } • catch (Exception ex) // Generic handler • { • ... • }

41. Improving Ado.Net Performance 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( SqlConnection conn = new SqlConnection( "server=(local);Integrated Security=SSPI;database=Northwind") ) • { • . . . • }

42. Improving Ado.Net Performance 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 DBConcurrencyException. • The specific exception classes SqlException and OleDbException 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.

43. Improving Ado.Net Performance Transactions • 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.

44. Improving Ado.Net Performance 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 OrderID=@OrderID • UPDATE [Order Details] SET Quantity=@Quantity Where OrderID=@OrderID • IF (@@ERROR > 0) • ROLLBACK TRANSACTION • ELSE • COMMIT TRANSACTION

45. Improving Ado.Net Performance 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.

46. Improving Ado.Net Performance 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.

47. Improving Ado.Net Performance Transactions • Use ADO.NET transactions for client-controlled transactions on a single data store. • SqlConnection conn = new SqlConnection(connString); • SqlTransaction trans = conn.BeginTransaction(); • try • { • SqlCommand cmd = new SqlCommand("MyWriteProc",conn, trans); cmd.CommandType = CommandType.StoredProcedure; • cmd.Parameters.Add(.... …) • // additional transactioned writes to database • trans.Commit(); • } • catch • { • trans.Rollback(); • }

48. Improving Ado.Net Performance 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.