RE: Derby Char Column Type Versus VarChar
Hello, Often I have faced that, when I use the derby database more (do lot of transactions for couple of months), Later it has been observed that database Response time becomes slower. What I did now is that I recreated the database freshly and imported the data back again. CREATE TABLE TIDLRREP ( REPOSITORY_KEY CHAR(10) NOT NULL, TRANSLATION_SOURCE CHAR(10) NOT NULL, SOURCE_TYPE CHAR(10) NOT NULL, SOURCE_SUB_TYPE CHAR(10) NOT NULL, PANEL_ID_7 CHAR(7) NOT NULL, TABLE_KEY CHAR(100) NOT NULL, OCCURRENCE CHAR(9) NOT NULL, BASE_LANGUAGE_TERM VARCHAR(4000) NOT NULL, ELEM_NBR CHAR(8) NOT NULL, TRANSLATED_TERM_79 VARCHAR(4000) NOT NULL, TRANS_TERM_SIZE INTEGER NOT NULL, MAX_TERM_SIZE INTEGER DEFAULT 0 NOT NULL, UPDATE_BY CHAR(8) NOT NULL, UPDATE_DATE CHAR(8) NOT NULL, VERIFICATION_DATE CHAR(8) NOT NULL, VERIFICATION_BY CHAR(8) NOT NULL, REFRESHED_DATE CHAR(8) NOT NULL, EXPORT_DATE CHAR(8) NOT NULL, REPOSITORY_STATUS CHAR(2) NOT NULL, STATUS_DATE CHAR(8) NOT NULL, STATUS_TIME CHAR(5) NOT NULL, LOCKED_REPOSITORY CHAR(1) NOT NULL, MULTIPLE_ROW_ENTRY CHAR(1) NOT NULL, REP_SYSTEM_DATA CHAR(1) NOT NULL, ALERT_GROUP_NAME CHAR(10) NOT NULL, FILE_NAME CHAR(50) NOT NULL, RESERVED_IND CHAR(1) NOT NULL, APPLICATION_VER CHAR(8) NOT NULL, MID_NUMBER CHAR(19) NOT NULL, LITERAL_ELEMENT_ID CHAR(8) NOT NULL, TIME_STAMP CHAR(26) NOT NULL); Auto commit is on... there is no control from code perspective. Import into Derby is through normal insert statement not batch import. I am currently using db-derby-10.5.1.1 version. Below are the Indexes I have on the table TIILRREP CREATE UNIQUE INDEX TIILRREP ON TIDLRREP (REPOSITORY_KEY); CREATE UNIQUE INDEX TIILRRE1 ON TIDLRREP (TRANSLATION_SOURCE, SOURCE_TYPE, SOURCE_SUB_TYPE, PANEL_ID_7, TABLE_KEY, OCCURRENCE, BASE_LANGUAGE_TERM, ELEM_NBR); CREATE INDEX TIILRRE2 ON TIDLRREP (BASE_LANGUAGE_TERM, ELEM_NBR); CREATE INDEX TIILRRE3 ON TIDLRREP (SOURCE_TYPE, REPOSITORY_STATUS); CREATE INDEX TIILRRE4 ON TIDLRREP (REPOSITORY_STATUS, MULTIPLE_ROW_ENTRY, SOURCE_TYPE, BASE_LANGUAGE_TERM); CREATE INDEX TIILRRE5 ON TIDLRREP (TRANSLATION_SOURCE, SOURCE_TYPE, REPOSITORY_STATUS, OCCURRENCE, TABLE_KEY); CREATE INDEX TIILRRE6 ON TIDLRREP (REPOSITORY_STATUS, LOCKED_REPOSITORY, BASE_LANGUAGE_TERM); My question is any reason why the database responding (select /inserts) becomes slower as the database usage becomes more. Thanks Mamatha Mamatha-Original Message- From: knut.hat...@sun.com [mailto:knut.hat...@sun.com] Sent: Tuesday, April 06, 2010 4:59 PM To: derby-user@db.apache.org Subject: Re: Derby Char Column Type Versus VarChar On 04/ 6/10 05:45 AM, Mamatha Kodigehalli Venkatesh wrote: Hello, Below was my table structure in Derby CREATE TABLE TIDLRREP ( REPOSITORY_KEY CHAR(10) NOT NULL, TRANSLATION_SOURCE CHAR(10) NOT NULL, SOURCE_TYPE CHAR(10) NOT NULL, SOURCE_SUB_TYPE CHAR(10) NOT NULL, PANEL_ID_7 CHAR(7) NOT NULL, TABLE_KEY CHAR(100) NOT NULL, OCCURRENCE CHAR(9) NOT NULL, BASE_LANGUAGE_TERM CHAR(79) NOT NULL, ELEM_NBR CHAR(8) NOT NULL, TRANSLATED_TERM_79 CHAR(79) NOT NULL, TRANS_TERM_SIZE INTEGER NOT NULL, MAX_TERM_SIZE INTEGER DEFAULT 0 NOT NULL, UPDATE_BY CHAR(8) NOT NULL, UPDATE_DATE CHAR(8) NOT NULL, VERIFICATION_DATE CHAR(8) NOT NULL, VERIFICATION_BY CHAR(8) NOT NULL, REFRESHED_DATE CHAR(8) NOT NULL, EXPORT_DATE CHAR(8) NOT NULL, REPOSITORY_STATUS CHAR(2) NOT NULL, STATUS_DATE CHAR(8) NOT NULL, STATUS_TIME CHAR(5) NOT NULL, LOCKED_REPOSITORY CHAR(1) NOT NULL, MULTIPLE_ROW_ENTRY CHAR(1) NOT NULL, REP_SYSTEM_DATA CHAR(1) NOT NULL, ALERT_GROUP_NAME CHAR(10) NOT NULL, FILE_NAME CHAR(50) NOT NULL, RESERVED_IND CHAR(1) NOT NULL, APPLICATION_VER CHAR(8) NOT NULL, MID_NUMBER CHAR(19) NOT NULL, LITERAL_ELEMENT_ID CHAR(8) NOT NULL, TIME_STAMP CHAR(26) NOT NULL); Now it has been changed to reflect CREATE TABLE TIDLRREP ( REPOSITORY_KEY CHAR(10) NOT NULL, TRANSLATION_SOURCE CHAR(10) NOT NULL, SOURCE_TYPE CHAR(10) NOT NULL, SOURCE_SUB_TYPE CHAR(10) NOT NULL, PANEL_ID_7 CHAR(7) NOT NULL, TABLE_KEY CHAR(100) NOT NULL, OCCURRENCE CHAR(9) NOT NULL, BASE_LANGUAGE_TERM VARCHAR(4000) NOT NULL, ELEM_NBR CHAR(8) NOT NULL, TRANSLATED_TERM_79 VARCHAR(4000) NOT NULL, TRANS_TERM_SIZE INTEGER NOT NULL, MAX_TERM_SIZE INTEGER DEFAULT 0 NOT NULL, UPDATE_BY CHAR(8) NOT NULL, UPDATE_DATE CHAR(8) NOT NULL, VERIFICATION_DATE CHAR(8) NOT NULL, VERIFICATION_BY CHAR(8) NOT NULL, REFRESHED_DATE CHAR(8) NOT NULL, EXPORT_DATE CHAR(8) NOT NULL, REPOSITORY_STATUS CHAR(2) NOT NULL, STATUS_DATE CHAR(8) NOT NULL, STATUS_TIME CHAR(5) NOT NULL, LOCKED_REPOSITORY CHAR(1) NOT NULL, MULTIPLE_ROW_ENTRY CHAR(1) NOT NULL, REP_SYSTEM_DATA CHAR(1) NOT NULL, ALERT_GROUP_NAME CHAR(10) NOT NULL, FILE_NAME CHAR(50) NOT NULL, RESERVED_IND CHAR(1) NOT NULL, APPLICATION_VER CHAR(8) NOT NULL, MID_NUMBER CHAR(19) NOT NULL, LITERAL_ELEMENT_ID CHAR(8) NOT NULL, TIME_STAMP CHAR(26) NOT NULL); Inserts to this table *TIDLRREP* was much faster
Re: Derby Char Column Type Versus VarChar
My question is any reason why the database responding (select /inserts) becomes slower as the database usage becomes more. If you can observe the performance impact in a reliable and reproducible fashion, you could try: 1) Set up the fast-responding behavior 2) Capture runtime statistics from several well-performing statements 3) Set up the poor-performing behavior 4) Capture runtime statistics from the same statements Then we could compare the output and look at the differences to understand the performance degradation in more detail. thanks, bryan
Derby Char Column Type Versus VarChar
Hello, Please let us know what will be the performance impact when I use VarChar (4000) instead of Char. I have found that the inserts to the table which contains this change are very slow... But on oracle, there is not much of impact on this. Based on the business requirement I need to use VarChar. Any suggestions will be of great help. Thanks Mamatha
Re: Derby Char Column Type Versus VarChar
I think Oracle Database and Derby behavior will be same. CHAR(size) will preallocate size*characters in database page/block whereas VARCHAR(size) will not. Maybe if you give us any hint what are you trying to do we could help better. Peter On Mon, Apr 5, 2010 at 3:41 PM, Mamatha Kodigehalli Venkatesh mamatha.venkat...@ness.com wrote: Hello, Please let us know what will be the performance impact when I use VarChar (4000) instead of Char. I have found that the inserts to the table which contains this change are very slow… But on oracle, there is not much of impact on this. Based on the business requirement I need to use VarChar. Any suggestions will be of great help. Thanks Mamatha
RE: Derby Char Column Type Versus VarChar
Hello, Below was my table structure in Derby CREATE TABLE TIDLRREP ( REPOSITORY_KEY CHAR(10) NOT NULL, TRANSLATION_SOURCE CHAR(10) NOT NULL, SOURCE_TYPE CHAR(10) NOT NULL, SOURCE_SUB_TYPE CHAR(10) NOT NULL, PANEL_ID_7 CHAR(7) NOT NULL, TABLE_KEY CHAR(100) NOT NULL, OCCURRENCE CHAR(9) NOT NULL, BASE_LANGUAGE_TERM CHAR(79) NOT NULL, ELEM_NBR CHAR(8) NOT NULL, TRANSLATED_TERM_79 CHAR(79) NOT NULL, TRANS_TERM_SIZE INTEGER NOT NULL, MAX_TERM_SIZE INTEGER DEFAULT 0 NOT NULL, UPDATE_BY CHAR(8) NOT NULL, UPDATE_DATE CHAR(8) NOT NULL, VERIFICATION_DATE CHAR(8) NOT NULL, VERIFICATION_BY CHAR(8) NOT NULL, REFRESHED_DATE CHAR(8) NOT NULL, EXPORT_DATE CHAR(8) NOT NULL, REPOSITORY_STATUS CHAR(2) NOT NULL, STATUS_DATE CHAR(8) NOT NULL, STATUS_TIME CHAR(5) NOT NULL, LOCKED_REPOSITORY CHAR(1) NOT NULL, MULTIPLE_ROW_ENTRY CHAR(1) NOT NULL, REP_SYSTEM_DATA CHAR(1) NOT NULL, ALERT_GROUP_NAME CHAR(10) NOT NULL, FILE_NAME CHAR(50) NOT NULL, RESERVED_IND CHAR(1) NOT NULL, APPLICATION_VER CHAR(8) NOT NULL, MID_NUMBER CHAR(19) NOT NULL, LITERAL_ELEMENT_ID CHAR(8) NOT NULL, TIME_STAMP CHAR(26) NOT NULL); Now it has been changed to reflect CREATE TABLE TIDLRREP ( REPOSITORY_KEY CHAR(10) NOT NULL, TRANSLATION_SOURCE CHAR(10) NOT NULL, SOURCE_TYPE CHAR(10) NOT NULL, SOURCE_SUB_TYPE CHAR(10) NOT NULL, PANEL_ID_7 CHAR(7) NOT NULL, TABLE_KEY CHAR(100) NOT NULL, OCCURRENCE CHAR(9) NOT NULL, BASE_LANGUAGE_TERM VARCHAR(4000) NOT NULL, ELEM_NBR CHAR(8) NOT NULL, TRANSLATED_TERM_79 VARCHAR(4000) NOT NULL, TRANS_TERM_SIZE INTEGER NOT NULL, MAX_TERM_SIZE INTEGER DEFAULT 0 NOT NULL, UPDATE_BY CHAR(8) NOT NULL, UPDATE_DATE CHAR(8) NOT NULL, VERIFICATION_DATE CHAR(8) NOT NULL, VERIFICATION_BY CHAR(8) NOT NULL, REFRESHED_DATE CHAR(8) NOT NULL, EXPORT_DATE CHAR(8) NOT NULL, REPOSITORY_STATUS CHAR(2) NOT NULL, STATUS_DATE CHAR(8) NOT NULL, STATUS_TIME CHAR(5) NOT NULL, LOCKED_REPOSITORY CHAR(1) NOT NULL, MULTIPLE_ROW_ENTRY CHAR(1) NOT NULL, REP_SYSTEM_DATA CHAR(1) NOT NULL, ALERT_GROUP_NAME CHAR(10) NOT NULL, FILE_NAME CHAR(50) NOT NULL, RESERVED_IND CHAR(1) NOT NULL, APPLICATION_VER CHAR(8) NOT NULL, MID_NUMBER CHAR(19) NOT NULL, LITERAL_ELEMENT_ID CHAR(8) NOT NULL, TIME_STAMP CHAR(26) NOT NULL); Inserts to this table TIDLRREP was much faster (2secs for 50 records) now with the table changes the imports are very slow it is taking 52 secs for 50 records. The same changes on oracle DB is not impacting the insert time for the same set of 50 records. TIDLRREP contains around 180,000 records. Please let me know if you need any other details. Thanks Mamatha -Original Message- From: Peter Ondruška [mailto:peter.ondru...@gmail.com] Sent: Monday, April 05, 2010 7:37 PM To: Derby Discussion Subject: Re: Derby Char Column Type Versus VarChar I think Oracle Database and Derby behavior will be same. CHAR(size) will preallocate size*characters in database page/block whereas VARCHAR(size) will not. Maybe if you give us any hint what are you trying to do we could help better. Peter On Mon, Apr 5, 2010 at 3:41 PM, Mamatha Kodigehalli Venkatesh mamatha.venkat...@ness.com wrote: Hello, Please let us know what will be the performance impact when I use VarChar (4000) instead of Char. I have found that the inserts to the table which contains this change are very slow... But on oracle, there is not much of impact on this. Based on the business requirement I need to use VarChar. Any suggestions will be of great help. Thanks Mamatha
