pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedAccess and Postgres question.
3+ messages / 2 participants
[nested] [flat]
* Access and Postgres question.
@ 2000-07-29 02:49 Tim Uckun <tim@diligence.com>
0 siblings, 2 replies; 3+ messages in thread
From: Tim Uckun @ 2000-07-29 02:49 UTC (permalink / raw)
To: pgsql-interfaces
I am testing postgres 7.02 and ms-sql server as back ends to ms access. I
created identical databases containing three tables with about 1.2 , 1.6
and 3 million records each. The machine that is running SQL server is a
dual pIII zeon machine with raid 0 and 512 megs of ram. The postgres
machine is a dual pIII 500 with 256 megs of ram no raid but nice fast scsi
drive running red hat. Given this scenario I do expect postgres to be
slower then ms sql server. What I find very odd is that in some tasks it
actually faster (very slightly) like when doing simple select * from table
where primarykey=x. But when I do select * from very_big_table (I know it's
not such a good idea) it is insanely slow. On one test the table with 1.2
million records too 12 seconds in postgres and less then a second in sql
server!. I suspect that this has something to do with the ODBC driver
because I am having a hard time believing that postgres is that slow. I
suspect that the MS-SQL server ODBC driver is not actually pulling up ALL
the records but just a few at a time (and perhaps spinning off another
thread to fetch the rest). Can anybody confirm this?
----------------------------------------------
Tim Uckun
Mobile Intelligence Unit.
----------------------------------------------
"There are some who call me TIM?"
----------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Access and Postgres question.
@ 2000-07-31 05:10 Grant Finnemore <gaf@ucs.co.za>
parent: Tim Uckun <tim@diligence.com>
1 sibling, 0 replies; 3+ messages in thread
From: Grant Finnemore @ 2000-07-31 05:10 UTC (permalink / raw)
To: Tim Uckun <tim@diligence.com>; +Cc: pgsql-interfaces
Tim Uckun wrote:
> I am testing postgres 7.02 and ms-sql server as back ends to ms access. I
> created identical databases containing three tables with about 1.2 , 1.6
> and 3 million records each. The machine that is running SQL server is a
> dual pIII zeon machine with raid 0 and 512 megs of ram. The postgres
> machine is a dual pIII 500 with 256 megs of ram no raid but nice fast scsi
> drive running red hat. Given this scenario I do expect postgres to be
> slower then ms sql server. What I find very odd is that in some tasks it
> actually faster (very slightly) like when doing simple select * from table
> where primarykey=x. But when I do select * from very_big_table (I know it's
> not such a good idea) it is insanely slow. On one test the table with 1.2
> million records too 12 seconds in postgres and less then a second in sql
> server!. I suspect that this has something to do with the ODBC driver
> because I am having a hard time believing that postgres is that slow. I
> suspect that the MS-SQL server ODBC driver is not actually pulling up ALL
> the records but just a few at a time (and perhaps spinning off another
> thread to fetch the rest). Can anybody confirm this?
I would suggest that the MSSQL ODBC driver is using a cursor. For most of the
PostgreSQL interfaces that I know of, this behaviour is not the default - they
normally just obtain the entire set of results at start.
Although it is possible to use cursors in PostgreSQL, you need an open
transaction while referring to one. See the docs for more information.
Regards
Grant
>
> ----------------------------------------------
> Tim Uckun
> Mobile Intelligence Unit.
> ----------------------------------------------
> "There are some who call me TIM?"
> ----------------------------------------------
--
> Poorly planned software requires a genius to write it
> and a hero to use it.
Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za)
Software Engineer Universal Computer Services
Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein
Fax (+27)(11)339-3421 Johannesburg, South Africa
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Access and Postgres question.
@ 2000-08-01 02:06 Tim Uckun <tim@diligence.com>
parent: Tim Uckun <tim@diligence.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tim Uckun @ 2000-08-01 02:06 UTC (permalink / raw)
To: Byron Nikolaidis <byron.nikolaidis@home.com>; pgsql-interfaces
At 09:25 PM 7/31/00 -0400, you wrote:
>Do you have the "use declare/fetch" option enabled on the driver settings?
>This will cause the driver to use a cursor and fetch only a 100 rows at a
>time.
>
>Byron
Won't this force the recordset to be read only? My reading of the declare
statement indicates that cursors are read only. Oh how I wish I am wrong on
that!
----------------------------------------------
Tim Uckun
Mobile Intelligence Unit.
----------------------------------------------
"There are some who call me TIM?"
----------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-08-01 02:06 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-29 02:49 Access and Postgres question. Tim Uckun <tim@diligence.com>
2000-07-31 05:10 ` Grant Finnemore <gaf@ucs.co.za>
2000-08-01 02:06 ` Tim Uckun <tim@diligence.com>
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