April 22, 2008 at 7:04 am
Grant, I disagree that removing a tier saves time. Tiers are there for a reason! But we can agree to disagree. Bob, good for you for spanking Steve on the acronym!
I've blogged a bit about LINQ to SQL and in my view it's a tool developed without talking to a DBA. Here's a quick rollup of my objections:
- It allows stored procedures (except for selects) but does nothing to encourage their use
- It requires table access or the table value function hack. Do we as DBAs' believe that the coding gains are worth allowing table level access in our apps?
- There is no easy way for the DBA to understand what has changed in data access. We should all understand that even the most minor change can affect performance, the most common example is adding a column that eliminates the effectiveness of a covering index
- When a performance problem occurs the best we can is try to intervene with a plan guide, otherwise its a recompile of the app (or at least the data access dll)
I think - and could be wrong - that it's less about time saved than the many developers just don't get/don't like data access. Most data access is vanilla and just not that hard to write/change when needed, but it does require a little knowledge (tools can generate most procs).
I'll also add that in many cases it's us - the DBA's - that have caused our own pain, making use and changing of the database so painful that developers will eagerly do an end run around us.
It's easy for this to turn into DBA vs developer, ala the great CLR debate. I think it's an opportunity for a hugely useful discussion about how we do data access and why, and perhaps it will be us that modifies our stance?
April 22, 2008 at 7:10 am
The best thing for LINQ would be for it to work off views with instead of triggers. The dba can optimize the queries at the level of the view. Instead of triggers are like set based stored procedures.
April 22, 2008 at 7:22 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).
Anders,
I agree with most of what you say, but, with my research into Linq, I'd rather have a developer using Linq than writing queries in the app. Granted I normally would require sp's, but I may not have control over that (purchased or inherited app) and Linq uses parameterized queries and is consistent in how it builds the queries. So if there is a poorly performing query I at least know it will always be called the same way and can index appropriately and be fairly confident I will have improved it.
I'm not for or against Linq or any other ORM tool, I just think they need to be used appropriately and with thought, not blindly.
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 7:54 am
I frickin' love LINQ. I was talking to my officemate about it and I think she was in the mindset that a lot of people are in, in equating LINQ to some kind of SQL replacement. Yeah it can do that but LINQ is more than that. LINQ is about like AJAX in that it's an amalgam of different technologies coming together. Lambda expressions, extension methods, and anonymous types are the key things I dig about LINQ. You can use these things in ways beyond querying a SQL database. Lambda expressions are by far the coolest thing to me! In case you don't know about them they're basically automatic / anonymous delegates. For example if you wanted to use the Find method from the List<> class you would have to pass it a delegate that described what it was you were searching for. Now with lambda expressions you can just pass something like this s => s == "red". No formal delegate type construction or anything but that's what happens behind the scenes. You can also kind of see how the where clause functionality of LINQ gets implemented.
As for mapping entities to your database objects I think it's a cool thing. It's an evolution over using typed datasets from previous versions of Visual Studio. I still use stored procedures to send and retrieve data from the database, only now I'm mapping the result columns to object properties instead of...typed dataset properties. When you look at it from that perspective it's not so different from typed datasets I guess. But once I get the collection of data from the database it is easier to work with it with LINQ than without.
April 22, 2008 at 8:19 am
Grant Fritchey (4/22/2008)
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.
I'll try to find some NHibernate links.
From a DBA perspective, iBatis.NET should really be promoted more than it is. It takes a different approach from most other mappers. It takes care of all the plumbing code developers don't like to write, but then the actual sql is all coded in xml file. For the super lazy developer, you can even use a code generator like code smith to lay down your basic domain/entity objects and basic crud iBatis mapping files.
Because every query is in an xml file, it can be easily changed without re-writing the app and the dba can easily see every query, procedure, etc executed by the application. It's perfect for performance tuning and knowing what apps are doing without having to browse code or profile.
April 22, 2008 at 8:24 am
Samuel, thats interesting. From a DBA perspective thats almost as good as stored procs. We can see the changes, tweak the data access, and to increase security the XML doc could be stored in the db even.
Stored procs are just packaging, but they are effective packaging!
April 22, 2008 at 8:27 am
Andy Warren (4/22/2008)
Grant, I disagree that removing a tier saves time. Tiers are there for a reason! But we can agree to disagree. Bob, good for you for spanking Steve on the acronym!I've blogged a bit about LINQ to SQL and in my view it's a tool developed without talking to a DBA. Here's a quick rollup of my objections:
- It allows stored procedures (except for selects) but does nothing to encourage their use
- It requires table access or the table value function hack. Do we as DBAs' believe that the coding gains are worth allowing table level access in our apps?
- There is no easy way for the DBA to understand what has changed in data access. We should all understand that even the most minor change can affect performance, the most common example is adding a column that eliminates the effectiveness of a covering index
- When a performance problem occurs the best we can is try to intervene with a plan guide, otherwise its a recompile of the app (or at least the data access dll)
I think - and could be wrong - that it's less about time saved than the many developers just don't get/don't like data access. Most data access is vanilla and just not that hard to write/change when needed, but it does require a little knowledge (tools can generate most procs).
I'll also add that in many cases it's us - the DBA's - that have caused our own pain, making use and changing of the database so painful that developers will eagerly do an end run around us.
It's easy for this to turn into DBA vs developer, ala the great CLR debate. I think it's an opportunity for a hugely useful discussion about how we do data access and why, and perhaps it will be us that modifies our stance?
Hey Andy,
Total agreement on this side. I'm trying my best to state the case from the developer's point of view and be as open minded as I can. Actually, I think you've got it wrapped up very nicely, especially the fact that we, the dba's, our own worst enemies in a lot of cases.
"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 8:31 am
Samuel Clough (4/22/2008)
From a DBA perspective, iBatis.NET should really be promoted more than it is. It takes a different approach from most other mappers. It takes care of all the plumbing code developers don't like to write, but then the actual sql is all coded in xml file. For the super lazy developer, you can even use a code generator like code smith to lay down your basic domain/entity objects and basic crud iBatis mapping files.Because every query is in an xml file, it can be easily changed without re-writing the app and the dba can easily see every query, procedure, etc executed by the application. It's perfect for performance tuning and knowing what apps are doing without having to browse code or profile.
Thanks for the input. I'll do some research on iBatis and bring it into our discussions. We have yet to settle on a tool or total direction.This whole discussion is proving very useful to me (have I said recently how great this web site is?).
"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 8:41 am
Grant, my two cents without having fully delved in is that there are two categories, those that generate static SQL that we can look at, and those that don't (LINQ to SQL without stored procs). I can see value in both approaches...though my earlier concerns still stand!
April 22, 2008 at 8:56 am
Richard Gardner (4/22/2008)
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?
Well the answer here is Dot Net. Our "packaged" product takes sales orders in the field. Various kinds. Regular orders, returns, credit memos, etc. We wrote a sales order class. Now the database on desktop is a bit different from SQL Sever Compact Edition. The class handles that. With ASP Dot Net the web looks very much like the desktop.
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....
That hurts! :crying: If you want me to only fetch data through views and update only with stored procedures that's fine. I can just adjust my classes to work that way. It only affects the Data Access Team. Then the Application Development Team uses those classes. As long as the interface does not change the application is unaffected.
ATBCharles Kincaid
April 22, 2008 at 9:15 am
Good comments and I'll be sure this thread gets to the LINQ guys in Redmond.
Apologies on the acronym. I've heard so much about this over the last 6 months that I sometimes forget that lots of people haven't. I did link 😀 the
references in the article. This was hard to explain easily.
The developers know LINQ is 1.0. They admit that and say that there are places where things can really go horribly wrong. That concerns me for large scale projects, but how many projects are large scale? Most aren't.
One of the problems with views is that you need to then specify CRUD procs for updates to work well. There are some limitations with views, so I'm not sure how much help you get with them. Don't forget that if you give back 10-20% of a developer's time, then this doesn't help.
As far as performance tuning, they know it's an issue. Right now if something changes, I'd have to capture the SQL, go back to VCS, grab the old code, compile that and get it's SQL and then diff them. Not fun.
Or if I need to "tune" a set of code, I'd have to capture it, apply the tuning, wrap it in a proc, and then have the developer change the code to use the proc instead of using dynamic SQL. Again, not great.
However it's a balance. How much code will you need to tune? There could be lots of stuff sent through that doesn't need tuning or it doesn't matter. One nice thing is that the data doesn't get called from SQL Server until it's needed, so all those wasted queries, those calls developers make and then never use the results, go away.
There's also a consistency factor. The same code gets sent through, so none of the subtle differences you might get between two programmers or after a cut/paste/reformat that might cause multiple plans to go through, can go away. Some testing from the teams using workloads from applications showed that overall LINQ had better performance although there were places it was worse.
One problem that I saw last week is that the parameters sent through are based on the data. So if I sent through an update with "Steve" (nvarchar(5)) and then another with "Andy" (nvarchar(4)), I have two plans. That's bad. The parameter needs to match the data type.
I'm not sold on LINQ, but how many poor developers out there write applications for 5-20 people. They could save tons of time, get better overall performance even if a few queries are bad. Don't forget that lots of people don't have DBAs.
I'd like to see more examples of people using LINQ and then some analysis comparing this to regular code. Maybe even some LINQ v non-LINQ programming contests that could show people how LINQ can impact an application. Maybe a Contoso or IBuySpy app re-written with LINQ would be interesting.
It's a tool. It can be used well or not well, and I'd definitely be wary of scale issues, but it's not something to dismiss out of hand.
April 22, 2008 at 12:20 pm
I can dismiss it out of hand! It helps that my application architect and I (Database Architect) both agrees on it =)
Jack,
I agree, if the options are dynamic SQL in the front end, and LINQ, I will take LINQ any time. But if someone was rewriting and application using dynamic SQL in the front end, I would rather make them use procdures properly.
Someone mentioned XML.... SHAME! I have yet to figure out how to get decent performance out of XML stored in the application. Granted, that might have something to do with the size of the blob it is in this one application....
The feeling about LINQ I got from the peopel representing SQL at Developers Connection in Vegas last fall was basicaly stay away from it if you can..... The problem is going to be that all these MS partner consulting companies are just chomping at the bits to come in and sell companies on the wonders of LINQ, and how it will make all their application much faster, much earlier,and much cheaper to develop. And, at least in my experience, most companies trust highly paid consultants a lot more about these things that those of us working every day with their data/applications.
April 22, 2008 at 12:48 pm
Grant Fritchey (4/22/2008)
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.
I just had a few minutes and couldn't find some of the ones I remember recommending NHibernate. I personally have been mostly on an Oracle project helping out with the Java/UNIX side of things so I haven't been doing much .NET in the last 6 month to a year. For that reason I can't remember some of my NHibernate links.
Anyway, if you ask around you should find a lot of NHibernate use under the radar. What impresses me is that it's often used by what I consider to be the top tier .NET developers. They are not necesarilly MVPs or Microsoft folks, but they are the folks doing very good development with Microsoft tools. They just know when to drink the kool aid and when to use something else 😉
Anyway, I would contact Oren Eini (works closely on Nhibernate). If you're into TDD, he's the one behind Rhino Mocks. His blog is at: http://www.ayende.com/Blog/Default.aspx
You might also want to contact Ben Day. Seems like I remember him using a log of Nhibernate and his blog seems to back it up. http://blog.benday.com/Default.aspx
Also, I didn't know if you've seen this:
http://forum.hibernate.org/viewtopic.php?p=2289131&sid=03711ab281370318275764ec30b08897
It is a little dated, but you can see there are quite a few folks who have been doing significant stuff with Nhibernate for years. Remember it helps that it has been built upon Hibernate and benefitted greatly from it's development.
If you're very concerned about the DBA side, like I said, give iBatis.NET a whirl and definitely include it in your comparison. It's very flexible and is a dba's best friend. NHibernate is pretty decent and for complex sql I'm assuming it will now let you specify the sql exactly. I know the Java version does.
Also, if you are into Linq, NHibernate has a Linq provider. Some are saying it's pretty slick. I don't know, I haven't fooled with it.
Also, to the one concerned about xml, you would just have to look at iBatis.NET. I share your frustration for ridiculous amounts of xml that are confusing to manage and slow to parse. In iBatis case (and Nhibernate) the xml makes sense if you understand the tool and it's only parsed once at startup.
April 22, 2008 at 1:01 pm
Excellent information. Thank you very much.
"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 1:36 pm
I am a developer and do the DBA stuff here. I personally like LINQ and Entities, but like many of you did not like all the SQL that I had no control over. I have found that you don't have to have either or, you can have both. I use LINQ and Entities, but I only allow it to access data through stored procedures. It saves me a ton of time by writing classes and wrappers for my SP's. I am missing out on the Change Tracking and stuff, but will sacrifice that.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply