StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Two SQL statements executed for each row added?

Two SQL statements executed for each row added?

Keith Chisarik — Mon, 10 Dec 2007 09:39:54 GMT

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

RE: Two SQL statements executed for each row added?

Keith Chisarik — Mon, 10 Dec 2007 09:39:54 GMT

OK thanks Ben.

RE: Two SQL statements executed for each row added?

StrataFrame Team — Mon, 10 Dec 2007 08:58:43 GMT

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.

RE: Two SQL statements executed for each row added?

Keith Chisarik — Fri, 07 Dec 2007 16:13:40 GMT

Above link didn't copy correctly.

[b]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)[/b]

RE: Two SQL statements executed for each row added?

Keith Chisarik — Fri, 07 Dec 2007 16:12:05 GMT

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