Poor insert performance

[nomorems]

I was trying to copy a range of records from a table to the same table
with a small modification. I was wondering why this takes a long time
compared to just doing a subselect from that table and insert to a new
table (without constraints). Could it be a problem with the constraints
checking capability or the index maintenance? The subselect itself
takes a small time, so that will not be the problem...

Before executing the query I tried to set the constraints checking off
by the following statements; but it doesn't work as I expected:

SET INTEGRITY FOR DB2INST1.ASSPC OFF
SET INTEGRITY FOR DB2INST1.ASSPCACT OFF
SET INTEGRITY FOR DB2INST1.ASSPCACTSIBLING OFF

Any suggestions are welcome...

Regards, Peter

TABLE:
======

CREATE TABLE "DB2INST1"."ASSPC" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1
INCREMENT BY +1 MINVALUE +1 MAXVALUE [phone] NO CYCLE NO CACHE
NO ORDER ),
"VERSIONID" INTEGER NOT NULL WITH DEFAULT 1,
"ASSVS_ID" INTEGER NOT NULL,
"ASSVS_VERSIONID" INTEGER NOT NULL,
"VS_PC_ID" INTEGER NOT NULL,
"VS_PC_VERSIONID" INTEGER NOT NULL,
"CREATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
"DELETE" TIMESTAMP,
"UPDATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
"MULTIFAC" DECIMAL(4,1)
) IN "USERSPACE1" ;
PRIMARY KEY:
============

ALTER TABLE "DB2INST1"."ASSPC"
ADD CONSTRAINT "CC1084276791058" PRIMARY KEY ("ID", "VERSIONID");

FOREIGN KEYS:
=============

ALTER TABLE "DB2INST1"."ASSPC"
ADD CONSTRAINT "CC1084276820350" FOREIGN KEY ("ASSVS_ID",
"ASSVS_VERSIONID")
REFERENCES "DB2INST1"."ASSVS" ("ID", "VERSIONID")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE "DB2INST1"."ASSPC"
ADD CONSTRAINT "CC1084276849722" FOREIGN KEY ("VS_PC_ID",
"VS_PC_VERSIONID")
REFERENCES "DB2INST1"."VS_PC" ("ID", "VERSIONID")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT ENFORCED
ENABLE QUERY OPTIMIZATION;
Query:
======

INSERT INTO
DB2INST1.ASSPC
(
ID,
VERSIONID,
ASSVS_ID,
ASSVS_VERSIONID,
VS_PC_ID,
VS_PC_VERSIONID,
MULTIFAC
)
(
SELECT
DB2INST1.ASSPC.ID,
2,
DB2INST1.ASSVS.ID AS ASS_VS_ID,
DB2INST1.ASSVS.VERSIONID AS ASS_VS_VERSIONID,
DB2INST1.VS_PC.ID AS VS_PC_ID,
DB2INST1.VS_PC.VERSIONID AS VS_PC_VERSIONID,
DB2INST1.ASSPC.MULTIFAC
FROM
DB2INST1.ORG,
DB2INST1.ORG_VS,
DB2INST1.VS,
DB2INST1.DR,
DB2INST1.VS_PC,
DB2INST1.ACTIVITY,
DB2INST1.ASSPC,
DB2INST1.ASSVS
WHERE
DB2INST1.ORG_VS.ORG_ID = DB2INST1.ORG.ID
AND DB2INST1.ORG_VS.ORG_VERSIONID = DB2INST1.ORG.VERSIONID
AND DB2INST1.VS.DR_ID = DB2INST1.DR.ID
AND DB2INST1.VS.DR_VERSIONID = DB2INST1.DR.VERSIONID
AND DB2INST1.ORG_VS.VS_ID = DB2INST1.VS.ID
AND DB2INST1.ORG_VS.VS_VERSIONID = DB2INST1.VS.VERSIONID
AND DB2INST1.VS_PC.VS_ID = DB2INST1.VS.ID
AND DB2INST1.VS_PC.VS_VERSIONID = DB2INST1.VS.VERSIONID
AND DB2INST1.ASSPC.VS_PC_ID = DB2INST1.VS_PC.ID
AND DB2INST1.ASSPC.VS_PC_VERSIONID = DB2INST1.VS_PC.VERSIONID
AND DB2INST1.ASSVS.ORG_VS_ID = DB2INST1.ORG_VS.ID
AND DB2INST1.ASSVS.ORG_VS_VERSIONID = DB2INST1.ORG_VS.VERSIONID
AND DB2INST1.ASSPC.ASSVS_ID = DB2INST1.ASSVS.ID
AND DB2INST1.ASSPC.ASSVS_VERSIONID = DB2INST1.ASSVS.VERSIONID
AND DB2INST1.ORG.DELETE IS NULL
AND DB2INST1.ORG_VS.DELETE IS NULL
AND DB2INST1.VS.DELETE IS NULL
AND DB2INST1.DR.DELETE IS NULL
AND DB2INST1.VS_PC.DELETE IS NULL
AND DB2INST1.ACTIVITY.DELETE IS NULL
AND DB2INST1.ASSPC.DELETE IS NULL
AND DB2INST1.ASSVS.DELETE IS NULL

AND DB2INST1.ASSPC.VERSIONID = 1
AND DB2INST1.ORG.ID = 1
AND DB2INST1.ORG.VERSIONID = 1
)

[Serge Rielau]

nomorems wrote:

I was trying to copy a range of records from a table to the same table
with a small modification. I was wondering why this takes a long time
compared to just doing a subselect from that table and insert to a new
table (without constraints). Could it be a problem with the constraints
checking capability or the index maintenance? The subselect itself
takes a small time, so that will not be the problem...

