Re: Performance advice
Debate over whether to tune the engine or the SQL first. Andrew Hamm said proper engine and hardware setup is independent of SQL tuning; Paul Watson starts from the server and works toward SQL. Andy Kent cut a report from 30 hours to 19 minutes by rewriting SQL alone, and Serge Rielau argued for design and SQL first. Consensus was that both matter; no single answer.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Andy Kent wrote: > Tune the SQL first. Always. Period. > > THEN look at the config. Nonsense. Setting up the engine properly (including the purchase of appropriate machinery) can be done independently of the SQLs. The faster the machinery and engine is, the less need there is to tune the SQLs. Both can be done. Neither should be neglected. A poorly tuned engine can cause a lot of SQL's to run much slower than they need, and no amount of SQL tuning will get around that. It will also waste a lot of programmer hours, including the need to debug. Which is more expensive on a large application? A few hours of engine tuning, or countless hours of SQL tuning while the useless sysadmin is down the pub getting loaded?
Andrew Hamm wrote: > > Andy Kent wrote: > > Tune the SQL first. Always. Period. > > > > THEN look at the config. > > Nonsense. Setting up the engine properly (including the purchase of > appropriate machinery) can be done independently of the SQLs. The faster the > machinery and engine is, the less need there is to tune the SQLs. Both can > be done. Neither should be neglected. A poorly tuned engine can cause a lot > of SQL's to run much slower than they need, and no amount of SQL tuning will > get around that. It will also waste a lot of programmer hours, including the > need to debug. Which is more expensive on a large application? A few hours > of engine tuning, or countless hours of SQL tuning while the useless > sysadmin is down the pub getting loaded? CDI had this argument a while back. I start with the server and work back via the engine and then the SQL. -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
At a site I was on recently I got a report down from 30 hours to 19 minutes by re-writing a piece of SQL. Not with a single ONCONFIG change. Not even with any changes to indexes. You are right, both config and SQL must be looked at. But short of doing something really silly like leaving buffers at 200 or sticking the whole thing on an uncached RAID 5 array, I don't see how you can do anything like the amount of damage with a dodgy config as you can with bad SQL (or bad indexing, or bad optimisation). The config that was posted might not have been perfect but it certainly wasn't disastrous. So I wouldn't expect tweaking it to buy more than an extra 10-20%. I suspect they're looking for a lot more than that. Andy "Andrew Hamm" <ahamm@mail.com> wrote in message news:<c6516j$7u5j7$1@ID-79573.news.uni-berlin.de>... > Andy Kent wrote: > > Tune the SQL first. Always. Period. > > > > THEN look at the config. > > Nonsense. Setting up the engine properly (including the purchase of > appropriate machinery) can be done independently of the SQLs. The faster the > machinery and engine is, the less need there is to tune the SQLs. Both can > be done. Neither should be neglected. A poorly tuned engine can cause a lot > of SQL's to run much slower than they need, and no amount of SQL tuning will > get around that. It will also waste a lot of programmer hours, including the > need to debug. Which is more expensive on a large application? A few hours > of engine tuning, or countless hours of SQL tuning while the useless > sysadmin is down the pub getting loaded?
Working for a hardware vendor I like the idea of throwing MIPS at a problem ;-) As an SQL Person I want to point out though that when you tune the design and the SQL first then you tune the engine to teh right design. If you do it the other way around you have tunes the goo and have to redo it when you tune the SQL. Example: LET i = 0; WHILE (i < 1000) LET b = (SELECT AVG(c1) FORM T1); LET i = i + 1; END WHILE If you start with the engine first you will see that it is important to keep the table T1 (or the index on C1) in the bufferpool (=?buy more memory?). Once you do that you may think you have a nicely tuned system because your hit ratio is magnificent. Couldn't be further from the truth. I have seen this happen first hand doing TPC-C. In reality you may actually encounter resistence to tuning the SQL because suddenly your engine health parameters get thrown of giving the false indication of regression. It is of course correct that the quick fix is usually to just buy a bigger box. IMHO the easiest way to improve performance of a specific area is to fix the App. The easiest way to improve overal performance is a bigger box. Starting from scratch: Start with the Design and SQL. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab