cfstoredproc vs query

[nikos101]

What advantage does using

a cfstoredproc which selects from a view

rather than

just selecting from the view in a cfquery  have?

[ilssac]

If you are just using basic SQL syntax, not much of a difference.

Some people like the idea that the SQL logic is contained in the database and access through procedures.  But other then that there is little difference.

On the other hand, stored procedures allows one to go beyond basic SQL syntax and create much more sophisticated constructs.

[Adam Cameron. · Accepted solution]

Agreed. Making a comparison of a <cfquery> passing an SQL statement and a procedure which simply runs the same SQL is not really a sensible one, but the answer is "very little difference".

The thing with using procs for one's SQL is that one can then block any other arbitrary SQL being run by a client (like a JDBC driver), meaning only procs can be run, meaning all the DB logic is on the DB, and no-one can run spurious queries.  So that's the security side of things.

From a functionality point of view, one can perform far more complex logic on the DB using a proc, rather than shipping data base to CF and manipulating it there.  Minimising CF's requirement to do data manipulation is desirable because it's not really the best tool for the job: the DB is.

Performance-wise, a proc is precompiled, so there's a slight saving there.  Running two different (unparameterised) queries against your view still requires each query to have and execution plan created and then for it to be compiled before it is run.  That you're querying a view instead of a table is neither here nor there as far as that goes.  Although depending on your DB, using a view still requires the underlying SQL which created the view to be re-executed each time you query it.

--

Adam

[Dan_Bracuk]

A stored proc might be a nanosecond or two faster because it is pre-compiled. 

[nikos101]

so are views no?

[ilssac]

Sort of, but not really the same way.

[TLC-IT]

There is no "hard and fast rules."  I have only encountered one company that successfully enforced the notion of "there shall be no queries outside of stored-procs."

But there is a very big advantage to the stored-proc approach:  the database engine knows about them.  Therefore, it knows about all of the dependencies.  The engine cannot know about all the reports, all the ColdFusion pages, all the god-knows-whats, that are out there which contain snippets of "live SQL strings."  Which means that, if you propose to restructure a table to add some fee-chur, the server cannot help you ascertain the potential impact of the change, i.e. "how many things will break when I do this."

Another consideration is that, when two-or-more things need to "do the same thing," a stored-proc is a great way to ensure that they all use "the same code" to do this "same thing."  (Plus, the database server knows about the existence of the procedure, and so does anyone else who is perusing the database using the administrative interface.)

One area where I push quite strongly to use stored-procs is when dealing with (yuck...) Crystal Reports.  You absolutely want to put all of your logic in a stored-proc; nothing at all in Crystal.  Even though CR claims to have lots of features, too-many of those are throwbacks to a day when personal computers had floppy-drives and no hard disks, and "office networking" meant Morse code ...

[nikos101]

procs are the way then,

I though views in ms sql 2005 were precompiled?

[Adam Cameron.]

I can't vouch for SQL Server, but in Oracle, whilst the SQL that defines the view is pre-compiled (so there's a saving there), the actual SQL still needs to be executed and the recordset constructed every time the view is queried.  It's not a snapshot, or some automatic sort-of-persistent-temporary-table that the DB server maintains under the hood, it's simply a pre-compiled SQL statement.  One needs to use a materialized view to not have to worry about the time it takes for the SQL to execute and the recordset to be built.

--

Adam