Before executing the query I tried to set the constraints checking off
by the following statements; but it doesn't work as I expected:

SET INTEGRITY FOR DB2INST1.ASSPC OFF
SET INTEGRITY FOR DB2INST1.ASSPCACT OFF
SET INTEGRITY FOR DB2INST1.ASSPCACTSIBLING OFF

Any suggestions are welcome...

Regards, Peter

TABLE:
======

CREATE TABLE "DB2INST1"."ASSPC" (
"ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1
INCREMENT BY +1 MINVALUE +1 MAXVALUE [phone] NO CYCLE NO CACHE
NO ORDER ),
"VERSIONID" INTEGER NOT NULL WITH DEFAULT 1,
"ASSVS_ID" INTEGER NOT NULL,
"ASSVS_VERSIONID" INTEGER NOT NULL,
"VS_PC_ID" INTEGER NOT NULL,
"VS_PC_VERSIONID" INTEGER NOT NULL,
"CREATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
"DELETE" TIMESTAMP,
"UPDATE" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
"MULTIFAC" DECIMAL(4,1)
) IN "USERSPACE1" ;
PRIMARY KEY:
============

ALTER TABLE "DB2INST1"."ASSPC"
ADD CONSTRAINT "CC1084276791058" PRIMARY KEY ("ID", "VERSIONID");

FOREIGN KEYS:
=============

ALTER TABLE "DB2INST1"."ASSPC"
ADD CONSTRAINT "CC1084276820350" FOREIGN KEY ("ASSVS_ID",
"ASSVS_VERSIONID")
REFERENCES "DB2INST1"."ASSVS" ("ID", "VERSIONID")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT ENFORCED
ENABLE QUERY OPTIMIZATION;

ALTER TABLE "DB2INST1"."ASSPC"
ADD CONSTRAINT "CC1084276849722" FOREIGN KEY ("VS_PC_ID",
"VS_PC_VERSIONID")
REFERENCES "DB2INST1"."VS_PC" ("ID", "VERSIONID")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT ENFORCED
ENABLE QUERY OPTIMIZATION;
Query:
======

INSERT INTO
DB2INST1.ASSPC
(
ID,
VERSIONID,
ASSVS_ID,
ASSVS_VERSIONID,
VS_PC_ID,
VS_PC_VERSIONID,
MULTIFAC
)
(
SELECT
DB2INST1.ASSPC.ID,
2,
DB2INST1.ASSVS.ID AS ASS_VS_ID,
DB2INST1.ASSVS.VERSIONID AS ASS_VS_VERSIONID,
DB2INST1.VS_PC.ID AS VS_PC_ID,
DB2INST1.VS_PC.VERSIONID AS VS_PC_VERSIONID,
DB2INST1.ASSPC.MULTIFAC
FROM
DB2INST1.ORG,
DB2INST1.ORG_VS,
DB2INST1.VS,
DB2INST1.DR,
DB2INST1.VS_PC,
DB2INST1.ACTIVITY,
DB2INST1.ASSPC,
DB2INST1.ASSVS
WHERE
DB2INST1.ORG_VS.ORG_ID = DB2INST1.ORG.ID
AND DB2INST1.ORG_VS.ORG_VERSIONID = DB2INST1.ORG.VERSIONID
AND DB2INST1.VS.DR_ID = DB2INST1.DR.ID
AND DB2INST1.VS.DR_VERSIONID = DB2INST1.DR.VERSIONID
AND DB2INST1.ORG_VS.VS_ID = DB2INST1.VS.ID
AND DB2INST1.ORG_VS.VS_VERSIONID = DB2INST1.VS.VERSIONID
AND DB2INST1.VS_PC.VS_ID = DB2INST1.VS.ID
AND DB2INST1.VS_PC.VS_VERSIONID = DB2INST1.VS.VERSIONID
AND DB2INST1.ASSPC.VS_PC_ID = DB2INST1.VS_PC.ID
AND DB2INST1.ASSPC.VS_PC_VERSIONID = DB2INST1.VS_PC.VERSIONID
AND DB2INST1.ASSVS.ORG_VS_ID = DB2INST1.ORG_VS.ID
AND DB2INST1.ASSVS.ORG_VS_VERSIONID = DB2INST1.ORG_VS.VERSIONID
AND DB2INST1.ASSPC.ASSVS_ID = DB2INST1.ASSVS.ID
AND DB2INST1.ASSPC.ASSVS_VERSIONID = DB2INST1.ASSVS.VERSIONID
AND DB2INST1.ORG.DELETE IS NULL
AND DB2INST1.ORG_VS.DELETE IS NULL
AND DB2INST1.VS.DELETE IS NULL
AND DB2INST1.DR.DELETE IS NULL
AND DB2INST1.VS_PC.DELETE IS NULL
AND DB2INST1.ACTIVITY.DELETE IS NULL
AND DB2INST1.ASSPC.DELETE IS NULL
AND DB2INST1.ASSVS.DELETE IS NULL

AND DB2INST1.ASSPC.VERSIONID = 1
AND DB2INST1.ORG.ID = 1
AND DB2INST1.ORG.VERSIONID = 1
)

SET INTEGRITY OFF pretty much takes the table out of the game. You will
not be able to operate DML (such as INSERT or SELECT) on the table.
Altering the constraints to NOT ENFORCED will do the trick.
After the INSERT you can then SET INTEGRITY OFF,
ALTER the constraints back to ENFORCED,
and then validate them in one shot by flipped SET INTEGRITY back on
either using the options of your choice.
If you don't use SET INTEGRITY OFF each ALTER TABLE statement willchech
the modified constraint right away, potentially resulting in more scans
=> longer time to finish.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab