Stored Procedures under .net
-
What future do we see for stored procedures in a .net environment? Our new application uses C# programming against SQL Server 2000 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. These queries look pretty convoluted to me ( more joins that I would expect ).
The database is very highly normalized and I think I've heard of some "back stepping" away from that for performance already. 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?
Randy Petty randyp@fdielt.com
-
I haven't got my head around the full ADO.NET feature set yet but from what I have seen .NET provides a great deal of freedom to get away with bad programming practice.
For example, it is alleged that you don't have to worry about garbage collection because .NET does it for you. Yes it does but if you ignore garbage collection altogether then your app is not going to be very efficient. Put your app on a high traffic web server and you will soon find to your cost that there is a benefit to old-fashioned programming practices.
As for stored procedures becoming obsolete. I don't think so.
The data-adapter allows you to specify what statements should be used to update, insert, delete and select records and therefore you can specify stored procedures.
If you use a datareader object then stored procedures are going to bring back the recordset very efficiently. Particularly if your recordset is the result of complex data manipulation.
.NET is a fantastic toolset and it makes a pretty good fist of being all things to all men. I think some of the features are put there so that people without specific SQL expertise can produce code that previously would have required that expertise.
-
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 at all )
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'
3. exec sp_executesql N'INSERT INTO XML_SERIALIZED_INSTANCE (LAST_MODIFIER,LAST_MOD_DATE_TIME,TRANS_SEQ_NUM,CONCRETE_TYPE,XML_DATA) VALUES ( @p1, @p2, @p3, @p4, @p5 ) ;select SCOPE_IDENTITY()', N'@p1 int,@p2 datetime,@p3 smallint,@p4 nvarchar(75),@p5 nvarchar(4000)', @p1 = 1001, @p2 = 'Jul 2 2004 5:45:06:143PM', @p3 = 0, @p4 = N'Fdi.Po.CaOwnershipDocPropertyAdditionalFields', @p5 = N'<?xml version="1.0"?> <NameValuePairs xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <NameValuePair key="MileageOrKilometerIndicator" /> <NameValuePair key="PriorHistoryCode" /> <NameValuePair key="Axles" /> <NameValuePair key="HullMaterialVessel" /> <NameValuePair key="VlfExemptionCode" /> <NameValuePair key="BrandCode" /> <NameValuePair key="CurrentOdometerDate" value="000000 " /> <NameValuePair key="TypeLicense" value="11" /> <NameValuePair key="EquipmentNumber" /> <NameValuePair key="MotivePowerOrVesselFuelCode" value="G" /> <NameValuePair key="PriorOdometerDate" value=" " /> <NameValuePair key="UnladenWeight" value="00000" /> <NameValuePair key="OdometerReading" value="000000" /> <NameValuePair key="OfficeWorkDate" value="3/12/1994 12:00:00 AM" /> <NameValuePair key="LicenseNumber" value="2YXZ246" /> <NameValuePair key="ExpirationDate" value="950408" /> <NameValuePair key="PropulsionVessel" /> <NameValuePair key="VesselLengthFeet" value="000" /> <NameValuePair key="EngineNumber" /> <NameValuePair key="BodyOrHullType" value="0" /> <NameValuePair key="Licensechange" value="12/30/1899 12:00:00 AM" /> <NameValuePair key="VesselLengthInches" value="00" /> <NameValuePair key="AsteriskYearOrVesselYearBuilt" value="94" /> <NameValuePair key="VlfClass" value="BB" /> </NameValuePairs>'
-
Hi there...
We are using .NET (VB .NET) frontend applications to analyse Data from SQLServer 2000. We use stored procedures where we can.
First of all, when using stored procedures through the Command Object and adding Parameters, SQL-Injection is avoided.
Secondly, by seperating the business logic and data logic more scalability is acchieved. If I have a reporting tool, that runs stored procedures, and also have the structure of the application in a table, I can easliy add new reports as stored procedures to the application, without having to change one piece of VB .NET code.
Last but not least, I find it easier to work with sp's from .NET than I did working with other environments. Because of this I used to rather build a statement and then just call the DB - not anymore.
About using DataReader vs. DataSet - I prefer using DataSet, because it gives me the chance to sort, filter in the frontend, even when the connection has been shut. Plus the DataReader can only be used once, and while it is in use the connection is still open. The DataSet gets the resultset, shuts the connection and lets you sort out the rest. It can be used more than once and can even be used to represent relations in the DB between multiple tables. I am normally not a microsoft fan, but I DO like .NET.
About those Queries you have there, talk to your developers, and see if you can get them to use stored procedures - talk to your boss about the efficiency of seperating logic and see if your company can make it a policy.
Greetings from germany
~nano
greetings from Germany
-
Looking at your sample SQL calls, those are exactly what would you would expect from a pre-.NET application that didn't use stored procedures...
I believe that changing your client data access method shouldn't really be having any effect on your database design at all - you should still be doing data operations close to the data, providing data access only through strictly-defined access paths (stored procedures), and definitely not saying 'oh we are using .NET, all our data access will be by ad hoc SQL' !
If I came to you and said, "I am porting my app from C++ to VB, is it OK if I don't use stored procedures any more?" you would quite rightly tell me that the front end language makes no difference to correct data access. This is no different.
ps moving from a legacy foxpro app to a shiny new C# over SQL environment is a wonderful opportunity for your developers to do it right from the start. Don't let them treat SQL Server like a Foxpro database
pps Denormalising a database 'for performance' is very very rarely a good idea
-
Thanks for the feedback. Now that this new product has loaded the first of 300+ clients' data, it may be difficult or impossible to get the devel team to switch their 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. Their 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. Could you suggest a better forum for this topic where I'd get more advice?
thx RP randyp@fdielt.com
-
This is a mixed bag. SPs will still have a great deal of use for SQL functionality that is very close to the data.
On the other hand, SPs have been historically (mis) used for wrapping more conventional programming tasks, and for these .NET provides enormous improvement. In SPs there is no object structure, no inheritance, no overloading, no namespace management and only primitive flow controls---in short, other than data manipulation, we're dealing with a language no more sophisticated than GW Basic.
In the future I see SPs used for what they do best, without needing to code a lot of other programming logic in them as well.
...
-- FORTRAN manual for Xerox Computers --
-
In our .NET applications, every database query, every SqlDataAdapter command object uses a stored procedure. It helps maintain an n-tier environment. Why have your queries in code where you will have to recompile and re-distribute for and data store change?
-
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.
I'm part of the legacy team which will inherit this new application and we're just now getting a peak at the data structures and data access techniques the development team chose. I can see in profiler that the queries do repeat from time to time -- how the query optimizer is able to cache these plans I don't know.
Right now we only have around 10,000 production records in the new datbase -- eventually that is supposed to grow to around 20 million so we'll see how it performs.
-
Stored procedures are the absolute way to go. Some app developers like to argue against them b/c they think that equals job security. Regardless of performance, you still have distribution and risk with app code that you don't have with proc code. If they are good app developers, you can assume the app code exists in one place in the app. So, a change would mean, in the least, a change to the app, a re-compile and re-distribution to 300 desktops. This also assumes that there is no other front-end. If a proc was used, the proc is updated in one place and distributed to a prod server. There is no re-compile, no distribution and, most importantly, NO DOWNTIME for end users (which can be significant depending upon the distribution method). Additional benefits come from scalability as that proc could feed more than one process/app, in which case the benefits of the proc outweigh the app code beyond comparison. Procs are not necessarily an application-level solution, but rather an enterprise solution. You should encourage developers to build things that promote re-use and scalability so you can spend more time delivering the changes the business needs, rather than changes driven by poor architectural choices.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply