I am debugging some performance issues in my app (DB2 datasource), I was surprised to find that:



1) it is executing an insert command for each row instead of a single INSERT statement

2) why the SELECT after each INSERT?



Is this right, doesn't look right to me. This is a single SAVE() call against my BO after ADD()ing records in a looping structure.



Thanks for taking a look... debug info below, a pair of these exists for each row added.



Command #:    171

Timestamp:    2007-12-07 16:14:23.606

General Command Settings

Command Type:    Text

Object Type:    DDTek.DB2.DB2Command

Connection String:    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Transaction:    False

Command Settings

CommandText:    INSERT INTO "SIGMA"."EDI_MILNDX" ("MIL_CODEID", "MIL_NAME", "MIL_NUM", "MIL_RV") VALUES (?, ?, ?, ?)

Command Parameters

P0:    'SHWX' [DbType: String | Size: 4 | Direction: Input | SourceColumn: MIL_CODEID | SourceVersion: Current]

P1:    'Shaw Industries' [DbType: String | Size: 15 | Direction: Input | SourceColumn: MIL_NAME | SourceVersion: Current]

P2:    '0' [DbType: Decimal | Size: 0 | Direction: Input | SourceColumn: MIL_NUM | SourceVersion: Current]

P3:    '0' [DbType: Int32 | Size: 0 | Direction: Input | SourceColumn: MIL_RV | SourceVersion: Current]





Command #:    172

Timestamp:    2007-12-07 16:14:23.668

General Command Settings

Command Type:    Text

Object Type:    DDTek.DB2.DB2Command

Connection String:    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Transaction:    False

Command Settings

CommandText:    SELECT "MIL_PK", "MIL_RV" FROM "SIGMA"."EDI_MILNDX" WHERE MIL_PK = IDENTITY_VAL_LOCAL()

Command Parameters

N/A    No parameters are defined

Keith Chisarik

Seems an INSERT for each row is the "recommended" way on SQL Server, though many are reporting improved performance with a single INSERT and UNION ALL, but I digress.

A quick Google (and my DB2 resource agrees) says this is how to insert multiple records in DB2 for better performance.

How might I accomplish this?

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyinsert.htm

Keith Chisarik

Above link didn't copy correctly.



You can also insert multiple rows into a table using the VALUES clause. The following example inserts two rows into the PROJECT table. Values for the Project number (PROJNO) , Project name (PROJNAME), Department number (DEPTNO), and Responsible employee (RESPEMP) are given in the values list. The value for the Project start date (PRSTDATE) uses the current date. The rest of the columns in the table that are not listed in the column list are assigned their default value.



INSERT INTO PROJECT (PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTDATE)

VALUES('HG0023', 'NEW NETWORK', 'E11', '200280', CURRENT DATE),

('HG0024', 'NETWORK PGM', ''E11", '200310', CURRENT DATE)

Keith Chisarik

The SELECT after the INSERT is to retrieve the auto-assigned primary key and row version column values from the database.  It is inefficient to use 2 queries, but unfortunately, the DB2 adapter that I was using did not support executing more than one text command at within the same querry and I could not find documentation that said that it was supported (where you can put 2 queries back to back in SQL Server with a ";").  You can, however, use stored procedures for your INSERTs to accomplish the same thing... one query to insert the record rather than 2.  As for inserting more than one record at a time, we would not be able to retrieve the assigned primary keys for the records.

OK thanks Ben.

Keith Chisarik