|
5 Surefire ADO.NET Performance Tips
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 of your ADO.NET applications.
by Jonathan Bruce
February 21, 2006
Technology Toolbox: SQL Server, ADO.NET, Oracle
Imagine receiving this feedback the next time you run your application: System.YourCodeIsRunningTooSlowException.
Although they have yet to add such an exception, it probably wouldn’t be a bad idea for Microsoft to flag significant bottlenecks when you run your applications. This is most significant in critical systems, where performance and scalability often make or break the overall success and usability of the application. 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. Yes, it would be nice if your system displayed the aforementioned System.YourCodeIsRunningTooSlowException as you worked. 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 (see Sidebar).
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 provides 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.
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.
Back to top
|