SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Level Access


Table Level Access

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)SSC Guru (248K reputation)

Group: Administrators
Points: 248133 Visits: 19805
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
My Blog: www.voiceofthedba.com
David J Worrall
David J Worrall
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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.
Richard Gardner-291039
Richard Gardner-291039
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1413 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 Wink
Confucius247
Confucius247
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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.
mhenderson
mhenderson
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 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.
Richard Gardner-291039
Richard Gardner-291039
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1413 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.
David J Worrall
David J Worrall
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161913 Visits: 33199
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)SSC Guru (161K reputation)

Group: General Forum Members
Points: 161913 Visits: 33199
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
BobAtDBS
BobAtDBS
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1412 Visits: 372
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search