Subject Re: [IBO] Prepare Queries..
Author Helen Borrie
At 04:39 PM 18/11/2007, you wrote:
>Im wanting to speed up my app..
>Ive built a Query, and I want to call it over and over in a loop..
>In the loop , i set the parameters, and open it..
>can I assume that I can prepare it once , when my app starts?

Prepare it the first time it is needed. The Open method of TIB_BDataset (the ancestor of TIB_Query) tests "if not Prepared then Prepare". Values have to be applied to the prepared statement, so apply them in the BeforeOpen event in each iteration of your loop.

>Then , just open it and close it in the loop?

Theoretically, calling Refresh should do the trick...the thing is, there was a problem at one point where BeforeOpen didn't get called during Refresh. That goes back some time but I've stuck with the "if Active then Close; Open" strategy 'cause old precautions never die. :-)

>Or, does it have to be re-prepared ever while the app is running?

Not at all. If you're using a TIB_BDataset descendant, the component will take care of it. If you use a TIB_Cursor instead, for which you call First, not Open, to access the first record, then you must do the check "if not Prepared then Prepare" yourself. Use Close, not Refresh, to stop fetching. (BeforeOpen is still OK for applying values to params, though, since IBO still calls Open before fetching First.)

-- you could call Unprepare after it is all done, if it a one-off routine that the user only runs occasionally and won't re-run in the current session. That will free up some resources for something else, if needed.

-- if the user will use the routine randomly several times in a session, unpreparing and re-preparing for each execution of the routine will slow you down more than the slight gain you might get from freeing the resources.

>My sql code wont be changing..

That sounds healthy. :-)