Adam wrote:

>--- 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.
>
>
>

You are correct, to a point. However, the query could be parameterized
to some degree. The query will basically be

select ATimeStampField
from <different tables>
where ID=:ID;

ATimeStamp gets set at insert/update via a trigger.

All I really need to know is whether a row changed during a batch
process of the same rows in a disconnected process. After the process
completes, i will check each row. If it hasn't changed, I can mark it
as having completed the process, else wait for the next iteration to go
through the process again.

The different tables will be grouped so that they are called all
together. So, I could build the query with the correct table and
prepare it once for each table. So, it seems this may be the best
approach considering bandwidth should not be the bottleneck in this
case, but rather, disk IO or CPU which would be the same or similar in
either case.

>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.
>
>
>

Can't use emebedded because the DB is accessed by other processes as
well as the middle tier. At least for the time being.

>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.
>
>

Alas, the DB is new, and there is no real data. in fact the DB changes
daily at this point, and an unrealistic deadline limits how much time
can be spent preparing test data. Therefore, it's more like half a day,
not 30 minutes. However, you make a good point, and I expect that will
be the final determination.

My thoughts were that if the answer happenned to be more cut and dry,
someone here would know that, and I could, therefore, save myself some
time.

Thanks for your answer!


[Non-text portions of this message have been removed]