On Thu, 2003-06-12 at 00:51, [EMAIL PROTECTED] wrote: > Hi Thomas, > > Thanks very much for the reply! > > So...if I have enough RAM and set the cache high enough, I can avoid the > usage of temp space for the joins completely? And that will avoid the temp > space growing very large issues that we're hitting?
>From my sapdb experience, I would say yes. > > Is there a limit to this? Other than your ram ? no, I don't think so. Maybe an SAP guy can provide a more definitive answer. > > Appreciate your insight! > > Cheers, > > David > > > > > > Thomas Cataldo <[EMAIL PROTECTED]> on 11/06/2003 06:33:17 AM > > To: [EMAIL PROTECTED] > cc: [EMAIL PROTECTED], [EMAIL PROTECTED] > Subject: Re: Performance / space issues > > > 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
