Thread: 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.
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!!
Joe Sircy
Webmaster
dnhawaii.com
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?
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.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