At 10:19 AM 21/10/2004 +0800, you wrote:

>Hi,
>
>Good day. I would like your opinion on which of the following solution
>will have better performance. Performing a search by changing the
>sqlwhereitem in afterprepare

You don't change a SQLWhereItem in AfterPrepare. The only place you do it
is in the OnPrepareSQL event.

Changing SQLWhereItems or SQLOrder causes the whole statement to be
invalidated, i.e. the structure of the statement gets altered and so the
dataset gets unprepared and reset. IBO provides this trick to avoid
completely resetting all of the properties, as would otherwise happen when
the SQL is changed. When you use SQLWhereItems or SQLOrder, the Fields[]
structures remain intact and the Params[] become are invalid.

So, using SQLWhereItems is cheaper than invalidating the whole
kit-and-caboodle, but it's considerably more costly than merely
substituting values into existing Params.

>or have a parametize where clause then just change the parameter value?

This is by far the recommended way to do things. Once the statement is
prepared, you just keep it prepared. IBO does the magic for you when you
call the methods of Params, by merely slotting the latest values into the
appropriate places of the XSQLDA structure that actually goes across the
wire when successive instances of the statement are called.

I've been watching your recent postings in support and I think maybe you
miss the difference between changing the *values* in parameters and
changing the parameters themselves. Here's a simple example:

select col1, col2, col3
from atable
where col1 = :col1
and col2 = :col2

This statement has three ib_column objects in fields[] and two ib_column
objects in params[]. There is a relationship between Fields[0] and
Params[0]; and another between Fields[1] and Params[1]. Whenever you run
this statement, that relationship is unchanged. The returned values in
col1 and col2 (and therefore, in col3) depend on the values that your app
passes to params[0] and params[1], respectively. That relationship doesn't
change, so no repreparation is necessary.

Now, let's change the columns that we specify in the WHERE clause:

select col1, col2, col3
from atable
where col2 = :col2
and col3 = :col3

Now, the *structure* of the statement has changed - it's a different
statement. If you do this by changing the whole of the SQL property, the
whole original statement is invalidated. the Fields[] and Params[] arrays
both disappear. The client lets go of the statement's handle and we are
back at ground zero.

The same applies if you add an ORDER BY clause that wasn't there before, or
change the ordering to be on a different column or in a different
direction. For example, adding an ORDER BY clause to our original
statement changes its structure:

select col1, col2, col3
from atable
where col1 = :col1
and col2 = :col2
order by col3

When they are rebuilt, although the columns in Fields[] are the same, the
relationships are different. Now Fields[1] is related to Params[0] and
Fields[2] is related to Params[2]. Params[] will be nil until the
statement is prepared. Then, if the server confirms that everything is
cool, the new Params[] are ready to use.

Now, as long as you don't change these structural relationships, you can
run the same statement many times, changing the VALUES in the Params[] as
often as you like.

With SQLWhereItems and SQLOrder, IBO just gives you some middle
ground. It's a way of taking account of the fact that the new structure,
created by the change in the columns designated by the changed WHERE clause
structure, doesn't affect the structure of the set itself....so it's a way
of saving some resources during the unprepare/reprepare cycle.

Helen