Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     WebFOCUS Joins using MS SQL Server Views generates inefficient SQL?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
WebFOCUS Joins using MS SQL Server Views generates inefficient SQL?
 Login/Join
 
Francis Mariani
Expert
posted
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,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
GinnyJakes
Expert
posted Hide Post
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
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Ginny, thanks for that - I'll move the position of column I'm joining with and see if that changes things.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
hammo1j
Master
posted Hide Post
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.


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
nubi
Guru
posted Hide Post
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?!??!


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
nubi
Guru
posted Hide Post
i think you are on the right track- i was leaving the webfocus side to the experts Smiler

file under 'a wild stab in the dark'


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
j.gross
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
hammo1j
Master
posted Hide Post
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


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     WebFOCUS Joins using MS SQL Server Views generates inefficient SQL?

Copyright © 1996-2020 Information Builders