Views help with security and can simplify coding.
I don't think using them will in itself help performance.
I would say as a rule of thumb, if your data is at all dynamic then
use SP's. If it's static then I agree, cacheing it on the client
is a good technique.
Nick
-----Original Message-----
From: Sean Renet [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 20, 2000 4:34 PM
To: CF-Talk
Subject: Re: Undo Delete
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.
For instance, if you have a view that is
CREATE VIEW MYVIEW
AS
SELECT *
FROM SOMETABLE, SOMEOTHERTABLE
WHERE SOMETABLE.SOMETABLE_ID = SOMEOTHERTABLE.SOMETABLE_ID
AND SOMEOTHERTABLE.APPROVE = 1
and yer CF CODE IS
SELECT [NAME]
FROM MYVIEW
WHERE [NAME] LIKE 'SEAN%'
It would seem that the above would be faster than sending the [name]
parameter into an SP because the virtual table is already cached. However,
what is the difference between the above and having a stored procedure that
generates the same record set, then scripting via wddx or otherwise the
"slice" in the above CFQUERY? 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? I have implemented this both ways and
I suppose my data has not been big enough to notice a difference either way.
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.
----- Original Message -----
From: "Dave Watts" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, December 19, 2000 7:46 PM
Subject: RE: Undo Delete
> > > > Here's another use for flagging records: Our db tables which
> > > > contain scientific data contain an "approved" field, whose
> > > > default value is "false". After data is inserted into the
> > > > database, an analyst checks it out, and then we toggle the
> > > > flag to "true". Of course, our non-admin canned queries all
> > > > include "WHERE approved = true".
> > >
> > > This is a good place to use views. You can create views for
> > > these tables using this WHERE clause, and allow end users to
> > > run whatever queries they want.
> >
> > Views???
>
> Views are "virtual tables". You can create a view with an SQL statement
that
> selects the slice of data that you want. That view can then be treated, in
> most respects, as if it were a table.
>
> CREATE VIEW ApprovedOnly AS
>
> SELECT abunchoffields
> FROM mytable
> WHERE approved = 1
>
> 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
