 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CLOSED] WebFOCUS SQL Optimization and Linked Server Joins
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] WebFOCUS SQL Optimization and Linked Server Joins Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] WebFOCUS SQL Optimization and Linked Server Joins | Login/Join |
| Gold member |
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 | ||
|
| Expert |
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...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Gold member |
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 | |||
|
| Expert |
Check the documentation on adding indexes. May or May Not help though. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Virtuoso |
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 | |||
|
| Platinum Member |
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 | |||
|
| Gold member |
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 | |||
|
| Virtuoso |
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 | |||
|
| Gold member |
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 | |||
|
| Virtuoso |
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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007 | |||
|
| Virtuoso |
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 : | |||
|
| Expert |
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...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Guru |
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) | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] WebFOCUS SQL Optimization and Linked Server Joins
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] WebFOCUS SQL Optimization and Linked Server JoinsCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |