August 28, 2015 at 6:57 am
Sorry Jeff, but you do not have the authority to make this statement,
People who don't really know much about databases are the ones that frequently suggest the approach [code-first].
That is probably based on a personal bad experience, or even the result of the humor from a bad day. Not a truth that can be universally applied.
I do not want to be rude or disrespectful, but your position is pointless and contradictory, in summary you said that people who suggest the code-first approach "frequently" are not proficient DBAs or they will not revise the generated script properly. So what is left for them? The database-first approach. Implicitly you are suggesting that an individual that is not able to revise a DDL script should write it and model the business logic of an application.
The OOP languages have a lot more instrumentation for business logic modeling and prototyping (e.g. abstract classes, interfaces, empty methods and etc.). For the sake of productivity DBAs can and should take advantage of generated DDL scripts considering the inalienable responsibility of revising it and applying the improvements and corrections that are invariably necessary.
August 28, 2015 at 12:45 pm
diegosiao (8/27/2015)
I agree.To a "from zero" robust corporative application, I think code-first oriented modeling is a good approach, taking advantage of the SQL script generation to revise it and then execute with appropriated corrections (or not, hopefully).
But as long as you're going with a code-driven, object-driving database model, you're looking at major issues. It really doesn't work well. You have to approach both toolsets as they're designed and as they work. Trying to force the models and methods of one, the application code, on the other, the relational storage engine, will lead to failure.
I'm not against ORM use. I've seen it work quite successfully. However, I've also seen it fail spectacularly and the failures were because the database and relational storage were not taken into account appropriately.
"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 29, 2015 at 12:58 pm
diegosiao (8/28/2015)
Sorry Jeff, but you do not have the authority to make this statement,People who don't really know much about databases are the ones that frequently suggest the approach [code-first].
Actually, I do have the "authority", much the same as the "authority" you have to say I don't. 😉
That is probably based on a personal bad experience, or even the result of the humor from a bad day. Not a truth that can be universally applied.
Based on the fact that every project (and there have been many) that I've seen or had to clean up after that was done on a "code first" basis turned out to be nightmare of very bad database design and very poorly performing, high resource usage code, I could have stated it universally but didn't. Go back and look.
I do not want to be rude or disrespectful, but your position is pointless and contradictory, in summary you said that people who suggest the code-first approach "frequently" are not proficient DBAs or they will not revise the generated script properly. So what is left for them? The database-first approach. Implicitly you are suggesting that an individual that is not able to revise a DDL script should write it and model the business logic of an application.
You sound like a "front-ender" (and that's no insult... you folks have a tough job. I know because I used to be one.) that might have been able to pull off a good "code first" implementation. That's a very rare thing from what I've seen and my hat's off to you if it's true.
But my position is neither pointless nor contradictory. To restate my position and without exception so far, I've not seen even one "code first" project work out. Oh sure, it "got the job done" and they where able to "ship something" that worked, but only for a short while. Once any decent volume of data was in the database, the code got slower and slower and finally got to the point where the application would frequently time out, many transactions were lost due to deadlocking, and making modifications to both the code and the underlying database became anything but easy frequently taking a day or two just to figure out where the change needed to be made and how to implement the change.
A couple of the companies decided to throw hardware at the problem. One company doubled the number of processors to 32, quadrupled their ram for 64GB to 256GB, and upgraded from SQL Server Standard Edition to the Enterprise Edition. It made things a little better but half a slow as molasses is still pretty slow. The reason why it didn't help is because the code couldn't take advantage of multiple processors and the number of logical reads from memory was still astronomical. Even memory IO has its limits.
The OOP languages have a lot more instrumentation for business logic modeling and prototyping (e.g. abstract classes, interfaces, empty methods and etc.). For the sake of productivity DBAs can and should take advantage of generated DDL scripts considering the inalienable responsibility of revising it and applying the improvements and corrections that are invariably necessary.
And THAT is a major part of the problem. While I agree that "business logic" is key to WHAT must be delivered, it usually has nothing to do with HOW the data is stored nor how it should be retrieved. Said "Business Logic" is usually driven only by requirements of what to display and when. It rarely mandates such things as Third Normal Form of the data nor does it usually mandate any kind of performance other than when folks working on screens seem to think that something is running slow. In most cases, they also have no clue as to how many resources are being consumed and so have an equally small clue as to what performance will be when the data in the database scales up.
I'm going through it right now with a 3rd party vendor that wrote "code first" code. To start with, they had no plan and no thought of common sense naming conventions. As a result (for example), there are dozens of tables that should all make reference to a "BatchID". Now, there are several versions of how that column name could be spelled out...
BatchID
Batch_ID
Batch_Num
Batch_Number
Batch#
BatchNo
Batch_No
Batch
... and they used ALL of them! Worse than that, some of the "developers" used NUMERIC(18) (which is acceptable in this case), some used NUMERIC(18,0) (also ok in this case), and some used NVARCHAR(256) or NVARCHAR(18). One even used NCHAR(18).
That's all because of exactly what I said. "Code first" is frequently done by people that have no plan, no standards, and no understanding of even a common naming convention or the importance of datatype matching and using the correct datatypes to begin with.
Most of the code is embedded SQL in "managed" code and for those stored procedures that they did write, they're ANSI-Only pieces of junk that handle just one row at a time for processes that handle tens of thousands of documents a day even though they're all staged in a nice staging table. As a result, their "managed" code hits the database with more than 22,000 single row calls every 30 seconds to process just 20 documents. Apparently, they don't even know how to use the code that they profess knowledge of because about half the calls are duplicate calls. Worse yet, they had to setup 12 machines to handle the load because the code wouldn't work fast enough to handle the load.
Another recent example is a small company that I'm trying to help for gratis. I won't get into why they need to do it but one of the things their code needs to do is create 250,000 rows in the database on demand when a customer creates a "new product". They accept 6 parameters from the user plus a start and end date and use an algorithm to generate 250,000 INSERT INTO/VALUES statements and send them all to the database to create the data. Then they wonder why the pipe fills up and the database takes a long time to insert the rows. The process takes about a half hour to execute. I created a single stored procedure that takes the inputs and creates/inserts the rows in sub-second times.
And, in a previous job, I spent most of my time fixing extremely long running and heavy hitting code written by the "code first" team. A lot of the slowness was caused by data-type mismatches and the fact that they did nearly everything in a RBAR fashion even when it was for some form of batch code including reporting to the screen. One example is that a weekly report would take 10 seconds, that same report for a month would take 12 minutes, and the yearly report would run (pegging 4 CPUs to the wall and absolutely killing memory and IO) for 20 minutes and time out. When the "developers" were called on the carpet about the performance, especially the required yearly report, their answer was "you've simply asked for too much data to be reported". When I asked the lead "developer" about the data-type mismatches, he literally stuck his tongue out me and then said "premature optimization is the root of all evil". He's right on that but this has nothing to do with premature optimization. They "just" wrote really bad code for the tasks and "designed" really bad table structure and it was the primary cause of all failures and performance problems especially since they didn't understand the nature of batch processing.
To summarize, to date, I've seen a lot of "code first" projects offer a robust feature list but I've yet to see one offer long term performance without having to go massively parallel on hardware and still need to do frequent upgrades to keep performance up.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2015 at 9:12 pm
For What It's Worth, my experience mirrors what Jeff has written. In fact, I even do an all day training sessions of bad things that are done to databases... and ORM tools do almost every one of them.
It's to the point that I like to say "Using ORM is the fastest way to a slow database".
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2015 at 5:38 am
Grant, Jeff, and Wayne:
Which ORMs do you have experience with? I would think that the designers of these ORMs would very much like to hear of your experiences with these tools.
In particular, perhaps you could invite Julie Lerman or Rowan Miller or other experts involved in the design and use of Entity Framework to join this discussion.
- Brian
August 31, 2015 at 7:11 am
briankwartler (8/31/2015)
Grant, Jeff, and Wayne:Which ORMs do you have experience with? I would think that the designers of these ORMs would very much like to hear of your experiences with these tools.
In particular, perhaps you could invite Julie Lerman or Rowan Miller or other experts involved in the design and use of Entity Framework to join this discussion.
- Brian
I've worked with nHibernate and Entity Framework. I've seen successful projects with Entity Framework. I still haven't seen a successful project with nHibernate. Successful in my definition is not simply functional, but one that works from a performance standpoint, a data integrity standpoint, and the ability to report from the data.
Julie and I have had conversations about this, most recently at Techorama in Belgium. I'd sum up to say that we probably agree a lot more than we disagree.
"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 31, 2015 at 7:26 am
Grant Fritchey (8/31/2015)
briankwartler (8/31/2015)
Grant, Jeff, and Wayne:Which ORMs do you have experience with? I would think that the designers of these ORMs would very much like to hear of your experiences with these tools.
In particular, perhaps you could invite Julie Lerman or Rowan Miller or other experts involved in the design and use of Entity Framework to join this discussion.
- Brian
I've worked with nHibernate and Entity Framework. I've seen successful projects with Entity Framework. I still haven't seen a successful project with nHibernate. Successful in my definition is not simply functional, but one that works from a performance standpoint, a data integrity standpoint, and the ability to report from the data.
Julie and I have had conversations about this, most recently at Techorama in Belgium. I'd sum up to say that we probably agree a lot more than we disagree.
I've mostly been fighting recently with Entity Framework. I suspect it's less EF itself and more developers not being careful about what they request (eg a login query which took 28 seconds on a good day and fetched half the database back to the client. The SQL query was 6 pages long)
I'm trying to push for more careful coding, looking at the generated code and performance testing as they develop, but it's an uphill battle.
Haven't seen nHibernate used, so can't comment on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2015 at 8:07 am
Thanks Jeff for sharing your experiences. Experience is something that we can not find at supermarket, glad you share yours.
It is an undeniable fact that DDL generated scripts are define inefficient objects in terms of resource consumption. In addition, the queries usually are prodigal.
Another fact is that in real world time is short and an expensive resource to corporations. I still did not knew a DBA professional that had a job with soft pressure - to create and maintain databases.
That probably explains why tools which purpose is helping DBAs are so shiny at our eyes.
I can not give myself to the luxury of ignoring a tool that normally save two or three days of scripting.
ORM tools are primarily oriented to productivity (development and - most important - maintenance) not to performance and consumption resource efficiency. They are not a good choice if you are unable to take the best from them and/or your company can not afford the price that comes with this choice.
September 1, 2015 at 10:48 am
Shortcuts taken early in the project can have significant costs in the later stages, so is it really a savings? For me the takeaway is that developers need to work with the DBAs and not implicitly trust the code generators to produce good code. And as Jeff pointed out, a consistent data dictionary should be absolutely required on most projects.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
September 2, 2015 at 6:19 am
We've developed a couple of apps using ORM and E/F. As far as I can see, they are supposed to obviate the DBA.
And they worked -- for a while. But since they created no indexes, it was only a matter of time.
They sure do generate a *lot* of bad code. Cursors that will only ever retrieve a single row!
And repetitive database calls. Lots and lots of those.
At the end of the day, they generated more work than they saved. They've since been re-written.
We've learned our lesson: no more E/F.
My $0.05. (Canada did away with the $0.01 coin).
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply