Обсуждение: MS Access out-performs PostgreSQL 7?

On Tue, 18 Jul 2000, Joseph Sircy wrote:

> I would like to know why access is outperforming pgsql in a simple
> database setup. I have the exact same databases, one in access, the
> other in pgsql, on the same server, a linux box running redhat 6.2.

You can run MS Access under Linux?

PostgreSQL side of things ... depending on what your queries look like, do
you have appropriate INDEXes setup?  Performed a VACUUM ANALYZE?

I would like to know why access is outperforming pgsql in a simple database setup. I have the exact same databases, one in access, the other in pgsql, on the same server, a linux box running redhat 6.2.They are being accessed by an asp page running IIS 4 on a NT box ( i cant change that).The connection between the 2 servers is not the issue, we have made sure of that.Go to http://www.dnhawaii.com/data_test.asp and see for yourself.One thing I haven't done is post the source code for the page, but all are simple SELECT or UPDATE sql commands.Any comments would greatly be appreciated, as I may have to explain to higher ups why we NEED to run linux!!

I assume your units a are in seconds.    If so, these numbers seem reasonable; PostgreSQL is doing a bit more than work than Access.    Your test for a LIGHT load indicates this.    You may want to consider how each of these perform under a heavy load.   Or not, if salability is not at issue.

For most of the work I have done using  web based transactions, less than a second response times, are more than good enough.   Most users cannot discriminate these time differences.     Its the other features of the two databases that make the difference.
 
 

In reponse to all of your replies,
It very well could be user error! Please understand that our test in
comparing access does not mean that access is our other choice, it just
happened to be available.
I have installed 7.0.2 from the website, it is not the version that comes
with redhat.
When I said access was "on the same server" I didn't mean it was actually
"running" under redhat, it is in fact just on the same drive as psql. This
was done to clear up communication/performance issues with the servers.
I am using ODBC to connect in my asp scripts, and I think this may be the
problem.
Maybe I need to try a different ODBC driver, or if anyone knows of a better
way to connect in asp scripts to psql, please let me know.
I would also like to note that if this was in a real world app, i would use
stored procedures for this, but since access cannot do this, it didnt do it
for this test.

I am a believer in Linux, and the open source revolution, and I didnt mean
for this to be one of those "NT vs Linux" posts!

Joe Sircy
Webmaster
dnhawaii.com
----- Original Message -----
From: "Gavin M. Roy" <gmr@readysetnet.com>
To: "The Hermit Hacker" <scrappy@hub.org>
Cc: "Joseph Sircy" <webmaster@dnhawaii.com>; "pgsql list"
<pgsql-admin@postgresql.org>
Sent: Tuesday, July 18, 2000 8:59 AM
Subject: Re: [ADMIN] MS Access out-performs PostgreSQL 7?


> Red Hat 6.2 isn't the best platform for speed either.  You may want to
look at
> Slackware or Debian.
>
> Also such a blanket statement can be viewed as antagonistic.  Access is
noted
> for being bloated, crappy, and slow.  PostgresQL is not.  I would say user
> error / ignorance may be the issue here.
>
> Also note that a Red Hat installed version of PostgresQL is not the same
as
> you downloading it, compiling it, and customizing it to your exact system.
If
> I were in your shoes I would do two things:
>
> 1) download 7.0.2, compile and install it.
> 2) make sure you're comparing apples to apples.  Microsoft Access query
syntax
> isn't exactly the same as ANSI SQL (I believe this to be the case, I know
it
> is the case with SQL Server).
> 3) What types of queries are you doing, and how big is your database.  If
> you're querying against 10 rows you may have one effect, vrs 10,000.
> 4) Seriously look at what your trying to do.  Comparing a solution like
Access
> to PostgresQL is like comparing a Yugo to a Mercedes.  Access has nice
tools
> and "features" but is a bloated piece.
>
> Gavin
>
> The Hermit Hacker wrote:
>
> > On Tue, 18 Jul 2000, Joseph Sircy wrote:
> >
> > > I would like to know why access is outperforming pgsql in a simple
> > > database setup. I have the exact same databases, one in access, the
> > > other in pgsql, on the same server, a linux box running redhat 6.2.
> >
> > You can run MS Access under Linux?
> >
> > PostgreSQL side of things ... depending on what your queries look like,
do
> > you have appropriate INDEXes setup?  Performed a VACUUM ANALYZE?
>
> --
> Gavin M. Roy, Director of Research & Development, C.T.O.
> Ready Set Net (formerly PathFinders)
> ----------------------------------------------------------------
> The information transmitted is intended only for the person or entity to
which
> it is addressed and may contain confidential and/or privileged material.
Any
> review, retransmission, dissemination or other use of, or taking of any
action
> in reliance upon, this information by persons or entities other than the
> intended recipient is prohibited. If you received this in error, please
> contact the sender and delete the material from any computer.
>

On Tue, 18 Jul 2000 21:52:54 Joseph Sircy wrote:
>I would also like to note that if this was in a real world app, i would
use
>stored procedures for this, but since access cannot do this, it didnt do
it
>for this test.

Use the stored procedures if it will speed up your application. If you don't
need or use features like this, then you may as well stick to Access.

I'm sure someone else has already mentioned that you should check the
application's performance under high peak loads and stability over time.

--
Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/>
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26  C484 A42A 60DD 6C94 239D
Linux. The choice of a GNU Generation. <http://www.linux.org/>

Ok, once again let me make this clear...

We are not thinking of using access for our dataserver, your right, its NOT
a data SERVER. My question simply was, why does a program like access, which
is not a data server, outperform pgsql in our comparison??

I know if we loaded it down with large amounts  of data and 10s of thousands
of users, access wouldn't even think of keeping up, we are aware of this.

But.. in our tests, we have
1. a 400mhz web server, running IIS 4.0
2. a 400mhz database server running postgress AND has the access database on
it.
So its not a question of machine, its a question of software.
There are 4 tests done on each database each time the page loads.
1. insert a record of 10 rows into the databases
2. select * from the table (seems to be the longest test for pgsql)
3. update col 1 in every record
4. update col 6 in every record where col 1 = "some_criteria"

So thats it. REMEMBER, we are not going to use access! so please dont reply
about how access cant keep up in a production server enviroment, believe us,
we know!

Joe Sircy
Webmaster
dnhawaii.com

----- Original Message -----
From: "Jacopo Silva" <jacoposilva@tin.it>
To: <pgsql-admin@postgresql.org>
Cc: <webmaster@dnhawaii.com>
Sent: Wednesday, July 19, 2000 2:02 PM
Subject: Re: MS Access out-performs PostgreSQL 7?


> >I would like to know why access is outperforming pgsql in a simple
database
> setup.
> >I have the exact same databases, one in access, the other in pgsql,
>
> >on the same server, a linux box running redhat 6.2.
>              ^^^^^^^^^^
>
> I think there has been a lot of misunderstandings...
>
> Postgresql is a "database server".
> Basically it means that
> the data is accessed by the CPU of the server (the
> machine where you install postgres).
> All other machines (e.g. your NT box with ODBC drivers)
> just send reqeusts to the server
> and get back the data they needed.
> The cpu of the clients is not performing
> any task involved in managing the data, e.g.
> checking constraints, saving files to disk, ordering,
> maintaing indexes, searching, performing joins,
> transactions, etc.
>
> If you have thousands of records and you want
> to run a complex query, with many joins, it doesen't
> matter to you if you are on a old 486 client or
> on a newer PIII.  All the work is done by the
> server, you just get the data you asked for.
>
> There are many database server available, commercial
> or OpenSource.  Microsoft's product is
> "MS SQL server" and it runs under NT.
>
> Access is not a database server.  It is just
> a file where you store your data.
> If you put the file on your hard disk or
> elsewhere in the network it does not matter.
> It is always your pc that is opening that file,
> searching for data, maintaining file integrity,
> saving again all the data when you close the
> file.
>
> If many user are opening the same Access file
> simultaneously, they are all managing the data
> in their own machine.  So if one box
> is an old 486 it will perform queries slowly
> than in another box with a newer PIII.
>
> And whether you access the file "straightly" or
> via ODBC Access driver it does not change.
> It is always your box that is running the database
> system.
>
> So first of all you cannot compare performances
> of Access on one machine and of postgres on
> another machine if you do not publish the
> differences between configurations of the boxes.
>
> But the most important difference is that
> Access is not a database server system.
> Try to guess what could happen when
> tens of users are accessing the same
> access file...  It tries to keep track
> of what users are doing and to
> manage locking of tables or rows, but...
> (that's what the .ldb file is for).
>
> So if you are in a single-user environment,
> if you don't have "mission critical" data,
> if you don't have many tables and if you
> have just few rows, if you do not want
> to know how to manage a SQL-based
> database system and if you prefer
> to user the "database creation wizards"
> go on and install Access on your Win9x box.
>
> Otherwise... give postgresql a try and you'll love it!
>
>
> Bye,
> Jacopo S.
>
>
>

Ok.
So if it is clear that Access is not a database server while postgres
is, speed is not so much a problem.

I mean that you are right.
You should anyway try optimizing your pgsql
configuration (e.g. -S , -B, -F options).
But still many tests will show that
Access is faster in performing some
operations: insert or select, etc.
I tried similar tests months ago
and I had more or less the same
results.  You can find
benchmarks between many different
database systems at
http://www.mysql.com/crashme/choose.php
(they compare features and they have
some graphics of performances)

Time ago, in this list, someone said
that you have on one side of the
databases systems the dbm libs: fastest
straight access to data, no concurrency,
no nothing...
At the other side you have pgsql or similar:
sometimes slower access to data, but many
complex features.


I think you should try to understand very
well what use of the database your application
will do.
If you think that you have just few insertions
and update of data and many reads with
select, and no transactions go to
a faster and less complex sql server.
Many sites with similar use of data are
using mysql that is much better (and faster) than
Access, and is still a server system.
Tucows and many other software download sites are
using mysql. Download sites have sporadic (if compared
to other operations, of course) updates and
thousands of reads.

If you need a more complex data management,
if you plan to implement transactions, if you
have many users inserting, updating and
deleting data, and not only reading data most
of times, choose pgsql.

Bye,
Jac

P.S.:

> Ok, once again let me make this clear...
>
> We are not thinking of using access for our dataserver, your right, its
NOT
> a data SERVER. My question simply was, why does a program like access,
which
> is not a data server, outperform pgsql in our comparison??
You want a simple answer?
Because it is simpler and it doesn't have many of the pgsql features...

On Tue, Jul 18, 2000 at 09:52:54AM -1000, Joseph Sircy wrote:
> Maybe I need to try a different ODBC driver, or if anyone knows of a better
> way to connect in asp scripts to psql, please let me know.

I know you said you needed to use MS-IIS, but do you also need to use
asp?

php has a feature called persistent database connections - it basicly
keeps the databaseconnections open all the time, and don't have to
reopen it for every webpage. It can improve performance a lot if you
have a lot of pages doing just a little bit of SQL.

(and you can run php under MS-IIS - there is even a tool to convert asp
to php).


--
Ragnar Kjorstad

> > Maybe I need to try a different ODBC driver, or if anyone
> knows of a better
> > way to connect in asp scripts to psql, please let me know.
>
> I know you said you needed to use MS-IIS, but do you also need to use
> asp?
>
> php has a feature called persistent database connections - it basicly
> keeps the databaseconnections open all the time, and don't have to
> reopen it for every webpage. It can improve performance a lot if you
> have a lot of pages doing just a little bit of SQL.

ODBC supports native connection pooling, which is used by IIS. It just has
to be enabled :-)
Go into the ODBC Datasource Administrator, under the tab "Connection
Pooling", and double-click on PostgreSQL. Set it to enabled, and set a good
timeout value. (Default will work most of the time)

