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 Pavel Bortnovskiy <PB...@Jefferies.com> on 2011/01/05 18:04:32 UTC

Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?

Hello, all:

I am using Derby as an in-memory DB. While executing a complex query
(which contains a union and several left-joins), we discovered that this
query with a left join would take about 3.5 mins to execute and fetch the
data, whereas the same query with the left join replaced with the inner
join would take just 30 seconds.

What tools or API is available at our disposal to tune the in-memory
Derby, to see its execution plan, to try to figure out why it is taking
such an extraordinary time to fetch about 2,500-3000 records.

I also noticed that by running the same query over and over, the time to
execute and fetch seems to be slowly creeping up as well - it started with
2.5 mins, then grew to 3 mins and now it's almost 4 mins.

Any help would be greatly appreciated!

Regards,

Pavel.

Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.

RE: how to show all tables in current schema without using ij tool?

Posted by Sam Aoudi <ao...@hotmail.com>.

Thanks Matt and Emmanuel,

The Show table issue is resolved.

 However still working on delete issue.

Date: Thu, 6 Jan 2011 08:29:12 -0700
Subject: FW: how to show all tables in current schema without using ij tool?
From: Matt.Pouttu-Clarke@icrossing.com
To: aoudi_s@hotmail.com

Message body

Hi Sam,

This comment also addresses your questions I hope...

Cheers,

Matt

------ Forwarded Message

From: Matt Pouttu-Clarke <Ma...@icrossing.com>

Reply-To: Derby Discussion <de...@db.apache.org>

Date: Thu, 06 Jan 2011 08:17:46 -0700

To: Derby Discussion <de...@db.apache.org>

Cc: Sam Aoudi <ao...@hotmail.com>

Subject: Re: how to show all tables in current schema without using ij tool?

Hi Emmanuel,

For number 1:

            Connection conn = DriverManager.getConnection("jdbc:derby:<db name>");  

            ResultSet tables = conn.getMetaData().getTables(null, null, null, null);

            while(tables.next()) {

                System.out.println(tables.getString("TABLE_NAME"));

            }

            tables.close();

For number 2: I don’t know because I never use cascading DB constraints, as they don’t scale to any significant data sizes.  However if you need cascading constraints with more scalability, try using a O/R mapping framework such as Hibernate...

Cheers,

Matt

On 1/6/11 5:34 AM, "Emmanuel Cecchet" <ma...@frogthinker.org> wrote:

   Hi,

I am struggling with issues when it comes to Apache derby:

 1-  I haven't been able to get a list of Tables in a schema without using ij. Is this feasible?

 SELECT * FROM SYS.SYSTABLES 

2- I haven't been able to use "On Delete Cascade", any good examples are appreciated.

 CREATE TABLE a (pk INT NOT NULL CONSTRAINT a_pk PRIMARY KEY)

 CREATE TABLE b (x LONG VARCHAR, id INT CONSTRAINT b_fk REFERENCES a(pk) ON DELETE CASCADE)

 INSERT INTO a VALUES (1)

 INSERT INTO b VALUES ('hello', 1)

 SELECT * FROM a,b

 PK X ID  

 1 hello 1    DELETE FROM b

 SELECT * FROM a,b

 PK X ID    

 Hope this helps,

 Emmanuel

iCrossing Privileged and Confidential Information

This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.

------ End of Forwarded Message

iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.

Re: how to show all tables in current schema without using ij tool?

Posted by Matt Pouttu-Clarke <Ma...@icrossing.com>.

Hi Emmanuel,

