Re: [ACFUG Discuss] SQL Server Question
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.comwrote:
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
** **
*From:* ad...@acfug.org [mailto:ad...@acfug.org] *On Behalf Of *Jason
Delmore
*Sent:* Tuesday, May 21, 2013 11:15 AM
*To:* 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 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
-
Re: [ACFUG Discuss] SQL Server Question
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 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.comwrote:
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
** **
*From:* ad...@acfug.org [mailto:ad...@acfug.org] *On Behalf Of *Jason
Delmore
*Sent:* Tuesday, May 21, 2013 11:15 AM
*To:* 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 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
Re: [ACFUG Discuss] SQL Server Question
I would also echo Dawn's feedback. I have been on both sides of the fence
with different companies regarding SQL's overall role in app dev. Where I
am at now, we're very SQL-heavy, and employ a lot of views procs vs a
separate DAO layer within CFCs or query heavy templates. I like this method
personally. It utilizes SQL well and it also provides direct access for
those who need to work on the reporting or data analysis side -- vs, those
folks having to dig through CF templates to figure out what's going on from
a data perspective.
Also on the subject of SQL in general, I wanted to mention Redgate's SQL
Developer Bundle for managing deployments within MS SQL. These tools have
been a godsend for us for managing SQL in source control, pushing new db
object changes to production, etc. While they are pricey, I highly
recommend these tools.
http://www.red-gate.com/products/sql-development/sql-developer-bundle/
-Steve Duys
On Wed, May 22, 2013 at 11:02 AM, Dawn Hoagland dawnhoagl...@gmail.comwrote:
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 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.comwrote:
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
** **
*From:* ad...@acfug.org [mailto:ad...@acfug.org] *On Behalf Of *Jason
Delmore
*Sent:* Tuesday, May 21, 2013 11:15 AM
*To:* 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
Re: [ACFUG Discuss] SQL Server Question
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.commailto: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
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 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
