Improve ADO.Net performance

Here are a few tips to improve ADO.Net performance

Poor performance is frustrating to an end user, and can lead to users not using your applications in the intended manner. Take advantage of these five tips to accelerate the performance

Although IT organizations invest significant resources in optimizing the network topology and database design of their applications, many IT organizations overlook the performance aspects of the database middleware. Developers overlook the impact that the ADO.NET provider has on the application, even though a significant percentage of the response time is related to the time spent requesting and receiving data from the database.

Performance and scalability problems can be debilitating to the overall success of an application and ultimately to the success of the development team. If an application suffers from poor response time, user productivity suffers, service-level agreements are violated, and the reputation of the development organization is maligned. For critical systems, application performance issues can be tied directly to business success in the form of increased cost, decreased revenue, and assumption of additional risk.

Some organizations deal with performance and scalability issues in a reactive fashion, because they lack the development and testing procedures necessary to optimize the application. They simply develop the application and deal with performance issues as they arise in the development environment.

A much better solution is to attempt to take the requisite steps to make sure your application performs to your user’s expectations, both from a functional and performance standpoint. However, there are quite a few steps you can take on your own to make sure your application processes task in the most efficient manner before production deployment.

One of the key reasons for user performance complaints in database apps is that developing .NET data access code that performs fast isn’t easy. The ADO.NET documentation includes only basic guidelines and interface definitions to help programmers develop apps using ADO.NET, and it provide next to nothing in terms of prescriptive guidance to developers who want to write code that performs well. There is very little guidance for writing code that performs well.

That said, you’re not entirely on your own. You can take quite a few steps that will result in code that performs faster and more reliably. I’ll walk you through several of the common performance pitfalls that I see made on a regular basis, as well as how to avoid them.

Tips: 1

Fast to Code != Fast Code

Many programmers use the DbCommandBuilder object because it can save time when coding a new application that uses DataSets. However, this shortcut can have a negative effect on performance. Built-in concurrency restrictions can lead to the DbCommandBuilder generating highly inefficient SQL statements. For example, suppose you have an eight-column table called EMP that contains employee records. The DbCommandBuilder object generates this UPDATE statement:

CommandText: "UPDATE EMP SET EMPNO = ?, ENAME = ?, JOB = ?, MGR = ?, HIREDATE = ?, SAL = ?, COMM = ?, DEPT = ? WHERE ( (EMPNO = ?) AND (ENAME = ?) AND (JOB = ?) AND ((MGR IS NULL AND ? IS NULL) OR (MGR = ?)) AND (HIREDATE = ?) AND (SAL = ?) AND ((COMM IS NULL AND ? IS NULL) OR (COMM = ?)) AND (DEPT = ?) )"

You can write much more efficient UPDATE and DELETE statements than the ones the DbCommandBuilder generates. For example, assume you’re working with the previous example, and you know the underlying database schema. Also, assume that you know the EMPNO column of the EMP table is the primary key for the table. You can create a much simpler UPDATE statement that retrieves the same results:

UPDATE EMP SET EMPNO = ?, ENAME = ?, JOB = ?, MGR = ?, HIREDATE = ?, SAL = ?, COMM = ?, DEPT = ? WHERE EMPNO = ?

This statement runs much more efficiently on the database server than the statement the DbCommandBuilder generated.

Another drawback of the DbCommandBuilder object—it generates statements at runtime. Each time a DataAdapter.Update method is called, the DbCommandBuilder analyzes the contents of the result set and generates UPDATE, INSERT, and DELETE statements for the DataAdapter. The programmer can avoid this extra processing time by specifying the UPDATE, INSERT, and DELETE statements for the DataAdapter explicitly.

Tips: 2
Avoid retrieving long data if you don’t need it. Retrieving long data across a network is slow and resource-intensive. Remember that when you use a DataSet, all data is retrieved from the data source, even if you never use it. However, some applications don’t formulate the select list before sending the query to the .NET data provider. In other words, some applications use this syntax to accomplish sending the query:

send SELECT * from ..
If the select list contains long data, most .NET data providers must retrieve that data at fetch time, even if the application never binds the long data result columns to display to the user. You should try to implement a method that limits the number of columns you retrieve whenever possible.

Users Don’t Want Long Data

It also helps to remember that most users don’t want to see long data. If the user does want to process these result items, the application can query the database again, specifying only the long columns in the select list. This method allows the average user to retrieve the result set without paying a high performance penalty for network traffic. Consider this query:

SELECT * from EMPLOYEES WHERE SSID = '999-99-2222'

