Обсуждение: Access and Postgres question.

Поиск
Список
Период
Сортировка

Access and Postgres question.

От
Tim Uckun
Дата:
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?"
----------------------------------------------


Re: Access and Postgres question.

От
Grant Finnemore
Дата:
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





Re: Access and Postgres question.

От
Tim Uckun
Дата:
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?"
----------------------------------------------