Forum OpenACS Q&A: Re: Monitoring and Controlling Queries
Unfortunately, in the real world, most of these performance and development problems probably tend to compound. If you have mediocre to poor developers writing lousy SQL queries, the data model underneath is probably substandard as well, any caching scheme they come up with in order to cope with the slow queries is probably also going to be lousy, the information and explanations they convey back to the client to help him make decisions will also be poor, etc...
Fortunately my personal observance of such phenomena has been limited and from a distance, but I have seen that a clueless client plus clueless and/or somewhat dishonest developers can easily conspire to turn the simplest project into a near insurmountable obstacle. (Note that this is just a general observation. I'm not saying that was the case with your client! In fact I doubt it could be, as his original question sounds too knowledgeable for that.)
Yeah, I agree with you, if the query really is going to stay slow for a good reason, you'll want to use or mix these different kinds of locking or caching strategies depending on your specific application.
Some of the infrastructure to make that easier is already there in OpenACS. I haven't checked lately so I can't you exactly what in detail, but people have definitely been improving those tools off and on. E.g.: The Developer Support page is really handy for query profiling. Simple one-off cacheing is often very easy and quick to do with just ad_memoize or the nsv commands. Don wrote a caching layer integrated into the db_* for one of his clients which I think is in the contrib CVS. Maybe more, I dunno.
The client isn't an IT professional but is a very savvy finance/mangement bloke and so is led by personal experience and information provided by 'professionals'. As I understand it, the particular report was running a query against a table of sales transactions and it didn't scale well under Tomcat/Postgres. In the first few months it was fine but now after a couple of years it can take several minutes to return and even, I'm told, cause IE to timeout waiting for the page to return. The problem is that they were under the illusion that if they hit the Stop button on the browser it would stop the request and kill the query. Not an unreasonable conclusion if you have no idea how the process occurs, afterall if you kill a report in Access then its dead. So if they think the first request has died and isn't going to come back they press Stop and then request the report again. Now of course there are two queries running so it takes even longer so they hit stop again and so on eventually bringing everything to a fullstop.
I think he's ok now with what we've told him of the way in which OpenACS works but the question got me thinking along these lines. Thankfully I've not needed the solutions but its good to know they're there just in case.