db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Derby Char Column Type Versus VarChar
Date Tue, 06 Apr 2010 11:28:47 GMT
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