It is tempting to use a CommandBuilder object because it generates SQL statements and can save the developer time when coding a new application that uses DataSets. However, this shortcut can have a negative effect on performance. Because of concurrency restrictions, the Command Builder can generate highly inefficient SQL statements. For example, suppose you have a table called EMP, an 8-column table with simple employee records. A CommandBuilder would generate the following 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 = ?) )"
The end-user can often write much more efficient UPDATE and DELETE statements than those that the CommandBuilder generates. For example, a programmer who knows the underlying database schema and that the EMPNO column of the EMP table is the primary key for the table, can code the same UPDATE statement as follows:
UPDATE EMP SET EMPNO = ?, ENAME = ?, JOB = ?, MGR = ?,
HIREDATE = ?, SAL = ?, COMM = ?, DEPT = ? WHERE EMPNO = ?
This statement will run much more efficiently on the database server than the statement generated by the CommandBuilder.
Another drawback is also implicit in the design of the CommandBuilder object. The CommandBuilder must generate statements at runtime. Each time a DataAdapter.Update method is called, the CommandBuilder must analyze the contents of the result set and generate UPDATE, INSERT, and DELETE statements for the DataAdapter. When the programmer explicitly specifies the UPDATE, INSERT, and DELETE statements for the DataAdapter, this extra processing time is avoided.
