db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamatha Kodigehalli Venkatesh" <Mamatha.Venkat...@ness.com>
Subject RE: Derby Char Column Type Versus VarChar
Date Tue, 06 Apr 2010 13:16:46 GMT
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.Hatlen@Sun.COM [mailto:Knut.Hatlen@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 (2secs for 50
> records) now with the table changes the imports are very slow it is
> taking 52 secs for 50 records.
>

Hi Mamatha,

This does indeed sound unreasonably slow. If you have scripts that
others could run to reproduce this, it would be great if you could file
a JIRA issue at https://issues.apache.org/jira/browse/DERBY and attach
them there so that we can investigate the problem.

I noticed that you changed the column size (from 79 to 4000) as well as
the data type. Do you also see this performance degradation if you use
VARCHAR(79) instead of VARCHAR(4000)? Increasing the column size may
change the size of the pages used to store the table and the indexes,
which may affect the performance (although not as much as what you see).

Also, are there any indexes or constraints defined on the columns
BASE_LANGUAGE_TERM and TRANSLATED_TERM_79?

-- 
Knut Anders


Mime
View raw message