An application might want to retrieve only this employee’s name and address. Unfortunately, a .NET data provider doesn’t know which result columns an application wants to retrieve when the query is executed. A data provider knows only that an application can request any of the result columns. When the .NET data provider processes the fetch request, it will most likely return one or more result rows across the network from the database server. In this case, a result row contains all the column values for each row, including an employee photograph if the Employees table contains such a column. Limiting the select list to contain only the columns you need results in decreased network traffic and a faster performing query at runtime.

Tips: 3
Another common performance pitfall concerns how you handle commits. Committing transactions is slow because of disk I/O and, potentially, network I/O. Always start a transaction after connecting; otherwise, you remain in Autocommit mode.

A commit involves several actions. The database server must flush back to disk every data page that contains updated or new data. This is usually a sequential write to a journal file. By default, Autocommit is on when connecting to a data source, and Autocommit mode usually impairs performance because of the amount of disk I/O needed to commit every operation.

Also, some database servers do not provide an Autocommit mode natively. For this type of server, the .NET data provider must issue a COMMIT statement explicitly and a BEGIN TRANSACTION for every operation sent to the server. You also pay a performance penalty for up to three network requests for every statement issued by an application—in addition to the large amount of disk I/O required to support Autocommit mode.

Consider this code fragment that starts an Oracle transaction:

