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] Performance tuning
Go
New
Search
Notify
Tools
Reply
  
[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: 175 | Registered: October 25, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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: 175 | Registered: October 25, 2017Reply With QuoteReport 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: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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, 2018Reply With QuoteReport 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: 175 | Registered: October 25, 2017Reply With QuoteReport 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: 1101 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport 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, 2006Reply With QuoteReport 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.1
AIX, All Outputs
 
Posts: 11 | Registered: July 06, 2016Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Performance tuning

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