Subquery Select with aggregate function running very slow

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Subquery Select with aggregate function running very slow

avaz
This post was updated on .
Hi,

I have file db started by follow command:
java -Xms512m -Xmx512m -cp hsqldb-2.3.1.jar org.hsqldb.server.Server
--database.0 file:./test --dbname.0 test
This database contains about 197 tables.
Bellow is the structure of table related with this issue:

create table test (
  id NUMERIC (8)  identity,
  id_account numeric (8) not null ,
  value NUMERIC (8) not null,
  BASE_DATE          DATE             not null
);

This table is populated with 174789 lines.

And the follow script take hours to return (this be honest, I don't know how
many hours it take, because after left for a long time running I cancelled
the execution):

SELECT  t1.id,
  (SELECT sum(t2.value) FROM test t2 WHERE t2.id_account = t1.id_account)
FROM test t1;

In oracle, with the same scenario, the script returns instantaneously.

This is the normal behaviour of hsqldb?

Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: Subquery Select with aggregate function running very slow

gnani.swami

Have you created index on id-account?

On 28 Oct 2013 03:15, "avaz" <[hidden email]> wrote:
Hi,

I have file db started by follow command:
java -Xms512m -Xmx512m -cp hsqldb-2.3.1.jar org.hsqldb.server.Server
--database.0 file:./test --dbname.0 test
This database contains about 197 tables.
Bellow is the structure of table related with this issue:

create table test (
  id NUMERIC (8)  identity,
  id_account numeric (8) not null ,
  value NUMERIC (8) not null,
  BASE_DATE          DATE             not null
);

This table is populated with 174789 lines.

And the follow script take hours to return (this be honest, I don't know how
many hours it take, because after left for a long time running I cancelled
the execution):

SELECT  t1.id,
  (SELECT sum(t2.value) FROM test t2 WHERE t2.id_account = t1.id_account)
FROM test t1;

In oracle, with the same scenario, the script returns instantaneously.

This is the normal behaviour of hsqldb?

Thanks!





--
View this message in context: http://hsqldb.10974.n7.nabble.com/Subquery-Select-with-aggregate-function-running-very-slow-tp3976.html
Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60135991&iu=/4140/ostg.clktrk
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Subquery Select with aggregate function running very slow

avaz
Hi gnami,

I didn't considere one for this amount of lines.
I've created one and the query is very responsible now.

Thanks!