On Apr 22, 10:40 am, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> > insert into tabletmp select * from csvread('c:/temp/
> > dw.bcp',NULL,NULL,' ')
>
> > I am getting a message 'java heap space'
> > [In addition leaving partly filled table! - so no transaction control
> > on this one?]
>
> It should not run out of memory... does your table have a primary key?
>
> A workaround is:
> CREATE TABLE ... AS SELECT * FROM CSVREAD(...)
> This will not try to load the whole table in memory. But then you may
> have to copy the data from there to the real table.
>
> > I suspect that csvread is trying to do everything in one go.
> > If so, the solution would be to introduce a new parameter to CSVREAD:
> > batchSize=N, default something like 10,000.
>
> The right solution would be to support a 'batch' parameter for INSERT,
> UPDATE, and DELETE. I will add a feature request for this. I think
> MySQL has something similar, at least for DELETE.
>
> Regards,
> Thomas
Additional info/clarification:
- the database is on disk.
- the size of the database file on disk, after inserting 150k rows is
about 100-150mb
- the problem occurs around 180k records
- there was no index nor primary key on the table
- the table was defined earlier, it was empty, the data were inserted
- currently running CSVREAD(...) LIMIT 100000 OFFSET XXXX - loaded 3m
records without problems
If this is csvread problem, another solution could be to extract
csvread as a bcp utility - a separate utility for data export import -
like SQL Server does. Then there will be no need to add params to
UPDATE - which could affect the ACID. Adding such param do delete -
seems a good idea to me - otherwise logging gets in the way too much.
Though both postgres and sql server simply have truncate table -
delete all but schema.
I'm ok, since I have a workaround, so that may be something for the
future.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
