On Wed, 2003-06-11 at 00:08, [EMAIL PROTECTED] wrote: > (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.
Ok, so you've got a 80MB of cache and 64M of disk space allocated for your database. When the "join cache" is exhausted, SAPDB use your devspace to fill the gap. Given the tiny database you created, your devspace is exhausted, and the query aborts. You should probably use a slightly bigger data cache so that your join does not hit the disk. You _must_ also increase your devspace size to something more reasonable (256M for exemple). a 64MB database is just enough to load to the system tables, and should not be used for anything but a 'hello world' application. So your plan should be : - increase the datacache to 15000 or 20000 (so that your hit rate is 100%) (needs a db restart) - add a second devspace (128MB for exemple) (does not need a db restart) _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
