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,

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.

Thank you Babak

Is there any other performance tuning that i can do when converting SQR to WF

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.

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 ?

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.

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.

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

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.