Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQLServerCentral.com » Editorials » Table Level Access
Add to briefcase
57 posts, Page 2 of 6
««12345»»»

Table Level Access Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Jack Corbett
Posted Tuesday, April 22, 2008 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #488549
Anders Pedersen
Posted Tuesday, April 22, 2008 6:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:19 PM
Points: 1,204, Visits: 649
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).
Post #488550
Samuel Clough
Posted Tuesday, April 22, 2008 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:03 AM
Points: 27, Visits: 108
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)
Post #488564
Grant Fritchey
Posted Tuesday, April 22, 2008 6:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #488576
Grant Fritchey
Posted Tuesday, April 22, 2008 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #488578
Andy Warren
Posted Tuesday, April 22, 2008 7:04 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Tuesday, June 11, 2013 6:34 AM
Points: 6,463, Visits: 1,388
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?

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter
Post #488581
gdelgado
Posted Tuesday, April 22, 2008 7:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 17, 2009 12:42 PM
Points: 161, Visits: 129
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.
Post #488586
Jack Corbett
Posted Tuesday, April 22, 2008 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 10,613, Visits: 11,959
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #488600
Timothy-313907
Posted Tuesday, April 22, 2008 7:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:44 PM
Points: 104, Visits: 720
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.
Post #488635
Samuel Clough
Posted Tuesday, April 22, 2008 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 12, 2009 10:03 AM
Points: 27, Visits: 108
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.
Post #488658
« Prev Topic | Next Topic »

Add to briefcase
57 posts, Page 2 of 6
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.