For number 1:
            Connection conn = DriverManager.getConnection("jdbc:derby:<db
name>");  
            ResultSet tables = conn.getMetaData().getTables(null, null,
null, null);
            while(tables.next()) {
                System.out.println(tables.getString("TABLE_NAME"));
            }
            tables.close();

For number 2: I don¹t know because I never use cascading DB constraints, as
they don¹t scale to any significant data sizes.  However if you need
cascading constraints with more scalability, try using a O/R mapping
framework such as Hibernate...

Cheers,
Matt

On 1/6/11 5:34 AM, "Emmanuel Cecchet" <ma...@frogthinker.org> wrote:

>    Hi,
>  
>> I am struggling with issues when it comes to Apache derby:
>>  1-  I haven't been able to get a list of Tables in a schema without using
>> ij. Is this feasible?
>>  
>  SELECT * FROM SYS.SYSTABLES
>> 2- I haven't been able to use "On Delete Cascade", any good examples are
>> appreciated.
>>   
>  CREATE TABLE a (pk INT NOT NULL CONSTRAINT a_pk PRIMARY KEY)
>  CREATE TABLE b (x LONG VARCHAR, id INT CONSTRAINT b_fk REFERENCES a(pk) ON
> DELETE CASCADE)
>  INSERT INTO a VALUES (1)
>  INSERT INTO b VALUES ('hello', 1)
>  SELECT * FROM a,b
>    
>  PK X ID  
>  1 hello 1    DELETE FROM b
>  SELECT * FROM a,b
>    
>  PK X ID    
>  
>  Hope this helps,
>  Emmanuel
>  

iCrossing Privileged and Confidential Information
This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.

Re: how to show all tables in current schema without using ij tool?

Posted by Emmanuel Cecchet <ma...@frogthinker.org>.

Hi,
> I am struggling with issues when it comes to Apache derby:
> 1-  I haven't been able to get a list of Tables in a schema without 
> using ij. Is this feasible?
SELECT * FROM SYS.SYSTABLES
> 2- I haven't been able to use "On Delete Cascade", any good examples 
> are appreciated.
CREATE TABLE a (pk INT NOT NULL CONSTRAINT a_pk PRIMARY KEY)
CREATE TABLE b (x LONG VARCHAR, id INT CONSTRAINT b_fk REFERENCES a(pk) 
ON DELETE CASCADE)
INSERT INTO a VALUES (1)
INSERT INTO b VALUES ('hello', 1)
SELECT * FROM a,b
PK 	X 	ID
1 	hello 	1

DELETE FROM b
SELECT * FROM a,b
PK 	X 	ID



Hope this helps,
Emmanuel

-- 
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development&  Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet

how to show all tables in current schema without using ij tool?

Posted by Sam Aoudi <ao...@hotmail.com>.

Hi,


I am struggling with issues when it comes to Apache derby:
1-  I haven't been able to get a list of Tables in a schema without using ij. Is this feasible?
2- I haven't been able to use "On Delete Cascade", any good examples are appreciated.

Thanks

Re: Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?

Posted by Pavel Bortnovskiy <PB...@Jefferies.com>.

Hello, Witold:

thanks for your response. It might be the same issue and I will try to
follow the same suggestions that Bryan gave you.

FYI: I am using Derby 10.6.2.1

If any of the Derby folks can shed some additional light onto this, it
would be greatly appreciated,

Pavel.

From:
Witold Szczerba <pl...@gmail.com>
To:
Derby Discussion <de...@db.apache.org>
Date:
01/05/2011 01:37 PM
Subject:
Re: Left-Join extremely slow in comparison to Inner-Join. How to tune
Derby?

Hi there,
Look at this:
https://issues.apache.org/jira/browse/DERBY-4595
Do you think it might be the same case I had some time ago?
There were some changes in the code in Jun/2010, maybe you have older
version or the change did not resolved the problem...

The funny thing is that the Apache Derby guys changed the issue type
from bug to improvement - so, for them, a query which should last 2 or
3 seconds is executing like 1 hour, fetching entire database, is just
a 'feature' :/

Regards,
Witold Szczerba

Re: Left-Join extremely slow in comparison to Inner-Join. How to tune Derby?

Posted by Witold Szczerba <pl...@gmail.com>.

Hi there,
Look at this:
https://issues.apache.org/jira/browse/DERBY-4595
Do you think it might be the same case I had some time ago?
There were some changes in the code in Jun/2010, maybe you have older
version or the change did not resolved the problem...

The funny thing is that the Apache Derby guys changed the issue type
from bug to improvement - so, for them, a query which should last 2 or
3 seconds is executing like 1 hour, fetching entire database, is just
a 'feature' :/

Regards,
Witold Szczerba