Hello, in most situations with joins, it's a good choice to do a "Optimize Statistic", if you had big changes in the sizes of your tables. On this statistic information the optimizer of the kernel decides/optimize his search and join strategy.
regards, Kolja -----Urspr�ngliche Nachricht----- Von: [EMAIL PROTECTED] [mailto:sapdb.general-admin@;listserv.sap.com]Im Auftrag von Rupsch, Alexander ECG Gesendet: Dienstag, 22. Oktober 2002 16:33 An: SAPDB Mailinglist (E-Mail) Betreff: SAPDB slow on joins? Hi, I have to evaluate free database for my company. So I picked up SAPDB an PostgreSQL and did some benchmarks... The results showed, to my surprise, that the SAPDB sometimes seems to be significant slower than PostgresSQL. About the test: * I used the selection part of the iX-SQL Benchmark (www.heise.de). Our application mainly depends on selections, that's why I only used selections. I testet in PHP (DB access: SAPDB = ODBC, PGSQL = native) * the data: => one table with ints (definition below) => 1.000.000 data entries (the same in both databases) * the machine: => AMD Athlon, Single CPU at 800Mhz, 256MB => OS: Linux Suse 8.0, iODBC, PHP 4.1.0 => sapdb 7.3 no modifications ,no tuning, I used the creation script for the demo database and only changed the name and increased the database size to 400MB (log 80MB) (after importing the data it was 35% filled) => postgresql 7.2, no modification or tuning * sugesstions?? well the test covers only a short range of the hole thing (no character, only one table etc.), but isn't it a little to different? * The result: (in sec) Select PostgreSQL SAPDB [... some uninteresting selects deleted ....] select count(*) from bench b1, bench b2 where b1.k100k = 49 and b1.k250k = b2.k500k 4 510 select count(*) from bench b1, bench b2 where b1.k40k = 49 and b1.k250k = b2.k500k 4 516 select count(*) from bench b1, bench b2 where b1.k10k = 49 and b1.k250k = b2.k500k 4 539 select count(*) from bench b1, bench b2 where b1.k1k = 49 and b1.k250k = b2.k500k 5 530 select count(*) from bench b1, bench b2 where b1.k100 = 49 and b1.k250k = b2.k500k 16 548 select count(*) from bench b1, bench b2 where b1.k100k = 49 and b1.k250k = b2.k500k and b2.k25 = 19 3 5 select count(*) from bench b1, bench b2 where b1.k40k = 49 and b1.k250k = b2.k500k and b2.k25 = 19 3 5 select count(*) from bench b1, bench b2 where b1.k10k = 49 and b1.k250k = b2.k500k and b2.k25 = 19 3 6 select count(*) from bench b1, bench b2 where b1.k1k = 49 and b1.k250k = b2.k500k and b2.k25 = 19 4 5 select count(*) from bench b1, bench b2 where b1.k100 = 49 and b1.k250k = b2.k500k and b2.k25 = 19 3 6 * the table definition (from pgsql, sapdb similar): CREATE TABLE "bench" ( "kseq" int4 NOT NULL, "k500k" int4, "k250k" int4, "k100k" int4, "k40k" int4, "k10k" int4, "k1k" int4, "k100" int4, "k25" int4, "k10" int4, "k5" int4, "k4" int4, "k2" int4, CONSTRAINT "bench_pkey" PRIMARY KEY ("kseq") ) WITH OIDS; * One line of the date which was imported: "17","159827","216595","31784","37419","9869","424","19","17","4","5","3","2 " Any comments?? _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
