Marcin, This exactly mimics our experience. We used very simple SQL with SAPDB for over 1 year, then upgraded our application to use a few simple joins. Performance was horrible. Way too much disk I/O, no matter what we did to cut it - always way more than other database systems.
SAPDB seems to like multi-field indexes, I suggest creating some to exactly match your WHERE close. But even with the right indexes in place - the JOIN performance is very poor compared to Microsoft Access or Microsoft SQL Server 2000 Standard Edition. Also, running UPDATE STATISTICS makes our entire database unusable for several hours - it locks tables (this is on 15,000 RPM RAID 0 drive array, with 6GB database, 2GB of RAM assigned to data_cache). There have been references to a new JOIN code branch in forthcoming versions of SAPDB. I believe it is supposed to eliminate the temporary table (disk I/O) usage on (some?) joins. Stephen Gutknecht currently in Seattle, Washington USA -----Original Message----- From: Marcin P [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 03, 2003 12:38 AM To: [EMAIL PROTECTED] Subject: very poor performance with joins? Hi! I use SAP DB for almost two years. Up till now I needed for rather simple structures and queries - rather small databases. But, a few days ago I tried to execute some more complicated (including joins) on a database which has following tables: TBL_SPRAWY - about 40 columns, 47.000 rows TBL_CLIENTS - about 20 columns, 4.000 rows TBL_USERS - about 20 columns, 90 rows TBL_WORK_TYPES - 5 columns, 40 rows And I try to execute following query (exactly, I have made a view with a the definition like this below): SELECT C.SKR_NAZWA AS C_SKR_NAZWA, C.NAZWA AS C_NAZWA, S.CREATE_DT AS S_CREATE_DT, S.RZECZNIK AS S_RZECZNIK, S.RODZAJ_ID AS S_RODZAJ_ID, S.NR_TECZ AS S_NR_TECZ, S.NR_ZGL AS S_NR_ZGL, S.SPR AS S_SPR, S.ID_KLI AS S_ID_KLI, S.STAN AS S_STAN, S.PCT AS S_PCT, S.NR_PIER AS S_NR_PIER, S.NR_REJ AS S_NR_REJ, S.NR_OCHR AS S_NR_OCHR, S.ZGLASZ AS S_ZGLASZ, S.D_ARCH AS S_D_ARCH, S.PRZYJ_OCHR AS S_PRZYJ_OCHR, S.PRZYJ_WYN_ZNAK AS S_PRZYJ_WYN_ZNAK, S.OP_OCHR_OPL_DO_DNIA AS S_OP_OCHR_OPL_DO_DNIA, S.OPIS AS S_OPIS, U.NAZWISKO AS U_NAZWISKO, W.WORK_TYPE_NAME AS W_WORK_TYPE_NAME FROM TBL_CLIENTS C, TBL_SPRAWY S, TBL_USERS U,TBL_WORK_TYPES W WHERE S.ID_KLI = C.CLIENT_ID AND S.RZECZNIK=U.USER_ID AND S.RODZAJ_ID = W.WORK_TYPE_ID I know, that returning all these rows to the client is not what should be done very often, but it is a must in my administrative application. Of course, I have defined all neded indices, primary keys, etc, so the EXPLAIN for this query returns: OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT O D T M C TABLE SCAN 107 S IDX_ID_KLI JOIN VIA INDEXED COLUMN 2813 U USER_ID JOIN VIA KEY COLUMN 1 W WORK_TYPE_ID JOIN VIA KEY COLUMN 1 RESULT IS COPIED , COSTVALUE IS 4320 And executing of this query, on my old PC (PIII600,192 MB of RAM) takes about SAP DB 7.3.0.40- 9m 58s, SAP DB 7.4.3.27 - 9m 51s, I have allocated about 10k of pages for DataCache. I was very surprised with this result, so I have exported this data to another database's and there are results DB time, cache MS SQL 2000 - 2s., 38 MB Access 97 - 12s., ? Interbase 6 - about 2 seconds for first 100 rows (returning all the remaining rows took about 2 minutes),40 MB So, I tried to do the same query, but instead of selecting all the columns I have only selected COUNT(*). And the result is: SAP DB - 2m 25s Access 97 - 9s MS SQL Server2k - 3s Interbase v6 - 7s It's worth to mention, that in SQL Server and MS Access I didn't define any indices nor primary keys, etc Anyone has any idea how to speed up SAP DB with this query? Is there something that I am missing? Any help would be appreciated, Marcin _______________________________________________ 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
