Thread: Caching
Whenever I execute a query and retrieve the results with the sequence:
ResultSet resultSet = statement.executeQuery("SELECT blah blah
blah...");
while (resultSet.next()) {
//do something with the resultSet
}
it looks like the query is completely carried out before any results are
returned. This contrasts with the behavior of other databases (Informix
comes to mind) where the query returns immediately and the results are
returned as the database fetches them. Is my supposition correct, and, if
so, is there any way to make postgres behave the same way as Informix?
My reasons for wanting this behavior is that I'm displaying results in real
time to a user through a GUI and the gradual display of results is
preferable to a long pause followed by all the results at once.
Yes, the current driver has setFetchSize() implemented.
Note that you must be inside a transaction as a cursor is used for implementing
this. Alternatively you can be using a server side prepared statement (you
don't need to be inside a transaction in that case).
If you are using an older driver, you can always declare a cursor yourself.
Regards,
Fernando
Lufkin, Brad wrote:> Whenever I execute a query and retrieve the results with
the sequence:
>
> ResultSet resultSet = statement.executeQuery("SELECT blah blah
> blah...");
> while (resultSet.next()) {
> //do something with the resultSet
> }
>
> it looks like the query is completely carried out before any results are
> returned. This contrasts with the behavior of other databases (Informix
> comes to mind) where the query returns immediately and the results are
> returned as the database fetches them. Is my supposition correct, and, if
> so, is there any way to make postgres behave the same way as Informix?
> My reasons for wanting this behavior is that I'm displaying results in real
> time to a user through a GUI and the gradual display of results is
> preferable to a long pause followed by all the results at once.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Fernando Nasser
Red Hat - Toronto E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
What is server side prepared statement and how I used it in jdbc ?
Thanks
Haris Peco
On Friday 29 August 2003 09:35 pm, Fernando Nasser wrote:
> Yes, the current driver has setFetchSize() implemented.
>
> Note that you must be inside a transaction as a cursor is used for
> implementing this. Alternatively you can be using a server side prepared
> statement (you don't need to be inside a transaction in that case).
>
> If you are using an older driver, you can always declare a cursor yourself.
>
> Regards,
> Fernando
>
> Lufkin, Brad wrote:> Whenever I execute a query and retrieve the results
> with
>
> the sequence:
> > ResultSet resultSet = statement.executeQuery("SELECT blah blah
> > blah...");
> > while (resultSet.next()) {
> > //do something with the resultSet
> > }
> >
> > it looks like the query is completely carried out before any results are
> > returned. This contrasts with the behavior of other databases (Informix
> > comes to mind) where the query returns immediately and the results are
> > returned as the database fetches them. Is my supposition correct, and, if
> > so, is there any way to make postgres behave the same way as Informix?
> > My reasons for wanting this behavior is that I'm displaying results in
> > real time to a user through a GUI and the gradual display of results is
> > preferable to a long pause followed by all the results at once.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
Fernando:
I get a "method not implemented error" when I use setFetchSize(); the
version of the driver I'm using is 7.3b1 jdbc3 build 104. Is there a later
production version that I should be using?
Brad
-----Original Message-----
From: Fernando Nasser [mailto:fnasser@redhat.com]
Sent: Friday, August 29, 2003 5:35 PM
To: Lufkin, Brad
Cc: 'psql'
Subject: Re: [JDBC] Caching
Yes, the current driver has setFetchSize() implemented.
Note that you must be inside a transaction as a cursor is used for
implementing
this. Alternatively you can be using a server side prepared statement (you
don't need to be inside a transaction in that case).
If you are using an older driver, you can always declare a cursor yourself.
Regards,
Fernando
Lufkin, Brad wrote:> Whenever I execute a query and retrieve the results
with
the sequence:
>
> ResultSet resultSet = statement.executeQuery("SELECT blah blah
> blah...");
> while (resultSet.next()) {
> //do something with the resultSet
> }
>
> it looks like the query is completely carried out before any results are
> returned. This contrasts with the behavior of other databases (Informix
> comes to mind) where the query returns immediately and the results are
> returned as the database fetches them. Is my supposition correct, and, if
> so, is there any way to make postgres behave the same way as Informix?
> My reasons for wanting this behavior is that I'm displaying results in
real
> time to a user through a GUI and the gradual display of results is
> preferable to a long pause followed by all the results at once.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Fernando Nasser
Red Hat - Toronto E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
Lufkin, Brad wrote: > Fernando: > I get a "method not implemented error" when I use setFetchSize(); the > version of the driver I'm using is 7.3b1 jdbc3 build 104. Is there a later > production version that I should be using? > Brad > This b1 sounds fishy. Have you downloaded the latest _stable_ driver from the web site? It has many fixes. http://jdbc.postgresql.org/download.html Regards. Fernando > -----Original Message----- > From: Fernando Nasser [mailto:fnasser@redhat.com] > Sent: Friday, August 29, 2003 5:35 PM > To: Lufkin, Brad > Cc: 'psql' > Subject: Re: [JDBC] Caching > > > Yes, the current driver has setFetchSize() implemented. > > Note that you must be inside a transaction as a cursor is used for > implementing > this. Alternatively you can be using a server side prepared statement (you > don't need to be inside a transaction in that case). > > If you are using an older driver, you can always declare a cursor yourself. > > Regards, > Fernando > > Lufkin, Brad wrote:> Whenever I execute a query and retrieve the results > with > the sequence: > >> ResultSet resultSet = statement.executeQuery("SELECT blah blah >>blah..."); >> while (resultSet.next()) { >> //do something with the resultSet >> } >> >>it looks like the query is completely carried out before any results are >>returned. This contrasts with the behavior of other databases (Informix >>comes to mind) where the query returns immediately and the results are >>returned as the database fetches them. Is my supposition correct, and, if >>so, is there any way to make postgres behave the same way as Informix? >>My reasons for wanting this behavior is that I'm displaying results in > > real > >>time to a user through a GUI and the gradual display of results is >>preferable to a long pause followed by all the results at once. >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >> > > > > -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
If you write:
setUseServerPrepare(true)
for a PreparedStatement the driver will try and ask the backend to keep
a parse tree of it for repeated use by the client (if supported).
But if you can keep your stuff in a transaction and avoid using server
side prepared statements you'll get a better performance (the
optimization may not be so good done in advance as it does not have all
the info).
Regards,
Fernando
snpe wrote:
> What is server side prepared statement and how I used it in jdbc ?
>
> Thanks
> Haris Peco
> On Friday 29 August 2003 09:35 pm, Fernando Nasser wrote:
>
>>Yes, the current driver has setFetchSize() implemented.
>>
>>Note that you must be inside a transaction as a cursor is used for
>>implementing this. Alternatively you can be using a server side prepared
>>statement (you don't need to be inside a transaction in that case).
>>
>>If you are using an older driver, you can always declare a cursor yourself.
>>
>>Regards,
>>Fernando
>>
>>Lufkin, Brad wrote:> Whenever I execute a query and retrieve the results
>>with
>>
>>the sequence:
>>
>>> ResultSet resultSet = statement.executeQuery("SELECT blah blah
>>>blah...");
>>> while (resultSet.next()) {
>>> //do something with the resultSet
>>> }
>>>
>>>it looks like the query is completely carried out before any results are
>>>returned. This contrasts with the behavior of other databases (Informix
>>>comes to mind) where the query returns immediately and the results are
>>>returned as the database fetches them. Is my supposition correct, and, if
>>>so, is there any way to make postgres behave the same way as Informix?
>>>My reasons for wanting this behavior is that I'm displaying results in
>>>real time to a user through a GUI and the gradual display of results is
>>>preferable to a long pause followed by all the results at once.
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>> http://www.postgresql.org/docs/faqs/FAQ.html
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9