Closed GoogleCodeExporter closed 9 years ago
I don't think it's a bug within the database. It's just that there seems to be
no
fast way to run the query.
One problem is that you didn't run ANALYZE. I suggest to do that. See also the
documentation.
You use a relatively large cache size (SET CACHE_SIZE 131072). This may be a
problem
(the system runs out of memory or gets very slow) unless you start with
-Xmx256m. I
suggest to use a smaller cache size. I used the default cache size, then then
the
query took around 7.7 seconds.
The the IN(..) query is relatively slow: SELECT count(T1.PublicID) FROM
pcst_zone T1, pcst_zone T2 WHERE T1.Code=T2.Code AND T1.Country=T2.Country
AND T1.ZoneType=T2.ZoneType AND T1.luwid > T2.luwid; - takes about about 2.5 s
for
me. It returns 26270 (there are many duplicates). Instead, you could use use:
select count(PublicID) from pcst_zone T1 where exists (select * from pcst_zone
T2
WHERE T1.Code=T2.Code AND T1.Country=T2.Country
AND T1.ZoneType=T2.ZoneType AND T1.luwid > T2.luwid)
This also takes about 2.5 seconds, but there are no duplicates (14973).
However, it
doesn't improve the final result.
The query: select count(*) FROM pcst_zone t3 WHERE t3.PublicID IN (
select PublicID from pcst_zone T1 where exists (select * from pcst_zone T2 WHERE
T1.Code=T2.Code AND T1.Country=T2.Country
AND T1.ZoneType=T2.ZoneType AND T1.luwid > T2.luwid)); -- takes about 7.3
seconds for
me. The new query takes around 7.7 seconds:
select count(*) FROM pcst_zone t3 WHERE t3.PublicID IN (select PublicID from
pcst_zone T1 where exists (select * from pcst_zone T2 WHERE T1.Code=T2.Code AND
T1.Country=T2.Country AND T1.ZoneType=T2.ZoneType AND T1.luwid > T2.luwid));
Original comment by thomas.t...@gmail.com
on 7 Mar 2010 at 4:50
I would be very happy if the query would complete at all, and if it took 7 or 8
seconds would be completely acceptable. It just hangs in our app, and now it's
running out of memory no matter what I do. How are you running it?
I am doing the following
In a cmd shell I run:
java -cp h2*.jar org.h2.tools.Server
The console in comes up in firefox and I enter
jdbc:h2:file:/tmp/guidewire/pc
for the jdbc url.
I then enter the query and in about 7-8 seconds it runs out of memory. Notice I
am
not specifying any memory requirements.
select count(*) FROM pcst_zone WHERE PublicID IN ( SELECT T1.PublicID FROM
pcst_zone
T1, pcst_zone T2 WHERE T1.Code=T2.Code AND T1.Country=T2.Country AND
T1.ZoneType=T2.ZoneType AND T1.luwid > T2.luwid);
java.lang.OutOfMemoryError: Java heap space
So how are you getting it to work???
Thanks
Randy
Original comment by youn...@gmail.com
on 8 Mar 2010 at 7:02
When I first run the query it also ran out of memory on my system (even when I
used
-Xmx128m). As I already wrote:
You use a relatively large cache size (SET CACHE_SIZE 131072). This may be a
problem
(the system runs out of memory or gets very slow) unless you start with
-Xmx256m. I
suggest to use a smaller cache size. I used the default cache size, then then
the
query took around 7.7 seconds.
The cache size is persisted in the database file. To change the cache size, you
have
to use SET CACHE_SIZE 16384
Original comment by thomas.t...@gmail.com
on 8 Mar 2010 at 7:37
Ok. Thanks for the clarificaton. I'll try this.
Original comment by youn...@gmail.com
on 8 Mar 2010 at 8:15
Running analyze after loading all the rows fixed the hang. Thank you!
Now if it only did an auto-update of statistics..... :)
Original comment by youn...@gmail.com
on 10 Mar 2010 at 7:43
Original issue reported on code.google.com by
youn...@gmail.com
on 5 Mar 2010 at 11:46Attachments: