Posted by Doug on 10/01/32 11:38

hmmmm.......

Like Serge said, there are a lot of different answers and it depends a
lot on how you think about things.

Personally, I hate views. I can't control what the thing does, and
sometimes the optimizer doesn't do what is best. With a stored
procedure, I have more options. With the more options, I do have more
responsibilities.

For sure I would agree to learn views first. It teaches data set
mentality. When views don't cut it, upgrade to stored procedures.
Shrug. that's just how I think about it.

A couple of dissentions. Within the stored procedure will be the exact
same code as is in the view, if you do the basics. The optimizer can
optimize that SQL code just like it can in the view.
Stored procedures can be used to create data sets, and are MUCH more
flexible in their capabilities then views.

As an example, perhaps you want 5 columns, one data row long, that are
calculated ffrom 7 tables. You can absolutely use a view to set up the
joins.
However, if you utilize a stored procedure, you can utilize your
knowledge of how the data really works to "optimize" your approach to
data retrieval. So instead of a giant join, you can select the data
utilizing optimized indexes to rifle in on the data you are after.

For a small database (sub 10 million rows) it probably really doesn't
matter. Get a larger database, or a LOT of hits, and all of a sudden a
few extra table scans can really add up.

Shrug, Smile, and listening!!!!!!

-doug

[Back to original message]