Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] WebFOCUS SQL Optimization and Linked Server Joins
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] WebFOCUS SQL Optimization and Linked Server Joins
 Login/Join
 
Gold member
posted
From what we have experienced first hand with our current configuration and from my research on the IBI support site, it looks like WebFOCUS currently does not do SQL optimization for linked servers. Is this still the case and if so, does anyone know if there are plans to support this in the future?

We're trying to figure out the best way to work with some data in our environment, and it looks like we might need to have our DBAs create views of the data on one of the servers if we cannot get WebFOCUS to create optimized SQL queries for the linked servers. How has everyone else handled this situation in their environments?

This message has been edited. Last edited by: FP Mod Chuck,


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
One question

How much info does your Master/Access files have ?

WebFOCUS will need to know indexes, etc

May or May Not help though.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
We currently just have the auto-generated ones from WebFOCUS, which doesn't appear to be indexing the fields.


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Check the documentation on adding indexes.

May or May Not help though.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Could you clarify what you mean by linked servers? What are the data sources? Are you talking about several WebFOCUS reporting servers linked together or a bunch of ORACLE tables on different servers?


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I know this won't help, but we avoid linked servers -- I'm asking our DBA right now to copy data to our reporting SQL server so I can avoid that!


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 142 | Location: Indiana | Registered: December 05, 2017Reply With QuoteReport This Post
Gold member
posted Hide Post
Babak, referring to this https://docs.microsoft.com/en-...view=sql-server-2017

Our DBAs are wanting to stop copying data over to our reporting database and instead setup linked server connections (both are instances of SQL). From their perspective, they do not see a noticeable performance hit as they are running native SQL. From our side in WebFOCUS, we are seeing MUCH worse performance, as WebFOCUS does not appear to be able to create SQL optimized queries with this type of setup.

They are saying this is a common setup in other companies, but I don't have the experience to comment on that. I figured that most places would have one central reporting database/server with data copied into it that would avoid this type of situation, but I don't know for sure. They're trying to avoid having to copy everything over and maintain two versions of the data, so we have been going back and forth regarding the topic.

DWaybright, we're trying to avoid having to use the linked server approach, but I'm not sure if we will win this battle. Thanks for the input!


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This sounds like the type of discussion you should have with your local IB technical resource. They are usually eager to help you figure out what the ideal solution is. If you don't know or have one, you can open a case with IB tech support to get them to help you. I've never tried this so I'm out of ideas.


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold member
posted Hide Post
Thanks, I believe we have something scheduled for tomorrow. I was just curious if other people had ran into the same issue.


WF 8.2.0.3
Windows 10 64bit
HTML, AHTML, PDF, Excel
 
Posts: 83 | Registered: April 13, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Not 100% sure but I think that FOCUS is doing two requests in that case : one per linked server, bring back the two data set in its internal matrix then join them as requested by your code.
That explain why you have that lack of performance when using a linked server.

But as I said, not 100% sure and it may have changed in a more recent version but I doubt…

You should go with Babak option


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2233 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
My reasoning may be wrong, but a linked server is meant to be transparent to its clients, right? It would show up as any normal DB server to them. WF wouldn't know that it is linked to some other server and hence would not do anything differently optimization-wise.

If your masters don't specify any indexes/keys, WF will do joins reporting-server side. Isn't _that_ what you're seeing?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1652 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
To check the SQL generated, use this.

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON

It has been mentioned in many posts.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Are you using the same connection for your linked servers or do you have different connections for each database?

If you have different connections then WebFOCUS Reporting Server will assume (perhaps incorrectly) that they are not on the same the machine and then which to a FOCUS Managed join.

If you turn on SQL traces you will see if this is the case. If you see warnings about a FOCUS managed join then WebFOCUS will basically bring back all records from the first table and then do a table scan on the child tables - very bad for performance.

Cheers

Stuart


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 242 | Location: Melbourne, Australia | Registered: February 07, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] WebFOCUS SQL Optimization and Linked Server Joins

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.