--- In firebird-support@yahoogroups.com, Mitchell Peek <mitchp@h...>
wrote:

> I have a procedure that does an operation on a single table.

However,

> which operation it does may very well depend on the value of a

timestamp

> in one of several tables.
>
> My options are, to build an SQL statement inside the procedure and
> execute it with "execute", or, handle this from the client

application

> by running the query before calling the procedure.
>
> This occurs in a batch type operation, and the procedure could be

called

> 3 or 4 thoudand times in a row. Of course, a query will have to be

run

> for each iteration.
>

Hi Mitch,

I don't think there is a single answer to your question because it
largely depends on your data shape. If I am not mistaken, you need to
build your query in a slightly different way depending on a
particular input.

Now if your batch of input means that there are significant amount of
queries that can be simply parameterised, then it would seem less
costly to prepare the query once and execute all of those records
first. If it is all over the place, then executing it within the
stored procedure will prevent the TCP loopback. For a middle tier,
you could probably use embedded which is significantly faster. It
also depends if you have the option to process the batch in any
convenient order, or whether it must go from top to bottom.

In any case, 30 minutes of testing against good test data (or better
still the real data against a test database) could demonstrate which
approach is better for you.