Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Table Level Access Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 7:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:53 PM
Points: 33,063, Visits: 15,179
Comments posted to this topic are about the item Table Level Access






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #488306
Posted Tuesday, April 22, 2008 2:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 23, 2009 2:41 AM
Points: 2, Visits: 14
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.
Post #488405
Posted Tuesday, April 22, 2008 3:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, Visits: 421
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 ;)
Post #488450
Posted Tuesday, April 22, 2008 4:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 27, 2009 4:01 AM
Points: 14, Visits: 40
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.
Post #488465
Posted Tuesday, April 22, 2008 4:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 19, 2011 8:20 AM
Points: 1, Visits: 108
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.
Post #488470
Posted Tuesday, April 22, 2008 4:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:29 AM
Points: 238, Visits: 421
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.
Post #488471
Posted Tuesday, April 22, 2008 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 23, 2009 2:41 AM
Points: 2, Visits: 14
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.
Post #488476
Posted Tuesday, April 22, 2008 6:03 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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. 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
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 #488524
Posted Tuesday, April 22, 2008 6:07 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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
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 #488531
Posted Tuesday, April 22, 2008 6:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 8:17 AM
Points: 298, Visits: 335
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
Post #488540
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse