Very Computer

Is there a patch or service pack which allows Foxpro to actually optimize
local views based on a table whose PRIMARY key is a CONCATENATION of two
fields?? For example:

The table has the following indexes:
Index1: FIELD1 + FIELD2 (primary)
Index2: FIELD1 (regular)
Index3: FIELD2 (regular)

set the KeyFieldList property of the view to FIELD1, FIELD2
set the SendUpdates property to .T.
use the view
turn on Foxpro's optimizing feedback feature (using SYS(3054), I believe).
change a row, then move off ot if (or issue a TABLEUPDATE if you want).

You will see Foxpro is making use of the Index2 and Index3, but not Index1.
Why isn't it using index1?  I know you might say that the primary key should
consist of just one field, but for the sake of arguement, let's just say
that it has to consist of two.  What then??

Hi Make MY day,

What is your name?

SYS(3054) does not tell you about any indexes being used to optimize join
conditions, you need to sue SYS(3054,11) to see that aspect of the plan.

JimB

Hi Make MY day,

What is your name?

SYS(3054) does not tell you about any indexes being used to optimize join
conditions, you need to sue SYS(3054,11) to see that aspect of the plan.

JimB

Thanks.  SYS(3054) aside, was there an answer to the actual question?

: Thanks.  SYS(3054) aside, was there an answer to the actual question?

In order for fox to take advantage of a concatenated key, the expression
must be also be a concatentation of those fields. Rushmore requires the
optimizable expression match the index expression exactly. When you issue
a table
update what essentially happens (and this is a bit of a lie) is that a sql
update gets executed. This update, when it includes two keys will not
issue
a where clause like

Update TableX ;
set field1 = y, field2 = z ;
where field1 + field2 = y+z

instead it will produce  

update TableX ;
set field1 = y, field2 = z ;
where field1 = y and field2 = z

The first is optimizable on the compund index, the second is not.
(Obviously the examples don't make much sense, but illustrate the point).
In terms of pure speed, I'm not sure that statement 1 will be any faster
on a view, X1 AND X2 is as optimizable as X1+X2, if you catch my drift.
HTH.

Terry Weiss

Yes, so now (using a local view) how do we force Foxpro to update the table
in the manner you specified? (i.e. Update
TableX set field1 = y, field2 = z where field1 + field2 = y+z)?  The index
is just sitting there waiting to be used in Rushmore, but it seems Foxpro
cannot make the intuitive leap and concatenate field1 and field2 in the
where class of the update (that it is using behind the scenes to update the
table).  Is there a service pack fix that adds this tiny bit of intelligence
to Foxpro's view?

: Yes, so now (using a local view) how do we force Foxpro to update the table
: in the manner you specified? (i.e. Update
: TableX set field1 = y, field2 = z where field1 + field2 = y+z)?  The index
: is just sitting there waiting to be used in Rushmore, but it seems Foxpro
: cannot make the intuitive leap and concatenate field1 and field2 in the
: where class of the update (that it is using behind the scenes to update the
: table).  Is there a service pack fix that adds this tiny bit of intelligence
: to Foxpro's view?

In short - no. Once it is broken out as a view, there is no way to flag
the concatenated field as a primary key. Even if you join them in the
view, e.g. select field1, field2, field1+field2, then that compound field
is not updateble and since it has no single corresponding field in the
underlying tables it cannot be taken advantage of. This is not a
limitation of fox, but a consequence of SQL. You would have the same
problem in SQL Server (unless you used an index hint). Also, I would like
to reiterate that there is probably only a minimal performance gain using
the compound index. I know that you requested in your original post that
the schema be left out of it, but a good rule of thumb in fox is to always
use a single surrogate primary key and it would get you out of this
situation rather easily. HTH.

Terry Weiss

So what is your name?

If you use SYS(3054,11) you may find that the view is using index 1 to
optimize the join condition.  You never showed us the select statement that
comprises the view, therefore we have no idea of how the where clause is
phrased, it must be phrased as WHERE Field1 + Field2 = Value1 + Value2 in
order for Rushmore to recognize the index as being useable for optimization.

JimB

Make my day,

No service pack will fixed a violation of design.  In the developer's Guide
it clearly says that Rushmore will optimize and operation when it sees an
EXACT match to the key of an index in the comparison operation of the where
clause.  Index 2 and index 3 match the operations but index1 does not match
any clause in the where operations so it doesn't get used.

JimB

Actually the problem originally arose out of a need for the code to work
with local Foxpro views as well as REMOTE Oracle views.  The Oracle view
version is extremely fast (due to the fact that in Oracle we set the primary
key to Field1,Field2, and as long as the where clause has those in order,
such as WHERE FIELD1 = y AND FIELD2 = z, it can be very well optimized).
The problem only exists in Foxpro.

Make my day,

No service pack will fixed a violation of design.  In the developer's Guide
it clearly says that Rushmore will optimize and operation when it sees an
EXACT match to the key of an index in the comparison operation of the where
clause.  Index 2 and index 3 match the operations but index1 does not match
any clause in the where operations so it doesn't get used.

JimB

So what is your name?

If you use SYS(3054,11) you may find that the view is using index 1 to
optimize the join condition.  You never showed us the select statement that
comprises the view, therefore we have no idea of how the where clause is
phrased, it must be phrased as WHERE Field1 + Field2 = Value1 + Value2 in
order for Rushmore to recognize the index as being useable for optimization.

JimB

Does it matter?  I mean, are you saying that when Foxpro is updating the
table from a view, the WHERE clause of the select statement that makes up
the view is used, while the KEYFIELDLIST property of the view is ignored?  I
thought the KEYFIELDLIST property was the one being used to determine how
the actual table is updated.  It must be in the case of a remote view,
obviously, but are you saying it's ignored in local views?  For my example,
the select statement could have simply been SELECT * FROM TABLE1 (with no
where clause at all).  In that case, wouldn't the KEYFIELDLIST property have
to come into play somehow?

--
Bill Morris

Exactly.  So what we're both saying is that the KEYFIELDLIST property of a
view is useless when we are dealing with a local foxpro table whose primary
key is composed of more than one field.