pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eirik Oeverby <ltning@anduin.net>
To: pgsql-performance@postgresql.org
Subject: Re: SELECT's take a long time compared to other DBMS
Date: Fri, 5 Sep 2003 06:54:24 +0200
Message-ID: <20030905065424.03e5c217.ltning@anduin.net> (raw)
In-Reply-To: <bj9565$2n3l$1@news.hub.org>
References: <bj611t$1o2g$1@news.hub.org>
	<bj7tlm$ngt$1@news.hub.org>
	<bj8h0o$dq4$1@news.hub.org>
	<m3fzjc58ll.fsf@chvatal.cbbrowne.com>
	<bj8rfn$1hep$1@news.hub.org>
	<1062729142.364.20.camel@tokyo>
	<bj9565$2n3l$1@news.hub.org>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I think the problem is the ODBC driver NOT using cursors properly even
if it should. The database itself is not doing anything it shouldn't do,
in fact it has all the needed functionality to handle this request in a
fast and effective way - just like any other respectable RDBMS.

I don't know what ODBC driver you are using, and how it is configrued -
and I never actually used PostgreSQL with ODBC myself. However in the
applications I have developed we DO use 'standardized' DB access
libraries, which work on just about any DBMS you throw them at. 
In our development system, which is running on a low-end dual P2-433mhz
box with IDE drives, we routinely test both simple queries as yours and
more complex ones, which at times returns several hundred
thousand (or sometimes even millions) of rows. And processing time is,
generally speaking, in range with what you are seeing on the other
DBMSes you have.

So if PG is indeed returning ALL the rows, it is because it is
explicitly told to by the ODBC driver, so you need to look there to find
the problem. Could there be some kind of connection parameters you are
overlooking, or is the driver too old? Just throwing out ideas here,
most likely you have already thought about it :)

Just thought I'd point out that this is NOT expected behaviour from PG
itself.

/Eirik

On Thu, 4 Sep 2003 21:59:01 -0700
"Relaxin" <noname@spam.com> wrote:

> > Is there a reason you can't use cursors (explicitely, or via ODBC if
> > it provides some glue on top of them) to keep the result set on the
> > server?
> >
> > http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-declare.html
> > http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-fetch.html
> 
> I can only use generally accepted forms of connectivity (ie. ODBC, ADO
> or OLEDB).
> This is what many of the people on the Windows side are going to need,
> because most of us are going to be converting from an existing already
> established system, such as Oracle, SQL Server or DB2, all of which
> have 1 or more of the 3 mentioned above.
> 
> 
> > > The next one is the handling of BLOBS.  PG handles them like no
> > > other
> system
> > > I have ever come across.
> >
> > Just FYI, you can use both the lo_*() functions, as well as simple
> > bytea/text columns (which can be very large in PostgreSQL).
> >
> > -Neil
> 
> I know PG has a ODBC driver (that's all I've been using), but it or PG
> just doesn't handle BLOBS the way people on the Windows side (don't
> know about Unix) are use too.
> 
> There is this conversion to octet that must be performed on the data ,
> I don't understand why, but I guess there was a reason for it long
> ago, but it seems that it can now be modified to just accept ANY byte
> you give it and then store it without any manipulation of the data.
> This will make Postgresql much more portable for the Windows
> developers...no need for any special handling for a data type that all
> large RDBMS support.
> 
> 
> Thanks
> 
> 
> 
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: if posting/reading
> through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that
>       your message can get through to the mailing list cleanly


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/WBcAdAvR8ct7fEcRAvZPAJ9FgkYxck6Yh5gPeomk8QgWraeV0gCfQF/v
CjyihMwTdrEZo2Y5YBwLVrI=
=Ng2I
-----END PGP SIGNATURE-----



view thread (34+ messages)  latest in thread

Message-ID: <20030905065424.03e5c217.ltning@anduin.net>
Permalink:  ../20030905065424.03e5c217.ltning@anduin.net/
Also on:    postgresql.org/message-id/20030905065424.03e5c217.ltning@anduin.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: ltning@anduin.net
  Subject: Re: SELECT's take a long time compared to other DBMS
  In-Reply-To: <20030905065424.03e5c217.ltning@anduin.net>
  References: <bj611t$1o2g$1@news.hub.org> <bj7tlm$ngt$1@news.hub.org> <bj8h0o$dq4$1@news.hub.org> <m3fzjc58ll.fsf@chvatal.cbbrowne.com> <bj8rfn$1hep$1@news.hub.org> <1062729142.364.20.camel@tokyo> <bj9565$2n3l$1@news.hub.org> <20030905065424.03e5c217.ltning@anduin.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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