This probably falls under the category "linked tables can only handle
simple queries" but I thought I'd ask.
I have a linked table to a large SQLServer 2005 table, 70,000,000+ rows.
The db is on a remote host over a vpn with a bit of a flaky connection.
Queries like
create table <localCopy> as select * from <remoteLinkedTable>;
work great on most tables but I am having trouble with larger tables like
this one. The query times out or the connection is reset or something
perhaps just because it is long running. It will move 1-2 GBs before the
network resets on it.
I tried the following but the limit and offset params don't seem to be
supported by the link table (I appreciated that they aren't supported by
SQLServer but I wondered if H2 attempted some translation to TOP and the
like.) I'd like to break the table up into pieces and run something like:
create table <localCopy> as select * from <remoteLinkedTable> limit
<batchSize>;
insert into <localCopy> as select * from <remoteLinkedTable> limit
<batchSize> offset <batchSize>;
insert into <localCopy> as select * from <remoteLinkedTable> limit
<batchSize> offset <2 * batchSize>;
etc....
Tests on smaller tables showed this working but I think now it was perhaps
just the caching making it looked like it worked.
The level 3 trace output shows the query hanging on the call to SQLServer
04-13 02:54:45 jdbc[3]:
/**/stat16.execute("select * from L_BigTable limit 100");
04-13 02:54:45 table: L_BigTable:
SELECT * FROM dbo.BigTable T;
04-13 02:57:09 jdbc[3]:
/**/stat16.cancel();
To get a 100 rows directly using TOP and MS jdbc is only 100 millisecs or
so.
I don't recognize the T in:
SELECT * FROM dbo.BigTable T;
Is there an easy way to "chunk" the table using linked tables or do I need
to do via something other than SQL directly in H2?
Many thanks, Peter
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
