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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Which is faster:Filtering data using Oracle procedure or webfocus FEX

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Which is faster:Filtering data using Oracle procedure or webfocus FEX
 Login/Join
 
Gold member
posted
I have created a oracle view.I want to filter data from that view in my webfocus report.

I have done this using Webfocus filter condition and Oracle procedure..

Kindly please let me know which one is faster.

Webfocus filter code is given below
  
TABLE FILE VW_REPORT1
PRINT
     'VW_REPORT1.VW_REPORT1.CAST_NO/A10' AS 'Cast No'
     'VW_REPORT1.VW_REPORT1.COUNT1/D3' AS 'Count'
     'VW_REPORT1.VW_REPORT1.FSO/A8' AS 'FSO'
     'VW_REPORT1.VW_REPORT1.CAST_DATE' AS 'Cast Date'
     'VW_REPORT1.VW_REPORT1.WEIGHT/D20' AS 'Weight'
     'VW_REPORT1.VW_REPORT1.AUTH/D20' AS 'Auth'
     'VW_REPORT1.VW_REPORT1.SCARF/D20' AS 'Scarf'
     'VW_REPORT1.VW_REPORT1.SLIT/D20' AS 'Slit'
     'VW_REPORT1.VW_REPORT1.SUBDB/D20' AS 'Subdb'
     'VW_REPORT1.VW_REPORT1.ALLOC/D20' AS 'Alloc'
     'VW_REPORT1.VW_REPORT1.TRANS/D20' AS 'Trans'
     'VW_REPORT1.VW_REPORT1.WIDTH/A6' AS 'Width'
     'VW_REPORT1.VW_REPORT1.THICKNESS/A6' AS 'Thickness'
     'VW_REPORT1.VW_REPORT1.LENGTH/A6' AS 'Length'
     'VW_REPORT1.VW_REPORT1.QUAL/A6' AS 'Qual'
WHERE DESTINATION EQ '&DESTINATION';


Oracle Procedue filter code is given below

 
-INCLUDE set_connection.fex
SQL SQLORA SET SERVER &&CONNECTION_NAME
SQL SQLORA
EX PRC_FSO '&DESTINATION';
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS VW_REPORT1
END

TABLE FILE VW_REPORT1
PRINT
     'VW_REPORT1.VW_REPORT1.CAST_NO/A10' AS 'Cast No'
     'VW_REPORT1.VW_REPORT1.COUNT1/D3' AS 'Count'
     'VW_REPORT1.VW_REPORT1.FSO/A8' AS 'FSO'
     'VW_REPORT1.VW_REPORT1.CAST_DATE' AS 'Cast Date'
     'VW_REPORT1.VW_REPORT1.WEIGHT/D20' AS 'Weight'
     'VW_REPORT1.VW_REPORT1.AUTH/D20' AS 'Auth'
     'VW_REPORT1.VW_REPORT1.SCARF/D20' AS 'Scarf'
     'VW_REPORT1.VW_REPORT1.SLIT/D20' AS 'Slit'
     'VW_REPORT1.VW_REPORT1.SUBDB/D20' AS 'Subdb'
     'VW_REPORT1.VW_REPORT1.ALLOC/D20' AS 'Alloc'
     'VW_REPORT1.VW_REPORT1.TRANS/D20' AS 'Trans'
     'VW_REPORT1.VW_REPORT1.WIDTH/A6' AS 'Width'
     'VW_REPORT1.VW_REPORT1.THICKNESS/A6' AS 'Thickness'
     'VW_REPORT1.VW_REPORT1.LENGTH/A6' AS 'Length'
     'VW_REPORT1.VW_REPORT1.QUAL/A6' AS 'Qual'
 


I am facing problem in performance issue.
Please help me.

Thanks in advance.

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.1
Windows, All Outputs
 
Posts: 50 | Location: Scun-thorpe,UK | Registered: November 14, 2012Report This Post
Master
posted Hide Post
Seems like that would be entirely testable on the RDBMS side. Using SQL Developer (or your Query client), run both and compare the result speed.

Applying RDBMS theory, stored procedures are faster than their counterpart queries because they are compiled and optimized by the query processor.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Master
posted Hide Post
Hello, Enable trace and check the SQL code generated by WebFOCUS when you use WHERE in TABLE FILE command. If that SQL is same as SQL used in stored procedure, then there should not be any performance difference between these 2 approach.

Note: There will be a performance issue if DESTINAION is a define/calculated field and when adapter could not resolve it to native sql.

Thanks,
Ram

This message has been edited. Last edited by: Ram Prasad E,
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Virtuoso
posted Hide Post
You can add a time stamp to the start and end of these procedures and that would give you the answers.
You can not answer this upfront unless you know more, how many records do you expect?
The way you have created the query when done by SQL gives a bit overhead since you first do the SQL then do a webfocus print to a hold file and then the final reporting, that is 3 steps.
If you could do the formatting of the fields on the server, it would leave one step out.
If it was a summing report the SQL should be much faster since you only send the end result to the user.
So in fact there is not 1 good answer.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
You should have titled this thread "How long is a piece of string?"


"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
quote:
ere is not 1 good an

Thank you everyone to share some tips.

Now my report performance is increased.


WebFOCUS 7.6.1
Windows, All Outputs
 
Posts: 50 | Location: Scun-thorpe,UK | Registered: November 14, 2012Report 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] Which is faster:Filtering data using Oracle procedure or webfocus FEX

Copyright © 1996-2020 Information Builders