Hi Christain, Thanks very much for your reply.
> With KIOSKS and FORMS having one row each, the index is almost useless. Kiosk_Id and Form_Id are not at all selective. All reports have the option of filtering on kiosk or forms, hence the reason for the indexes. > 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. Not Null values should be pretty low. Would you put a joint index on ExecTime and Copies, or are 2 indexes better? > 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. I didn't realise that a join will automatically order it too! I guess that makes sense. Unfortunately, I need the ordering done by Upper case - for next version I will add UpperTitle and UpperName columns that are automatically updated, but I'm stuck without them at the moment. I guess as this is a select query, there is no way to get rid of the "result is copied" ie keep it all in memory? > 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. Unfortunately I only need it rarely, so buffereing isn't going to help much! > 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. Thanks for your help. I have found some indexes I can add which help. I can't seem to get the estimated result in the explain less than about 3500 though. Guess I'm stuck there. Cheers, David "Knappke, Christian" <[EMAIL PROTECTED]> on 17/06/2003 03:59:57 AM To: [EMAIL PROTECTED] cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject: RE: Is performance really this bad?! Experiences, PLEASE! 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
