August 11, 2012 at 10:09 pm
Comments posted to this topic are about the item Avoiding Stored Procedures
August 11, 2012 at 11:22 pm
I'm a little bit sour on ORMs right now. Admittedly, I'm sour on them all because of what happened with just one. As with so many people, I agree that ORM's are a God-send to replace writing CRUD. Or at least that's the way I used to feel.
We're U.S. centric and, as a result, made the choice a long time ago to NOT use NVARCHAR unless we absolutely needed to do so and then only for the columns that absolutely need it. Imagine my surprise when going through the TextData column of an SQL Profiler run to see that all of the character based criteria (WHERE clauses) coming from the apps where coming across as NVARCHAR. It turned out that the ORM being used does that as a default and it requires that the developers first be aware of that "little" problem and then write code in their app to overcome it. So much for simplicity. And so much for INDEX SEEKs!!! HELLLLLOOOOO table scans!
I'm still looking for a way out of this mess but, so far, it's looking like we're going to have to modify all of the application code to make it work right. And, yes, there have been major complaints about how "slow" the database is because of it all.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2012 at 10:09 am
I think Kevin Lawry gets it about right with his article. I think he only mentions re-plumbing to a new db as a side benefit, which is about right in my book as it has never actually occurred for me. (Well, I've re-written some 3rd party code that accessed MS Access databases but that is a different kind of thing).
I think there is a reasonable 60% - 30% - 10% split between 'can be written easily with no loss using EF or similar', 'can be written acceptably, might write a procedure' and 'needs T-SQL' in the data driven applications we create. The big advantage is the keeping more logic in the code, which tends to be easier to maintain, and less prone to error. I would say the speed of development is 60% quicker than using a pure sp approach - due to avoiding switching environments and maintaining more concise and comprehensible code.
I would not say you can keep all business logic out of the database. Constraints and relationships (even triggers occasionally) should always be used IMO and do constitute such. Personally I cannot consider starting anywhere except in the database, for instance using CodeFirst is not something I can really endorse (of course if it works for you!). You do of course need to think pretty carefully about how you use them too - in my experience EF / L2S lend themselves brilliantly to a good caching strategy for frequently used small tables, multiple updates are an issue, surrogate keys are a good idea (so they may not be appropriate for database purists).
All in all, not withstanding the potential pitfalls, ORMs have worked wonders for us.
August 12, 2012 at 10:58 am
It completely shut off when I saw "your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database" since I think this rarely happens.
I would agree with you in the case of enterprise database development where organisational standards tend to dictate common platforms. However, for those developing database-agnostic COTS applications, where they have to support multiple database platforms (to be consistent with organisational standards), there are obvious benefits to database-agnostic ORM frameworks rather than database-specific stored procedures, functions etc
August 12, 2012 at 9:14 pm
I write a lot of SSRS reports and I prefer to have everything run as stored procs so I can see and manage all of them in the database. If I need to change a table I can query across stored procs to see where tables are referenced and know exactly what will be impacted.
I also keep complete comments in the stored procs so I know which reports use them. Admittedly this is extra maintenance that not everyone would adhere to.
The other reason I use stored procedures is because you can't combine multiple datasets into the same tablix ,i.e. data from multiple sources, so doing that by using linked servers in the database and presenting SSRS with one dataset is an efficient solution.
If you had a single code base and the ORM queries were all in one place then that would be easy to manage, but most operational databases I've seen have multiple front ends & ETL or reporting systems attached. If your queries are in more than one place, or in places that are hard to quickly assess the impact of underlying table & view changes then that's an argument for using stored procs. I realise ORM's are supposed to make that problem go away, but if you can't use your ORM of choice for all systems that interact with the database then that argument doesn't apply.
The phrase 'Database Agnostic' makes me think jack of all trades, master of none.
August 12, 2012 at 9:45 pm
FIshNChipPapers (8/12/2012)
It completely shut off when I saw "your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database" since I think this rarely happens.
I would agree with you in the case of enterprise database development where organisational standards tend to dictate common platforms. However, for those developing database-agnostic COTS applications, where they have to support multiple database platforms (to be consistent with organisational standards), there are obvious benefits to database-agnostic ORM frameworks rather than database-specific stored procedures, functions etc
I also think the (counter)argument is largely circular: it rarely happens because the strongly encouraged and therefore widespread use of stored procedures (and other vendor specific features) makes it too expensive.
August 12, 2012 at 9:57 pm
I agree with the vast majority of both the original article and your comments. I was endeavouring to identify a scenario where the ability to develop code that can be ported between database back ends could be a requirement.
August 12, 2012 at 10:27 pm
FIshNChipPapers (8/12/2012)
I agree with the vast majority of both the original article and your comments. I was endeavouring to identify a scenario where the ability to develop code that can be ported between database back ends could be a requirement.
Maybe I caused some confusion: I meant that Steve's counter-argument is circular.
I think there are lots of reasons why portability would be desirable or even a requirement. But as soon as stored procedures become a "best practice", portability goes out the window as a result (and not because we didn't want it in the first place).
August 12, 2012 at 10:59 pm
Alex Fekken (8/12/2012)
FIshNChipPapers (8/12/2012)
I agree with the vast majority of both the original article and your comments. I was endeavouring to identify a scenario where the ability to develop code that can be ported between database back ends could be a requirement.Maybe I caused some confusion: I meant that Steve's counter-argument is circular.
I think there are lots of reasons why portability would be desirable or even a requirement. But as soon as stored procedures become a "best practice", portability goes out the window as a result (and not because we didn't want it in the first place).
The issue is less about stored procedures and more about optimised code for that environment, whether it be T-SQL or PL/SQL or something else. I doubt any ORM could generate more efficient code than a seasoned DB dev. There's a trade-off between portability and performance. Note Jeff's comment on the lack of index usage.
August 12, 2012 at 11:41 pm
I doubt any ORM could generate more efficient code than a seasoned DB dev. There's a trade-off between portability and performance.
Plus ça change! I worked for both Oracle and Sybase in the mid-80s and early-90s and recall the challenges faced by organisations looking to adopt SAP where they had standardised on the latter. The absence of row-level locking, coupled with the database design which originated on the mainframe, posed significant performance challenges and resulted in SAP's lack of support for Sybase (and by implication SQL Server). For COTS developers in particular, other factors need to be considered in the trade-off: time-to-market; market reach; cost of cross-database platform development, support and maintenance. In some cases the trade-off will favour ORM, in others database-specific code and in others a hybrid approach.
August 13, 2012 at 2:30 am
The issue is less about stored procedures and more about optimised code for that environment
Agreed that's it is really about the optimised, non-portable code (that is often, but not always, wrapped in a stored procedure).
August 13, 2012 at 4:24 am
Í am both a developer of ASP.NET MVC solutions and a professional MSSQL DBA. I think it is not so much about stored procedures being better or worse, it is a lot more about the lack of set based programming skills of nearly every developer I know (excluding those who are also a DBA). Most developers do not know how to translate a data processing task into efficient set based code, because they can not think about it that way. Did you ever (as a professional DBA) bumped into a stored procedure written by a ordinary programmer that did something useful without a cursor?
So here's the tradeoff when developing software: you either need a DBA or accept that you use the database only as a rather crippled persistence layer. I say 'rather crippled' because every programmer can give you plenty examples about the impedance mismatch between object-oriented and relational data models. DBA's should act a lot more like data consultants, understanding the needs of programmers and translate those needs into flexible dadatabase models. They should be able to participate in the application development task offloading these programmers from the tasks that can be handled so much better by a stored procedure inside a database. This requires additional skills for DBA's to fully understand what programmers expect from the database (not just what they ask) to avoid all kinds of work-arounds nobody wants to maintain.
Yes, I've seen code-first solutions and the ugly databases resulting from that approach. But DBA's are expensive and generally considered a hurdle in the development process, so it's our task to change that image and cooperate instead of pointing at each other. It is true that in many applications most of the code to access databases involves CRUD-style actions. However, any real line-of-business application involves some business specific processing that might not involve a lot of code, but some small pieces of heavily used code that may benefit from using stored procedures to ensure the processing is done with the proper tools. When a database grows and performance degrades, well chosen indexes may offer some relief. If an application implements the same task using row-by-row processing, nothing will help to bring it up to speed again.
ORM's provide programmers with a tool to access the data. When used properly it may speed up development of applications without significant loss of quality. In certain scenario's you don't even need a DBA anymore to offer solutions involving data storage and persistence. But in most cases, it is just a tool that should be used wisely. You need the skills of a DBA to avoid fundamental design flaws that will cause performance problems as soon as the amount of data grows way beyond the size of the small testset used by the original developers. Don't blame the database (or the DBA) for these design flaws, just blame those companies that develop software fast and cheap without consulting a DBA.
August 13, 2012 at 5:43 am
Vliet, you took the words right out of my mouth. I think our .Net, ORM, no-DBA database and application is also hampered by the fact that its serving as both the OLTP and OLAP database ( for now ).
August 13, 2012 at 5:48 am
I have to side with Vliet. I have worked in both worlds at the same time. I have had .Net programmers bring me SQL code that consisted of cursors because they had only been trained to think 1 record at at time, not one set of rows at a time. That type of sequential processing was born with tape storage and is still supported today by virtually any environment with a FOR, WHILE or DO construct. When I showed them the same query with a couple of sub-selects...and sub-second response, they were amazed.
The problem I see with delegating that development responsibility for SQL code is you provide a tool that can do things but can't tell you that it is a bad idea. Just because you can join 2 tables together doesn't mean you should. A true SQL developer who understands the set-based execution method of SQL Server should see that.
I am all for abstraction layers if that helps you develop better code, and that is the power in ORMs. Any ORM worth it's salt can call a stored procedure. Now the data layer acts as a black box. The ORM and the programmer need not know what machinations are necessary in the stored procedure, CRUD or not. This achieves decoupling and I think even that ancient concept is still considered a good idea.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
August 13, 2012 at 7:24 am
Then you have to weigh the fact MS is dumping LINQ in favor or to be deteremined. Many folks are still saying Entity Framework but rumor is that will be replaced as well. My beefthough with using ORM for security by paramterizing the code is that now open the database to a direct attack as the service account has to have access to the tables directly. My last project I wrapped all transaction logic in Stored Procedures becuase developers are not to have direct access to the data in the database. Not that it is unbreachable but the developers who found out the service account still cannot get information as easy as select * from table, they are stuck with going through the stored procedures. A good hacker can breach your code and get the information neccessary to get in the database, to believe parameterizing the CRUD code is going to be the end all solution basically are locking the front door while leaving the back door open, eventually someone will get in from a public site.
Viewing 15 posts - 1 through 15 (of 142 total)
You must be logged in to reply to this topic. Login to reply