I'll get my rant (and comments) out now...
First, if the database is ever used by something other than coldfusion,
Stored Procedures(SPs) and Views is your only real choice. Personally, I
would implement any type of advanced logic in SP's... SP's are created
in a db-specific scripting language that is usually an ideal environment
for implementing business logic.
On the other hand views can be useful as well however, IMHO, they do not
have the flexibility of SP's. I normally use views for two reasons...
The first reason is to give someone a limited view of the database. You
can redesign a table and eliminate specific fields with the view so that
a particular user does not have access to certain fields of a table
(e.g. credit cards, or SSN's). This is helpful in web apps as well
because restricting areas of your website to a db user with limited
rights can enhance the security of the site.
The second reason I normally would use views is to reduce the complexity
of queries. This happens more frequently with databases that were poorly
designed in the first place. As long as the database is normalized, and
the joins are simple inner joins on key fields, I see little benefit to
using a view. When you start joining tables based on date fields or when
the join needs functions like Min(), Max(), or type conversions, I would
consider a view. (Yes, I have been subjected to systems with implied
type conversions and even string functions required during joins...)
As for performance, I would normally expect the SP's to perform better
than Views under the logic that the more information the db engine has
before hand, the better execution plan it can come up with... In a SP,
all the information is there before it is ever executed and the entire
execution plan is precompiled; In a view, your code can always add items
to the where clause which can vastly alter the actual execution plan.
(Of course, every situation can be wildly different, and then there are
special views that will actually create temporary tables and return
results of the simpler temp table instead. (I *think* these are
Materialized Views in Oracle.)
In situations where coldfusion will always be used, personally I prefer
using <CFQUERY> instead of SP's or views.
Normally, coldfusion will never be as quick as a SP. Coldfusion can send
either an SQL String, or a Prepared SQL to the database for execution. A
SQL String is sent when you do not use <CFQUERYPARAM>, and for security
this should only happen when there are no variables in the SQL (i.e. all
constants and that is rare.) When using <CFQUERYPARAM>, a prepared SQL
is sent to the db server. What this means is that in place of
<CFQUERYPARAM>, a ? is put in the string and sent to the database,
followed by each variable individually along with the data type you
specified. Unlike the normal SQL String, with a Prepared SQL, the
database will cache the execution plan. While the initial execution
takes longer, each subsequent call to the database with the same
Prepared SQL will be just as quick as a SP. How long a prepared SQL is
cached depends on memory, server load, and db settings; it can be cached
as long as microseconds, or even days. (Surprisingly, the same place
with the horrible joins got Oracle to cache for days.) As far as saying
"Normally" coldfusion will never be as quick, this goes back to a poor
MySQL driver I had on one system, for some reason, any SP call would be
a minimum of 30 seconds regardless of what kind of call I was making.
One other thing to remember, the performance benefit of SPs is due to
the execution plan not needing to be compiled on demand. The more
complex the query is, the more time it takes for the database to
determine. Normally this is just a fraction of the of the overall query
performance... (exceptions for complex queries that only return very
little data.) The i/o will be the same whether the data is requested
through an SP or from CF.
One other benefit of SP's is that all the code accessing the database
are in the same place. (I noticed that Steve Duys brought up this point
while I was writing this book.) This is huge and it promotes the ideals
of MVC. If you have a group of professional programmers, you can do the
same thing by putting all SQL into a set of CFCs. Do not allow SQL to be
embedded on individual pages. This is my personal preference... The
programmers have great flexibility with this and are not forced into a
different ide just to write some sql. (Of course in a group environment,
all it takes is one bad apple to make you rethink this policy.)
As for ORM/Hibernate... I am not a fan of this at all. Essentially it
allows people that do not know how to design databases the ability to
create databases. While this makes it easy overall, I think this ends up
as a disaster waiting to happen. As an application grows in complexity
or scale, the lack of a structured data design will come back to haunt
you. People will end up dealing with bad performance and overly
complicated ways needed to compensate for the poor design. (I know
multiple DBA's that also have a hatred of ORM, for similar reasons.)
On 05/22/2013 11:02 AM, Dawn Hoagland wrote:
My 2 cents.....
I've seen more poorly put together Hibernate implementations than I
can count. If you don't REALLY know the data, how it's put together,
how it's used and keep the objects updated, Hibernate will
automatically generate the worst path imaginable to get the data you
need. I've also seen more developers who couldn't write an efficient
SQL Query if their life depended on it.....
Some implementations may REQUIRE stored procedures for efficiency.
Study ALL of the tools available including their strengths and
weaknesses and choose the proper tool for the solution. I've written
a lot of all 3. If I could ONLY choose one method (and there have
been instances of that) - stored procs are the most versatile. Just
like anything else, the source should be in source control. It's no
different than putting a process in place for any other code. You
just do it.
--Dawn
On Wed, May 22, 2013 at 9:58 AM, MCG <mger...@gmail.com
<mailto:mger...@gmail.com>> wrote:
Let me add, no DBA here at all. So same guys updating stored proc
and code. So we are making the assumption that they have the same
skills to update things (which may or may not be true); the code
from what I understand is MVC-ish. Didn't realize that stored
procs were cached as well. It sounds like it boils down to 'it
depends'. I'm partial to views, namely because my back end, deep
down skills are weaker. And access is just easier. You only have
to write a funky query once. Do not fear the outer join! :)
mcg
On Tue, May 21, 2013 at 6:23 PM, Bill Brock
<bbr...@tech-projects.com <mailto:bbr...@tech-projects.com>> wrote:
Mary-Catherine,
I’m with Jason, for the most part, and would only add these
notes for whatever it is worth:
·Stored procedures do cache their execution plans, and that
can lead to greater efficiency.
·Stored procedures are a nice way of separating church and
state for the DBA’s
·In-line SQL is part of the beauty of ColdFusion, and is
optimal for testing and is also fine, in my opinion, for
production as long as parameters are consistently wrapped in
queryparams.For everyone except the DBAs, in line is probably
preferable.It is certainly easier to edit / maintain, as well
as to read (ie, you can figure out what your query is doing
without having to go open another file)
·Nothing beats the MVC framework in the long run for
consistent application structure and for the incredible
flexibility (think model callbacks) offered via ORM.
Best wishes,
Bill
===================================
*Bill Brock*
*Technology Projects*
1014 Dallas Road, Suite 102
Chattanooga, TN 37405
p 423.267.7375 <tel:423.267.7375>
*From:*ad...@acfug.org <mailto:ad...@acfug.org>
[mailto:ad...@acfug.org <mailto:ad...@acfug.org>] *On Behalf
Of *Jason Delmore
*Sent:* Tuesday, May 21, 2013 11:15 AM
*To:* discussion@acfug.org <mailto:discussion@acfug.org>
*Subject:* Re: [ACFUG Discuss] SQL Server Question
I don't think there is anything wrong with using stored procs
over inline SQL to retrieve data. However, in my view of the
world this is application behavior and not reporting.
Application behavior should be modeled in objects and the
best way is to use persistent components (Hibernate/ORM.)
The code would then look like
claim = entityload("claim", {claimid=1234});
documents = claim.getDocuments(); // this uses ORM to
generate SQL that really just does select * from documents
where claimid=1234
document = documents[1];
formfields = document.getFormFields();
IMHO, there is no value of inline SQL over stored procs other
than perhaps that people seem to do a poor job of version
control on stored procs. An additional benefit of a stored
proc is that database guys will go in and review and tune a
stored proc, but are unlikely to go and edit inline SQL as
that falls in the application space. I would say leave your
stored proc alone unless you are going to go all the way to ORM.
But... that is just my view of the world.
Jason
On Tue, May 21, 2013 at 9:43 AM, MCG
<mger...@gmail.com<mailto:mger...@gmail.com>> wrote:
Yes it's been a while, but I've gotten pulled into
database design, and this group has always been the best
place to turn to for technical knowledge.
The team here inherited a crap database, over 200+ tables
for an unexciting claim system, and is now adding new
features. Yes, views are better than straight SQL in the
code in the case of many tables for your query. Maybe
I've just been out of the game, but the team is using
stored procedures to build queries to output data on a
page, which seems odd to me. Maybe it is fear of the outer
join, unions, subqueries.
But do I have a case for pushing the funky view SQL versus
running stored procedures? The case here is nothing
exceptional, we are getting data for the list of all
documents related to a claim and then the one record for
form fields for a single document for a claim.
Thanks!
Mary-Catherine
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
--
Dawn
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
