 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / February 2006Identity Column slow down the insert
I have 2 same windows machine, same instance configure and Database , all run DB2 UDB V8.1.5 Test 1 : create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)); insert into out_1 (line) values ('C000000002XYTNF102020201855000000075000519600040547000003256510 0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726 00041020206900381468 00000000000'); On machine A, db2batch give the reslut of insert 0.066 second On machine B, db2batch give the result of insert 0.001 second If no Identity column Test ddl_2 : create table OUT_1 (LINE VARCHAR(350)); insert into out_1 (line) values ('C000000002XYTNF102020201855000000075000519600040547000003256510 0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726 00041020206900381468 00000000000'); 2 Machine give the same result of insert 0.001 second Test ddl_3 : create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 200)); CARert into out_1 (line) values ('C000000002XYTNF102020201855000000075000519600040547000003256510 0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726 00041020206900381468 00000000000'); 2 Machine give the same reulst of insert 0.001 second My question is what is the secret of GENERATED NO CACHE, why takes 50 times longer? Thanks > I have 2 same windows machine, same instance configure and Database , > all run DB2 UDB V8.1.5 [quoted text clipped - 37 lines] > times longer? > Thanks Evreey time DB2 has to load a cache (which with NOCACHE means always) DB2 needs to update the LASTASSIGNVAL column in SYSIBM.SYSSEQUENCES. Also DB2 needs to write a log record for forward logging. You should have very special circumstances that require the use of NOCACHE... what are they? Cheers Serge  SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab It is a temp table used in one job (create when job start and drop after finish). This job run fine in machine B and machine A for years, but very slow in machine A suddenly. I get this "NO CACHE" DDL from dynamic sql snapshot, found the insert is the most time comsuing sql. > It is a temp table used in one job (create when job start and drop > after finish). > This job run fine in machine B and machine A for years, but very slow > in machine A suddenly. > I get this "NO CACHE" DDL from dynamic sql snapshot, found the insert > is the most time comsuing sql. If it's a temp table there is really no reason for NOCACHE. Also could it be the temp table is declared as LOGGED? Did someone change the USER TEMPORARY TABLESPACE (SMS vs DMS), hot tablespace, ... Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab It is not GLOBAL TEMP table defined by "DECLARE GLOBAL TEMPORARY TABLE", it is a temp REGULAR table created within a job by "create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE));", use regular tablespace. I could not find the reason the insert slow down suddenly. > It is not GLOBAL TEMP table defined by "DECLARE GLOBAL TEMPORARY > TABLE", it is a temp REGULAR table created within a job by "create > table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL > GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO > CACHE));", use regular tablespace. > I could not find the reason the insert slow down suddenly. How did you arrive at this statement? Purely by elapsed time? Take a look at the basics. Do you have an I/O bottleneck? Is it limitted to this tablespace? ... If I were you I'd remove the NOCACHE (on V8.2 it's just an ALTER TABLE ALTER COLUMN statement, online) That will exclude the idnetity column as culprit right there. Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab I get this statement from the dynamic sql snapshot, found this insert has the longest execution time. And make the sript above accordingly, benchmarked using db2batch and proved the "No Cache" identity slow down the insert. Have told the developer to optimize the code to use cache 500, but why slow down suddenly for this "No Cache" insert but other insert with "Cache" is still OK. If there are bottleneck on this tbs, how come it doesnot affect other insert. The "No Cache" identity insert take 50 times more than general insert. > I get this statement from the dynamic sql snapshot, found this insert > has the longest execution time. [quoted text clipped - 3 lines] > slow down suddenly for this "No Cache" insert but other insert with > "Cache" is still OK. You mean "NOCACHE" right? > If there are bottleneck on this tbs, how come it doesnot affect other > insert. The "No Cache" identity insert take 50 times more than general > insert. Other insert against the same table? When is thsi other insert run? Eg. you could run into a concurrency issue. Possibly you're waiting on a lock...? There are many possibilities, but to understand requires more than simply staring at teh individual statement. Cheers Serge SignatureSerge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab > I get this statement from the dynamic sql snapshot, found this insert > has the longest execution time. [quoted text clipped - 3 lines] > slow down suddenly for this "No Cache" insert but other insert with > "Cache" is still OK. Serge answered that already. Adding the NOCACHE option to an identity means that every single insert into the table will ALSO require a write to SYSIBM.SYSSEQUENCES and the corresponding logging. When DB2 caches the identity column values, they are held in memory; thus you only incur the overhead of writing to SYSIBM.SYSSEQUENCES every N inserts (where N = the size of the cache). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.