August 4, 2009 at 1:45 am
We're at a potentially critical point in one of our major software products, where we're (finally!!!) switching from VB6 to .NET for new development. I'm in charge of getting the architecture right before we start developing. Well, right enough so that it can be changed relatively quickly and painlessly later if need be. We won't really be changing the database, but we have that option if need be. The VB6 program usually just alters/inserts into the tables with individual SQL statements, but the odd SP does exist. Once again, this will be for NEW DEVELOPMENT, so the old VB6 program will remain like it is for now.
What are your thoughts, as DBAs, on ORM tools/frameworks such as ADO.NET Entity Framework/NHibernate? I've already looked at some tools that we've purchased like DevExpress[/url] but our needs are quite different to the new ORM part of their amazing UI suite. Any suggestions for ORM tools? I'm looking for easy-to-use tools that we can almost just slot into or situation with minimal code adjustments to the tools. While I'm really hoping that we won't need to write it ourselves, we're prepared to do that.
Thanks.
Note: This has been moved from "Database Design » Design Ideas and Questions » Your Thoughts on ORM Tools." My apologies for posting in the wrong section.
August 4, 2009 at 8:51 am
You could have opened a big can of worms here. I have not had a lot of experience with ORM tools, I have used Linq To SQL in a small personal project just to have the experience. What I know about them I don't like. I am a huge proponent of doing data access and manipulation through stored procedures and many ORM tools make this difficult.
The positives of ORM tools from the developer side is you don't have to write a lot of SQL and the classes are built for you so you should get faster initial development.
The negatives are that the ORM tools usually don't provide the most performant SQL and make it hard to use stored procedures. You also have to change the compiled app to address many performance problems. You can't make changes to the database without making changes to the application. If you use stored procedures or views as an API (see today's editorial[/url]) to your database then you can performance tune the SQL more easily and you can make schema changes without affecting the application.
Just my 2¢ worth.
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
August 5, 2009 at 6:55 am
Jack has largely nailed it. ORM tools speed up development, but at the cost on the database side of things. Using the tool straight, no modifications, you get lowest common demoninator SQL. This works for most of CUD of CRUD, but it doesn't work that well for a pretty substantial portion of the 'R' or reads of CRUD. What's worse, many of the ORM tools, nHibernate is my own personal bugaboo, allow developers to drive objects straight out to the database, turning the relational storage system into an object persistance layer. In other words, ignoring how SQL Server works and pretending that simply calling it something else will make all the icky parts of database work go away. Unfortunately, it doesn't. In fact, storing objects without thought to relational design leads to many problems when it comes time to retrieve the data for reporting or integrate with other systems.
But, properly applied, working with the developers to arrive at a good relational desgin, monitoring development so that you can spot when a particular process needed a stored procedure rather than generated code, ORM tools could be a god-send for fast & accurate delivery of well structured systems. But I think that this is the exception and not the rule.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2009 at 8:51 am
I don't have much experience, other than as a client, but I'll let you know that we have a bunch of nHibernate here. It made the initial development quicker, but we now have some performance issues, and it is incredibly hard at times to change things. To me, the initial developer of the app wasn't worth the issues we now have.
August 6, 2009 at 9:04 am
I want to appoligize up front if my assumptions are wrong and if they are then ignore this post, but I wanted to put out a word of warning in any case.
It sounds like you are possibly an application developer and not a DBA. It also sounds like the application development group may drive the database design. This is the biggest no-no in the development world.
Database design left to application developers will almost certainly have flaws and often large ones and these flaws will manifest themselves in time. Sometimes on day one when the application actually goes live but more often down the road when more users are added or more data builds up in the database.
Often application development groups will look at re-writing certain code or at new technology, when the actual problems reside in the underlying database structure and processing.
If you do not have a dedicated and qualified DBA I would suggest you bring this concern to management. If you do, congratulations and use that person wisely.
Good luck.
August 6, 2009 at 9:21 pm
I am not a DBA and I speak from experiences as a developer. Most ORM tools support Stored Procedures. Sometimes you might need to tweak something. For example, Entity Framework (at least in current reincarnation) will not work with Insert Stored Procedures that return newly inserted record ID as an output parameter. So, you might be forced to change your SP generator to return that ID as a recordset (Select SCOPE_IDENTYTY() AS ID) . This is some sacrifice of performance but it's not going to "make of break" your app or a database. As long as you do not deal with some heavy traffic applications the boost that you get in development, time-wise and code quality-wise, is worth having all those inserts and updates to use all fields in the table (assuming that ORM tool generates them that way). Performance issues are most often the result of badly written transaction code, misuse of business entities for reporting purposes and sometimes (rarely) using individual calls to Insert/Update/Delete when situation asked for sending a batch of statements.
I have seen code that would fetch an object after an object from a database 30,000 times to create one aspx form but I do not think that the blame should go to ORM. It was easy to see that page does not load right (even in development). It's just that people are rushed to write and check in code and nobody cares about consequences because the deadline has to be met (who cares about slow database under such circumstances) and only after things go to production and start causing trouble or make application unusable for end users we send escalation team to fix an issue.
Sorry for digression 🙂 but I would rather use ORM and deal with some issues that arise vs. write CRUD SPs or statements by hand. I would feel like moved back in time 10 years. We are supposed to deliver a business value and SQL server hardware and architecture could help with this a little :-).
August 7, 2009 at 4:11 am
Jack Corbett (8/4/2009)
You could have opened a big can of worms here.
Why do you think I'm asking this on one of the biggest SQLServer sites around populated with of the most experienced DBA people that I know of? 🙂
I am a huge proponent of doing data access and manipulation through stored procedures and many ORM tools make this difficult.
Which is why I'm "rolling my own" O/RM system.
The positives of ORM tools from the developer side is you don't have to write a lot of SQL and the classes are built for you so you should get faster initial development.
For this project, meh.
The negatives are that the ORM tools usually don't provide the most performant SQL and make it hard to use stored procedures. You also have to change the compiled app to address many performance problems. You can't make changes to the database without making changes to the application. If you use stored procedures or views as an API (see today's editorial[/url]) to your database then you can performance tune the SQL more easily and you can make schema changes without affecting the application.
This is a time where I can get the architecture for new developments going in a direction that doesn't suck. Please see the bottom of this post, after the quotes, for architecture questions.
Just my 2¢ worth.
Appreciated, thanks.
Grant Fritchey (8/5/2009)
But, properly applied, working with the developers to arrive at a good relational desgin, monitoring development so that you can spot when a particular process needed a stored procedure rather than generated code, ORM tools could be a god-send for fast & accurate delivery of well structured systems. But I think that this is the exception and not the rule.
See my reply to todd.mcdaniel below.
Steve Jones - Editor (8/5/2009)
but we now have some performance issues, and it is incredibly hard at times to change things.
Why, don't you have experienced DBA's on board? Have you considered outsourcing? 😉
To me, the initial developer of the app wasn't worth the issues we now have.
That's why I'm "rolling my own" O/RM system. The existing systems just weren't a good enough fit for our system, and I want to use SPs to update/insert records, especially because we have some business rules that need to be implemented when certain things are inserted into the database.
todd.mcdaniel (8/6/2009)
It sounds like you are possibly an application developer and not a DBA. It also sounds like the application development group may drive the database design. This is the biggest no-no in the development world.Database design left to application developers will almost certainly have flaws and often large ones and these flaws will manifest themselves in time. Sometimes on day one when the application actually goes live but more often down the road when more users are added or more data builds up in the database.
Not only am I the Lead Developer, Lead Programmer, Senior User Interface Designer and Lead Architect, but I am also the the DBA-in-Chief (I like the term "DBA-by-accident" that I learnt here 🙂 I recognise your concerns, that's why I've spent a lot of time hanging around SQLSS and trying to sharpen my database skills quite a bit in the last few months.
If you do not have a dedicated and qualified DBA I would suggest you bring this concern to management. If you do, congratulations and use that person wisely.
We're understaffed as it is, so...
Good luck.
Thanks.
aloj (8/6/2009)
I am not a DBA and I speak from experiences as a developer. Most ORM tools support Stored Procedures. Sometimes you might need to tweak something. For example, Entity Framework (at least in current reincarnation) will not work with Insert Stored Procedures that return newly inserted record ID as an output parameter. So, you might be forced to change your SP generator to return that ID as a recordset (Select SCOPE_IDENTYTY() AS ID).
This is a legacy database, what is this strange "SCOPE_IDENTYTY()" that you speak of? 🙂 When I got here three years ago there were no keys. None. The only index was a UNIQUE index on the history table. I'm not allowed to modify some aspects of the database because those changes (e.g. adding fields) would break the VB6 app and we don't have enough resources right now to make a "significant breaking change" to the VB6 app. The work I've done I've tried to do to the best of my ability, both code- and database-wise with keys, ID fields, indices and stored procedures.
It was easy to see that page does not load right (even in development). It's just that people are rushed to write and check in code and nobody cares about consequences because the deadline has to be met (who cares about slow database under such circumstances) and only after things go to production and start causing trouble or make application unusable for end users we send escalation team to fix an issue.
Use a database loaded full of test data during development! I try and use at least 200 000 rows or so, preferably more. Thanks for teaching me this SQLSS, I picked up some problems in the database by doing this.
----------
Regarding the data objects interfacing with the database, I want see is objects reading and writing via stored procedures. I feel that the coding of the SPs is really sped up by using SSMS's "Script Table As -> SELECT to", as well as avoiding "Select * from". This is not for performance, but because I am trying to build a good API into the database. I discovered this about two years ago on a different project. Also, it will hopefully make things easier to learn if/when a new guy joins us.
The database architecture, well, I need a professional DBA to look at that and sit with me so that we can get things going in a decent direction. Until that gets approved, I guess I'm just going to soldier on.
August 7, 2009 at 5:26 am
Sounds like you have it well enough in hand based on what you're dealing with. Did you get the kind of information you were looking for?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2009 at 6:25 am
Ja, thanks Grant. I wanted to hear some opinions from the DBA world, and I got them. I was expecting a bit more of a harsh response ("O/RM tools are evil! You're banned from the forum for mentioning their names! We have to reformat our servers now!") to be honest. 🙂
It's nice to discover that there are more people than I thought, from both the DBA and the developer camps, who are adopting more of a "middle ground" attitude as opposed to a "my way is right" attitude. Not only here, but other places too.
Thanks guys.
August 7, 2009 at 7:12 am
Well, understand, I am adopting a middle way on this. Unfortunately I'm working with developers who are not. They're goal, stated, is to get DBA's out of the way so that they can deliver code faster. There is no though to data integrity, data integration, reporting systems or long term maintenance. They're not alone in this attitude and most of the people who have this attitude have latched on to ORM tools as the mechanism to enable a code only approach to application development. No more need for that messy TSQL stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2009 at 7:17 am
As far as experienced DBAs, we have had Brad look to tune things. The problem is the ORM layer isn't "tuneable" in that we can't specify the queries in some places. The ORM handles that and it creates it's own SQL. In places we have rewritten queries that improve performance, but in other places the framework is buckling a touch under load.
August 7, 2009 at 7:39 am
This is a legacy database, what is this strange "SCOPE_IDENTYTY()" that you speak of? When I got here three years ago there were no keys.
Scope_Identity returns the last value of your Identity key which may include gaps if there are deletes and aborted transactions.
http://msdn.microsoft.com/en-us/library/ms190315.aspx
Kind regards,
Gift Peddie
August 7, 2009 at 8:20 am
@Grant: Ouch. That doesn't sound nice at all!
@steve-2: While I was jokingly referring to you with that remark (surely the head of any SQLServer site MUST be an experienced DBA?), it is nice to know what's going on behind the scenes here. How long did it take to write with nHibernate? How long do you think it would've taken without it?
@Gift: Thanks Gift, but I was trying to emphasize that the database was a legacy (SQL2000) database and so "SCOPE_IDENTYTY()" wasn't available (AFAIK...) We're now on SQL2005 so things are a bit better now.
August 7, 2009 at 8:34 am
@Gift: Thanks Gift, but I was trying to emphasize that the database was a legacy (SQL2000) database and so "SCOPE_IDENTYTY()" wasn't available (AFAIK...) We're now on SQL2005 so things are a bit better now.
Actually there was Scope_Identity in 2000 may be your application just did not use it.
http://msdn.microsoft.com/en-us/library/aa259185(SQL.80).aspx
Kind regards,
Gift Peddie
August 7, 2009 at 8:41 am
Thanks Gift, I didn't know that.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply