prepare SPL

} } } jvemo@cyberspace.com (Jon C. Vemo) writes: } >I know that any SQL statement to executed more than once or twice, benefits } >from having the statement prepared first. Does this also apply to "execute } >procedure <sp_name>". Are there any benefits or reasons not to prepare an } >execute procedure statement?? } > } >TIA } >Jon } >==================================================== } >Jon Vemo Internet: jvemo@cyberspace.com } >Bothell, WA USA } > } >Watch out for road-kill on the information superhighway....SPLAT!! } >==================================================== } > } } } I don't know for a fact, but I would not think so, since the SPL lives on the } server, as long is it a valid SPL, there should be nothing to optimize as there } is with a regular , unprepared DML statement. It is optimized } when you first create the procedure. } Provided that the underlying table statistics do not change and that all of the tables and columns exist that the SPL requires. As for preparing an EXECUTE SPL statement - if you're going to hit it multiple times then yes you might get a little speed boost out of the CALL to it. the SPL itself will take the same amount of time to run. Of course after the first invocation it will hang out in memory for a bit so your second call to it stands a good chancce of finding it already there. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ Discover America, get lost on a rally. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________