Обсуждение: [INTERFACES] JDBC - speed with big 'select's
Hi, Having just read the results of the post concerning INSERT times, I thought I should ask a related question that has been causing me some problems. I have a servlet which performs a SELECT which returns up to 6000 rows. These contain just 6 ints and need to be read to perform a specialised search. The query run's pretty quick, less than a second (although the time to display is much longer), using the psql interface. Therefore I assume the majority of the time the servlet requires is repeated calls to rs.next() and then the requests for each of the fields across the socket connection. JDBC 2.0 has a setFetchSize() function which looks as though it may allow me to transfer the results in larger chunks, but I am shooting in the dark really. Any suggestions as how I might speed this whole thing up, the INSERT trick by using COPY FROM STDIN got me thinking there may be other alternatives. Unfortunately I can not perform the selection using a more selective SELECT statement. Many thanks in advance Simon _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ _/ Simon Hardingham - netXtra Ltd - UK _/ _/ Tel: +44 (0) 1787 319393 Fax: +44 (0) 1787 319394 _/ _/ http://www.netxtra.co.uk simon@netxtra.co.uk _/ _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ************
I'm not sure if I'll get that one sorted for 7.0, but I was planning on rewriting the JDBC2 version of ResultSet to use cursors, and reducing the fetch size from the current "All Rows in the result". The delay isn't in rs.next(), but by the fact that the result is read in full before returning from the protected ExecSQL() method in Connection. Peter -- Peter Mount Enterprise Support Maidstone Borough Council Any views stated are my own, and not those of Maidstone Borough Council. -----Original Message----- From: Simon Hardingham [mailto:simon@netxtra.net] Sent: Monday, December 06, 1999 2:35 PM To: pgsql-interfaces@postgreSQL.org Subject: [INTERFACES] JDBC - speed with big 'select's Hi, Having just read the results of the post concerning INSERT times, I thought I should ask a related question that has been causing me some problems. I have a servlet which performs a SELECT which returns up to 6000 rows. These contain just 6 ints and need to be read to perform a specialised search. The query run's pretty quick, less than a second (although the time to display is much longer), using the psql interface. Therefore I assume the majority of the time the servlet requires is repeated calls to rs.next() and then the requests for each of the fields across the socket connection. JDBC 2.0 has a setFetchSize() function which looks as though it may allow me to transfer the results in larger chunks, but I am shooting in the dark really. Any suggestions as how I might speed this whole thing up, the INSERT trick by using COPY FROM STDIN got me thinking there may be other alternatives. Unfortunately I can not perform the selection using a more selective SELECT statement. Many thanks in advance Simon _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ _/ Simon Hardingham - netXtra Ltd - UK _/ _/ Tel: +44 (0) 1787 319393 Fax: +44 (0) 1787 319394 _/ _/ http://www.netxtra.co.uk simon@netxtra.co.uk _/ _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ************
Is there any way to get something like this into valid sql? Postgres doesn't like this: INSERT INTO application (key, creator, name) values ( 1 ,(select userkey from user where username = 'b' ), 'newapplication') ;
Joseph Shraibman <jks@p1.selectacast.net> writes:
> Is there any way to get something like this into valid sql? Postgres
> doesn't like this:
> INSERT INTO application (key, creator, name) values ( 1 ,(select userkey
> from user where username = 'b' ), 'newapplication') ;
FWIW, this works in current sources and will be in the next release
(6.6 or 7.0 or whatever...). 6.5 doesn't support sub-selects in
target lists, which is what you have here. You could work around it
with something like
INSERT INTO application SELECT 1, userkey, 'newapplication' FROM
user WHERE username = 'b';
although this doesn't mean quite the same thing (if there are multiple
user rows with username = 'b', the former raises an error while the
latter inserts multiple rows into application).
BTW: I dunno what version you are running, but "user" has been a
reserved word at least since 6.5. Might be good to rename your table
before upgrading.
regards, tom lane
On Tue, Dec 07, 1999 at 09:14:37PM -0500, Joseph Shraibman wrote: > INSERT INTO application (key, creator, name) values ( 1 ,(select userkey > from > user where username = 'b' ), 'newapplication') ; INSERT INTO application (key, creator, name) SELECT 1, userkey, 'newapplication' from user where username = 'b'; Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
Michael Meskes wrote: > On Tue, Dec 07, 1999 at 09:14:37PM -0500, Joseph Shraibman wrote: > > INSERT INTO application (key, creator, name) values ( 1 ,(select userkey > > from > > user where username = 'b' ), 'newapplication') ; > > INSERT INTO application (key, creator, name) SELECT 1, userkey, > 'newapplication' from user where username = 'b'; > owl=> INSERT INTO application (key, creator, name) values (select 1,userkey, 'newapplication' from user where username = 'b' ) ; ERROR: parser: parse error at or near "select" owl=> INSERT INTO application (key, creator, name) SELECT 1, userkey, owl-> 'newapplication' from user where username = 'b'; ERROR: parser: parse error at or near "user" owl=> Any other ideas?
Joseph Shraibman wrote: > Michael Meskes wrote: > > OK I actually figured part of the problem was that postgres doesn't like the table name 'user' so I had changed the name to 'usertable'. Thanks all.