A future for stored procedures?
-
( Posted earlier in Strategies but hoping for more feedback here )
What future do we see for stored procedures in a .net environment ( outside of dba admin tasks )? Our new application uses C# programming against SQL Server 2000 Enterprise with a browser interface for our customers. ( an upgrade from our legacy foxpro environment ). So far I see no use at all of stored procedures. Running profiler shows that the development team for this new application is generating the queries on the fly via an object. The queries may be standardized somewhat in the C# code with varying parameters passed in ( see sample queries below )
The database is very highly normalized. I realize the query optimizer can cache execution plans for adhoc queries if the same query is run over and over again. Are stored procedures becoming a thing of the past except for administrative functions?
Now that this new product has loaded the first of 300+ clients' data, it may be difficult or impossible to change the data access approach.
Since the plan is to have all client data in one big database, I'm now pondering the backup/restore implications of that along with the performance issues that may arise with this data access approach as we get into millions of records. The argument was that with no stored procs, only the C# code has to be changed even if the database design changes or even if we went to a different DB platform -- very unlikely. Also, and this is true, C# is a much richer development tool than Tsql.
The method by which the "adhoc" or dynamic queries are created is a black box object in C#. Somehow, based upon the user input from the browser, queries with parameters are generated.
This is the sort of adhoc query I'm seeing in profiler: application name is:.Net SqlClient Data Provider ( again, no stored procedures in use except sp_executesql )
1. exec sp_executesql N'SELECT DISTINCT A0.BUSINESS_PARTNER_STEP_DEF_ID AS A0_BUSINESS_PARTNER_STEP_DEF_ID,A0.BUSINESS_PROCESS_STEP_DEF_ID AS A0_BUSINESS_PROCESS_STEP_DEF_ID,A0.BUSINESS_PARTNER_ID AS A0_BUSINESS_PARTNER_ID FROM BUSINESS_PARTNER_STEP_DEF A0 INNER JOIN ((BUSINESS_PROCESS_STEP_DEF A1 INNER JOIN (BUSINESS_PROCESS_DEF A2 INNER JOIN BUSINESS_PROCESS_ITEM_DEF A3 ON A2.BUSINESS_PROCESS_ITEM_DEF_ID=A3.BUSINESS_PROCESS_ITEM_DEF_ID) ON A1.PARENT_BUSINESS_PROCESS_DEF_ID=A2.BUSINESS_PROCESS_ITEM_DEF_ID) INNER JOIN BUSINESS_PROCESS_ITEM_DEF A4 ON A1.CHILD_BUSINESS_PROCESS_ITEM_DEF_ID=A4.BUSINESS_PROCESS_ITEM_DEF_ID) ON A0.BUSINESS_PROCESS_STEP_DEF_ID=A1.BUSINESS_PROCESS_STEP_DEF_ID WHERE ((A0.BUSINESS_PARTNER_ID = @p1) AND A3.NAME = @p2) AND A4.NAME = @p3', N'@p1 int,@p2 nvarchar(4000),@p3 nvarchar(4000)', @p1 = 9004, @p2 = N'RELEASE_INTEREST_ELECTRONICALLY_BP', @p3 = N'RESOLVE_ELT_ERROR_RESPONSE'
2. exec sp_executesql N'SELECT DISTINCT A0.BUSINESS_PROCESS_ITEM_DEF_ID AS A0_BUSINESS_PROCESS_ITEM_DEF_ID,A0.CREATED_DATE_TIME AS A0_CREATED_DATE_TIME FROM BUSINESS_PROCESS_DEF A0 INNER JOIN BUSINESS_PROCESS_ITEM_DEF A1 ON A0.BUSINESS_PROCESS_ITEM_DEF_ID=A1.BUSINESS_PROCESS_ITEM_DEF_ID WHERE A1.NAME = @p1', N'@p1 nvarchar(4000)', @p1 = N'CONVERT_DATA_BP'
exec sp_executesql N'SELECT DISTINCT COUNT(*) FROM BUSINESS_PARTNER A0 INNER JOIN ORGANIZATION A1 ON A0.ORGANIZATION_ID=A1.ORGANIZATION_ID WHERE A1.SHORT_NAME = @p1', N'@p1 nvarchar(4000)', @p1 = N'CA'
-
The arguments in favour of stored procedures are mainly:
- Performance - pre-optimised query plans and smaller command text ?
- Expertise - an SQL expert can do the queries
- Re-use - putting your business rules in the SQL layer ?
- Security
You can use stored procedures to control security, but only if the application is designed that way from the start. The strategy has problems with using any dynamic SQL at all, which I have experienced to be almost a fatal flaw. But if you have multiple clients in one database, stored procedures might good if you have to restrict client groups to specific subsets of data (row-oriented security). If you rely on the application to do row-level security, it could be bypassed by users with an ad-hoc tool like MS-Access. I'm only guessing whether thats relevant.
Expertise and Re-use: as you say, the more logic you put into stored procedures, the more you would have to maintain logic in two places instead of just the application. I have worked in an environment which attempted to put all(?) business rules logic into stored procedures so the front-end would be trivial, and I confirm its bogus.
Performance: I believe there is a potential performance benefit, which might be significant with a high level of interactive traffic. I have not tested it myself but I have read sufficiently authorative statements to believe it, however, I have never seen it quantified. If a complex query (with joins and non-unique whereclause conditions) is used 50 times per minute (total for all users) then it might be significant. I think that, even if the query is parameterised using a Command object, it is specific to the user, so with a high number of users(?), there might be too many query-plans for them to remain cached for long.
Again, from experience of trying to use T-SQL for sophisticated programming, I confirm its really not up to it - its not a complete robust programming language. Error handling, for example, is pathetic. SPs are best used to wrap a simple query or insert/update statement, not a lot of procedural logic. Editing and debugging has improved a lot with dot-Net Visual Studio. SQLServer "Yukon"/2005 promises the potential to have C# stored procedures. I'm still thinking about that - does the potential to write more procedural code on the database server make it better or worse ?
My final advice on performance is: don't theorise - test. It should only be a moderate effort to create a mini application that performs a number of typical queries with random parameters, and run 10 instances on 10 workstations. Then try it with stored procedures. A couple of days work is better than a weeks speculation, especially if you have concerns.
-
I use .NET and Stored Procs heavily. Since I basically control the Business Logic and Database aspects of an application, I want to make sure that other programmers do NOT have to know the Business Logic. This can be handled in the Stored Procs. This allows far less training time for new Programmers. All they need to do is code, not learn every single Business process and logic.
-
There are several very good reasons that those who know what they are doing will continue to use stored procedures.
Performance. Although SQL does have the capability to cache the execution plans of ad hoc queries, stored procedures have a much better chance of having thier plans reused on subsequent executions. There is an interesting article that discusses this point here: http://www.sql-server-performance.com/gv_sp_views_selects.asp I recently ran some tests on one of our busiest servers (over 2 million SQL requests per hour at peak) and the difference in compilation time between a stored procedure and the same query passed in as an ad hoc request came out to be over 15 minutes of CPU time per hour and that's before I factored in the network. The call to a stored procedure took a single network packet while the ad hoc query took 5 (and this was a fairly simple select statement) so you are generating about five times the network traffic than is necessary. I had no way to determine just how much of the servers resources were required to respond to those extra network packets, but rest assured, it wasn't zero.
Business Logic. A database is NOT just a dumping ground for data. A properly designed database is a representation of the business rules that govern how the data in question is to be created, modified, and used. In the absence of those rules (constraints) data is meaningless. A well designed database supports the concept of data independence, which in part means that the data is not dependent on any given application. Applications tend to come and go, but data has a way of sticking around. Data also tends to be accessed through multiple applications. Given these facts, it makes sense to keep the business rules that give the data meanig, close to the data, NOT in an application.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
dcpeterson, I couldn't have said it better!
-
We have saved lots and lots of time spent explaining "data discrepancies" by having the rules centralized in stored procedures and functions.... I wouldn't like to think of life without them.
-
A few other factors that get overlooked a lot or OUTPUT paramters. Many times I will do an insert or update and not care to get a recordset back so I will use the Nor resultset option of an object. But if there is a piece of info I want back like the identity I will pass back out thru a OUTPUT parameter which is much simplier to do with an Proc.
Also, with parameters you get a stricter typing of data than you get with building a string dynamically. And even thou you could submit say code in the input the SP will generally protect you from the potential of an injection attack this is the biggest security issue you get with Dynamic SQl queries.
On top of that I do not give any permissions to tables and in most cases views due to the security strucutre of how Procs work which is another boon to security.
On top of that you can hide some support tables from applications so a user cannot glen more information than you want them too, especially with regards to Web Apps.
You get protection of source, protection of logic, and protection of data.
Furthermore you get the ability to add specialed checking and can raise cutom errors in the code to make user friendly errors when the application hits. Some would say do all checks app side to avoid the trip but I many times add error checks in the Proc so I can throw clear errors for any apps built without guidance of things to be aware of.
-
"The argument was that with no stored procs, only the C# code has to be changed even if the database design changes or even if we went to a different DB platform -- very unlikely."
- If the database design changes, then in this case the C# code Has to Change!, If Stored Procedures were used maybe the C# app doesn't have to be recompiled at all!!!
- If the Business processes change then both Sp's and C# would have to change, true.
If You consider the Sp's as "Interfaces" to the database then IMO a cleaner design is accomplished and only the C# components using the Sp's would have to be found and modified in this case.
Considering things as security and code reuse etc I would prefer Sp's as the only way to access the database, and clients Interface to the db through these sp's.
"Also, and this is true, C# is a much richer development tool than Tsql."
- Not for data manipulation at database level. Insert, Update, Delete, Select!
/rockmoose
You must unlearn what You have learnt -
Thanks for all the great input. I already believe in stored procs -- not sure now how to reverse over a year of development which doesn't use them.
Since this hasn't been done, a stress test of the new product on a few million records would be in order now ( I know, why hasn't that been done yet -- don't ask me I'm on the legacy team
-- working on getting that done. -
One thing to also point out is that SQL Server 2005 will allow not only code to be written in c#/vb.net but will also be able to store serialized "objects" within the database itself although it still will not be a true object db. The concept of where SQL Server starts/stops and where the program starts/stops is going to change dramatically. We have been testing both 2005 and .net 2.0 and I have to say, there is going to be alot of need for reconceptualizing what parts of an app go where. Although storing an object in SQL server is probably a waste, generating the object as a serialized xml object and passing it back to the application will allow interaction with a wide variety of clients while centralizing the business logic in SQL server stored procedures. We are still struggling with the implications but we see moving much of our business logic to sql server and stored procedures while having the "client" programm whether it is a web service, a mobile pda, a website, a heavy client, etc. just focus on data presentation.
Just something to add a bit of fuel to the fire of this discussion.
Alex Gadea
Apptik Inc.
-
One point that has not been mentioned (but is perhaps implied) is that the major performance gain of stored procedures is in Network I/O.
A well designed sproc may encapsulate 10 or more SQL statements to achieve a business function. This results in a single request and reply over the network, instead of 10 or more if the client were doing each statement separately (in, say, C#).
Alexqad has discussed the developments in the 2005 beta which allow C# code to run on the server. It may be that this will spell the end of the stored procedure as we know it (or at least a reduced usage) since SQLServer 2005 will allow developers to develop in their preferred language and still avoid extra network I/O.
(I actually still prefer T-SQL for much development work for its brevity when dealing with SQL).
-
I am not rabidly against SPs (I did say performance might be significant), but I am really interested in whether you other guys' have used SPs extensively for absorbing a lot of the complexity out of the application.
There are many powerful programming concepts:
- Encapsulation
- Abstraction
- Hierarchical scope
- 'Complex' data types (eg arrays, structures)
- Structured error handling
.. that T-SQL does not have !
Are all those other languages (eg C#) wasting their time, or is T-SQL inferior for complex programming ?
- Wouldnt you like to be able to read a ROW into a ROW variable (or object) ?
- Dont you ever need to trap potential foreign key errors, and want to identify which constraint is violated ?- an application can get the specific message including object names, but T-SQL can only capture the number and get the generic message, no specific names.
- A call from one SP to another is clumsy in capturing the returned resultset, which limits the value of re-use.
- There are no groupings of stored procedures, there is no scope outside the current procedure but less than the entire database, such as current screen or user-action (apart from user/ownership ?)
Do you end up with complex SPs including things like nested loops, or are they mostly just confirming and maintaining data consistency along the lines of foreign keys ?
-
Clay has a point. This often comes up when designing an interface / presentation layer to my database objects. Recently though, I've been taking advantage of .NET's DataSets & Relationships within those. This has allowed me to handle most of the Foreign-Keys (if you will) in memory Client-Side. The Constraits are mirrored in memory exactly as they are in the Database (and .NET also gives more control).
I often only use (2) Stored Procs per Table. One for Saving (Let's face it, it's pointless to develop one for Inserting, one for Updating) and one for Deleting. In those Stored Procs, I will do F-Key checking myself. I never rely on .NET, T-SQL, or any other thing to display the correct Error for me. Instead, I will raise an Error from the Stored Procs back to the calling code, and I can make it as descriptive as I want.
As far as returning Resultsets from a Database, I never use SPs. I have resorted to User Defined Functions though because they are "syntactically" better!
- Ex.
- Select * From dbo.udf_GetCustomerHeirarchy(CustID)
- Select * From Employees Where EmployeeID IN(Select * From dbo.udf_GetEmployeeHeirarchy(ManagerID))
If I wanted to read a ROW into a ROW object, I would use the .NET DataAdapter and have it "Fill" a DataTable or DataRow with that information. If I wanted that info into a Business Class, I would pass that DataRow into the Class.
-
Tymberwyld has made good points. I too find sprocs excellent for the bread and butter stuff of loads and saves. (And the use of udfs on loads is a good idea too).
The minimal syntax required to pass data from one piece of SQL to another makes T-SQL superior to C# (even without the performance gains).
I think you lose the benefits once you need to start parsing strings trying to pass array data between sprocs - although this all can and has been done.
-
Fellow posters,
First I can state that I am for stored procedures.
You can percieve of the database as "only" a storage media for your data, and let the client application handle all the data manipulation and business logic.
In my opinion this is a bad idea because:
1. All the responsability for understanding the database and the inherent physical design of the database is left to the people designing the client application ( may or may not be good at sql )
2. Changing the database design will be more difficult because all of the client(s) data access logic is tightly coupled to the original design of the database
3. The chances of having duplicate sql code scattered in several client components will be high unless great care is taken in designing the data access components
3a. If new clients will use the database duplicate sql data access code is almost a certainty 🙂
4. Performance will probably not! benefit from dynamic sql executed from clients.
5. Data manipulation routines, data related business rules and data integrity rules are easier to maintain in Structured Query Language than in any other language I can think of ( Yes I do code extensively in C# for example )
On the other hand You can percieve the database as complete application layer, complete with well defined interfaces ( stored procedures, udfs, views ) and with the ability to store and retrieve the data needed by any client application.
The client should not need to know HOW the data is stored in the database ( how and where in which table... )
The client should be presented a layer of well defined stored procedures, views, udfs with which to work with the data stored in the database.
Concluding remarks:
Certainly for data retrieval and manipulation leave this responability to the database and the database designers. The clients using the database should not be concerned with the internals of the database, just use the interfaces provided by the database to get at the data.
I have seen plenty of cases where the database design has needed improvement/changes, but the client coders have long gone home and left all the sql in different business components and not so much as a column name can be changed because it would break the client code - not even a column added( curse of select * ).
Now that is Agile !
/rockmoose
You must unlearn what You have learnt
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply