3 Replies - 5616 Views - Last Post: 06 July 2013 - 02:29 PM

#1 Dormilich  Icon User is online

  • 痛覚残留
  • member icon

Reputation: 3569
  • View blog
  • Posts: 10,404
  • Joined: 08-June 10

View vs. Stored Procedure

Posted 16 June 2013 - 11:52 PM

Hi,

Id like to know, what points speak for or against Views/Stored Procedures when it comes to fetching data. Both have the advantage that the underlying tables are left alone (i.e. both provide a kind of access API) though Views seem a bit more flexible (and you have the fields public) while Procedures are save against incidental modification and feel more like an API than Views.

Dormi
Is This A Good Question/Topic? 0
  • +

Replies To: View vs. Stored Procedure

#2 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: View vs. Stored Procedure

Posted 17 June 2013 - 05:11 AM

First of all, a view is kind of analogous to a table, except that it is read-only. It can also be used to provide a more "logical" view of the data in a table by traversing foreign key fields or formatting data. Even simple things such as renaming fields from their database friendly name, to their user friendly name.

A stored procedure is most analogous to a function / method, inside a SP you can execute SQL code e.g. insert new rows etc. However what the main benefit of a SP is is that it is stored at the database level of an application; so, if you have a desktop application, web application, mobile application etc all using the same database you can write business logic code in a stored procedure and it can just be invoked from all the applications, any updates automatically get applied to all applications. Also (and possibly most importantly) is that stored procedures are compiled on the server - every time you send a query to the database it has to be checked and optimized etc - but with a stored procedure it is already done. They also help to prevent SQL injection attacks.

The drawbacks of views are that any keys you have on tables no longer exist; so querying large views is equivalent to querying an unindexed table, views do not accept parameters either, but stored procedures can.

In database speak there is two different things you can do with data, "project" and "restrict". If you want to project data (i.e. include fields, join tables, do unions etc etc) use a view, if you want to restrict, you should probably use a stored procedure, because these can have parameters. You can execute a stored procedure on a view as well.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: View vs. Stored Procedure

Posted 17 June 2013 - 07:40 AM

Simply, use a procedure if you MUST. If you can logically compartmentalize your query is a select, do it. If you can't, think on it a little more. Databases are best at SQL. Using procedural extensions of a database to retrieve data means all other options have been exhausted.

A procedure can be a good way to compartmentalize how changes are made to the database. You might use a procedure in place of a view if accessing certain data should be audited.


View Postandy_pleasants, on 17 June 2013 - 08:11 AM, said:

a view ... is read-only.


This is NOT true. It can be, if the join logic is complex enough. However, never assume it is unless you've secured or validated it.


View Postandy_pleasants, on 17 June 2013 - 08:11 AM, said:

The drawbacks of views are that any keys you have on tables no longer exist


Also untrue. Your database optimizer should decompose the view and go from there. You will run into issues with joining views mostly because you're probably dragging around a lot of extra baggage you don't need.
Was This Post Helpful? 2
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 800
  • View blog
  • Posts: 1,686
  • Joined: 30-January 09

Re: View vs. Stored Procedure

Posted 06 July 2013 - 02:29 PM

/agree with baavgai on all points regarding MySQL. I have found though, that in MSSQL (even post-2005 when this was meant to be fixed) there is call for stored procs on SELECT statements, as execution plans for SPs are cached globally, and so get reused across different connections. I found this after long investigations that demonstrated that even connection pooling does not cache ad-hoc query execution plans properly for global reuse. This is, mind you, an MSSQL issue.

Back on topic though, I generally use Views where I'll be using the same SQL code over and over, either across different queries or within the same query. Views can be used as a method on centralising SQL code, thereby achieving DRY techniques. Doing this can also make it easier to debug core data retrieval techniques across your application, as you only have one place you need to fix errors. One thing to keep in mind though is that Views circumvent permissions on the tables themselves, so they may expose sensitive data that you otherwise want hidden.

Stored procedure are the scripting element of databases, and can take complex logical code to achieve a result. They need not just SELECT, as they can perform any function within. Stored procs can also be parameterised, in much the same way you would parameterise a PS in PHP for example.

I'm contemplating using stored procedures rather than ad-hoc queries in my application, though it is not for performance benefits. The main benefit I see to using stored procedures is maintenance and governance of the SQL. I am only contemplating this though, as I don't see a great benefit to doing this - I most likely will not go ahead with it. Amongst the big drawbacks of SPs are:
  • Not portable between SQL dialects
  • Error reporting can be non-helpful
  • SQL is not designed fro programming and is therefore a weak platform

This post has been edited by e_i_pi: 06 July 2013 - 02:32 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1