[CLOSED] Which is faster:Filtering data using Oracle procedure or webfocus FEX
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
February 13, 2013, 02:20 PM
ABT
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.
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, RamThis message has been edited. Last edited by: Ram Prasad E,
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
February 18, 2013, 11:22 AM
dhagen
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