Hi, Thanks very much for the reply.
I have since changed the way I do the query - the one below "blows up" when I have 100 kiosks! Am VERY interested in tuning my database, but as my previous notes, I am really struggling to find how to do that. Please could you point me to some documentation, or give me some pointers? Do you have any comments on my previous email to the list (I'll forward it to you, too). Thanks!!! David "Janusz Jeczmionka" <[EMAIL PROTECTED]> on 12/06/2003 02:41:39 PM To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject: Query taking LOADS of extra room AND TIME Hi, I think you shoud change a little an application logic to avoid joins on the tables. I think it is not too difficult to rewrite following query: SELECT 'Total', SUM(CASE WHEN Kiosks.Name='bodie-macon 2' THEN Metrics.Copies ELSE 0 END) AS "bodie-macon 2", SUM(Metrics.Copies) as Total FROM Kiosks INNER JOIN Metrics ON Kiosks.ID=Metrics.Kiosk_Id WHERE ExecTime IS NOT NULL AND Copies IS NOT NULL in that way: select kiosk_id into :kiosk_id from kiosks where name = 'bodie-macon 2'; SELECT 'Total', SUM(CASE WHEN Kiosk_id= :kiosk_id THEN Copies ELSE 0 END) AS "bodie-macon 2", SUM(Copies) as Total FROM Metrics WHERE ExecTime IS NOT NULL AND Copies IS NOT NULL (I hope you do not have several kiosk_IDs for one Name, have you?) or even select kiosk_id, sum( Copies ) from metrics where exectime is not null and copies is not null group by kiosk_id an then sum results to get Total and to add 'Total' before printing results. All other statements can be rewrited that way. If you have an index on kiosk_id and form_id (for other queries) it works quite well. Of course I don't know if the tool you are using allows this. In my opinion allowing SQL to do all the job in ONE query is not always a good choice... BTW: tuning db is always a good practice :) Regards Janusz _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
