Hello,
I have an application that relies heavily on in-database
transformations using
CREATE TABLE Y AS SELECT ... FROM ...
The H2 database first processes the query part and accumulates the
results in LocalResult object backed by the disk and then iterates
through the LocalResult and adds rows to the final table Y.
I made a few changed to the code to bypass that temp file and write
records directly to the target table as the SELECT query runs.
Basically, I added
protected abstract void queryWithoutCache(int limit, LocalResult
result);
so LocalResult object can be passed from the outside rather than
created deeper down the stack. In the Insert#update method I'm
constructing the LocalResult object and override the
LocalResult#addRow(Value[] values) method so that values are added to
the target table instead of internal rows collection. As a result I
was able to cut the processing time in half.
To test this, I've added a INSERT_QUERY_DIRECT property and made the
changes conditionally enabled based on that value. Here are the result
of a test that repeatedly executes CREATE AS SELECT alternating the
INSERT_QUERY_DIRECT property value:
-- Table test construction
Inserting 17%
Inserting 37%
Inserting 57%
Inserting 75%
Inserting 89%
-- Creating table TEST2 as select from TEST
Create table as select direct=false ... 11.780 sec.
Create table as select direct=false ... 11.429 sec.
Create table as select direct=true ... 6.563 sec.
Create table as select direct=false ... 11.686 sec.
Create table as select direct=true ... 6.467 sec.
Create table as select direct=false ... 11.556 sec.
Create table as select direct=true ... 6.500 sec.
There are two concerns though:
1. I'm calling queryWithoutCache from Update#insert, so no query
result cache (lastResult in the Query object) is used.
2. Bypassing LocalResult internal storage only works if the query does
not do DISTINCT or ORDER BY as these are handled by LocalResult class.
However, in some applications (like mine, where there are no ORDER BY
or DISTINCT) this optimization gives very considerable performance
improvement so I need to carefully work around the two issues
mentioned.
The solution I see is
a) have INSERT_QUERY_DIRECT option, setting which to true will enable
the optimization at the cost of not reusing query results when doing
CREATE TABLE ... AS SELECT..., so that it can be turned off in cases
when using the benefit of using cached result outweighs the direct
insert.
b) add getters to the Query class, so that Insert#update can
interrogate the Query class and check if it would ultimately do
queryFlat() and no sorting so that it can chose to pass an extended
LocalResult object which would write directly to the table.
At this point I would like to get some feedback, so that I can proceed
to creating a patch for this.
Thank you.
--Alexey
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
