>>Store procedures are in the database, can get any more central than that. With tools like sql-compare you can easily manage schema changes from your developement db to your production db.>>
What if you have more than one data source, i.e., an XML feed from one system, an MS-SQL database, a nightly import from some other system...?
IMHO, I have to disagree. Databases are not 'central' to the application. The 'application' is central to the application. Databases are one or more places to STORE data. Using stored procs and functions and security and such is a good idea, but is a
side issue to application design.
Additionally, you make have to a create a new application to work with an existing database. The new application should be designed to mirror the business process and best represent the business objects, which may not be precisely how the database is designed.
Your DB layer would translate between DB schema and object schema, but your object schema should be the bible on what the application looks like.
A stored procedure allows for more modular programming. You can create reusable, discrete pieces of functionality using stored procedures. You can create functions. You have all the programming constructs that are needed to perform complex logic.
Since you can also do this with application code, it does not represent a benefit of a stored proc.
GOHDS
Store procedures are in the database, can get any more central than that. With tools like sql-compare you can easily manage schema changes from your developement db to your production db.
Personally, i prefer to centralize everything in my codebase rather than split functionality between code and DB.
GOHDS
Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL.
Incorrect. The execution plan for a stored proc is created when the proc first runs and placed in a memory cache. Parameterized SQL statements also get their execution plans created the first time they execute and then cached.
Both techniques will reuse execution plans if they are in the cache and both techniques must create the plan when they are first run.
GOHDS
If you have a stored procedure that manipulates large amounts of data, that data will remain on the server and not be transported across the network.
agreed
GOHDS
You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting them permissions on objects manipulated by those stored procedures.
True, but this assumes that you require/desire your dba to be your applications security admin. Some applications require the user to be able to grant /revoke rights to certain objects.
GOHDS
With a good code genrator you can build you dal from the metadata on the database. you also have the generator build wrappers for you store procedures, providing you with strongly typed properties and methods, this then become your business objects. If you
change your data structure or store procedure or views, you dal is generated with one click.
This doesnt make stored procs better though. A really good DAL can get/set any data without the need for any stored procs. The speed increase you get is measured in application development time. No procs to write, means less time / less cost. many mappers
dont preclude the use of stored procs though if you still need them.
GOHDS
Good TSQL code will outperform an ORMapper.
By how much? Remember, execution plan caching appies to both stored procs AND to ad-hoc parameterized sql.
GOHDS
ORMapper is a another layer between your app and the database. Why introduce another level of complexity into the mix, why learn another non standard proprietary syntax. I rather invest my time learning and improving my t-sql skills.
A layer is supposed to exist between your app and the database. The mapper exists as part of your DAL.
The selection of an O/R mapper is not to be taken lightly but the benefits that a good one can provide defineately [IMHO] outweigh the learning curve.
As far as where to invest my time learning...since adopting a mapper based DAL approach, i now have much more time to invest. our mapper technology provides us with a very rich set of functionality that we used to have to recreate for each application.
Thi s does not mean i would never use a stored proc, but i find very few instances where i could conclude that a proc would truly be a better choice.
Stored procedures have the parsing and execution plan built at the time they are created so they execute faster than dynamic SQL.
You can also use stored procedures for security. You can grant users execute permissions on stored procedures without granting them permissions on objects manipulated by those stored procedures.
Ahh... the age old debate... I always put in a reference to Frans Bourma's blog. He if any should know a thing or two about stored procs and o/r mappers.
In my post i gave you the reason why i use store procedures and how it has worked well on my situation. I'm not saying they are better, than this or that, some of the issues that are being raised aginst not using store procedures have more to do with the
developer or the shop than the actual technology. Making a statement "Store Procedures are Bad" without backing up with facts serves no purpose,
In my shop is all about agility the faster, i can deliver the solution the happier the customer. I find that having a simple proccess that's repetitive and not error prone will give me the edge. I find the database the ideal place to put my business logic.
The database has all the meta data i need to create my dal, is a central depository. When I make changes to my database, my dal generator picks those up and creates a new dal componenent, my application is always in-sync with the database. Once i'm done
testing my changes, i run sql-data compare and it creates a script for me, to update my production database, it also documents all my changes. This is all automated, push a button no brainer type of a process, that gives me the agility to make my customers
happy, that's the bottom line for me
Also, one other point, with the advent of SqlSever2005, where REAL, OO languages such as VB.NET and C#.NET can be used in the databse rather than SQL, to prefer the use of SQL is a stance that is somewhere quite a bit south of insane.
(But, of course, this applies only to SqlServer2005 and, I hope, all other databases that choose to evolve rather than rot.)
Other than that, I have no strong feelings on the matter. br />
That all just my opinion. Take it or leave it. Note that I reserve the right to wrong.
IIRC, you can write stored procs in .NET on Oracle now too -- as long as it's Oracle on Windows. You can write Java StoredProcs for Oracle on most any platform though.
But also, IIRC, the glue between the Java/.NET StoredProcs and the rest of the system was pretty weak.
*hypothetically* stored procs offer faster development becuase you may have more in-house talent for DBA stuff than you do C# (for example) coders.
Applications can be developed while DB stuff is developed simultaneously.
I love OO but prefer stored procs becuase it keeps people who are good at DB stuff but mediocre at application development out of the application development side.
It is also possible that (gasp) I may not actually be that good at writing SQL queries, regardless of where they are written, but be REALLY good at coding C# (or whatever).
I think procs and non-procs are a wash for all the reasons being debated, but I will submit that from a project management point of view, they are easier to keep track of and assign duties.
*hypothetically* stored procs offer faster development becuase you may have more in-house talent for DBA stuff than you do C# (for example) coders.
Let me rephrase my previous statement slightly
As a developer, i do not need to write sql or stored procs nor do i need a DBA. I simply write code that uses business objects. Lets assume hypothetically that there is just one (1) of me.
Is a Developer AND a DBA better? That would be double the manpower / double the expense.
one of the beauties of the o/r mapper data layer is that the "DB stuff" you refer to does not need to be developed at all. the mapper can perform your CRUD without any procs or any custom methods. Given a good mapper and a well formed map, the mapper is
fully capable of getting any data you need.
The benefit of simultaneous development is lost since I dont need a DBA, nor am i writing anything custom to get my data. My data access layer already exists and i can resuse in against all of my databases.
How can writing stored procs for all your CRUD be faster than not writing stored procs?
I don't write crud store procedures, my code generator does that for me. the only store procedures i write are the non-cruds on that do backend processes, or pull reports, or queries. The code generatoe takes care building wrappers for these.
agent_smith
Contributor
2030 Points
572 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 11:44 AM|LINK
>>Store procedures are in the database, can get any more central than that. With tools like sql-compare you can easily manage schema changes from your developement db to your production db.>>
What if you have more than one data source, i.e., an XML feed from one system, an MS-SQL database, a nightly import from some other system...?
IMHO, I have to disagree. Databases are not 'central' to the application. The 'application' is central to the application. Databases are one or more places to STORE data. Using stored procs and functions and security and such is a good idea, but is a side issue to application design.
Additionally, you make have to a create a new application to work with an existing database. The new application should be designed to mirror the business process and best represent the business objects, which may not be precisely how the database is designed. Your DB layer would translate between DB schema and object schema, but your object schema should be the bible on what the application looks like.
AGENT_SMITH
mbanavige
All-Star
134967 Points
15422 Posts
ASPInsiders
Moderator
MVP
Re: Business Layer in stored procedure?
Feb 23, 2006 01:35 PM|LINK
Since you can also do this with application code, it does not represent a benefit of a stored proc.
Personally, i prefer to centralize everything in my codebase rather than split functionality between code and DB.
Incorrect. The execution plan for a stored proc is created when the proc first runs and placed in a memory cache. Parameterized SQL statements also get their execution plans created the first time they execute and then cached.
Both techniques will reuse execution plans if they are in the cache and both techniques must create the plan when they are first run.
agreed
True, but this assumes that you require/desire your dba to be your applications security admin. Some applications require the user to be able to grant /revoke rights to certain objects.
This doesnt make stored procs better though. A really good DAL can get/set any data without the need for any stored procs. The speed increase you get is measured in application development time. No procs to write, means less time / less cost. many mappers dont preclude the use of stored procs though if you still need them.
By how much? Remember, execution plan caching appies to both stored procs AND to ad-hoc parameterized sql.
A layer is supposed to exist between your app and the database. The mapper exists as part of your DAL.
The selection of an O/R mapper is not to be taken lightly but the benefits that a good one can provide defineately [IMHO] outweigh the learning curve.
As far as where to invest my time learning...since adopting a mapper based DAL approach, i now have much more time to invest. our mapper technology provides us with a very rich set of functionality that we used to have to recreate for each application.
Thi s does not mean i would never use a stored proc, but i find very few instances where i could conclude that a proc would truly be a better choice.
macrap
Participant
870 Points
174 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 01:57 PM|LINK
Ahh... the age old debate... I always put in a reference to Frans Bourma's blog. He if any should know a thing or two about stored procs and o/r mappers.
Stored procedures are bad, m'kay?
Sane Productions
- Because it's too easy being insane
www.sane.dk
GOHDS
Member
480 Points
97 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 02:54 PM|LINK
Hi All
In my post i gave you the reason why i use store procedures and how it has worked well on my situation. I'm not saying they are better, than this or that, some of the issues that are being raised aginst not using store procedures have more to do with the developer or the shop than the actual technology. Making a statement "Store Procedures are Bad" without backing up with facts serves no purpose,
In my shop is all about agility the faster, i can deliver the solution the happier the customer. I find that having a simple proccess that's repetitive and not error prone will give me the edge. I find the database the ideal place to put my business logic. The database has all the meta data i need to create my dal, is a central depository. When I make changes to my database, my dal generator picks those up and creates a new dal componenent, my application is always in-sync with the database. Once i'm done testing my changes, i run sql-data compare and it creates a script for me, to update my production database, it also documents all my changes. This is all automated, push a button no brainer type of a process, that gives me the agility to make my customers happy, that's the bottom line for me
ray
mkamoski
Contributor
5694 Points
1565 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 04:04 PM|LINK
macrap--
Regarding this...
...I think the term "Bad Penny" is more suitable.
:-)
What can be said on the topic has been said.
Bouma is right. That should be clear by now.
(BTW, thanks for doing all that research, Frans.)
Also, one other point, with the advent of SqlSever2005, where REAL, OO languages such as VB.NET and C#.NET can be used in the databse rather than SQL, to prefer the use of SQL is a stance that is somewhere quite a bit south of insane.
(But, of course, this applies only to SqlServer2005 and, I hope, all other databases that choose to evolve rather than rot.)
Other than that, I have no strong feelings on the matter. br />
That all just my opinion. Take it or leave it. Note that I reserve the right to wrong.
:-)
rponton
Participant
1460 Points
292 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 07:10 PM|LINK
But also, IIRC, the glue between the Java/.NET StoredProcs and the rest of the system was pretty weak.
mbanavige
All-Star
134967 Points
15422 Posts
ASPInsiders
Moderator
MVP
Re: Business Layer in stored procedure?
Feb 23, 2006 07:56 PM|LINK
That statement supports the concept of NOT using stored procs.
How can writing stored procs for all your CRUD be faster than not writing stored procs?
agent_smith
Contributor
2030 Points
572 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 08:14 PM|LINK
*hypothetically* stored procs offer faster development becuase you may have more in-house talent for DBA stuff than you do C# (for example) coders.
Applications can be developed while DB stuff is developed simultaneously.
I love OO but prefer stored procs becuase it keeps people who are good at DB stuff but mediocre at application development out of the application development side.
It is also possible that (gasp) I may not actually be that good at writing SQL queries, regardless of where they are written, but be REALLY good at coding C# (or whatever).
I think procs and non-procs are a wash for all the reasons being debated, but I will submit that from a project management point of view, they are easier to keep track of and assign duties.
AGENT_SMITH
mbanavige
All-Star
134967 Points
15422 Posts
ASPInsiders
Moderator
MVP
Re: Business Layer in stored procedure?
Feb 23, 2006 09:26 PM|LINK
Let me rephrase my previous statement slightly
As a developer, i do not need to write sql or stored procs nor do i need a DBA. I simply write code that uses business objects. Lets assume hypothetically that there is just one (1) of me.
Is a Developer AND a DBA better? That would be double the manpower / double the expense.
one of the beauties of the o/r mapper data layer is that the "DB stuff" you refer to does not need to be developed at all. the mapper can perform your CRUD without any procs or any custom methods. Given a good mapper and a well formed map, the mapper is fully capable of getting any data you need.
The benefit of simultaneous development is lost since I dont need a DBA, nor am i writing anything custom to get my data. My data access layer already exists and i can resuse in against all of my databases.
GOHDS
Member
480 Points
97 Posts
Re: Business Layer in stored procedure?
Feb 23, 2006 09:54 PM|LINK
How can writing stored procs for all your CRUD be faster than not writing stored procs?
I don't write crud store procedures, my code generator does that for me. the only store procedures i write are the non-cruds on that do backend processes, or pull reports, or queries. The code generatoe takes care building wrappers for these.
ray