This is a *must* for good performance with any db, but with postgresql it's
even more so (it's more expensive to open a connection in postgres than it
is in for example MS SQL. Connection pooling makes that difference almost
completely go away).


> (and you can run php under MS-IIS - there is even a tool to
> convert asp to php).
This is always an option anyway, but not required for this case :-)

//Magnus

> -----Mensagem original-----
> De: pgsql-admin-owner@hub.org [mailto:pgsql-admin-owner@hub.org]Em nome
> de Ragnar Kjørstad
> Enviada em: quinta-feira, 20 de julho de 2000 17:22
> Para: Joseph Sircy
> Cc: Gavin M. Roy; The Hermit Hacker; pgsql list
> Assunto: Re: [ADMIN] MS Access out-performs PostgreSQL 7?
>
>
> On Tue, Jul 18, 2000 at 09:52:54AM -1000, Joseph Sircy wrote:
> > Maybe I need to try a different ODBC driver, or if anyone knows
> of a better
> > way to connect in asp scripts to psql, please let me know.
>
> I know you said you needed to use MS-IIS, but do you also need to use
> asp?
>
> php has a feature called persistent database connections - it basicly
> keeps the databaseconnections open all the time, and don't have to
> reopen it for every webpage. It can improve performance a lot if you
> have a lot of pages doing just a little bit of SQL.
>
> (and you can run php under MS-IIS - there is even a tool to convert asp
> to php).
>
>
> --
> Ragnar Kjorstad
>

Where can I find this tool to convert from asp to php?

-----
Paulo Roberto Siqueira
paulo.siqueira@usa.net
Database Administrator
Goiania - GO - Brazil

On Fri, Jul 21, 2000 at 11:15:02AM -0300, Paulo Roberto Siqueira wrote:
> > (and you can run php under MS-IIS - there is even a tool to convert asp
> > to php).
>
> Where can I find this tool to convert from asp to php?

[ totally OT, but I'm CCing to the list since you're the third person to
  ask ]

http://asp2php.naken.cc/

But I have never tried it myself, so I don't know how well it works.


--
Ragnar Kjorstad