1 2 Previous Next 25 Replies Latest reply: May 5, 2012 10:20 PM by 850247 RSS

    View Vs Stored Procedure

    CP
      Using Oracle 11g
      Front End .NET

      For Displaying the data in front end, we have to query the DB tables and display. For this case which is better option - Views or Stored Procedures?
        • 1. Re: View Vs Stored Procedure
          hitgon
          View is better option for your requirements

          Stored Procedures is used if you need any manipulations with table data

          Edited by: hitgon on May 2, 2012 4:19 PM
          • 2. Re: View Vs Stored Procedure
            CP
            Will you recommend even if we have 10 or more table joins with huge data. Can you justify please or any reference will also be helpful in comparison.
            • 3. Re: View Vs Stored Procedure
              Billy~Verreynne
              CP wrote:

              For Displaying the data in front end, we have to query the DB tables and display. For this case which is better option - Views or Stored Procedures?
              Views and stored procedures have nothing in common. They are even coded in different languages.

              A stored procedure can return a select statement (which could be on a view) as a ref cursor to the client (such as .Net).

              A view is a stored SQL statement. Now whether you use that view via TOAD, .Net, a PL/SQL procedure or whatever - the very SAME SQL code (that is defined as the view) is executed.

              This SQL code runs inside an Oracle server process. This does not care who/what/where the client is. That SQL will be executed in the very same fashion and SQL performance will depends on factors such as the execution plan for that SQL, and speed and availability of server resources.
              • 4. Re: View Vs Stored Procedure
                Billy~Verreynne
                hitgon wrote:
                View is better option for your requirements
                Stored Procedures is used if you need any manipulations with table data
                Incorrect.
                • 5. Re: View Vs Stored Procedure
                  _Karthick_
                  CP wrote:
                  Using Oracle 11g
                  Front End .NET

                  For Displaying the data in front end, we have to query the DB tables and display. For this case which is better option - Views or Stored Procedures?
                  Different organization follow different standards in application development.

                  What i have followed in a very long time is that Never put SQL in the Presentation Layer. Any result that is required to be returned to Client is done in the form of REFCURSOR.

                  So said that i would prefer to use a procedure that will Open a refcursor for the given Select statement and return it as an OUT parameter.

                  To add further, You can create a View if you like. But still the view will be queried inside a Procedure and the result would be returned to the Client as a REFCURSOR.

                  Edited by: Karthick_Arp on May 2, 2012 3:59 AM
                  • 6. Re: View Vs Stored Procedure
                    indra budiantho
                    If u use a tools, like TOAD, u can see the data in a view and u can filter the data and likely makes your work easier. View can accomodate more than one table, so for query only, i prefer VIEW to Store Procedure. And i found it is more maintainable and scalable.
                    • 7. Re: View Vs Stored Procedure
                      Marwim
                      Hello CP,

                      our database offers many views and procedures as a service for other applications. Usually we ask the developer of the front end what he likes more ;-)

                      It is not a general decision between procedure or view. It depends on the concrete case.

                      Usually our Java developers prefer a view for result sets with more than one record. Procedures are only used when the result can not be returned by a single SQL. But even then you could use a pipelined table function to return the result in a view.

                      Regards
                      Marcus
                      • 8. Re: View Vs Stored Procedure
                        _Karthick_
                        1B wrote:
                        If u use a tools, like TOAD, u can see the data in a view and u can filter the data and likely makes your work easier. View can accomodate more than one table, so for query only, i prefer VIEW to Store Procedure. And i found it is more maintainable and scalable.
                        You can never prefer VIEW over STORED PROCEDURE or STORED PROCEDURE over VIEW. Because they are different tools used for different purpose.

                        View cant do the work of an SP and SP cant do the work of a view.
                        • 9. Re: View Vs Stored Procedure
                          Marwim
                          even if we have 10 or more table joins with huge data
                          Contrary to many other DBMS out there Oracle is capable of joining multiple tables without blocking other users. The question should be: Can it be done in a single SQL? If yes, then use a view.

                          This is not only the mantra of many Oracle gurus ;-) but it also corresponds with my own experience. Until now I only once had to write a function to get a result quicker though it could be done in SQL.

                          Regards
                          Marcus
                          • 10. Re: View Vs Stored Procedure
                            indra budiantho
                            Is it correct this mantra?
                            SQL > PL/SQL > C > JAVA
                            and view is SQL only, so it is better for the sake of performance? tx
                            • 11. Re: View Vs Stored Procedure
                              Billy~Verreynne
                              1B wrote:
                              Is it correct this mantra?
                              SQL > PL/SQL > C > JAVA
                              and view is SQL only, so it is better for the sake of performance? tx
                              Yes. But the mantra in this case means not to use PL/SQL to read the rows from the view, copy (fetch) these into a collection (residing in expensive PGA memory), and returning that to the caller.

                              That is wrong and horribly so.

                              The correct approach would be for the caller NOT to use the view.

                              Why? Because the caller (app program and developer) need to understand the database model, need to understand Oracle SQL and performance concepts, and so on.

                              If the data model or view changes - the app program also needs to be updated.

                              This is why using a PL/SQL procedure is preferable. It resides in the database. It is written (hopefully) by database experienced developers. The procedure can be changed to support new features and modifications to the data model, without having to touch a single line of code in the app program.

                              All that procedure needs to do is accept the call from the app program, decide how to build the SQL statement (on the view/table/whatever) for the caller, create a SQL cursor in the server's Shared Pool, and then return the cursor handle (using a ref cursor data type) to the caller.

                              So instead of the caller passing source SQL to Oracle and getting a cursor handle, the caller calls a PL/SQL procedure that returns the SQL cursor handle.

                              The app program is now abstracted from the database layer and Oracle technical concepts and analytical SQL, recursive SQL, Oracle performance, Oracle CBO hints, and so on. The PL/SQL procedure takes care of all of these.

                              Which is why using a PL/SQL stored procedure would be a better choice than the client app itself dealing with the Oracle SQL language and the Oracle objects (such a views) in the database.
                              • 12. Re: View Vs Stored Procedure
                                _Karthick_
                                1B wrote:
                                Is it correct this mantra?
                                SQL > PL/SQL > C > JAVA
                                and view is SQL only, so it is better for the sake of performance? tx
                                You have wrong understanding of this mantra. What it implies is that, Do as much as work possible using SQL so that you minimize the IO and Context switch.

                                In the OP case a stored procedure is going to be used as a Wrapper for containing the SQL. By which you dont expose the SQL to the client directly.
                                • 13. Re: View Vs Stored Procedure
                                  Gaff
                                  Billy is correct. There are reasons to choose one over the other, but it isn't a performance issue as they both run within the database.

                                  Using procedures is a good practice because it forces the front end developer to specify what they want for data (it's a contract in a way) and most importantly, as Billy said, it lets you have DATABASE DEVELOPERS write the database code. Exposing your data in views to developers who really don't know how to write efficient SQL is a recipe for disaster. Different people have different skill sets and SQL (or PL/SQL) programming is a different animal than front end development.
                                  • 14. Re: View Vs Stored Procedure
                                    JonWat
                                    I would normally use a view.

                                    One reason is that for paging through datasets the application development tool I use will often read only one page at a time. It may then ask for the next page by constructing SQL with an additional WHERE clause, i.e. where something >= the row you are currently on. It works fine, but with a stored procedure I'd have to manually write the ability to handle paging into the stored procedure, and that's a wheel I don't think I need to reinvent.

                                    The second is that my application development tool can, without any real effort on my part, construct SQL to limit by any of the columns in the dataset and change ORDER BYs. Again, using a stored procedure, that would be something that would have to be handled explicitly, with a parameters of potentially different datatypes, case statements to handle different sort orders etc.

                                    Although I can see some merit in the "provide a stored procedure as the interface and all sorts of things can be changed in the database without the application having to worry about it" approach, I think it would be a very rare change you make in the database that would have no impact on what pesky humans need to have access to. If you add an extra column it's likely that some human somewhere will be required to put data into it. I guess you could have stored procs for that too -- and your stored proc can deal with the fact that the parameter that used to go into the client table now goes into client_other_details or something. But that supposes that you got the definitions of all of your stored procs right, it's just that you fooled up the data model. So, I'm a sceptic on that as a reason.

                                    Another alternative I have occasionally used is to use GLOBAL TEMPORARY TABLES to return data. You call a stored procedure to fill the temporary table, and your UI reads from the table.

                                    I mostly work on small projects where I design the database and the front end, so the separation between the "developers" and the "DBAs" is not a distinction I need to make.

                                    Jon
                                    1 2 Previous Next