You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Mamatha Kodigehalli Venkatesh <Ma...@ness.com> on 2010/04/05 15:41:00 UTC

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

Posted by Bryan Pendleton <bp...@gmail.com>.
 > 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

RE: Derby Char Column Type Versus VarChar

Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
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

Re: Derby Char Column Type Versus VarChar

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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

RE: Derby Char Column Type Versus VarChar

Posted by Mamatha Kodigehalli Venkatesh <Ma...@ness.com>.
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.ondruska@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

<Ma...@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

Posted by Peter Ondruška <pe...@gmail.com>.
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
<Ma...@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
>
>
>
>