March 6, 2014 at 8:01 am
My environment is somewhat different. We have MS SQL and MS SQL Express, so in essence the DBMS. However we still use a ORM to help us write our code. You have to, I feel, because it really speeds up development. It's much faster to define an Entity Framework against the databases, instantiate the object, give it the search parameters and have it return a result, rather than creating a connection, defining a command, specifying whether it is a stored procedure or a SQL statement, parameters, etc. Using an ORM is at least an order of magnitude faster, if not two orders of magnitude faster. And with deadlines pressing down on you, well you just have to use them.
However, I do agree with you, Jim Youmans, the SQL generated can be slower. In fact I kind of wonder if this isn't a part of the problem we're experiencing now. In January we released a new application, to replace a really old, buggy one. It does work better, is much more reliable and saves the data, whereas the older system would occasionally loose it. However, our users are complaining that it is slower than the old system. I point out that we've gained more than we've lost, because we're not loosing data like we did with the old system. That argument does work, but the users still aren't happy with a slower system. Now some of the slowness I believe is due to the way in which we save the data, but this article makes me wonder if some of it might also be due to the ORM's SQL code. I've not looked at it (indeed, I'm not even sure how I could get to it). It's a thought. I'm going to try the Database Engine Tuning Advisor first, to see if it can recommend some indices I could apply which might resolve the issue, but who knows, maybe we'll have to replace a few of those SQL statements with stored procedures instead, in the ORM.
Kindest Regards, Rod Connect with me on LinkedIn.
March 6, 2014 at 8:28 am
It's really not that complicated.
For drop-downs or simple edit forms for entities, it would be pretty retro to drop to sp's. For marking a row deleted from a grid, for simple grid listings, to retrieve settings, to put that stuff into sp's wastes lots of time = wastes lots of your, and your customers, money.
If you are doing something that requires complex business logic on multiple entities this is often best written in code. This makes it more elegant and readable and maintainable. This is clearly a matter of opinion, but excessively long, single threaded sp's can be hard to follow, with little to no performance benefit. To my mind this is a judgement call on each case, with a bias according to personal preference perfectly acceptable in the circumstances.
Other cases are clearly best implemented as stored procedures or at least dynamic SQL. Multiple (where count is not limited) inserts, updates and deletes say, or complex retrievals involving strange joins or flexible filters I'd put in that category.
Between all these fairly clear-cut cases there's a lot of grey area. Most younger devs (at our shop) hate the extra maintenance of procs (though competent to implement them) and will avoid whenever possible. Older folk drop out at the drop of a hat or even only work in procs. Personally if I struggle with the correct LINQ for 30 minutes then I'll drop out to a proc.
March 6, 2014 at 8:30 am
I think the fundamental issue that drives people to using EF and similar tools is a lack of understanding for relational data concepts. SQL is not OO, or anything else. Most of the developers seem to think in that fashion and only see the database as variation of a file system.
At the moment I would think the best approach would be is to design the database “correctly” and implement the GUI through something like MVC. Leave as much of the business logic as possible in the database, and use the MVC side to just deal with screen navigation, security, etc.
Mind you, that is just my opinion: I have no practical experience with transactional applications; I’m more involved with data warehousing. However, the more I’ve been studying SQL lately, specifically SET theory and relational algebra, the more I realize that it cannot be ignored.
Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
March 6, 2014 at 8:48 am
I think there is a lot of "it depends" on these cases. For basic row-level interactions (add, update, read) a single row - by all means use the ORM. For more complex interactions, look closely at the SQL generated.
We had one extreme case that was round-tripping to the database an insane number of times because the ORM was poorly implemented. The first was done for ease of use. But enhancements tended to be tacked on instead of revisiting the whole. That led to poor performance. We re-implemented that with a combination of stored procs and tweaking the actual code to behave in a more sane manner. That also led to us looking more closely at the code our developers were writing. The ORM wasn't the main issue there, but poor coding by some of our developers.
Overall, I'm content to let the ORMs handle the more basic interactions at a single-row level. It's easier to handle and some ORMs do well with that. For calls involving multiple rows or more complex SQL, I generally prefer stored procedures. I'm definitely not going to make a blanket statement against ORMs. They can be useful when properly implemented.
March 6, 2014 at 8:56 am
In this case, the DAL (Data Access Layer) should be improved to be more intelligent so that 90% of the work for the developer results in good TSQL and the 10% is the outlier that needs to be manually tuned.
March 6, 2014 at 8:57 am
At the moment I would think the best approach would be is to design the database “correctly” and implement the GUI through something like MVC. Leave as much of the business logic as possible in the database, and use the MVC side to just deal with screen navigation, security, etc.
Yeah, that doesn't really solve the problem though. It's the "M" (model) in MVC that's the issue. Regardless of where your business logic resides, you need something that gets the data from your data store and sends data back to the data store and otherwise handles data store interaction. You need to load your model with data, and you need to persist data changes in the model back to the data store (and deal with any problems that might arise during the interaction).
The "something" that gets data to and from your data store is the data access layer of your application/system, and that's where you need to choose an appropriate strategy for interaction with the data store (that includes properly isolating your data access layer from the rest of your application). Do you choose to use an ORM? Do you choose to do direct access via some other method (e.g., via ADO.NET in a .NET application)? It depends on your needs -- choose the appropriate tool for the job, and be sure that you are well versed in the strengths and weaknesses of the tool you choose.
March 6, 2014 at 9:15 am
dmbaker (3/6/2014)
At the moment I would think the best approach would be is to design the database “correctly” and implement the GUI through something like MVC. Leave as much of the business logic as possible in the database, and use the MVC side to just deal with screen navigation, security, etc.
Yeah, that doesn't really solve the problem though. It's the "M" (model) in MVC that's the issue. Regardless of where your business logic resides, you need something that gets the data from your data store and sends data back to the data store and otherwise handles data store interaction. You need to load your model with data, and you need to persist data changes in the model back to the data store (and deal with any problems that might arise during the interaction).
The "something" that gets data to and from your data store is the data access layer of your application/system, and that's where you need to choose an appropriate strategy for interaction with the data store (that includes properly isolating your data access layer from the rest of your application). Do you choose to use an ORM? Do you choose to do direct access via some other method (e.g., via ADO.NET in a .NET application)? It depends on your needs -- choose the appropriate tool for the job, and be sure that you are well versed in the strengths and weaknesses of the tool you choose.
I understand and agree; I probably should have been a little more verbose in my description.
Regarding my MVC reference, I wouldn’t use a “code first” design; that is basically using the ORM / EF approach to designing the database, and what you indicated. I was thinking (though not stating) that the database be designed first, using proper SQL design technics, code, etc. Then have the Model part inherit from that. I believe that would be the “database first” approach in MVC.
As to the DAL, that would depend on the application requirements, technology involved, etc. just as you indicated.
Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
March 6, 2014 at 9:21 am
Having learned from a DBA code ninja many years ago, he beat into our heads that we were DBAs first and foremost before become TSQL masters. His point being from all that was to get you to use Stored Procedures versus relying on ORM or some other framework that would dynamically develop the SQL for any of the SIUD operations because they were more secure. He also told us to not be lazy and just build the damn Stored Procedures for each table when you create them.
His other item (and absolute pet-peeve) he hammered home was to DOCUMENT DOCUMENT DOCUMENT! Stored Procedures should ALWAYS have some kind of documentation with them at least explaining what the stored procedure is doing and with what tables, queries, functions, etc. that it is working with. Nothing is more aggravating to come behind someone and have to clean up their mess and not have any documentation explaining what they were thinking when they did it. Hell, ANYTHING you code should be documented.
March 6, 2014 at 9:26 am
DEK46656 (3/6/2014)
I think the fundamental issue that drives people to using EF and similar tools is a lack of understanding for relational data concepts. SQL is not OO, or anything else. Most of the developers seem to think in that fashion and only see the database as variation of a file system.At the moment I would think the best approach would be is to design the database “correctly” and implement the GUI through something like MVC. Leave as much of the business logic as possible in the database, and use the MVC side to just deal with screen navigation, security, etc.
Mind you, that is just my opinion: I have no practical experience with transactional applications; I’m more involved with data warehousing. However, the more I’ve been studying SQL lately, specifically SET theory and relational algebra, the more I realize that it cannot be ignored.
I am in agreement with you, that the database should be designed first, but that's because I've been working in this field for a while now, and I'm just used to doing it that way. Entity Framework allows the developer to design the database in code, and then create the database. In EF terms it's called "code first". It's quite popular too, believe me. Doing it that way, I believe, guarantees that there won't be any stored procedures at all. Indeed, except for primary and foreign keys, there be no other indexes defined. Now I, personally, don't like using EF in that fashion, but I'm in the minority.
Kindest Regards, Rod Connect with me on LinkedIn.
March 6, 2014 at 9:26 am
DEK46656 (3/6/2014)
dmbaker (3/6/2014)
At the moment I would think the best approach would be is to design the database “correctly” and implement the GUI through something like MVC. Leave as much of the business logic as possible in the database, and use the MVC side to just deal with screen navigation, security, etc.
Yeah, that doesn't really solve the problem though. It's the "M" (model) in MVC that's the issue. Regardless of where your business logic resides, you need something that gets the data from your data store and sends data back to the data store and otherwise handles data store interaction. You need to load your model with data, and you need to persist data changes in the model back to the data store (and deal with any problems that might arise during the interaction).
The "something" that gets data to and from your data store is the data access layer of your application/system, and that's where you need to choose an appropriate strategy for interaction with the data store (that includes properly isolating your data access layer from the rest of your application). Do you choose to use an ORM? Do you choose to do direct access via some other method (e.g., via ADO.NET in a .NET application)? It depends on your needs -- choose the appropriate tool for the job, and be sure that you are well versed in the strengths and weaknesses of the tool you choose.
I understand and agree; I probably should have been a little more verbose in my description.
Regarding my MVC reference, I wouldn’t use a “code first” design; that is basically using the ORM / EF approach to designing the database, and what you indicated. I was thinking (though not stating) that the database be designed first, using proper SQL design technics, code, etc. Then have the Model part inherit from that. I believe that would be the “database first” approach in MVC.
As to the DAL, that would depend on the application requirements, technology involved, etc. just as you indicated.
Nothing that I said really had anything to do with code first or database first, really. All I was saying is that regardless of whether or not you choose to use an ORM, you should properly design your data access such that the data store access is properly isolated from the rest of your application. If you do that, then it's less disastrous if you later find that you chose the wrong data access methodology.
That said, I prefer database first myself. 🙂
March 6, 2014 at 9:40 am
It's true that by coding batch processing logic within the application, your SQL code can be simplified and constructed in a more cross-platform fashion. Basic SELECT, INSERT, and UPDATE operations can be coded using ANSI compliant syntax, using none of the T-SQL and PL/SQL extensions.
However, object relational impedance mismatch, that thingy for which some application developers fret about when accessing tables directly; it is actually mitigated when parameter driven stored procedures perform batch processing on the database server side.
Also, client side cursors are still cursors, and locks can be held open for longer periods of time. From an architentural perspective, streaming your data across the network, serializing it into objects, processing it one record at a time, and then streaming it back across the network to the database server can have performance ramifications. Databases typically fail because they failed to scale, not because they failed to port.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 6, 2014 at 10:00 am
Eric M Russell (3/6/2014)
It's true that by coding batch processing logic within the application, your SQL code can be simplified and constructed in a more cross-platform fashion. Basic SELECT, INSERT, and UPDATE operations can be coded using ANSI compliant syntax, using none of the T-SQL and PL/SQL extensions.However, object relational impedance mismatch, that thingy for which some application developers fret about when accessing tables directly; it is actually mitigated when parameter driven stored procedures perform batch processing on the database server side.
Also, client side cursors are still cursors, and locks can be held open for longer periods of time. From an architentural perspective, streaming your data across the network, serializing it into objects, processing it one record at a time, and then streaming it back across the network to the database server can have performance ramifications. Databases typically fail because they failed to scale, not because they failed to port.
I really wasn't talking about trying to code generic "cross-platform" SQL. Personally, I think that's an unrealistic pipe dream (you generally end up coding to the least common denominator and though your code may work, it'll never work as well as it could work).
Can you elaborate more on how stored procedures mitigate "impedance mismatch"? I've not really found that they have anything to do with the matter personally (although they can certainly be a player in solving the problem), but I'd like to better understand what you mean in that regard.
March 6, 2014 at 10:06 am
I work on a ERP product that is delivered to many countries. For that reason, we have the need to build our product across multiple database platforms (SQL being my favorite of course) and multiple operating systems. For us, if we don't port, we lose a large potential of customers (they pay a LOT of money to us too, so it's extremely important for us to attract these different customers). I agree with your point, if we were a simple homegrown SQL application, then yes, scalability is probably one of your biggest concerns. But, for large applications, maintaining a wide variety of code bases would require a much larger staff of developers. I've worked on other large ERP applications where SQL was our only code base and I've learned an amazing amount of performance tuning on that application. I agree, seperating the CRUD out to a DAL does have some tradeoffs, but it is important to do that. In my opinion, there is no right answer to this debate for everyone as this particular debate has been happening for more than a decade. In order for the answer to be correct for your application you must know who the potential set of customers are to be able to architect appropriately. Example, one DBA friend of mine said that when working on an Amazon type of store front (not Amazon), milliseconds were important, so in that case, yes, TSQL is going to have to be manually tuned in all queries. But, if functionality > performance then the argument shifts for obvious reasons.
March 6, 2014 at 10:22 am
I also have to agree that the use of object frameworks to access the data can cause real problems. Years ago, a division at Yahoo! contracted me to come in and optimize one of their failing SQL Server instances that served up video content on the internet. They mostly used Oracle and MySQL, but one of the companies they purchased was running a Java middle tier against a SQL Server 2005 database. In this case, they were using Hibernate to generate the SQL queries. Hibernate may have improved, but back then, if Hibernate was used to join more than about three tables, it just went stupid. Yes, it was difficult to read, but it sometimes added a joined table multiple times. One of the queries joined 192 tables (yes, that was one hundred and ninety two).
But, there are object frameworks that work intelligently. I do not remember the name of the tool, I think it was EntitySpace which was based on MyGeneration?, but it generated stored procedures for all of the C# to database object access. So, the stored procedures could be compiled and optimized by SQL Server, and in those cases where the generated code was really bad, a database developer could completely re-write the procedure.
Object based access to databases is a great way for developers to code, but imho, it needs to be balanced and rely on stored procedures on the database side.
The more you are prepared, the less you need it.
March 6, 2014 at 10:35 am
I agree strongly with the preceding comments of Mr Chris Bargh. I too am a software architect and application developer, and frankly thought the article confuses application developers with vendors which develop database I/O middleware.
With the increasing acceptance of cloud computing, SOA, and web services, application developers are moving away from direct database calls. This is unavoidable; and indeed, I find it preferable. While I agree application developers should have some fundamental knowledge of RDBMSs, we have enough on our plates without having to worry about differences in I/O call implementations between the plethora of various RDBMSs. APIs and web services provide abstraction layers which free developers from those concerns.
If the author's beef is about the lack of quality and efficiency in the I/O middleware tool, blame the vendor thereof, not the developer who is obligated to use the tool.
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply