pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedin memory views
19+ messages / 7 participants
[nested] [flat]
* in memory views
@ 2006-05-10 09:18 Thomas Vatter <thomas.vatter@network-inventory.de>
0 siblings, 1 reply; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 09:18 UTC (permalink / raw)
To: pgsql-performance
is there a possibility for creating views or temp tables in memory to
avoid disk io when user makes select operations?
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 09:35 Tino Wildenhain <tino@wildenhain.de>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
0 siblings, 1 reply; 19+ messages in thread
From: Tino Wildenhain @ 2006-05-10 09:35 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: pgsql-performance
Thomas Vatter schrieb:
> is there a possibility for creating views or temp tables in memory to
> avoid disk io when user makes select operations?
No need. The data will be available in OS and database caches if
they are really required often. If not, tune up the caches and
do a regular "pre select".
Regards
Tino
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 09:55 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 2 replies; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 09:55 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: pgsql-performance
Tino Wildenhain wrote:
> Thomas Vatter schrieb:
>
>> is there a possibility for creating views or temp tables in memory to
>> avoid disk io when user makes select operations?
>
>
> No need. The data will be available in OS and database caches if
> they are really required often. If not, tune up the caches and
> do a regular "pre select".
>
> Regards
> Tino
>
>
hmm, I am selecting a resultset with 1300 rows joined from 12 tables.
with jdbc I am waiting 40 seconds until the first row appears. The
following rows appear really fast but the 40 seconds are a problem.
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 10:08 Tino Wildenhain <tino@wildenhain.de>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
1 sibling, 1 reply; 19+ messages in thread
From: Tino Wildenhain @ 2006-05-10 10:08 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: pgsql-performance
Thomas Vatter schrieb:
> Tino Wildenhain wrote:
>
>> Thomas Vatter schrieb:
>>
>>> is there a possibility for creating views or temp tables in memory to
>>> avoid disk io when user makes select operations?
>>
>>
>>
>> No need. The data will be available in OS and database caches if
>> they are really required often. If not, tune up the caches and
>> do a regular "pre select".
>>
>> Regards
>> Tino
>>
>>
>
> hmm, I am selecting a resultset with 1300 rows joined from 12 tables.
> with jdbc I am waiting 40 seconds until the first row appears. The
> following rows appear really fast but the 40 seconds are a problem.
Well you will need the equally 40 seconds to fill your hypothetical
in memory table. (even a bit more due to the creation of a datastructure).
So you can do the aproaches of semi materialized views (that are in fact
writing into a shadow table) or just prefetch your data at time - just
at the times you would refill your memory tables if they existed.
A cronjob with select/fetch should do.
Regards
Tino
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 10:43 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 1 reply; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 10:43 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: pgsql-performance
Tino Wildenhain wrote:
> Thomas Vatter schrieb:
>
>> Tino Wildenhain wrote:
>>
>>> Thomas Vatter schrieb:
>>>
>>>> is there a possibility for creating views or temp tables in memory
>>>> to avoid disk io when user makes select operations?
>>>
>>>
>>>
>>>
>>> No need. The data will be available in OS and database caches if
>>> they are really required often. If not, tune up the caches and
>>> do a regular "pre select".
>>>
>>> Regards
>>> Tino
>>>
>>>
>>
>> hmm, I am selecting a resultset with 1300 rows joined from 12 tables.
>> with jdbc I am waiting 40 seconds until the first row appears. The
>> following rows appear really fast but the 40 seconds are a problem.
>
>
> Well you will need the equally 40 seconds to fill your hypothetical
> in memory table. (even a bit more due to the creation of a
> datastructure).
>
> So you can do the aproaches of semi materialized views (that are in fact
> writing into a shadow table) or just prefetch your data at time - just
> at the times you would refill your memory tables if they existed.
> A cronjob with select/fetch should do.
>
> Regards
> Tino
>
>
If the in memory table is created a bootup time of the dbms it is
already present when user selects the data. Of course the challenge is
to keep the in memory table up to date if data are changed. What do you
mean with semi materialized views, I have tried select * from this_view
with the same result. Also, if I repeat the query it does not run faster.
regards
tom
--
Mit freundlichen Grüßen / Regards
Vatter
Network Inventory Software
Sun Microsystems Principal Partner
www.network-inventory.de
Tel. 030-79782510
E-Mail thomas.vatter@network-inventory.de
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 11:12 Hakan Kocaman <Hakan.Kocaman@digame.de>
0 siblings, 0 replies; 19+ messages in thread
From: Hakan Kocaman @ 2006-05-10 11:12 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; Tino Wildenhain <tino@wildenhain.de>; +Cc: pgsql-performance
Hi,
there was a similar discussion with a ramdisk:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg01058.php
You need to populate the data on serverstart, of course.
But as Timo mentionend, it's maybe not worth the trouble.
Maybe their is a way to speed up the queriy itself.
To analyze this, you should post the query- and table-definition
and the output of explain analyze of the offending query.
Best regards
Hakan Kocaman
Software-Development
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Thomas Vatter
> Sent: Wednesday, May 10, 2006 12:43 PM
> To: Tino Wildenhain
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] in memory views
>
>
> Tino Wildenhain wrote:
>
> > Thomas Vatter schrieb:
> >
> >> Tino Wildenhain wrote:
> >>
> >>> Thomas Vatter schrieb:
> >>>
> >>>> is there a possibility for creating views or temp tables
> in memory
> >>>> to avoid disk io when user makes select operations?
> >>>
> >>>
> >>>
> >>>
> >>> No need. The data will be available in OS and database caches if
> >>> they are really required often. If not, tune up the caches and
> >>> do a regular "pre select".
> >>>
> >>> Regards
> >>> Tino
> >>>
> >>>
> >>
> >> hmm, I am selecting a resultset with 1300 rows joined from
> 12 tables.
> >> with jdbc I am waiting 40 seconds until the first row appears. The
> >> following rows appear really fast but the 40 seconds are a problem.
> >
> >
> > Well you will need the equally 40 seconds to fill your hypothetical
> > in memory table. (even a bit more due to the creation of a
> > datastructure).
> >
> > So you can do the aproaches of semi materialized views
> (that are in fact
> > writing into a shadow table) or just prefetch your data at
> time - just
> > at the times you would refill your memory tables if they existed.
> > A cronjob with select/fetch should do.
> >
> > Regards
> > Tino
> >
> >
>
> If the in memory table is created a bootup time of the dbms it is
> already present when user selects the data. Of course the
> challenge is
> to keep the in memory table up to date if data are changed.
> What do you
> mean with semi materialized views, I have tried select * from
> this_view
> with the same result. Also, if I repeat the query it does not
> run faster.
>
> regards
> tom
>
> --
> Mit freundlichen Grüßen / Regards
> Vatter
>
> Network Inventory Software
> Sun Microsystems Principal Partner
>
> www.network-inventory.de
> Tel. 030-79782510
> E-Mail thomas.vatter@network-inventory.de
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 11:57 Tino Wildenhain <tino@wildenhain.de>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
0 siblings, 1 reply; 19+ messages in thread
From: Tino Wildenhain @ 2006-05-10 11:57 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: pgsql-performance
Thomas Vatter schrieb:
> Tino Wildenhain wrote:
...
>> Well you will need the equally 40 seconds to fill your hypothetical
>> in memory table. (even a bit more due to the creation of a
>> datastructure).
>>
>> So you can do the aproaches of semi materialized views (that are in fact
>> writing into a shadow table) or just prefetch your data at time - just
>> at the times you would refill your memory tables if they existed.
>> A cronjob with select/fetch should do.
>>
>> Regards
>> Tino
>>
>>
>
> If the in memory table is created a bootup time of the dbms it is
> already present when user selects the data. Of course the challenge is
> to keep the in memory table up to date if data are changed. What do you
> mean with semi materialized views, I have tried select * from this_view
> with the same result. Also, if I repeat the query it does not run faster.
>
Semi materialized views are just views with aditional rules and some
triggers which copy data to another table. There are several receipes
if you google accordingly.
I do not know what you mean by "bootup time" - do you really reboot
your database server? *hehe* just kidding ;)
In your first email you told me your query indeed runs faster the 2nd
time (due to the caching) now you are telling me that it is not.
Btw, judging from your analyze output you are using very cryptic
table and column names - you can use aliasing in the query and dont
have to resort to tiny tags when you actually name the objects ;)
Maybe others have comments on your query. Btw, better use
explain analyze to get realistic results.
Regards
Tino
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 12:23 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 0 replies; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 12:23 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: pgsql-performance
Tino Wildenhain wrote:
> Thomas Vatter schrieb:
>
>> Tino Wildenhain wrote:
>
> ...
>
>>> Well you will need the equally 40 seconds to fill your hypothetical
>>> in memory table. (even a bit more due to the creation of a
>>> datastructure).
>>>
>>> So you can do the aproaches of semi materialized views (that are in
>>> fact
>>> writing into a shadow table) or just prefetch your data at time - just
>>> at the times you would refill your memory tables if they existed.
>>> A cronjob with select/fetch should do.
>>>
>>> Regards
>>> Tino
>>>
>>>
>>
>> If the in memory table is created a bootup time of the dbms it is
>> already present when user selects the data. Of course the challenge
>> is to keep the in memory table up to date if data are changed. What
>> do you mean with semi materialized views, I have tried select * from
>> this_view with the same result. Also, if I repeat the query it does
>> not run faster.
>>
> Semi materialized views are just views with aditional rules and some
> triggers which copy data to another table. There are several receipes
> if you google accordingly.
>
> I do not know what you mean by "bootup time" - do you really reboot
> your database server? *hehe* just kidding ;)
>
> In your first email you told me your query indeed runs faster the 2nd
> time (due to the caching) now you are telling me that it is not.
>
> Btw, judging from your analyze output you are using very cryptic
> table and column names - you can use aliasing in the query and dont
> have to resort to tiny tags when you actually name the objects ;)
>
> Maybe others have comments on your query. Btw, better use
> explain analyze to get realistic results.
>
> Regards
> Tino
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
The subsequent rows are shown faster not the subsequent queries - if
you really read my first e-mail ;-) . Yes, I have done analyse
yesterday, the database has not changed, afaik it is necessary when the
database contents are changing.
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 15:11 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
1 sibling, 1 reply; 19+ messages in thread
From: Scott Marlowe @ 2006-05-10 15:11 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
On Wed, 2006-05-10 at 04:55, Thomas Vatter wrote:
> Tino Wildenhain wrote:
>
> > Thomas Vatter schrieb:
> >
> >> is there a possibility for creating views or temp tables in memory to
> >> avoid disk io when user makes select operations?
> >
> >
> > No need. The data will be available in OS and database caches if
> > they are really required often. If not, tune up the caches and
> > do a regular "pre select".
> >
> > Regards
> > Tino
> >
> >
>
> hmm, I am selecting a resultset with 1300 rows joined from 12 tables.
> with jdbc I am waiting 40 seconds until the first row appears. The
> following rows appear really fast but the 40 seconds are a problem.
Are you selecting the whole set at once? Or are you placing it into a
cursor?
What happens if you do this by declaring it as a cursor and then
fetching the first row?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 15:41 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 15:41 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
Scott Marlowe wrote:
>On Wed, 2006-05-10 at 04:55, Thomas Vatter wrote:
>
>
>>Tino Wildenhain wrote:
>>
>>
>>
>>>Thomas Vatter schrieb:
>>>
>>>
>>>
>>>>is there a possibility for creating views or temp tables in memory to
>>>>avoid disk io when user makes select operations?
>>>>
>>>>
>>>No need. The data will be available in OS and database caches if
>>>they are really required often. If not, tune up the caches and
>>>do a regular "pre select".
>>>
>>>Regards
>>>Tino
>>>
>>>
>>>
>>>
>>hmm, I am selecting a resultset with 1300 rows joined from 12 tables.
>>with jdbc I am waiting 40 seconds until the first row appears. The
>>following rows appear really fast but the 40 seconds are a problem.
>>
>>
>
>Are you selecting the whole set at once? Or are you placing it into a
>cursor?
>
>What happens if you do this by declaring it as a cursor and then
>fetching the first row?
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
I do executeQuery(), for the resultSet I do next() and return one row,
but wait, I have to review the logic in this area, I can tell you tomorrow
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 15:45 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
1 sibling, 1 reply; 19+ messages in thread
From: Scott Marlowe @ 2006-05-10 15:45 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:
> Scott Marlowe wrote:
> > What happens if you do this by declaring it as a cursor and then
> > fetching the first row?
> >
>
> I do executeQuery(), for the resultSet I do next() and return one row,
> but wait, I have to review the logic in this area, I can tell you
> tomorrow
A good short test is to run explain analyze on the query from the psql
command line. If it shows an execution time of significantly less than
what you get from you application, then it is likely that the real
problem is that your application is receiving the whole result set via
libpq and waiting for that. A cursor will solve that problem.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 15:45 me@alternize.com
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
1 sibling, 0 replies; 19+ messages in thread
From: me@alternize.com @ 2006-05-10 15:45 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: pgsql-performance
> is there a possibility for creating views or temp tables in memory to
> avoid disk io when user makes select operations?
you might also want to look into "materialized views":
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
http://www.varlena.com/varlena/GeneralBits/64.php
this helped us alot when we had slow queries involving many tables.
cheers,
thomas
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 20:54 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 20:54 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
Scott Marlowe wrote:
>On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:
>
>
>>Scott Marlowe wrote:
>>
>>
>
>
>
>>>What happens if you do this by declaring it as a cursor and then
>>>fetching the first row?
>>>
>>>
>
>
>
>>>
>>>
>>>
>>I do executeQuery(), for the resultSet I do next() and return one row,
>>but wait, I have to review the logic in this area, I can tell you
>>tomorrow
>>
>>
>
>
>A good short test is to run explain analyze on the query from the psql
>command line. If it shows an execution time of significantly less than
>what you get from you application, then it is likely that the real
>problem is that your application is receiving the whole result set via
>libpq and waiting for that. A cursor will solve that problem.
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
Yes, the difference between psql command line and application is 6
seconds to 40 seconds. It is
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I
use next() as a cursor
through the resultSet, but I fear this is not enough, do I have to use
createStatement(resultSetType,
resultSetConcurrency) respectively prepareStatement (resultSetType,
resultSetConcurrency) to
achieve the cursor behaviour?
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 21:01 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
1 sibling, 1 reply; 19+ messages in thread
From: Scott Marlowe @ 2006-05-10 21:01 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
On Wed, 2006-05-10 at 15:54, Thomas Vatter wrote:
> >
> Yes, the difference between psql command line and application is 6
> seconds to 40 seconds. It is
> exactly the step resultSet = excecuteQuery() that needs 40 seconds. I
> use next() as a cursor
> through the resultSet, but I fear this is not enough, do I have to use
> createStatement(resultSetType,
> resultSetConcurrency) respectively prepareStatement (resultSetType,
> resultSetConcurrency) to
> achieve the cursor behaviour?
Not sure. I don't use a lot of prepared statements. I tend to build
queries and throw the at the database. In that instance, it's done
like:
create cursor cursorname as select (rest of query here);
fetch from cursorname;
You can find more on cursors here:
http://www.postgresql.org/docs/8.1/interactive/sql-declare.html
Not sure if you can use them with prepared statements, or if prepared
statements have their own kind of implementation.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 21:08 Dave Dutcher <dave@tridecap.com>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
1 sibling, 1 reply; 19+ messages in thread
From: Dave Dutcher @ 2006-05-10 21:08 UTC (permalink / raw)
To: 'Thomas Vatter' <thomas.vatter@network-inventory.de>; +Cc: pgsql-performance
Are you using the Postgres JDBC driver? Or are you using an ODBC JDBC
driver? The Postgres specific driver is usually faster.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Thomas
Vatter
Sent: Wednesday, May 10, 2006 3:54 PM
To: Scott Marlowe
Cc: Tino Wildenhain; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] in memory views
Scott Marlowe wrote:
On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:
Scott Marlowe wrote:
What happens if you do this by declaring it as a cursor and then
fetching the first row?
I do executeQuery(), for the resultSet I do next() and return one row,
but wait, I have to review the logic in this area, I can tell you
tomorrow
A good short test is to run explain analyze on the query from the psql
command line. If it shows an execution time of significantly less than
what you get from you application, then it is likely that the real
problem is that your application is receiving the whole result set via
libpq and waiting for that. A cursor will solve that problem.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Yes, the difference between psql command line and application is 6
seconds to 40 seconds. It is
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I
use next() as a cursor
through the resultSet, but I fear this is not enough, do I have to use
createStatement(resultSetType,
resultSetConcurrency) respectively prepareStatement (resultSetType,
resultSetConcurrency) to
achieve the cursor behaviour?
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 21:18 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 21:18 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance
Dave Dutcher wrote:
> Are you using the Postgres JDBC driver? Or are you using an ODBC JDBC
> driver? The Postgres specific driver is usually faster.
I'm using the postgres driver
regards
tom
>
>
>
> -----Original Message-----
> *From:* pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] *On Behalf Of
> *Thomas Vatter
> *Sent:* Wednesday, May 10, 2006 3:54 PM
> *To:* Scott Marlowe
> *Cc:* Tino Wildenhain; pgsql-performance@postgresql.org
> *Subject:* Re: [PERFORM] in memory views
>
> Scott Marlowe wrote:
>
>>On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:
>>
>>
>>>Scott Marlowe wrote:
>>>
>>>
>>
>>
>>
>>>>What happens if you do this by declaring it as a cursor and then
>>>>fetching the first row?
>>>>
>>>>
>>
>>
>>
>>>>
>>>>
>>>>
>>>I do executeQuery(), for the resultSet I do next() and return one row,
>>>but wait, I have to review the logic in this area, I can tell you
>>>tomorrow
>>>
>>>
>>
>>
>>A good short test is to run explain analyze on the query from the psql
>>command line. If it shows an execution time of significantly less than
>>what you get from you application, then it is likely that the real
>>problem is that your application is receiving the whole result set via
>>libpq and waiting for that. A cursor will solve that problem.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>>
>>
> Yes, the difference between psql command line and application is 6
> seconds to 40 seconds. It is
> exactly the step resultSet = excecuteQuery() that needs 40
> seconds. I use next() as a cursor
> through the resultSet, but I fear this is not enough, do I have to
> use createStatement(resultSetType,
> resultSetConcurrency) respectively prepareStatement
> (resultSetType, resultSetConcurrency) to
> achieve the cursor behaviour?
>
> regards
> tom
>
--
Mit freundlichen Grüßen / Regards
Vatter
Network Inventory Software
Sun Microsystems Principal Partner
www.network-inventory.de
Tel. 030-79782510
E-Mail thomas.vatter@network-inventory.de
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 21:24 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 21:24 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
Scott Marlowe wrote:
>On Wed, 2006-05-10 at 15:54, Thomas Vatter wrote:
>
>
>
>>>
>>>
>>>
>>Yes, the difference between psql command line and application is 6
>>seconds to 40 seconds. It is
>>exactly the step resultSet = excecuteQuery() that needs 40 seconds. I
>>use next() as a cursor
>>through the resultSet, but I fear this is not enough, do I have to use
>>createStatement(resultSetType,
>>resultSetConcurrency) respectively prepareStatement (resultSetType,
>>resultSetConcurrency) to
>>achieve the cursor behaviour?
>>
>>
>
>Not sure. I don't use a lot of prepared statements. I tend to build
>queries and throw the at the database. In that instance, it's done
>like:
>
>create cursor cursorname as select (rest of query here);
>fetch from cursorname;
>
>You can find more on cursors here:
>
>http://www.postgresql.org/docs/8.1/interactive/sql-declare.html
>
>Not sure if you can use them with prepared statements, or if prepared
>statements have their own kind of implementation.
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>
>
Yes, I have used embedded sql and create cursor, fetch before I started
with jdbc, seems that
I have to find out if new jdbc has a better way than simply resultSet =
statement.executeQuery().
regards
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 22:16 Kris Jurka <books@ejurka.com>
parent: Thomas Vatter <thomas.vatter@network-inventory.de>
0 siblings, 1 reply; 19+ messages in thread
From: Kris Jurka @ 2006-05-10 22:16 UTC (permalink / raw)
To: Thomas Vatter <thomas.vatter@network-inventory.de>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
On Wed, 10 May 2006, Thomas Vatter wrote:
> Yes, the difference between psql command line and application is 6
> seconds to 40 seconds. It is exactly the step resultSet =
> excecuteQuery() that needs 40 seconds. I use next() as a cursor through
> the resultSet, but I fear this is not enough, do I have to use
> createStatement(resultSetType, resultSetConcurrency) respectively
> prepareStatement (resultSetType, resultSetConcurrency) to achieve the
> cursor behaviour?
http://jdbc.postgresql.org/documentation/81/query.html#query-with-cursor
Kris Jurka
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: in memory views
@ 2006-05-10 23:08 Thomas Vatter <thomas.vatter@network-inventory.de>
parent: Kris Jurka <books@ejurka.com>
0 siblings, 0 replies; 19+ messages in thread
From: Thomas Vatter @ 2006-05-10 23:08 UTC (permalink / raw)
To: Kris Jurka <books@ejurka.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
Kris Jurka wrote:
>
>
> On Wed, 10 May 2006, Thomas Vatter wrote:
>
>> Yes, the difference between psql command line and application is 6
>> seconds to 40 seconds. It is exactly the step resultSet =
>> excecuteQuery() that needs 40 seconds. I use next() as a cursor
>> through the resultSet, but I fear this is not enough, do I have to
>> use createStatement(resultSetType, resultSetConcurrency) respectively
>> prepareStatement (resultSetType, resultSetConcurrency) to achieve the
>> cursor behaviour?
>
>
> http://jdbc.postgresql.org/documentation/81/query.html#query-with-cursor
>
> Kris Jurka
I was just returning to my mailbox to report success, I was just a bit
faster than your e-mail, I have found the fetchSize function, it
reduces the delay to 6 seconds. thanks a lot to all who helped, this was
really great support, I am glad that the problem is solved
tom
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2006-05-10 23:08 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-10 09:18 in memory views Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 09:35 ` Tino Wildenhain <tino@wildenhain.de>
2006-05-10 09:55 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 10:08 ` Tino Wildenhain <tino@wildenhain.de>
2006-05-10 10:43 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 11:57 ` Tino Wildenhain <tino@wildenhain.de>
2006-05-10 12:23 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 15:11 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-10 15:41 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 15:45 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-10 20:54 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 21:01 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-10 21:24 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 22:16 ` Kris Jurka <books@ejurka.com>
2006-05-10 23:08 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 21:08 ` Dave Dutcher <dave@tridecap.com>
2006-05-10 21:18 ` Thomas Vatter <thomas.vatter@network-inventory.de>
2006-05-10 15:45 ` me@alternize.com
2006-05-10 11:12 Re: in memory views Hakan Kocaman <Hakan.Kocaman@digame.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox