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 Luis Ribeiro <lu...@gmail.com> on 2011/04/29 04:22:56 UTC

help with sql query for Derby

Dears,
    My name is Luis Ribeiro, I`m brazilian and I study Computer Science at
Universidade Presbiteriana Mackenzie (
http://www.mackenzie.br/portal/principal.php). I and Henrique Teixeira (cc)
are on the last semester and we`re doing our final project. To be honest,
we`re finishing its. It`s about Comparison between HD databases and
In-Memory databases.
    We`re trying to use Apache Derby to know how it works with the simple
systems that we developed.
    We have two doubts and I would be greatfull if you could answer to us:
    - Does exist some function or method that you use similar to
"GROUP_CONCAT" (from MySQL)? I want to know if I can do this on a simple way
with Apache Derby?

Original table
ID | NAME
1  | Luis
2  | Henrique
3  | Luis

After do the query
ID | NAME
1, 3 | Luis
2  | Henrique

    - What we need to do to have a exceptional performance with In Memory
mode? I`d increased jvm heap, but it didn`t work.

    I`m sorry if I was offensive with this e-mail.

    Thank you very much.

Best regards,
Luis Ribeiro

Re: help with sql query for Derby

Posted by Lukas Eder <lu...@gmail.com>.

Hello

> Derby does not support a GROUP_CONCAT aggregate function. However, it would
> be trivial to write if we implemented DERBY-672, user-defined aggregates. By
> voting for that feature, you can boost its chances of being implemented in
> our next feature release.

That would be a very interesting addition, I upvoted it too.

Cheers
Lukas

Re: help with sql query for Derby

Posted by Rick Hillegas <ri...@oracle.com>.

Hi Luis,

Derby does not support a GROUP_CONCAT aggregate function. However, it 
would be trivial to write if we implemented DERBY-672, user-defined 
aggregates. By voting for that feature, you can boost its chances of 
being implemented in our next feature release.

Many performance-enhancing techniques can be found in the Derby Tuning 
Guide: 
http://db.apache.org/derby/docs/10.7/tuning/tuning-single.html#ctunoptimz39739 
If you describe your problem queries, the community may be able to help 
you speed them up.

Hope this helps,
-Rick

On 4/28/11 7:22 PM, Luis Ribeiro wrote:
> Dears,
>     My name is Luis Ribeiro, I`m brazilian and I study Computer 
> Science at Universidade Presbiteriana Mackenzie 
> (http://www.mackenzie.br/portal/principal.php). I and Henrique 
> Teixeira (cc) are on the last semester and we`re doing our final 
> project. To be honest, we`re finishing its. It`s about Comparison 
> between HD databases and In-Memory databases.
>     We`re trying to use Apache Derby to know how it works with the 
> simple systems that we developed.
>     We have two doubts and I would be greatfull if you could answer to us:
>     - Does exist some function or method that you use similar to 
> "GROUP_CONCAT" (from MySQL)? I want to know if I can do this on a 
> simple way with Apache Derby?
>
> Original table
> ID | NAME
> 1  | Luis
> 2  | Henrique
> 3  | Luis
>
> After do the query
> ID | NAME
> 1, 3 | Luis
> 2  | Henrique
>
>     - What we need to do to have a exceptional performance with In 
> Memory mode? I`d increased jvm heap, but it didn`t work.
>
>     I`m sorry if I was offensive with this e-mail.
>
>     Thank you very much.
>
> Best regards,
> Luis Ribeiro

Re: help with sql query for Derby

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

Hi Luis,

If you want to load the whole database into memory, you have to set the page
cache size and the Xmx java setting:

java -Dderby.storage.pageCacheSize=524288 -Xmx1G

Derby is polite and will not take up more cache memory than the default
size, even if you increase the heap.  So you have to tell Derby how much
memory you want allocated.  This can be very handy in an embedded database
application where you don¹t want the DB to take up all available memory.

Also, please make sure to make the distinction between an embedded
in-process cache versus something like memcached.  The serialization and IPC
(inter-process communication) overhead makes out-of-process databases much
slower.  This explains why an in-process Java memory cache out performs
memcached:
http://gregluck.com/blog/archives/2007/05/comparing-memcached-and-ehcache-pe
rformance/

And if part of your study is to compare multiple Java in-memory embedded
databases, please note whether the database is concurrent rather than merely
³thread-safe².  A concurrent database allows queries and updates in parallel
from multiple threads, while ³thread-safe² typically means only one thread
at a time can access or modify the embedded database.   Concurrency makes a
big difference for scalability because multiple threads share the same cache
of in-process memory, so the overall cache scales across multiple threads.
In a ³thread-safe² model each process must have it¹s own cache, which
duplicates cache entries and limits scalability of the solution.

I did a performance study of multiple Java embedded databases for use in the
Hadoop framework, and found that Derby offers superior multi-threaded
performance:
http://mpouttuclarke.wordpress.com/2010/12/10/java-embedded-db-for-ip2locati
on-in-hadoop/

Hadoop has a multi-threaded option, so concurrency within embedded Derby was
a major selection criteria.

Anyway, enjoy your study, and I hope this helps!

Cheers,
Matt

On 4/28/11 7:22 PM, "Luis Ribeiro" <lu...@gmail.com> wrote:

> Dears,
>     My name is Luis Ribeiro, I`m brazilian and I study Computer Science at
> Universidade Presbiteriana Mackenzie
> (http://www.mackenzie.br/portal/principal.php). I and Henrique Teixeira (cc)
> are on the last semester and we`re doing our final project. To be honest,
> we`re finishing its. It`s about Comparison between HD databases and In-Memory
> databases.
>     We`re trying to use Apache Derby to know how it works with the simple
> systems that we developed.
>     We have two doubts and I would be greatfull if you could answer to us:
>     - Does exist some function or method that you use similar to
> "GROUP_CONCAT" (from MySQL)? I want to know if I can do this on a simple way
> with Apache Derby?
> 
> Original table
> ID | NAME
> 1  | Luis
> 2  | Henrique
> 3  | Luis
> 
> After do the query
> ID | NAME
> 1, 3 | Luis
> 2  | Henrique
> 
>     - What we need to do to have a exceptional performance with In Memory
> mode? I`d increased jvm heap, but it didn`t work.
>   
>     I`m sorry if I was offensive with this e-mail.
> 
>     Thank you very much.
> 
> Best regards,
> Luis Ribeiro
> 

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.