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     [CLOSED] Performance tuning

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Performance tuning
 Login/Join
 
Platinum Member
posted
Hi,
I am looking at FEX codes that is been converted from SQR reports.
The FEX files uses SQL passthru and after holding the SQLOUT in a hold file(HOLD 1) there is DEFINE on that hold file for data type conversions.

DATE/YYMD = DATE1;
CHG_DATE/A20 = END_CHG_DATE;

Can this type conversions be handled in SQL passthru itself.If I did that is it gonna affect the performance.

WF 8105M

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 181 | Registered: October 25, 2017Report This Post
Virtuoso
posted Hide Post
If you can code it in SQL Passthru and send it to the RDBMS, it's more efficient. The only other consideration is the labor involved in maintaining this stuff in the future.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
Thank you Babak

Is there any other performance tuning that i can do when converting SQR to WF
 
Posts: 181 | Registered: October 25, 2017Report This Post
Virtuoso
posted Hide Post
If you're passing all the SQL there aren't a lot of other things within WebFOCUS you can do to make it run more efficiently.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Gold member
posted Hide Post
Hi,
You can use :
SELECT CONVERT(varchar, '2019-04-01', 101);
to convert you date to Alpha..
This will return an alpha column which you can use further.

Quick question why do you want to use SQL passthrough ?


WF 8.2.04
Windows/Unix
All Formats
In Focus since 2006
 
Posts: 74 | Location: UK | Registered: September 17, 2018Report This Post
Platinum Member
posted Hide Post
This is not what i wanted to use.
There is already SQL Passthru in the code and i am looking at it to improve.

Sadly they do not have a metadata layer and they don't want to implement it now.
 
Posts: 181 | Registered: October 25, 2017Report This Post
Virtuoso
posted Hide Post
I'm not sure what you are looking for here.

If you are looking at improving the performance without having to do much work, put the casting into the select - as Addy suggested. Then table the result without creating a hold file.

Removing unnecessary IO is always a good idea for improving throughput.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Gold member
posted Hide Post
Hi Siva

I have to ask a basic question - is your performance significantly worse feeling when your run via SQL passthru?

I'm thinking there's a reason you're worried about it.

If it's slow even when you run from outside of WebFOCUS, then you need to get together with your DBA for advice on how to make the queries more efficient.

WebFOCUS can't go any faster than the native RDBMS tools would go.

Generally speaking it's always better to get the RDBMS to do the work. However, in past years, I've run into some RDBMS that was really slow at casting fields into different values. It was actually faster to let WebFOCUS handle the result with DEFINE. Do some testing and add some -TYPEs showing the clock before and after your requests to know for sure.

More than likely, the queries you inherited are simply inefficient from the beginning. Maybe your DBA will update the statistics of the RDBMS catalog or add an index or something to help you out.

Just my 2 cents.

Toby Mills, CISSP
 
Posts: 62 | Registered: October 31, 2006Report This Post
Member
posted Hide Post
I completely agree with Toby. Adding indexes most definitely should reduce data retrieval time. In the 3 years I have been exposed to WF from the system side, adding indexes to the "base" master tables in the RDBMS has helped. I really wanted to flatten the data that is used in WF from a 3rd normal form DB to a completely flat table eliminating as much of the "on the fly" temporary table creation at execution time as possible. Unfortunately, the filters on the BIP as so vast that I could never map out a "all inclusive" table that contains all possible filters and our WF developers just do not have the time to rebuild a test page to prove out my theory (flattened tables versus the temporary on the fly data extractions requested by WF), Our DB is enormous as is and this would push it over the top, I believe. Now if I could get Networking to drop some more pipe into my location and streamline the network traffic hops, I know this would reduce response time. Oh well. Not my call.


8.2.3 PROD/DEV 826 TEST
AIX, DB2 All Outputs
 
Posts: 14 | Registered: July 06, 2016Report 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     [CLOSED] Performance tuning

Copyright © 1996-2020 Information Builders