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] Which is faster:Filtering data using Oracle procedure or webfocus FEX
Go
New
Search
Notify
Tools
Reply
  
[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, 2012Reply With QuoteReport 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, 2010Reply With QuoteReport 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, 2009Reply With QuoteReport 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, 2006Reply With QuoteReport 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, 2004Reply With QuoteReport 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, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.