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
