Is there any point in views? vs stored procedures?

I do all my data access through stored procedures. Is there any benefit in creating views that the stored procedure accesses. At the moment, I tend to just write the select and join within the stored procedure.

Do you have to do any manipulation of the data? Are the stored procedures doing anything else than just returning data? Will the data change? Do you need to pass any parameters to the stored procedure?

As you can probably guess by my questions, there isn't a straight forward answer as it really depends on the data and what you are doing with it as to which method you should choose for each function.

|||

An interesting philosophical discussion is no doubt about to erupt.

If you are going to be re-using a very complex join, it makes sense to use a view; kind of like a re-usable business object. Its generally easier to maintain than a sp and is more accessible to people coming behind you. You can more easily make little tweaks if you need to make variations on a theme.

But of course sps are a lot more powerful.

|||I have seperate stored procedures for updating the data. These stored procedures return data. But my real question is, within the stored procedure, is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x|||

jagdipa:

is it better to do this:

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM View_myView
WHERE x = x

Or do this

CREATE PROCEDURE mySp1
@.x int
AS
SELECT *
FROM myTable1
INNER JOIN myTable2 ON ...
INNER JOIN myTable3 ON ...
WHERE x = x

There isn't really a right answer. You've given a dummy scenario so we can only really offer general advice and we can't say whether one method will be better than the other.

Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not. You will also have to look at the execution plans to see if there are any performance issues to take note of.

|||"Either method will work, and it really depends on what other objects will be accessing this data as to whether a view is needed or not."

This is really what I want to know. The example I gave is actually what I am doing (with a few more tables).

As far as I understand, a view is optimised. But so is a stored procedure. So, in theory, using either method should have the exact same performance (they are optomised in the same way using the same algorithms). I am probably wrong here.

But there is also the design point of view. Is there a design reason for using views? (exect the obvious putting security on a view seperatly from a stored procedure).|||

jagdipa:

This is really what I want to know.

But we don't know what other objects will be accessing the data. It's your database so only you know the answer to that question.

As I've said there is no right answer. You will have to decide for yourself which is the best approach, based on how often the data gets accessed, what other functions may need the same data and any performance issues that come out of your testing. Sorry I can't give you a direct answer as to which one is best but that's because one isn't simply better than the other. It all boils down to the individual needs of the database and that's something that only you can answer.

jagdipa:

But there is also the design point of view. Is there a design reason for using views?

If the data will be reused in several places then it makes sense to consolidate it into a view. Again, this is just theory and may not be the best solution for your needs.

|||

There are a number of real-world parameters to consider beyond ease of initial coding. Its probably easier to maintain a view in a large organization, plus its a whole lot easier for someone to look at in order to determine whether it's something they need to use (you could set up a view schema to act like a business object library). If you leave, the person coming in behind you can look at the views with a click,as opposed to having to execute the stored procedures.

Generally, the more static an object is, the easier it is to use a view for it's datasource. If you have something that gets databound on page load and doesn't change, a view is fine and dandy. And you could use a view as your base datasource and add filters to it if you are doing cascading ddls, for example.

But if you build a large object library, especially if you build generators for it, it's probably better to go with stored procedures. People following after you will have to be pretty high-speed anyway to be able to work with your code, so accessability really isn't an issue, and users can look at using your business objects as datasources instead of your views. I guess the higher you are scaling, the better sps are versis views.

|||

I stand firmly and forthrightly with those who say, "It depends!"

I would just add these comments (but really I agree with just about everything everyone else has said):

1. If the join is complicated, using a view sure makes it easier to reuse. It's much less error prone.

2. Views sometimes come with an unanticipated consquence, namely that you are guaranteed to access all the tables in the view. Other programmers, who may not know exactly what's in the view, may use a view incorrectly as a result. For example, lets say you have a view that joins 5 tables. Someone else comes along and retrieves data from the view, but really they only need data from 3 of the tables. By using the view, you not only force them to access all 5 but -- more importantly -- it may affect what data gets returned depending on whether you're using inner joins vs outer joins. I've seen this happen.

3. Views also have a nice security implication -- you can control access through views, though in practice this isn't done all that much.

Also, remember that your choice is NOT view vs proc because you can (should, some would argue) use a proc with a view.

|||Thanks for the clarifications. Views are ok, but I think I will use them sparingly. The reason for this is because I use a few user defined functions as well. In a stored procedure, I can create a temp table and pull the data I want into this. Then I can run the function on just that data (instead of all the data).

I think dbland07666 is right - especially with point 2. When I did not have much experience, I stuck everything in views, even when only pulling out very little. The view died very quickly when I added another 5 user defined functions to it !!!|||

Now that you are more experienced, I think you should look into developing a business object layer. It will allow you to do things programatically that would require dozens of lines of code with a single call. For example, you could so something like Personnel.GetList() to create a collection of Personnel objects which you can sort or filter or what-have-you in the object layer as opposed to calling stored procedures to do these various things with parameters. It's faster than going to the database and can be made to reside in cache so it scales up really nicely for multi-user environments.

|||Hi Charles,

I would love to learn more on this. Have you got a good tutorial I can use?
At the moment, I am going towards a sort of middle tier - I am new at this so its not great. I just create classes that access the relevant data (via stored procedures). It has come in very useful at times. But I am only creating web pages for a website that is probably only accessed 5 times an hour !! (its a B2B website).|||

http://aonaware.com/OOP1.htm

You can do stuff like build server-side validation into your objects to protect your database, you can write generic screens that have behaviors that are inherited by child screens which handle specific objects, all sorts of good stuff.

Here's the framework we use at this shop. It's free and its growing...

http://forums.lhotka.net/forums/default.aspx