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,

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

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,

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.

You should have titled this thread "How long is a piece of string?"

quote:

ere is not 1 good an

Thank you everyone to share some tips.

Now my report performance is increased.