 |
|||||
|
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] Performance tuning | Login/Join |
| Platinum Member |
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 8105MThis message has been edited. Last edited by: FP Mod Chuck, | ||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
Thank you Babak Is there any other performance tuning that i can do when converting SQR to WF | |||
|
| Virtuoso |
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 | |||
|
| Gold member |
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 | |||
|
| Platinum Member |
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. | |||
|
| Virtuoso |
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 | |||
|
| Gold member |
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 | |||
|
| Member |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 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 |