Out application with pgsql (but I think the problem is zeoslib) is a little slower than ms-access ! We have good results with MySQL and Firebird (using zeoslib).
access ? really ?
what is the size of your dataset ?
i've seen an access application literally die, belly-up with like 15 minutes queries, and the data set size was tiny by postgresql standards !
If you do 10.000 inserts, each in its transaction, sure postgres is going to be slow as hell... IMHO you should check if your library isn't turning on autocommit and/or adding begin's and commit's behind your back.
You can make the following test :
CREATE TABLE dummy( id serial, data integer ); INSERT INTO dummy (data) VALUES (0); INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy; INSERT INTO dummy (data) SELECT data FROM dummy;
EXPLAIN ANALYZE INSERT INTO dummy (data) SELECT data FROM dummy;
Seq Scan on dummy (cost=0.00..1388.05 rows=68870 width=4) (actual time=0.084..1024.668 rows=65536 loops=1)
Total runtime: 2428.795 ms
With a vanilla IDE 7200rpm disk on Linux. If you get 10 seconds, there's a problem in your conriguration.
Now, a little demonstration of MySQL, with an half-completed query :
mysql> create table test (id integer); Query OK, 0 rows affected (0.03 sec)
mysql> insert into test (id) values (1),(2),(3),(3),(4),(5); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0
mysql> create table testu (id integer, unique(id)); Query OK, 0 rows affected (0.00 sec)
mysql> insert into testu select * from test; ERROR 1062: Duplicate entry '3' for key 1 mysql> select * from testu; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
--- Now, even better
mysql> delete from testu; Query OK, 3 rows affected (0.00 sec)
mysql> insert into testu (id) values (1),(2),(3),(3),(4),(5); ERROR 1062: Duplicate entry '3' for key 1 mysql> select * from testu; +------+ | id | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec)
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
