Postgres Pro
Downloads
Home   >   mailing lists

Thread: What is the fastest way to get a resultset

What is the fastest way to get a resultset

From
"Bupp Phillips"
Date:
I'm very new to Postgresql, so don't beat me up to bad if you see a problem,
just Inform me what I've done wrong.

I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB RAM 10GB
of Free space 800 Mhz, using the ODBC driver 7.03.01.00.

I have a table that has 103,000 records in it (record size is about 953
bytes) and when I do a select all (select * from <table>) it takes a
whopping 30 secs for the data to return!!

SQLServer on the other hand takes 6 secs, but you can also use what is
called a firehose cursor, which will return the data in < 1 sec.

I have done everything that I know how to speed this up, does anyone have
any advise?

Thanks

Re: What is the fastest way to get a resultset

From
"scott.marlowe"
Date:
On Sun, 24 Aug 2003, Bupp Phillips wrote:

> I'm very new to Postgresql, so don't beat me up to bad if you see a problem,
> just Inform me what I've done wrong.
>
> I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB RAM 10GB
> of Free space 800 Mhz, using the ODBC driver 7.03.01.00.
>
> I have a table that has 103,000 records in it (record size is about 953
> bytes) and when I do a select all (select * from <table>) it takes a
> whopping 30 secs for the data to return!!
>
> SQLServer on the other hand takes 6 secs, but you can also use what is
> called a firehose cursor, which will return the data in < 1 sec.
>
> I have done everything that I know how to speed this up, does anyone have
> any advise?

Have you tried declaring a cursor?

Re: What is the fastest way to get a resultset

From
Martijn van Oosterhout
Date:
On Sun, Aug 24, 2003 at 05:19:52PM -0700, Bupp Phillips wrote:
> I'm very new to Postgresql, so don't beat me up to bad if you see a problem,
> just Inform me what I've done wrong.
>
> I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB RAM 10GB
> of Free space 800 Mhz, using the ODBC driver 7.03.01.00.
>
> I have a table that has 103,000 records in it (record size is about 953
> bytes) and when I do a select all (select * from <table>) it takes a
> whopping 30 secs for the data to return!!

From what I remeber there is an option in the ODBC driver (or was it Access)
to use cursors when selecting from tables. The problem was that the boss
would attempt to open a 9 million row table and Access would attempt to load
the whole lot into memory (and fail).

With this option on it simply created a cursor and fetched just enough to
display on screen while you scroll around. Much better and faster.

When dealing with large resultsets, cursors are the way to go.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
Attachment