I JOIN two Masters that correspond to MS SQL Server Views and I get the following SQL Trace message:

[code]
(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2505) A SEGMENT IN THE STRUCTURE IS NON-KEYED: : XXX
{/code]
This results in an inefficient request.

As far as I can tell, aren't all SQL Server views non-keyed, unless it's an Indexed View?

I have created many views and do not get this message when joining them, I'm trying to figure out why I get the message with this particular JOIN.

Thanks,

Sometimes I get that when the join-to field is not the first field in the master of the join target. This happens in Teradata as well.

It seems to me that when the target is relational, WF shouldn't be as picky regarding the position of the column in the MFD.

Ginny, thanks for that - I'll move the position of column I'm joining with and see if that changes things.

Fran it may be as simple as changing KEYS=0 in your .acx to KEYS=1 which is what I always use regardless of the actual number of keys.

While I agree with you, John, it is not always wise to do in some shops. I have a 228 page document that lists all of the masters that we have. While not all are relational, most are and we have a rule to not modify the generated master or access file by hand as it would become a maintenance nightmare.

And in the scenario I described above, KEYS=1 would not solve the problem if the key is the 2nd column in the master. We either wind up doing the join in the reverse order or use some other technique to architect the report.

The ideal solution would be for the SQL Translator to ignore the postion of the join-to field in the master if it is a homogeneous join against a relational engine and let the engine worry about whether the field is indexed or not.

quote:

Originally posted by Francis Mariani:
As far as I can tell, aren't all SQL Server views non-keyed, unless it's an Indexed View?
Thanks,

yes, from my limited memory, that is correct - but im baffled as to why one type of view works but another doesn't, and can only think that mnaybe the problem view is actually referencing other views where the working views are going straight against tables?!??!

The message that Francis is getting is coming from the SQL Translator not from the data base. I sincerely believe that this problem is caused by how the master and access file are parsed. The code never gets to the backend or you would see an RDBMS message as well as a FOCUS message.

i think you are on the right track- i was leaving the webfocus side to the experts

file under 'a wild stab in the dark'

I agree it's wrong-headed of IBI to manage the join, and deny the database server the opportunity to optimize, based on whether the join fields are the primary index (or indexed at all). That runs counter to fundamental relational database principles. At the least, there should be a means of controlling that behavior ("OPTIMISE=REGARDLESS"?)

But meanwhile a suggestion ... Using primary key as the criterion for optimization seems to apply only to dynamic joins (JOIN command). Instead, try creating and using a synonym with embedded joins. That should sidestep the KEYS= issue and allow optimization to take place without regard to the second table's primary key declaration, since the synonym of the join structure is self-contained, and the 'indexed' nature of the fields is stated (whether true or not for the underlying tables) in the KEYFLD and IXFLD declarations.

quote:

And in the scenario I described above, KEYS=1 would not solve the problem if the key is the 2nd column in the master. We either wind up doing the join in the reverse order or use some other technique to architect the report.

The ideal solution would be for the SQL Translator to ignore the postion of the join-to field in the master if it is a homogeneous join against a relational engine and let the engine worry about whether the field is indexed or not.

A. Ginny we use the Oracle and Sequel Server adapters and join to a foreign key works regardless of whether the foreign key is encompassed in the settings of keys. Otherwise most joins would be wf managed. The only setting that deoptimizes is keys=0.

B. Remember that multipath retrieval will deoptimize what might be a logical rdbms result. As a result I also usually code all rdbms joins as unique and only worry about ALL for wf databases. and

C. Also non homogenous adapters deoptimizes unless wf databases are at the leaf nodes of the tree.

You need to be sure that what you are seeing is A. not B. or C. as the cause of deoptimization.

Of course I have different adapters but I can't see why IBI would throttle their performance by deoptimizing so many joins...

Regards

John