(Holgar - please note ps at bottom!)
Hi,
I am trying to get my head around issues that affect performance and
database tempory space.
Can someone explain, please, how the cache_size affects it? Will a larger
cache_size always be better? Will a larger cache_size reduce the tempory
space required?
I am mainly concerned with a join that takes three tables - one with around
50 records, one with up to 500, and then the last one (record of printing)
which can grow upwards to a million or more.
To run a report on this, I have thrown away my complicated query, and am
going to do the following join:
SELECT Forms.Title, Forms.Id, Kiosk_Id, Kiosks.Name, Copies FROM Metrics
INNER JOIN Kiosks ON Metrics.Kiosk_Id = Kiosks.Id INNER JOIN Forms ON
Metrics.Form_Id = Forms.Id WHERE ExecTime IS NOT NULL AND Copies IS NOT
NULL ORDER BY UPPER(Forms.Title)
For 100,000 records in the metrics table, 500 in the forms, and 50 in the
kiosks, I get the following Explain result (after doing update stat *):
KIOSK METRICS TABLE SCAN
1058
KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE INDEXED COL.
15
ID (USED INDEX COLUMN)
KIOSK RESULT IS COPIED , COSTVALUE IS
10234
The data is around 18 MB, and apparently this join is going to take 80MB! Is that
normal?!
I am running on PIII 600 machine, with 384MB RAM. My database is 64MB, made up of one
volume, and data is around 18 MB. I am using the default
cache size of 10000.
When I run the above in the Analyzer I get some W1 warnings:
* W1 Catalog cache hitrate (SQL Pages) 87.84%, 154 of 1266 accesses failed
CON: Cat_Hit < 90 && ( SQL_Cmds ) > INTERVAL
VAL: 87.84 < 90 && ( 332 ) > 6
When I increase up to a million records, I get lots of W3 messages regard catalog and
data cache hitrate.
Can someone point me in the right direction to help me optimize this the best, please?
Many thanks,
David
PS Holgar - my situation has changed somewhat now...having thrown away the
complicated query! The explain for the query you gave me is as follows:
KIOSK KIOSKS TABLE SCAN
1
KIOSK METRICS METRICS_KIOSKID_FORMID_EXECTIME JOIN VIA RANGE OF MULTIPLE INDEXED
COL. 1058
KIOSK_ID (USED INDEX COLUMN)
KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE INDEXED
COL. 15
ID (USED INDEX COLUMN)
KIOSK RESULT IS COPIED , COSTVALUE
IS 10342
Worse than above!!!!
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
