On 8/3/06, hansell baran <[EMAIL PROTECTED]> wrote:
Hi. I'm new at using PostgreSQL.
Where I work, all databases were built with MS Access. The Access files are
hosted by computers with Windows 2000 and Windows
XP. A new server is on its way and only Open Source Software is going to be
installed. The OS is going to be SUSE Linux 10.1
and we are making comparisons between MySQL, PostgreSQL and MS Access. We
installed MySQL and PostgreSQL on both SUSE and
Windows XP (MySQL & PostgreSQL DO NOT run at the same time)(There is one HDD
for Windows and one for Linux)
The "Test Server" in which we install the DBMS has the following
characteristics:
CPU speed = 1.3 GHz
RAM = 512 MB
HDD = 40 GB
The biggest table has 544371 rows(tuples?) with 55 rows. All fields are
float8. Only 1 is varchar(255) and 1 timestamp.
We query the MS Access databases through Visual Basic Programs and ODBC
Drivers. We made a Visual Basic program that uses ADO
to connect to ALL three DBMS using ODBC drivers.
When we run the following query "SELECT * FROM big_table", we get the
following resutls:
MS Access
- Execution time ~ 51 seconds (Depending on the client machine, it can go as
low as 20 seconds)
- Network Utilization ~ 80 Mbps (According to Windows Task Manager)
MySQL 5.0 (under Windows)
- Execution time ~ 630 seconds
- Network Utilization ~ 8 Mbps
PostgreSQL 8.1 (under Windows)
- Execution time ~ 290 seconds)
- Network Utilization ~ 13 Mbps
MS Access (under Linux. MS Access files are in the Linux computer which has
the SAMBA server running. The client computer has
a mapped network drive that conects to the Linux files.)
- Execution time ~ 55 seconds (Depending on the client machine, it can go as
low as 20 seconds)
- Network Utilization ~ 76 Mbps (According to Windows Task Manager)
MySQL 5.0(under Linux)
- Execution time ~ 440 seconds
- Network Utilization ~ 11 Mbps
PostgreSQL 8.1(under Linux)
- Execution time ~ 180 seconds)
- Network Utilization ~ 18 Mbps
Very different results are obtained if a the query "SELECT * from big_table
ORDER BY "some_column"". In this scenario
you have to be careful comparing access to mysql/postgresql in this
way because the architecture is different...these results are a bit
misleading. access can do some optimization tricks on very simple
queries, especially select * from bigtable becuase the result does not
have to be fully materialized and returned to the client.
PostgreSQL is faster than MS Access or MySQL by more than 100 seconds.
We have run many other queries (not complex, at most nesting of 5 inner
joins) and MS Access is always faster. We have seen
i find this really hard to believe. is your postgresql database
properly indexed and did you run analyze? do the standard
-performance thing, run the query in with explain analyze:
explain anaylze 5_table_join_query
and post the results to this list.
merlin
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