DbProviderFactoryf = DbProviderFactories.GetFactory( "DDTek.Oracle");
try {
conn = f.CreateConnection();
conn.ConnectionString = ("Connection String info");
conn.Open();
DbTransaction transId = conn.BeginTransaction();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from users";
DbTransaction transId = conn.BeginTransaction();
cmd.Transaction = transId;
DbDataReader reader = cmd.ExecuteReader();
// Continue to work with transaction boundary.
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

This approach can make an enormous difference. I once had a customer who performed 5,000,000 inserts to DB2. He heard me give this tip during a talk and made the change to his application. The insert went from taking five hours to taking ten minutes!

Using transactions can help application performance tremendously, but don’t take this technique too far. Leaving transactions active can reduce throughput by holding locks on rows for long times, preventing other users from accessing the rows. You should commit transactions in intervals that allow maximum concurrency.

Tips: 4

Use DbCommand.Prepare() Appropriately

Using the DbCommand.Prepare method can have a significant positive (or negative) effect on query execution performance. The DbCommand.Prepare method tells the underlying data provider to optimize for multiple executions of statements that use parameter markers. Note that you can Prepare any command regardless of the execution method used (ExecuteReader, ExecuteNonQuery, or ExecuteScalar) .

Consider a .NET data provider that implements DbCommand.Prepare by creating a stored procedure on the server that contains the prepared statement. Creating stored procedures involves substantial overhead, but you can execute the statement multiple times. Doing so minimizes the cost of executing that statement because the query is parsed and optimization paths are stored at create procedure time. Applications that execute the same statement multiples times can benefit greatly from calling DbCommand.Prepare and then executing that Command as needed.

However, using DbCommand.Prepare for a statement that is executed only once results in unnecessary overhead. Furthermore, applications that use DbCommand.Prepare for large, single-execution query batches exhibit poor performance. Similarly, applications that either always use DbCommand.Prepare, or never use DbCommand.Prepare, do not perform as well as those that use a logical combination of prepared and unprepared statements.

Tips: 5
Much has been written about when to choose DataReaders over DataSets. However, it is a critical choice when it comes to performance, so I will add to the significant amount of advice that is already there.

Let me sum it up as bluntly as I can: The DataReader will always be faster at fetching data. Period.

The DataSet uses a DataAdapter to retrieve the data from the database. The DataAdapter uses the DbDataReader when it is reading data. Given this, you might wonder why the DataReader is always faster. The answer lies in the fact that DataSet performs additional processing once the data is fetched from the DataReader, converting the data to the internal format of the DataSet and storing it in memory.

Once it has the data in memory, the DataSet maintains both the original and any changed data, leading to even higher memory usage. This can also lead to a scalability problem, depending on the size and number of copies that you keep around.

All that said, the DataSet is much more functional. It allows for random fetching (the DataReader is forward-only), it gives XML capabilities to relational data, it is disconnected from the database so it does not use up resources on the server, and it is updateable. My bottom line recommendation for performance is to use a DataReader when you don’t need the additional functionality of the DataSet.

Each one of the tips I’ve mentioned can improve the performance of your applications. Taken as a whole, these tips can have a considerable impact, not just on raw application speed, but in user satisfaction and in the ability of your company to meet its business goals.

DBA recommendations for writing Stored Procedure

1. Try to use derived tables when possible, instead of temporary tables to gain better performance.
ex:
SELECT MIN(Salary)
FROM (SELECT TOP 5 Salary FROM Employees ORDER BY Salary Desc)

2. Do not use 'SELECT * from table'. Only return the columns you want/need. This brings more data back over the network and also eliminates the use of some indexes (covering indexes). This may also screw up apps that are expecting a certain number of columns when a column is added.

3. Use the @@error global variable for error-handling, but store the value into your own user variable, as the value will be reset after the next statement. All Global variables should be stored into a local variable if they are going to be used later in processing.

4. Always 'SET NOCOUNT ON' at the beginning of stored procedures. This will reduce the network round trips your SQL will have to make.

5. Avoid using 'print' in SPs unless it is needed. Print statements cause network trips and acknowledgments from the client. If the client does not need to know - or nothing is receiving the 'print' - this is unneeded.

6. Develop and stick with a standard naming convention for your area/project.

7. Never use a wildcard character (%) at the beginning of a search string as it will always result in a table scan.

8. When doing an insert in an SP - Always specify the column names - to avoid issues when a column is added to the table.

9. ALWAYS specify the 2 part name when executing SPs.
ex: EXEC dbo.sp_proc. When the 2 part name is not used SQL Server first checks for an SP owned by the executer, then dbo.

10. Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks.

11. Don't use the prefix "sp_" in a stored procedure name. The reason for this is that whenever a stored procedure is executed with the prefix of 'sp_' SQL Server first looks in the MASTER database to execute it - if it does not find it in MASTER it will then use the current database.

12. If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure.

13. Avoid nesting transactions in stored procedures. If you need transaction support from SP to SP use a savepoint.

14. If your SP does not require 'transaction safety' use the 'NOLOCK' table hint. A table hint lets the SQL engine ignore and not perform locks for a given operation.

15. Try to avoid using temporary tables inside your stored procedures. Use Table Variables if possible. Temp Tables Reduce the chance of plan reuse. Table variables also have less locking overhead, making them faster.

16. Place all DDL language at the top of the Stored Procedure. All DML should follow. This will increase the chance of plan reuse.

17. Use the 'TOP' operator over the 'SET ROWCOUNT' command to limit the number of rows returned, as there is less overhead. (And ROWCOUNT is going away after SQL2K).

18. Use Global temp tables only if you absolutely have to.

19. Clearly document and mark any optimizer hints.

20. Limit your joins to 4 tables or less. The SQL Server optimizer is only so smart (and so is your DBA).

21.Do not use a function in the where clause.
For example if you are looking for records with a timestamp greater
than 7 days old don't say
where ts > getdate() - 7.
Set a variable "SELECT @date = getdate() -7" and then use:
Where ts > @date

22. Do not use ‘Where not exists’.
Instead use a left outer join where the left side is null

Advantages of INSTEAD OF Triggers

You can write a trigger for a view, but if the view is updateable it isn't necessary. Triggers on the underlying table fire automatically. (Of course, you may have your own reasons why you want triggers on such views.) Of all the advantages INSTEAD OF triggers offer, the main one is that they allow views that would normally not be updateable to support updates. A view that involves multiple tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table. For example, you can write an INSTEAD OF trigger that inserts rows in multiple tables from a single view.

Another important advantage to INSTEAD OF triggers is that they allow you to write logic that accepts parts of a batch while rejecting other parts. Finally, INSTEAD OF triggers allow you to take some alternative action in the event of some particular condition that the application defines as an error.

Isolation level in SQL

Transaction Isolation Levels

Closely tied in with the modes and methods of locking is the transaction isolation level. To understand the new locking behavior, you need to understand the four transaction isolation levels in SQL Server 7.0: Uncommitted Read (also called "dirty read"), Committed Read, Repeatable Read, and Serializable.

IsolationLevels
The isolation level that your transaction runs in determines how sensitive your application is to changes other users' transactions make, and consequently, how long your transaction must hold locks to protect against these changes. The ANSI SQL standard defines four levels of transaction isolation. Although previous versions of SQL Server let you specify all four distinct levels of transaction isolation, there were only three different behaviors because SQL Server internally treated two of the syntactic specifications (i.e., Repeatable Read and Serializable) as synonymous.

You can change the level of isolation that a particular connection is operating in by using the SET TRANSACTION ISOLATION LEVEL command. Keep in mind that the SET command applies only to your current connection, and every time you make a new connection (or open a new window in the Query Analyzer), you'll be back in the default isolation level. I'll use each of the four isolation levels in the examples to follow.

To see how each level behaves, you can use the script in Listing 1, page 20, to create a table with a few rows in it. I'll refer back to this table in examples for each of the four isolation levels.

UncommittedRead
Uncommitted Read, or dirty read, lets a transaction read any data currently on a data page, whether or not that data has been committed. For example, although another user might have a transaction in progress that has updated data, and that transaction is holding exclusive locks on the data, your transaction can read the data anyway, and possibly take further actions based on the values you read. The other user might then decide to roll back his or her transaction, so logically, those changes never occurred. Although this scenario isn't desirable, with Uncommitted Read you won't get stuck waiting for a lock, nor will your reads acquire share locks that might affect others.

Let's see how Uncommitted Read behaves. Use the SQL Server 7.0 Query Analyzer, and start two separate connections. Use the pubs database in each one. In the first connection, begin a transaction, but don't commit it:

BEGIN TRAN
UPDATE ISOLATION_TEST
SET col2 = 'New Value'

Now, use the second connection, and change your isolation level before trying to access the same table.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM ISOLATION_TEST

All the values in col1 are 0, even though the transaction in the first connection has not committed yet. In fact, the transaction might never commit. If you took some action based on the fact that all the values are 0, you could regret it if the changes turned out not to be permanent. Back in the first connection, roll back the transaction:

ROLLBACK TRAN

Now rerun the SELECT statement in the second connection to see that all the values are back to what they were before. If you're following along with these examples, make sure you close your connections after each one, so that all outstanding locks are released.

CommittedRead
Committed Read is SQL Server's default isolation level. It ensures that an operation will never read data another application has changed but not yet committed. Because you can never read uncommitted data, if a transaction running with Committed Read isolation revisits data, that data might have changed, or new rows might appear that meet the criteria of the original query. Rows that appear in this way are called phantoms.

So Committed Read behavior has two aspects. To see the first aspect, you can run the above example, without setting the second connection to use isolation level Read Uncommitted. The second connect would then block on the SELECT statement; it can't read the changes the first connection has made but not yet committed (or rolled back). To see the second Committed Read behavior, close all the connections in the Query Analyzer from the previous example, and open two new connections using pubs again. In the first connection, run the following batch:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT AVG(col1) from ISOLATION_TEST

In the second connection, update the table:
UPDATE ISOLATION_TEST
SET col1 = 500 WHERE col1 = 50

Notice that the update is successful, even though the first connection is still inside a transaction.
Go back to the first connection and run the same SELECT statement:

SELECT AVG(col1) from ISOLATION_TEST

The average value is now different. The default isolation level does not prevent another connection from changing data you have read. Because you are not guaranteed to see the same data if you rerun the SELECT within the transaction, the read operations are not guaranteed to be repeatable.

RepeatableRead
If you want the read operations to be repeatable, choose the third isolation level. The Repeatable Read isolation level adds to the properties of Committed Read by ensuring that if a transaction revisits data or if a query is reissued, the data will not have changed. In other words, issuing the same query twice within a transaction won't pick up any changes to data values that another user's transaction has made. No other user can modify the data that your transaction visits as long as you have not yet committed or rolled back your transaction.

To see Repeatable Read behavior, close all the connections, and open two new ones in pubs. Issue the same two queries as above, but this time, have the first connection

SET ISOLATION LEVEL REPEATABLE READ.

The second connection will have to use a slightly different update statement, because the value of 50 for col1 no longer exists:

UPDATE ISOLATION_TEST
SET col1 = 5000 WHERE col1 = 500

This update will block when it tries to update the ISOLATION_TEST table. And the first connection will get the same result when it reissues its original SELECT. Preventing nonrepeatable reads is a desirable safeguard, but it comes at a price. The cost of this extra safeguard is that all the shared locks in a transaction must be held until the completion (COMMIT or ROLLBACK) of the transaction.

However, Repeatable Read isolation doesn't prevent all possible changes. It protects only the data that you have read. The following example shows you what this protection means. Close all connections, and open two new ones connecting to pubs. In the first connection, start a transaction in Repeatable Read isolation level and look for all rows that meet a certain condition.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM ISOLATION_TEST
WHERE col1 BETWEEN 20 AND 40

In the second connection, insert a new row:
INSERT INTO ISOLATION_TEST
VALUES (25, 'New Row')

Go back to the first connection, and reexecute the SELECT:
SELECT * FROM ISOLATION_TEST
WHERE col1 BETWEEN 20 AND 40

The second time you execute the same statement, the new row appears. Because the row doesn't even exist the first time you run the SELECT statement, it isn't locked. This new row that appears is called a phantom. You can prevent phantoms with the fourth isolation level.

Serializable
The Serializable isolation level ensures that if a query is reissued, no data will have changed and no new rows will appear in the interim. In other words, you won't see phantoms if the same query is issued twice within a transaction. Rerun the example from the Repeatable Reads section, inserting a row with a col1 value of 35. But this time, set your isolation level to SERIALIZABLE. The second connection will block when you try to do the INSERT, and the first connection will read exactly the same rows each time.

You pay a price to prevent phantoms. In addition to locking all the data you have read, enforcing the Serializable isolation level requires that SQL Server also lock data that doesn't exist! The Serializable level gets its name from the fact that running multiple serializable transactions at the same time is the equivalent of running them one at a time—that is, serially—regardless of sequence.

Controlling the Isolation Level SQL Server's default isolation level is Committed Read, but as you've seen, you can override this setting within your application. The most straightforward way is by using the SET command:

SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED READ COMMITTED REPEATABLE
READ SERIALIZABLE]

