Hi,
I've posted a lot of performance-related questions to the list recently,
and have been surprised not to get many answers.
Apologies if I have posted too many - we are trying to desperately to
improve the performance we're seeing, and can't find any documentation etc.
to do so.
Or maybe I'm hitting a sore spot - is performance really this bad?
As my other posts explained, we've had to back off our initial expectation
of being able to run queries with up to a million records. We are now
running them against a table with 100,000 records, but still seeing lots of
tempory space usage, and poor speed (we are basically running the query I
am copying below). We've done the usual stuff - made sure our indexes are
okay, run Explain, run "update stat *" before the query etc. but we are
still struggling.
PLEASE could others give some of their experiences with performance with
this kind of numbers. Is what I am seeing the norm? With a basic join of
1000 - 100 - 100,000 records in the respective tables, we are using 62MB of
tempory space, and it is taking approximately 15 seconds.
I have a single data volume of 256MB, on a PIII 600MHz machine with 384MB
RAM, and have set the CACHE_SIZE to 20,000. All relevant join columns are
indexed etc..
Another alarming fact we have just discovered, is that with the default
cache size (10,000) we are seeing the database service take up 430MB of
RAM! With 20,000 it is over 500MB! Can this be normal?
As posted before, is the tempory space I am seeing the RESULT of the query
- ie the ResultSet created?
What kind of hardware are people out there employing to get good
performance?
Thanks very much in advance,
David
QUERY & EXPLAIN
----------------------------------------------------------------------------------------------------------------------------
SELECT Forms.Title AS rowHeading, Kiosks.Name AS colHeading, Metrics.Copies AS
calcField
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), UPPER(Kiosks.Name)
----------------------------------------------------------------------------------------------------------------------------
KIOSK METRICS TABLE SCAN
1053
KIOSK FORMS FORMS_FORMS_ID_AVAILABLE JOIN VIA RANGE OF MULTIPLE INDEXED COL.
29
ID (USED INDEX COLUMN)
KIOSK KIOSKS ID JOIN VIA KEY COLUMN
1
KIOSK RESULT IS COPIED , COSTVALUE IS
13195
----------------------------------------------------------------------------------------------------------------------------
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
