July 30, 2008 at 8:34 pm
I write to make the point that development standards differ depending on whether an application is being written for sale as a software product or for use within the company by, for instance, researchers. The SQL Server work I have enjoyed the most has been creating dictionaries documenting tables, views, ***and stored procs** for use by the data owners in writing ad-hoc queries. In documenting the T-SQL I originally wrote, I have discovered where it was necessary to consolidate procs and improve queries. A well-documented, well-tuned database supported by a DBA whose skills include business process modeling, ER diagramming, T-SQL writing, SQL Server sysadmin, and knowledge transfer-- this is my ideal. In this ideal world, all the T-SQL is in the database where it can be read with queries; database permissions are the keys to the kingdom; and a UI developer who forgets that the users are the owners of their data is shown the door.
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
July 31, 2008 at 6:11 am
I'm not sure where I come down on the issue, but I've got a question for those folks who think stored procedures should always be used to access the database (and have good experience with the approach).
- Do you find yourself writing a substantial new set of Stored Procedures for each new application? Or do you normally set up the bulk of the stored procedures for the first application and then make minor tweaks and additions as you increase the number of applications that target the database?
If it's the first scenario, I'd argue that the stored procedures aren't really encapsulating or abstracting the database - they're really part of the application. If it's the second, then I can see how you could consider the stored procedures to be an API for your data.
Thanks for any response.
July 31, 2008 at 6:46 am
sgoldman (7/31/2008)
- Do you find yourself writing a substantial new set of Stored Procedures for each new application? Or do you normally set up the bulk of the stored procedures for the first application and then make minor tweaks and additions as you increase the number of applications that target the database?
In my experience the bulk of the work is done early and then there are tweaks and additions with the additions normally being for reporting purposes.
I admit to being a stored procedures guy, but I understand that in a small app, you may choose to go with SQL in the app as well. I have an application I have developed for personal use and I don't use stored procedures at all. Partly because I wanted to try out Linq to SQL and partly because it is a small, personal, app that does not need to scale and that I am the sole developer of.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 6:56 am
sgoldman (7/31/2008)
- Do you find yourself writing a substantial new set of Stored Procedures for each new application? Or do you normally set up the bulk of the stored procedures for the first application and then make minor tweaks and additions as you increase the number of applications that target the database?
...
This question is not quite right is it?
Should it not be ".....writing a substantial new set of Stored Procedures for each new database...."?
Because if you used stored procs and connect to a new application you'd be using the same db and the same stored procs so the answer is no - unless you were adding a new "view" of the data for the new app.
If its a new db then yes for the new structure - this would apply for to any ORM/DAL as well -you would have to generate a new interface to the data.
Hell did that make sense? 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
July 31, 2008 at 7:00 am
I must have been lumped in the PRO sprocs column but "it depends" is really how I work
In my current job, I use stored procedures exclusively - but I've gotten into a groove
I do the same basic kinds of data access - getting a dataset to bind to a drop-down, getting a dataset to bind to a datagrid or updating one record
in my previous ASP.NET I found myself often needing one indicator from one table - in that case I would string together the SQL needed in code
in my current application all SQL is in stored procedures, in the previous ASP.NET application mine was by no means the only embedded SQL
so do as the Romans (or in one case South Africans) do
July 31, 2008 at 7:03 am
Seggerman (7/31/2008)
...in my previous ASP.NET I found myself often needing one indicator from one table - in that case I would string together the SQL needed in code
....
You are just talking about a snapshot value?
Thats ok as long as your table/column names are static.
Did you wrap these up in reusable functions and put them all together in a code module?
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
July 31, 2008 at 7:27 am
Shaun McGuile (7/31/2008)
sgoldman (7/31/2008)
- Do you find yourself writing a substantial new set of Stored Procedures for each new application? Or do you normally set up the bulk of the stored procedures for the first application and then make minor tweaks and additions as you increase the number of applications that target the database?
...
This question is not quite right is it?
Should it not be ".....writing a substantial new set of Stored Procedures for each new database...."?
I'm envisioning a situation where you've got an existing database containing a lot of business information, and you want to start using the data in a new way. For example, let's say you've got a database that holds your inventory and work-in-process information. You already have applications that allow you to move items, change status, and see reports necessary to do your normal production. Now you want an application that allows you to view a flow diagram of dollars through your plant by combining the pricing information (also in your database) with the production information.
It's just a silly example, but the underlying question is - how reusable are these stored procedures? If, because of the different needs for each different application, you end up writing new stored procedures for almost all data access by each application, I don't see the real benefit.
July 31, 2008 at 7:38 am
Well as I said "no" is your answer, same db no need to change your stored procs, you just add the new code that does the new things you require.
You don't re-carpet your sitting room when you buy a new Television set, do you? 🙂
--Shaun
Hiding under a desk from SSIS Implemenation Work :crazy:
July 31, 2008 at 8:04 am
sgoldman (7/31/2008)
I'm not sure where I come down on the issue, but I've got a question for those folks who think stored procedures should always be used to access the database (and have good experience with the approach).- Do you find yourself writing a substantial new set of Stored Procedures for each new application? Or do you normally set up the bulk of the stored procedures for the first application and then make minor tweaks and additions as you increase the number of applications that target the database?
Good point. In my experience applications that have many stored procedures over time will end up with 2 or 3 version of similar stored procedure, as to not break compatibility with other applications or modules. This is because SQL is really not that dynamic itself, only the results are dynamic based on parameters. Think of every facet of a single line SQL statement, the field selection, group by, having, joins, etc. This are all very static constructs, while they can have conditions the language is far from flexible.
When you contrast this with a modern OOP language such as C# or Java you can have polymorphic queries or persisitence. Therefore your code can actually be resused and extended. With an ORM solution you end up with the same consistency of having one person write all of your stored procedures but you get the OOP flexibility letting you design more agile applications that can easily manage change.
July 31, 2008 at 8:14 am
giving an app direct access to all your tables is like writing a class where EVERY member is public.
both are bad ideas.
---------------------------------------
elsasoft.org
July 31, 2008 at 8:20 am
scott.w.white (7/31/2008)
sgoldman (7/31/2008)
I'm not sure where I come down on the issue, but I've got a question for those folks who think stored procedures should always be used to access the database (and have good experience with the approach).- Do you find yourself writing a substantial new set of Stored Procedures for each new application? Or do you normally set up the bulk of the stored procedures for the first application and then make minor tweaks and additions as you increase the number of applications that target the database?
Good point. In my experience applications that have many stored procedures over time will end up with 2 or 3 version of similar stored procedure, as to not break compatibility with other applications or modules. This is because SQL is really not that dynamic itself, only the results are dynamic based on parameters. Think of every facet of a single line SQL statement, the field selection, group by, having, joins, etc. This are all very static constructs, while they can have conditions the language is far from flexible.
When you contrast this with a modern OOP language such as C# or Java you can have polymorphic queries or persisitence. Therefore your code can actually be resused and extended. With an ORM solution you end up with the same consistency of having one person write all of your stored procedures but you get the OOP flexibility letting you design more agile applications that can easily manage change.
Come on guys thats just sloppy lazy coding, I'll can your *** if I catch you doing it! 😀
Code reuse is at the heart of both sides - using an interface to expose the functionality required is what its all about. I am in neither camp. Use what works for you, but above all else do it right in a logical consistent manner. 😎
Hiding under a desk from SSIS Implemenation Work :crazy:
July 31, 2008 at 8:21 am
jezemine (7/31/2008)
giving an app direct access to all your tables is like writing a class where EVERY member is public.both are bad ideas.
Not really you control your access still to Insert, Update, Delete, Select still to different users.
If you have sprocs that do CRUD operations then users still have access to insert, update, delete.
July 31, 2008 at 8:36 am
No the (application)users have acces to the application(s).
The application(s) has/have access to the stored procs.
The stored procs have access to the tables.
This is what is happening using either method - ORM or not.
This is not the same as users accessing tables at all, it is done in a controlled manner.
There are no application users who are database users - i.e. that have a login to SQL Server or the box it sits on.
If SQL Server (or any RDBMS) provided its own ORM layer then we'd all be happy 😀
or not! 😉
Alternative DB = Cache a Post Relational Database (it does objects and SQL)
Hiding under a desk from SSIS Implemenation Work :crazy:
July 31, 2008 at 8:39 am
scott.w.white (7/31/2008)
jezemine (7/31/2008)
giving an app direct access to all your tables is like writing a class where EVERY member is public.both are bad ideas.
Not really you control your access still to Insert, Update, Delete, Select still to different users.
If you have sprocs that do CRUD operations then users still have access to insert, update, delete.
Sure all users have rights to insert/update/delete, but especially with update and delete I am controlling HOW they can do it. By granting those rights directly on the table a user can (and it only takes 1) do update personnel set salary = salary * 1.1 in EXCEL, ACCESS, etc..., but if they can ONLY update using stored procedures they can only run the updates with the parameters I require. I also don't have to worry about the delete without a WHERE clause because I don't allow deletes outside the API I provided through the stored procedures.
Direct Access to the tables means direct access from ANY application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 9:20 am
DBA's are arguing for their share of "control" and non DBA's arguing for openness. After having read the article and the forum replies, I see a trend that is largely ignored.
In the real world, small workplaces solving real life issues with a SQL Server instance, 2 vb/c# developers and no DBA are going to do what they do best and put most of the data access in the code. It works for them and they can maintain it. Forget the beer truck syndrome, it doesn't come into play, since vb/c# devs are cheap and available by the dozen compared to a DBA.
At places that are a bit bigger with DB server farms and slotted DBAs, they are fanatical about DB security and "everything in a sproc" mentality.
It used to be where creation of the SQL was a "voodoo" subject and only the DBA's managed the procs, triggers and SQL. Since the advent of Access on the desktop, everyone and their grandmother thinks they can write SQL code and actually be good at it. This has eroded the position of DBA's since that time.
I am of the opinion that the CRUD should be in the DAL, since the tools like SubSonic, LLBLGen, NHibernate have trivialized the creation of the DAL. I am of the opinion that sprocs written by credible DBA's hold a HUGE amount of value when dealing complex business logic as it relates to maintaining data normalcy and relationships as well as performance.
We somehow have to get to a happy place in the middle.
Viewing 15 posts - 91 through 105 (of 160 total)
You must be logged in to reply to this topic. Login to reply