Previous versions of SQL Server treated Repeatable Read and Serializable as synonymous. I thought the difference was that Repeatable Reads prevented UPDATE operations, and Serializable prevented INSERTs and DELETEs. But the difference is in what data is locked. Repeatable Read locks only the data that has been read. With Serializable, SQL Server has to guarantee complete serializability, so it locks ranges of data.

Tips to choose the appropriate data types in SQL 2000

SQL Server 2000 stores data in a special structure called data pages that are 8Kb (8192 bytes) in size. Some space on the data pages is used to store system information, which leaves 8060 bytes to store user's data. So, if the table's row size is 4040 bytes, then only one row will be placed on each data page. If you can decrease the row size to 4030 bytes, you can store two rows within a single page because two rows can be placed into data page. The lesser the space used, the smaller the table and index, and lesser the I/O SQL Server has to perform when reading data pages from disk. So, you should design your tables in such a way as to maximize the number of rows that can fit into one data page. To maximize the number of rows that can fit into one data page, you should specify the narrowest columns you can. The narrower the columns are, the lesser the data that is stored, and the faster SQL Server is able to read and write data.

Try to use the following tips when choose the data types:

If you need to store integer data from 0 through 255, use tinyint data type.
The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store the columns with smallint, int and bigint data types accordingly. For example, if you design tables for a small company with 5-7 departments, you can create the departments table with the DepartmentID tinyint column to store the unique number of each department.

