April 21, 2008 at 7:18 pm
Comments posted to this topic are about the item Table Level Access
April 22, 2008 at 2:08 am
I can see the advantages of Linq in a common language to access data and the use of a data abstraction layer but I wonder if the overhead of writing all the business entities is not more work than directly accessing the databases as developers do now.
April 22, 2008 at 3:45 am
As I see it this could really help with the organisation of data. Think about a sales order, this can be created as an object with properties (Order number, Item Purchased, Quantity, Price) and methods (create, delete, hold, archive, fulfil).
The underlying RDBMS logic behind these properties and methods could be highly dispersed, with an order itself spanning 2 tables and the details coming from more (eg customer number can be an FK to a customer table, Item Numbers another FK to an Item table etc). Essentially by doing all the back end relational work and exposing the Sales Order and it's properties and methods you could save many many weeks of work.
So I'll take issue with the previous post - I currently have an in house sales team taking orders. The application makes use of the Sales Order object (in effect, although I'm not that organised yet in reality). Say I wanted to uplift that functionality to the web - what is better, spending maybe an extra 20% of my time organising my data in the first place and saving 50% of my development time on the web by already having this object to play with, or defining every single last transaction I need to use to create a sales order again?
As far as Steves point about table level access is concerned, I agree. Programmers simply can't be trusted with the relational model, it's like getting the builders in to make an ice sculpture.... I never liked the idea of CLR either, and I think that a good DBA will have to understand this stuff to a good enough level to present the developers with the finished data object model. I don't count myself as a good DBA and I may have missed the point, looking forward to a learning experience 😉
April 22, 2008 at 4:22 am
As a developer I've seen some truely awful SQL. Is LINQ going to make that better or worse? Personally I think it'll make the problem even worse.
In most environments I've worked in the DB has always been the key to good performance. If there is a complex data object hierarchy, which is where LINQ is supposed to be the timesaver, it will need competent and efficient SQL to access it. And who here honestly thinks LINQ will be generating competent and efficient SQL?
LINQ is the DBA and professional developer's worst nightmare. It muddles the clear distinction between a relational data orientated layout and an object orientated layout. Knowing and understanding that relationship is often where good programming for performance lies.
April 22, 2008 at 4:31 am
I've experimented with LINQ and find it compelling. As a developer turned DBA, to use LINQ in a production way would require a big shift in thinking. I was taught as a developer/DBA that an application must always interact with the database via stored procedures. This methodology causes extended debates, but we'll set that aside for now.
Based on my experimentation, you can still restrict the application to stored procedures, but doing so appears to negate the productivity gains LINQ offers. So now I am faced with faced with the prospect of reversing everything I've preached for the last eight years, and give table access to the application. I may do that, but will be a real cultural shift for me personally.
April 22, 2008 at 4:31 am
I agree to a point, but I also think programmers aren't the best people to control the relational model, this needs to be done for them, it is pretty rare to find a programmer who truly understands the relational model.
I totally agree that getting LINQ to autogenerate queries is going to be a complete waste of time, in truth I looked at the entity framework and didn't read a lot into Linq. However, muddying the difference between OO and relational frameworks I think is completely necessary - to me, in a nut shell, programmers should use well defined objects whose methods are well understood relational transactions. The entity relationship model NEEDS mapping to the OO model, if that causes performance problems deal with the problems, but split the problem into three :
1) ER Model
2) ER - OO Mapping Model
3) Application access
Oh yeah, that'll be the three tier model won't it??? Same %$&£ different TLAs.
So, next problem - how to deal with performance issues in layer 2
I really don't see any other way forward, there's just so many advantages to organising stuff this way.
April 22, 2008 at 4:35 am
Steve just to clarify I meant that currently most programmers are using sql that has been built for them by a DBA and business rules are set in the DB. The overhead of redoing it in Linq and the type of SQL we may get is unproven remember the excel stuff no error checking no feedback on sql errors such as deadlocking. I am using LINQ and remain hopeful but somewhat sceptical.
April 22, 2008 at 6:03 am
Excellent, and timely, question. I've been trying to track down the exact same information. The whole idea of ORM (Object Relational Mapping), which LINQ and Entity Framework are examples, has recently become the new buzz-word with one of our larger development projects. They have a history of latching on to buzzwords introducing SOA, Agile, and generated code, only to toss part or all of them after a year or so. The new one is ORM. Based on all my research, ORM is probably a good thing. Unfortunately, like any good tool, it can be used in horribly wrong ways. Our guys seem to want to go down the "horribly wrong" path. Since objects are their goal, not so much data (although the whole purpose of the app is to collect data for use elsewhere in the enterprise), then we should make the database look like the objects, not that messy relational junk. Then the full flower of ORM can be applied. To say the least I'm concerned.
Our guys have already dismissed LINQ (too simplistic) and Entity Framework (too database oriented) in favor of nHibernate. From what I can see, this is very widely used around the world, but not on large scale projects. It really seems to be a web-focused or hobby-focused engine. I've been trying to find any large scale implementations, especially any that have gone to production and seen a year or two of maintenance and troubleshooting and bug fixes and hot patches, etc.
I've posted about it here on SSC. I also posted about it (very similar content, with a bit of different discussion) over at my (tiny) blog[/url]. I'll be watching, and taking part, in this discussion, but I also encourage anyone to jump into both of those as well since they're related.
Thanks for posting this Steve. It's really helpful.
"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
April 22, 2008 at 6:07 am
There are three tiers of code to developers, client, business, data access. If you want to make development faster, you need to reduce or eliminate one of these tiers. They can't get rid of the client tier or the business tier, but that data access tier, that one has always been a pain. So much of it can be automated. Let's face it, most CRUD statements can be generated and as long as they use parameterized queries, there's no worry about SQL Injection and the performance of a parameterized query is identical with stored procedure right down to code reuse. Automate all that and you cut one third off your coding time (so they say). You can see why it's attractive to developers and project leaders.
"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
April 22, 2008 at 6:17 am
Pet Peeve: I hate it when someone writes an article (even someone whom I greatly respect) and uses an acronym that (in my world at least) is not exactly in common usage. I have to go on a fact finding expedition just to find out if I even care about the subject of the article!
Student of SQL and Golf, Master of Neither
April 22, 2008 at 6:28 am
I have not looked at the Entity Framework, but have looked into and played with Linq to SQL. Linq to SQL does do some good things and, I think, another tool to add to the tool kit, but definitely not always the right tool. I think MS could have made it easier to to use sp's for CRUD with Linq although I am not sure how they could make it work with sp's for select because of the variety in selects. It is interesting as I have never granted table access, but may move toward that for selects.
We have to keep in mind that Linq is currently version 1, hopefully MS will get good feedback from us on it and improve it. We got SQL Server 2005 SP3, so if we find something with Linq that needs to be changed let them know.
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
April 22, 2008 at 6:29 am
Horrible idea in my opinion.
Who cares if a developer saves 10%-20% (the numbers I have heard bandied aroud) by using LINQ during development time? How much time in man hours, is an application under development, versus being used as a production application? Doesn't it stand to reason that making the queries efficient is more important than saving the developer a bit of time?
With LINQ how am I as a DBA suppose to track down a performance problem? We all know that whenever there is a performance issue with an application, the database is the first to be blamed and we are the first to have to investigate to prove it is not the database before the application developers jump on the app code.
With LINQ queries against SQL could come from anywhere. With zero control from the DBA on how the data is accessed (and not even speaking about security issues, just the queries themselves).
I took over this position about a year and a half ago. I had a trace running on the server to capture all stored procedure and function calls for a total of a year... (yes a bit of an overkill). I took samples of the data at 3 month intervals and worked on the queries that took up the most cummulative time on the server. By very easily being able to look at the stored procedures, I optimezed the worst 3 to take less than 5% of the original time to run, the worst one I cut down to run in 1/1000th of the time. How would I be able to do that with LINQ?
Sorry, I fail completely to see why LINQ should be compelling to any organization. I can see why it would be to front end developers, but as a DBA I will fight against the use of LINQ in any organization I work in. Unless I can be proved wrong (same view I had on CLR, and I have been proven partially wrong about the CLR).
April 22, 2008 at 6:46 am
As a developer I don't find linq compelling at all. It seems like a step in the wrong direction. First, it tries to take data access concepts and make them language constructs which feels like adding the proverbial kitchen sink to a programming language.
Secondly, this will lead to developers having even less of an understanding of what they are doing and being even more ignorant of relational data concepts.
Third, as mentioned a lot of unoptimized sql will be run with little or no tuning ability.
ORM is hard and that needs to be accepted. There are some decent and interesting solutions out there (NHibernate, iBatis, even Subsonic perhaps), but linq tries to let the programmer ignore the data store and live in a OO fantasy.
Linq will work for small, quick apps but I'll bet it becomes a bottleneck for serious applications. It would have been much better to see MS put some energy behind something like NHibernate (As the Java community did when Sun basically adopted Hibernate for their EE data access strategy) that gives you most all of linq's advantages which better control over the queries themselves. We have also used iBatis.NET on mission critical projects because it solves much of the object to relational mapping problem but lets you specify every query explicitly which is great for performance tuning and dbas.
As for the comment about NHibernate not being widely used, if you look around at the upper tier of .NET developers you will find a lot of NHibernate use. It is definitely industrial strength and Hibernate, which it is based on, is arguably the defacto Java standard for Enterprise apps.
iBatis.NET is also being used more and more even by MySpace (Not that MySpace has great performance, but their technology guy has said they are getting fantasic performance out of iBatis.NET doing data access at loads that most people will never see)
April 22, 2008 at 6:57 am
Anders Pedersen (4/22/2008)
Horrible idea in my opinion.Who cares if a developer saves 10%-20% (the numbers I have heard bandied aroud) by using LINQ during development time? How much time in man hours, is an application under development, versus being used as a production application? Doesn't it stand to reason that making the queries efficient is more important than saving the developer a bit of time?
With LINQ how am I as a DBA suppose to track down a performance problem? We all know that whenever there is a performance issue with an application, the database is the first to be blamed and we are the first to have to investigate to prove it is not the database before the application developers jump on the app code.
With LINQ queries against SQL could come from anywhere. With zero control from the DBA on how the data is accessed (and not even speaking about security issues, just the queries themselves).
I took over this position about a year and a half ago. I had a trace running on the server to capture all stored procedure and function calls for a total of a year... (yes a bit of an overkill). I took samples of the data at 3 month intervals and worked on the queries that took up the most cummulative time on the server. By very easily being able to look at the stored procedures, I optimezed the worst 3 to take less than 5% of the original time to run, the worst one I cut down to run in 1/1000th of the time. How would I be able to do that with LINQ?
Sorry, I fail completely to see why LINQ should be compelling to any organization. I can see why it would be to front end developers, but as a DBA I will fight against the use of LINQ in any organization I work in. Unless I can be proved wrong (same view I had on CLR, and I have been proven partially wrong about the CLR).
I can't disagree with anything you've said. These are all the same arguments that we're currently accumulating. And we're not even addressing the security concerns.
"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
April 22, 2008 at 7:00 am
Samuel Clough (4/22/2008)
As for the comment about NHibernate not being widely used, if you look around at the upper tier of .NET developers you will find a lot of NHibernate use. It is definitely industrial strength and Hibernate, which it is based on, is arguably the defacto Java standard for Enterprise apps.
EXCELLENT! Do you have links to documentation showing large scale systems using it and any long term uses, case studies, anything. I've been doing searches and I just haven't found it.
There is a page on the nHibernate development site that outlines a bunch of implentations, but they all look pretty small scale.
"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
Viewing 15 posts - 1 through 15 (of 56 total)
You must be logged in to reply to this topic. Login to reply