Re: SELECT's take a long time compared to other DBMS

Expect that the Declare/Fetch only creates a forwardonly cursor, you can go
backwards thru the result set.

""Patrick Hatcher"" <PHatcher(at)macys(dot)com> wrote in message
news:OFAD2A2CF4(dot)499F8F67-ON88256D98(dot)00527BCB-88256D98(dot)00538130(at)fds(dot)com(dot)(dot)(dot)
>
> Relaxin,
> I can't remember during this thread if you said you were using ODBC or
not.
> If you are, then your problem is with the ODBC driver. You will need to
> check the Declare/Fetch box or you will definitely bring back the entire
> recordset. For small a small recordset this is not a problem, but the
> larger the recordset the slower the data is return to the client. I
played
> around with the cache size on the driver and found a value between 100 to
> 200 provided good results.
>
> HTH
> Patrick Hatcher
>
>
>
>
>
> "Relaxin" <noname(at)spam(dot)com>
> Sent by: To:
pgsql-performance(at)postgresql(dot)org
> pgsql-performance-owner(at)post cc:
> gresql.org Subject: Re:
[PERFORM] SELECT's take a long time compared to other DBMS
>
>
> 09/04/2003 07:13 PM
>
>
>
>
>
> Thank you Christopher.
>
> > Change fsync to true (you want your data to survive, right?) and
> > increase shared buffers to something that represents ~10% of your
> > system memory, in blocks of 8K.
>
> I turned it off just in the hope that things would run faster.
>
> > None of this is likely to substantially change the result of that one
> > query, however, and it seems quite likely that it is because
> > PostgreSQL is honestly returning the whole result set of ~100K rows at
> > once, whereas the other DBMSes are probably using cursors to return
> > only the few rows of the result that you actually looked at.
>
> Finally, someone who will actually assume/admit that it is returning the
> entire result set to the client.
> Where as other DBMS manage the records at the server.
>
> I hope PG could fix/enhance this issue.
>
> There are several issues that's stopping our company from going with PG
> (with paid support, if available), but this seems to big the one at the
top
> of the list.
>
> The next one is the handling of BLOBS. PG handles them like no other
> system
> I have ever come across.
>
> After that is a native Windows port, but we would deal cygwin (for a very
> little while) if these other issues were handled.
>
> Thanks
>
>
>
>
>
> "Christopher Browne" <cbbrowne(at)acm(dot)org> wrote in message
> news:m3fzjc58ll(dot)fsf(at)chvatal(dot)cbbrowne(dot)com(dot)(dot)(dot)
> > A long time ago, in a galaxy far, far away, "Relaxin" <noname(at)spam(dot)com>
> wrote:
> > >> Have you changed any of the settings yet in postgresql.conf,
> > >> specifically the shared_buffers setting?
> > >
> > > fsync = false
> > > tcpip_socket = true
> > > shared_buffers = 128
> >
> > Change fsync to true (you want your data to survive, right?) and
> > increase shared buffers to something that represents ~10% of your
> > system memory, in blocks of 8K.
> >
> > So, if you have 512MB of RAM, then the total blocks is 65536, and it
> > would likely be reasonable to increase shared_buffers to 1/10 of that,
> > or about 6500.
> >
> > What is the value of effective_cache_size? That should probably be
> > increased a whole lot, too. If you are mainly just running the
> > database on your system, then it would be reasonable to set it to most
> > of memory, or
> > (* 1/2 (/ (* 512 1024 1024) 8192))
> > 32768.
> >
> > None of this is likely to substantially change the result of that one
> > query, however, and it seems quite likely that it is because
> > PostgreSQL is honestly returning the whole result set of ~100K rows at
> > once, whereas the other DBMSes are probably using cursors to return
> > only the few rows of the result that you actually looked at.
> > --
> > "cbbrowne","@","cbbrowne.com"
> > http://www3.sympatico.ca/cbbrowne/linuxdistributions.html
> > Rules of the Evil Overlord #14. "The hero is not entitled to a last
> > kiss, a last cigarette, or any other form of last request."
> > <http://www.eviloverlord.com/>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>