If you need to store integer data from -32,768 through 32,767, use smallint data type.
The columns with smallint data type use only two bytes to store their values, in comparison with four bytes and eight bytes used to store the columns with int and bigint data types accordingly. For example, if you design tables for a company with several hundred employees, you can create an employee table with the EmployeeID smallint column to store the unique number of each employee.

If you need to store integer data from -2,147,483,648 through 2,147,483,647, use int data type.
The columns with int data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with bigint data types. For example, to design tables for a library with more than 32,767 books, create a books table
with a BookID int column to store the unique number of each book.

Use smallmoney data type instead of money data type, if you need to store monetary data values from 214,748.3648 through 214,748.3647.
The columns with smallmoney data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with money data types. For example, if you need to store the monthly employee payments, it might be possible to use a column with the smallmoney data type instead of money data type.

Use smalldatetime data type instead of datetime data type, if you need
to store the date and time data from January 1, 1900 through June 6, 2079,
with accuracy to the minute.
The columns with smalldatetime data type use only four bytes to store their values, in comparison with eight bytes used to store the columns with datetime data types. For example, if you need to store the employee's hire date, you can use column with the smalldatetime data type instead of datetime data type.

Use varchar/nvarchar columns instead of text/ntext columns whenever possible.
Because SQL Server stores text/ntext columns on the Text/Image pages
separately from the other data, stored on the Data pages, it can
take more time to get the text/ntext values.

Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.
The char/varchar value uses only one byte to store one character,the nchar/nvarchar value uses two bytes to store one character,so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

Indexed view

An indexed view allows indexes to be created on views, where the result set of the view is stored and indexed in the database.

Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be higher than the cost of maintaining a table index. If the underlying data is updated frequently, then the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.

Indexed views improve the performance of these types of queries:

• Joins and aggregations that process many rows.
• Join and aggregation operations that are frequently performed by many queries.
• Views can be used to partition data across multiple databases or instances of Microsoft® SQL Server™ 2000.
• Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view.