Hi David, > What kind of hardware are people out there employing to get good > performance?
I'm not a big fan of "throw hardware on it until it stops complaining..." ;-) Let's look at your tables and the query: on Jun 10 2003 you stated, that you have one row in KIOSKS, one row in FORMS and a million rows in METRICS. METRICS looks like: > CREATE TABLE metrics (Kiosk_Id INTEGER, Form_Id INTEGER, Copies INTEGER, > Pages INTEGER, Script_Id INTEGER, AutoFilled BOOLEAN, ExecTime TIMESTAMP) > // > CREATE INDEX metrics_kioskId_formId_exectime ON metrics (Kiosk_Id, Form_Id, ExecTime) With KIOSKS and FORMS having one row each, the index is almost useless. Kiosk_Id and Form_Id are not at all selective. > 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) The only selective fields you give here are ExecTime and Copies. How many NOT NULL values are there in METRICS? 1%, 50% or almost all? If there are few (less than 10..20%) NOT NULL values in ExecTime I'd recommend an index on ExecTime and Copies. The ORDER BY is senseless with unique Forms.Title and Kiosks.Name. But it tells the database to perform it anyway, thus the "RESULT IS COPIED" and a lot of temp space usage and sort activity is necessary. > -------------------------------------------------------------- > -------------------------------------------------------------- > KIOSK METRICS TABLE SCAN > 1053 You read all of the biggest table. A Table Scan makes sense if the table is (really) small or if you have enough memory, tempspace and patience :-) and you must read all of the table contents. In the latter case, I recommend that you buffer the result set in your application. > 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 > -------------------------------------------------------------- > -------------------------------------------------------------- Even with more than one row in KIOSKS and FORMS, the principle doesn't change a lot. Look at your data and the data distribution. Check, which data you need in your application. Formulate your queries according to the findings. Design your indexes throroughly. Buffer repeatedly needed results in your application. HTH Christian -- #include <std_disclaimer.h> /* The opinions stated above are my own and not necessarily those of my employer. */ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
