Forum OpenACS Q&A: Saving execution plan with OACS 4 API?

Collapse
1: Saving execution plan with OACS 4 API?
Posted by Roberto Mello on 01/04/02 10:18 PM
Hi all,

I have this big query that I'm running with a db_multirow. In my
application, I may need to run that query very soon after being run
the first time, on the same script.

Does the ACS 4 DB API reuse the execution plan if the same query is
run more than once? Is there a way to save the execution plan? Tips
and hints appreciated.

Collapse
2: Response to Saving execution plan with OACS 4 API? (response to 1)
Posted by Jonathan Ellis on 01/04/02 11:04 PM
it's more dependant on your DB than the ACS... basically the DB can re-use the execution plan if (a) it is in a stored proc or (b) it is executed with bind variables.  The PG driver doesn't support bind variables, but the Oracle one does.
Collapse
3: Response to Saving execution plan with OACS 4 API? (response to 1)
Posted by Don Baccus on 01/05/02 03:52 AM
PG doesn't have bind variables and doesn't cache query plans.  The only way to do this currently is to wrap it in a SQL function (which can return rowsets) and call the SQL function.

A more general issue is that you can't cache db_multirows directly in the db/templating universe.  I've been thinking about how I might implement an easy way to cache pages built by such queries, for our second development round.

Also ... my guess is that the plan step is only a small part of the time spent building the page.  Executing the query and building the multi-row structure is probably taking a lot more time than planning.

Collapse
4: Response to Saving execution plan with OACS 4 API? (response to 1)
Posted by Roberto Mello on 01/05/02 08:03 PM
I actually was able to solve my problem by appending a block of code after my db_multirow and saving the results to some Tcl vars that were passed to the adp template.

I was only able to find out that you could have block of code after a db_multirow by looking at the source code for the function. The documentation doesn't clearly say so. Another thing to review 😊

Collapse
5: Response to Saving execution plan with OACS 4 API? (response to 1)
Posted by Jun Yamog on 01/07/02 08:58 AM
Hello Guys,

Not sure if this will help but what Luke P has done ETP is to use util_memoize for the page contents/attributes.  So each page hit does not have to fetch data to the db until the max age has reached.  See the proc etp::get_page_attributes on tcl/etp-procs.tcl.  This will only be useful if your page has values that does not change often.