Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cr8nk
Yak Posting Veteran
77 Posts |
 Posted - 2007-11-08 : 15:31:16
|
| Hello,Is anyone using an abstraction layer for the middle tier to interface with so the actual table design is hidden for the application? I have read several articles that it can be done using views [url]http://www.sqlservercentral.com/articles/Database+Design/61335/[/url]. However, I don't see myself creating a view ontop of every table and allowing dml modifications to happen through them. I think it would make the query optimizier throw up after a certain level of data and views are encountered. I know snonyms are available in 2005, but I have only seen what can be done not what should be done.I don't have a fear of allowing my .NET developers (I work with very competent people) to create middle tier objects directly against the db tables for oltp dml operations. I don't want to create stored procedures for these because I think more flexibility exists within the middle tier for business logic. Then all select operations are performed against the db with stored procedures. Does anyone out there think I am crazy? Has anyone successfully created an abstraction layer strategy for their entire db?Thanks! |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-11-08 : 15:55:38
|
| Sprocs are the abstraction layer.e4 d5 xd5 Nf6 |
 |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-08 : 16:30:29
|
| I dont agree. There is more flexibility in the stored sps then in the middle tier (exception: when you are processing fingerprints, images etc - things you can not do in pure SQL without CLR)Procedures can be changed individually (no need to deploy the whole application) - and without any downtime! are always in sync with db (backed up together with data)What is important on my (DBA) side, stored procs allow us to make some optimisations.Just imagine. You have some operation based on SSN. Search, or modification, whatever. Everything works perfectly except for SSN='N/A' (illegal immigrant :) ) because there are too many such records while normally SSN is unique.If SP you can write something like IF @SSN='N/A' ... do something differentlyELSE ... normal branch.SPs are also a place where we can put query hints and other stuff like that.With a middle tier generating ad-hoc queries, what can we do? How can we optimize it? Are you going to be happy when I ask developers to add OPTION (FORCE ORDER, MAXDOP 1) in their C# code?What if I am not sure and I want to change this several times a day when PROD is falling apart? |
|
|
|
cr8nk
Yak Posting Veteran
77 Posts |
Posted - 2007-11-08 : 19:17:13
|
| I had envisioned the ORM to only be using DML to support transactions, simple inserts and updates on 1 record at a time. All selects would be done with stored procedures, I didn't foresee the need to optimize the code on the insert or update statements. Is anyone using linq, nhibernate or other ORM technology? |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-09 : 04:08:47
|
| There is a topic about LINQ right in this subforum :)Also, I think that envelopping DML into SP is more important, then selects |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-11-09 : 04:57:42
|
| I am a sproc boy too but for a bit of balance and an alternative have a look at:http://www.tonymarston.net/php-mysql/good-bad-oop.htmlI have no direct experience of what the guy suggests but, if you are going to do your dml via a middle tier, then his methodology *looks* to my eyes better than most I have seen. Personally, I like to put together sprocs and expose meta data about these to the middle tier rather than about the tables directly. Best of both worlds IMHO. I have not worked on an outward facing project for some time now though so others' advice may hold more water. |
|
|
|
cr8nk
Yak Posting Veteran
77 Posts |
Posted - 2007-11-10 : 20:05:43
|
| Yeah, I read through the linq post and I saw your glowing review of it. :) I have the feeling that the development team is going to choose that as their ORM tool since it is the latest MS technology out there. I guess a better choice of a title is has any body successfully implemented ORM or has any DBA worked in an ORM environment before... I know this isn't a popular choice out on this site, but it is something I am going to have to deal with so I am wondering what I should be preparing myself for. |
|
|
|
|
|
|
|
|
Abstraction Layer