> So I have a dba question. What is the difference between using
> a view instead of a stored procedure. Now I am well aware that
> you cannot make views as complex as sp's. I am actually wondering
> what the performance or otherwise difference between making a
> stored procedure which is implemented like a view. Don't they
> both cache the record set similarly? Why would you use a view
> instead of a sp.
Neither views nor stored procedures cache the recordset, really. They may
cause the query execution plan to be cached (and this may or may not be a
good thing depending on how much the optimal dataset retrieval patterns are
similar across multiple executions). Assuming that it's a good thing to
cache query execution plans - which is usually the case - this gives you a
significant performance advantage, as much of the work of getting your
recordset is actually figuring out the best execution plan, which the query
optimizer does for you.
The big difference between views and stored procedures, from an SQL
programmer's perspective, is that a view is a layer of abstraction. When you
use a view, you query it as if it were a table, but it's not.
In some cases, this abstraction is a good thing. In the earlier example,
which mentioned the use of views to hide records that had been
"soft-deleted", the writer of the query which retrieves the records doesn't
have to know about how soft deletes are handled, since she queries the view
rather than the underlying table.
Of course, there's always a performance penalty for abstraction, which is
why you're generally better off querying the underlying tables yourself.
> ... Isn't it less expensive to have the whole record client-side
> and scripting the slice rather than hitting the database everytime
> you want a different slice?
Yes, I'd agree with that. Unfortunately, that's not always an option, but
you'll certainly be able to support volume usage better. There's one catch,
though - there will be an initial big lump of data you have to retrieve and
send to the client before they can use all the nifty client-side stuff.
> However, I am about to empark on a huge project that any
> performance corners I can cut are going to make a great deal
> of difference as the data is going to be enormous.
The ideal solution to this is a prototyping process, with stress testing and
capacity planning testing during the prototyping phase. This way, you can
try alternative architectures, and get a good feel for the advantages and
disadvantages of each. Unfortunately, it's rare that web applications go
through a good prototype phase. More often, people simply throw hardware at
the problem after the fact.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
