Hi,
I am trying to insert data into a table using INSERT INTO SELECT using
CSV file
INSERT INTO <table> SELECT * from CSVREAD('<file>');
The query is creating temporary file before inserting the data
into .db file.
For large input data size this process is very slow.
Is it necessary to create temporary file as as in case of CREATE TABLE
AS no temp files are created and data is directly dumped into actual
db file.
CREATE TABLE <table> AS SELECT * from CSVREAD('<file>');
I cannot use CREATE TABLE statement instead of INSERT statement as I
am using the database for concurrent request by setting
MULTI_THREADED=1 in connection url.
Each concurrent request is creating a separate table.
When I use CREATE TABLE statement for concurrent requests all requests
fails except one with error
Timeout trying to lock table "SYS"
I searched the H2 code and found that a parameter insertFromSelect in
Insert class controls the creation of temporary file which is set true
in case of CREATE TABLE and not set in case of INSERT INTO
Following is the code Snippet from CreateTable.update() method that
sets the insertFromSelect parameter for avoiding temp file creations:
Insert insert = null;
insert = new Insert(session);
insert.setSortedInsertMode(sortedInsertMode);
insert.setQuery(asQuery);
insert.setTable(table);
insert.setInsertFromSelect(true);
insert.prepare();
insert.update();
To avoid the temp file creation I tried modifing the code in
Parser.parseInsert() Method and set the insertFromSelect to true in
case of INSERT INTO SELECT queries and tried executing the scenario
again and it worked fine.
So my question are: -
1. Why in case of CREATE TABLE I get timeout error and could I change
any parameter or configuration to rectify it?
2. Is it really necessary to create temp file in case of INSERT INTO
SELECT? Was this change not done in case of INSERT INTO tablename
SELECT deliberately to cover any case that I might have missed.
I reason I need to know this is by doing this small change, I am able
to achieve huge throughput.
Any help in this regards will be highly appreciated.
Thanks,
Saurabh
--
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.
