Databases Reference
In-Depth Information
Performance Tip
Although using a
CommandBuilder
object to generate SQL statements is
tempting because it can save you time when coding an application that
uses
DataSets
, this shortcut can impair performance.
Suppose you have an 8-column database table named employees that con-
tains employee records. A
CommandBuilder
object would generate the following
Update
statement:
"UPDATE employees 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 = ?))
If you know the underlying database schema and know that the
empno
col-
umn of the employees table is the primary key for the table, you can code the fol-
lowing
Update
statement, which is more efficient than the previous
Update
statement generated by the
CommandBuilder
:
UPDATE employees SET empno = ?, ename = ?, job = ?,
mgr = ?, hiredate = ?, sal = ?, comm = ?, dept = ?
WHERE empno = ?
In this example, although we've lost some concurrency control, we've
improved performance. Notice that in the first example, the
Where
clause com-
pares every column value, which ensures the value hasn't changed since the data
was retrieved. In the second example, the
Update
statement compares only the
value of the
empno
column. So before you implement this performance tip, you
must decide how tolerant your database is to a lower level of concurrency.
Which ADO.NET object should you use to retrieve the results of a SQL state-
ment?
