Re: Access and Postgres question.

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(at)ucs(dot)co(dot)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