Re: Pointers for stored procedures?

Scott Stonehouse wrote:
> ... If we had the stored procedures looking at a view instead, we would
> only have to change the view. ... I suspect it would drastically slow
> down the execution of the procedure. ...

My experience is mainly with Oracle, with a little DB2 and Sybase thrown in ... but *no* SQL Server. Feel free to factor that into the discussion any way you wish.

"Way back" (late '80s, Oracle v5) I was faced with the performance argument by the programmers who wanted to use tables directly rather than the views that I was trying to make them use. I did a bunch of testing that failed to find any appreciable difference with use of tables v. views. And that was on a whole lot less hardware (memory, processor horsepower) than you likely have today, which I think would've been more likely to reveal such differences!

When you think about it, it shouldn't be too surprising. At worst, the difference should only be apparent during the statement evaluation phase, which should only occur once. The bulk of the working (i.e., I/O :-) is typically in fetching the data by which time the difference should be n/a.

My cardinal rule *used* to be that *all* code had to be written against views for the sort of reason you cite: provide a layer of "flexibity" between the data base structure and the applications. (The ANSI/X3/SPARC work from the 1970s provides good information on this if you can track it down.)

The hard part is knowing *where* the references to data base objects are in code outside the data base. With a strategy surrounding the use of views, you add a layer of flexibility in administering the data base.

I recently relaxed my rule for procedures stored within the data base *only* since Oracle's catalog tables track dependencies for me, and the full source is immediately available to me anyway. Don't assume I prefer it, though!

I also find views useful for distrubuted queries. "Client"-side tools seem more likely to pull the contents back for local processing, I often find it useful to build a view at the server end that forces joins, etc. to be done there, leaving the "client" tool to think it is looking at a single "table".

HTH ... Barry J. Received on Sun Feb 04 2001 - 22:56:54 CET