MaxDB/JDBC: Large table scan results in "space exhausted"

[Former Member]

I need to synchronize a database table against a sorted XML file.

I'm using this query: "SELECT * FROM ABDA_ART_MED_OBJECT ORDER BY pzn"

The column "pzn" is the primary key.

This is the output of "explain select".

SCHEMANAME;TABLENAME;COLUMN_OR_INDEX;STRATEGY;PAGECOUNT;

PHOENIX;ABDA_ART_MED_OBJECT;;TABLE SCAN;     25367

;JDBC_CURSOR_24;;   RESULT IS NOT COPIED , COSTVALUE IS;     25367

;JDBC_CURSOR_24;;QUERYREWRITE : APPLIED RULES:;

;JDBC_CURSOR_24;;   DistinctPullUp;         1

The query works nicely from within Database Studio.

With Hibernate, the query takes a very long time (regardless of the setting "fetch size" and "max results") and then terminates with "-904 space for result tables exhausted".

Have you got any ideas what I could do to improve the situation?

Thanks a lot, best regards,

Matthias

[Former Member · Accepted solution]

I've got a screenshot showing the position in the JDBC driver where the query "hangs". It seems that indeed the MaxDB copies the whole table and the application waits. Here is the URL of the screenshot:

https://office.meona.de/files/JdbcStacktrace.png

[thorsten_zielke · Accepted solution]

Both Database Studio and your Java Beans are using the JDBC driver.

But I think the difference must be in the 'result set' cursor options. According to the 'explain select' you had attached, the Database Studio select did not need any additinal space on disk (this is indicated by the line 'RESULT IS NOT COPIED'). Also, Database Studio only fetches some part of the result set to return the rows you are looking at faster.

So the cursor options for your java bean select statement must be different from database Studio. I wonder why space for a result table on disk would be needed at all here? If the select is ordered by the only primary key field, then the database should not need any extra table at all, because MaxDB already and always stores table data ascending in primary key order.

[Former Member · Accepted solution]

Thanks a lot! You've been completely right.

Indeed I needed to specify cursor options.

Now I use ScrollMode.FORWARD_ONLY and this did the trick.

It seems Hibernate chose the wrong cursor options without this parameter.

May I ask a last question? 😉

Is it better (in terms of performance, stability and generated amount of DB log entries) to have a million INSERT/UPDATE statements in a single transaction or should I divide this up to multiple transactions each having let's say 100 or 1000 INSERTs/UPDATEs?

[thorsten_zielke · Accepted solution]

Each 'commit' costs a bit of performance, so for fastest performance you would use only one 'commit' in the end. For stability, it is indeed better to issue a 'commit' once in a while to reduce possible redo/undo times.

I know that the MaxDB 'Loader' tool commits every 10000 rows, so my suggestion would be to compare the runtimes between 'only one commit' and 'commits every 10000 rows' and if there is only a minor performance difference, then use th 10000 setting.

[thorsten_zielke]

The error "-904 space for result tables exhausted" means that the database has not enough free space left on the Data Volumes to store the temporary result. To workaround this, please add some data volumes to increase the free space.

However, I cannot think of any reason why this behaviour should be related to Windows Hibernating? As far as I know, the Database is not be made aware of the OS going in hibernate state and the OS memory is just put in some sort of temp file which gets reloaded to memory once the computer wakes up.

Is there any chance of the '-904 error' beeing a coincidence and not related to hibernating or can you reproduce this error repeatedly and only after hibernate?

And both times this query was issued via Database Studio?

[Former Member]

Thank you very much for your response! I'll try to add more data volumes.

Does the MaxDB need to duplicate this (very large) table just to scroll through the sorted table? Sorting is done by the (indexed) primary key, this is why I thought this should work without temporary results.

With Hibernate, I refer to a Java library that does ORM (object-relational mapping). It generates SQL queries that are sent via JDBC and "wraps" the result set in Java beans. I checked that the Hibernate-generated SQL query is equivalent to the query I posted above so that I think Hibernate is not responsible for the problem. (But the JDBC driver might be...). I'm wondering why it seems the query works from within Database Studio but not via JDBC. Are there different ways a "scrollable result" is implemented?

[Former Member]

With more disk space, the query completes, but takes very long. After finishing scrolling, it also takes a rather long time for completing the transaction.