| Author |
Topic |
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
 Posted - 2009-04-22 : 10:06:29
|
| I believe in strong objected oriented architecture and make sure to go with a 2/3-tier object model. I always use ad-hoc queries in my methods.Now, we got the presentation layer, the business logic layer, and the database abstraction layer.The presentation layer (entities) and the business logic layer are unaware of the database and it's structure. The DAL is fully aware of the database structure. The presentation layer is unaware of the DAL. It only has access to the BLL. The presentation layer passes entities to the business logic layer which may do various tasks such as validation and to persist these changes, the BLL passes the entities directly to the DAL.The DAL saves the entity or loads it etc.The idea of stored procedures is that the application layer should be unaware of the database structure. Instead the app layer should tell the database what data it needs (not how to get the data) and the database will get it. The problem with this is that the database abstraction layer already handles this since it is fully aware of the DB structure. If you're using stored procedures on top of the DAL, you're adding another redundant layer therefore increasing maintainance significantly. When there are multiple applications using the same database, the applications would either do one of the following:1) They would use the same data abstraction layer, therefore making the use of SPs redundant.2) External applications would communicate through a 4th layer (web service) which would communicate to the business logic layer which would communicate to the DAL.You have a solid process using this approach.Against the argument of data lasting longer than the application, it's true. In a properly designed application, when the life-cycle is over, you don't create a new application from scratch. You re-use what you got and incorporate new features/modifications or optimize etc.So why should I use stored procedures again? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 10:36:06
|
| Problem with doing all this ion the DAL rather than SPs is that you will need to control transactions from the DAL which can lead to blocking and deadlocks and general lower performance.Also means that if you change the database structure you will need to synchronise the release with a change to the DAL rather than containing it withing the database.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-04-22 : 10:50:35
|
my general objection to using ORM generated sql is security. you can grant/deny rights per sproc. in adhoc sql the user has to have access to all objects that are used in the query.this is of course irrelevant if you're using sa account. but using sa again violates the deny all, grant little security principle.sure you can handle security in your app and that is also a good thing but i think that db itself should also have security implemented.i still use ORM's at work though  EDIT:oh and your approach isn't bad. your attitude sucked ___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 10:53:41
|
quote:
Originally posted by nr
Problem with doing all this ion the DAL rather than SPs is that you will need to control transactions from the DAL which can lead to blocking and deadlocks and general lower performance.Also means that if you change the database structure you will need to synchronise the release with a change to the DAL rather than containing it withing the database.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
How can controlling transactions within the DAL lead to blocking and deadlocks compared to SP's controlling the transactions? There is a transaction manager in .NET with various types of transaction levels you can specify. Please explain how DAL transactions are not good compared to SP transactions.On your second point...Loosely coupling makes sense but not in this case. If you make a change to the database structure, it would have to go through various phases of development (testing, staging, deployment, verifying). You simply cannot make a change to the database and do some simple testing and deploy it to the production environment.Also, a database structure change would 90% of the time mean a change in the application layer regardless of using SPs or not. In a .NET application, the application front-end would be seperate from the DAL layer. Therefore, only the DAL needs to be recompiled and deployed, nothing else. |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 11:00:13
|
quote:
Originally posted by spirit1
my general objection to using ORM generated sql is security. you can grant/deny rights per sproc. in adhoc sql the user has to have access to all objects that are used in the query.this is of course irrelevant if you're using sa account. but using sa again violates the deny all, grant little security principle.sure you can handle security in your app and that is also a good thing but i think that db itself should also have security implemented.i still use ORM's at work though EDIT:oh and your approach isn't bad. your attitude sucked ___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
ORM? A DAL is not an ORM. We don't use an ORM. We got a DBA writing the queries and he sends the queries to us which we put in our application layer.In my years of experience, I have never seen a instance where you need to grant each user permissions to each sproc. Why would you create multiple users and give permissions to each sproc? Are your end-users really logging into your high-risk sql server? Aren't you returning the data to the application layer anyways?? What purpose does it have?In our environment, we got a single user for each database application. The application connects to the DB using that account. SA is by default disabled on our configurations. Our architecture already handles security within our application layer. External applications will have to go through our "interface" layer which will hide/unhide access to certain methods. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 11:20:26
|
| Are you using integrated security?If so then the user needs access to the tables which is often considered a risk.If the application uses a sql server login then that might also be considered a risk.Using SPs it means that the client has no access to the underlying tables (if the system is architected properly).How important that is to you is up to you but often if people can find a way to access tables they will use it and a single user deciding to read data can stop a system working (let alone updating).I just like the flexibility that SPs give you and never allow client access to tables.It means that I can monitor SPs and if there is are any problems can optimise/change the offending SP without having to know what the client or application is doing.Also means I can test the SP without having to extract code from the client.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 11:29:18
|
quote:
Originally posted by nr
Are you using integrated security?If so then the user needs access to the tables which is often considered a risk.If the application uses a sql server login then that might also be considered a risk.Using SPs it means that the client has no access to the underlying tables (if the system is architected properly).How important that is to you is up to you but often if people can find a way to access tables they will use it and a single user deciding to read data can stop a system working (let alone updating).I just like the flexibility that SPs give you and never allow client access to tables.It means that I can monitor SPs and if there is are any problems can optimise/change the offending SP without having to know what the client or application is doing.Also means I can test the SP without having to extract code from the client.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
If a client has found a way to access your tables directly, then you have a bug in the system. That is the whole purpose of code-reviews, controlled testing, verification, and monitoring. This should be done regardless of using SPs or not. In my applications, the client has no access to the tables and I don't use any SPs.Obviously, with .NET, you can monitor your application health too along with self-logging analysis as well.So I still await for a valid relevant response, "How can controlling transactions within the DAL lead to blocking and deadlocks compared to SP's controlling the transactions"?Also, just so you know, based on your response.....powering on a SQL server is a security risk, so you might as well leave it off.EDIT:"Also means I can test the SP without having to extract code from the client."LOL. So you mean you don't have a copy of your application, preferably in source control? You have to ask the client to give back your application so you can extract the code????!?!?! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-22 : 11:54:15
|
| I'm only going to raise one point and it probably isn't applicable to your local situation but you've raised a general scenario so please bear with me.What happens when a third party (another company / another application / some legacy system / whatever) wishes to access your database but do not want to use any of your application frameworks? If everything you have goes through your application it could be the best / most secure / efficient / fabulous / amazing framework in the world but the other company want to use their own software (which you might think is terrible but you aren't going to change their mind) then stored procedures / views / whatever are going to be more useful to you (and to the third party) to retain some measure of control.That of course doesn't apply if your data-store is just that -- a data-store for one application.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-04-22 : 11:54:15
|
| @whitefangit seems that you and we are looking at this from a very different angle:your starting point is that an app is properly designed, etc.our starting point is that it isn't. and i don't mean your app. i mean apps in general.large majority of apps are just cobbled together because there was no time for a proper design process or some other BS.the lowest common denominator for all these apps in a company is the database. and since data is business the database must be protected.your approach is good and there's nothing wrong with it IF you're starting from scratch.for example where i work we hava a db that is accessed by 6 different systems.one of those is old classic asp, 1 asp.net, 3 windows forms, 1 reporting server and one ETL job.now the old asp app is basicaly a core app and is already 8 years old and there's no reason to change it because it serves its purpose well.the etl (old DTS) process is also already 5 years old and works flawlessly.for the .net based apps we have a framework similar to yours, but for other apps there's simply no bussines reason to change them. therefore the database must handle all DB logic and security.note that DB logic isn't business logic.and come on, everyone smart in enterprise development uses source control.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-22 : 11:58:13
|
quote:
Originally posted by spirit1
@whitefangit seems that you and we are looking at this from a very different angle:your starting point is that an app is properly designed, etc.our starting point is that it isn't. and i don't mean your app. i mean apps in general.
++Absolutely.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 11:59:20
|
quote:
Originally posted by Transact Charlie
I'm only going to raise one point and it probably isn't applicable to your local situation but you've raised a general scenario so please bear with me.What happens when a third party wishes to access your database but do not want to use any of your application frameworks? If everything you have goes through your application it could be the best / most secure / efficient / fabulous / amazing framework in the world but the other company want to use their own software (which you might think is terrible but you aren't going to change their mind) then stored procedures / views / whatever are going to be more useful to you (and to the third party) to retain some measure of control.That of course doesn't apply if your data-store is just that -- a data-store for one application.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Good point, but invalid. You said the third party company would like to use their own software? Well then they better plan to rewrite their software to be compatible with your data structure OR your SP API. Same applies if they are using my application framework, they have to make their software compatible. This would be solved by a bridge interface (middle-tier) between the two frameworks (regardless of SPs or not).The application layer makes even more sense in these situations. They just drag and drop our interface into their software and it will provide all the documentation and intellisense they need (if their using a high level language). |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 12:06:17
|
| spirit,Yes, in your case where you are dealing with 6 existing legacy apps...SPs are valid and makes perfect sense to put business logic in. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 12:09:56
|
| >> LOL. So you mean you don't have a copy of your application, preferably in source control? You have to ask the client to give back your application so you can extract the code????!?!?!No but I also don't have to build a client test system just to test a database query.By client I mean the DAL which in your case is the user of database services.>> So I still await for a valid relevant response, "How can controlling transactions within the DAL lead to blocking and deadlocks compared to SP's controlling the transactions"?Because there is a round rtip to the client while the transaction is open. The longer the transaction is open the longer locks will be held and the more chance of blocking and deadlocks. Thought that would be fairly obvious.You say that the dba writes the queries and they are implemented in the dal. That sounds a bit like the people implementing them are application coders so giving a risk that they don't understand the impact of locking in the database.>> If a client has found a way to access your tables directly, then you have a bug in the system. If you need to give client access to the database then that might be a security issue. They might not be able to connect now but given that they have a login to the server it doesn't mean that they won't be able to use it in the future.Seems like this thread is getting a bit silly - a bit troll-like.I take it you agree with the other points which for me is enough to come down in favour of SPs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 12:15:28
|
quote:
Originally posted by nr
>> LOL. So you mean you don't have a copy of your application, preferably in source control? You have to ask the client to give back your application so you can extract the code????!?!?!No but I also don't have to build a client test system just to test a database query.
Maybe you never worked in a enterprise environment but nobody is allowed to make ANY changes to ANY of the applications or databases without a CMI request where everything is document down to the last detail.quote:
Originally posted by nrBecause there is a round rtip to the client while the transaction is open. The longer the transaction is open the longer locks will be held and the more chance of blocking and deadlocks. Thought that would be fairly obvious.
The round trip is so minimal when all servers are located in the same datacenter with massive bandwidth and low latency links between them.quote:
Originally posted by nrYou say that the dba writes the queries and they are implemented in the dal. That sounds a bit like the people implementing them are application coders so giving a risk that they don't understand the impact of locking in the database.
Urm...nope. We tell the DBA what data we need and he writes a query for an example:public Car GetById(int id){ string sql = "SELECT * FROM [car] WHERE id = @id;" <-- this is what dba writes}Also, the client in under no circumstances should be able to login to your SQL server OR view the DB in an enterprise environment. We did an application for one of the top universities in the world and they had their internal IT team look at the "overall" application...but never the application code or DB.quote:
Originally posted by nrI take it you agree with the other points which for me is enough to come down in favour of SPs.
No, I don't agree with any of the other points you made, they are invalid. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 12:23:29
|
| >> Maybe you never worked in a enterprise environment Why do you say that - and do you think it's restricted to enterprise environments?>> but nobody is allowed to make ANY changes to ANY of the applications or databases without a CMI request where everything is document down to the last detail.That's fine if you also aren't interested in making the changes as easy as possible to implement and test. Just because something is documented doesn't mean that you should makje it difficult.>> The round trip is so minimal when all servers are located in the same datacenter with massive bandwidth and low latency links between them.Sounds like you aren't handling many transactions so this might not be an issue but the round trip time isn't the main impact on the transaction length when you move the control outside the database.>> string sql = "SELECT * FROM [car] WHERE id = @id;" <-- this is what dba writeslol - he writes a select *?This is another issue - this (and all client embedded sql) will cause more data to be transferred from the database so using up bandwidth and more importantly server memory which is the most important resource for sql server.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-22 : 12:28:06
|
quote:
Originally posted by whitefang
Maybe you never worked in a enterprise environment but nobody is allowed to make ANY changes to ANY of the applications or databases without a CMI request where everything is document down to the last detail.
I assume this is true for triggers too?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 12:30:32
|
quote:
Originally posted by nr
>> Maybe you never worked in a enterprise environment Why do you say that - and do you think it's restricted to enterprise environments?
Your inexperience shows. If you think making a quick change in a production environment is professional, you need to go find a new job.quote:
Originally posted by nrThat's fine if you also aren't interested in making the changes as easy as possible to implement and test. Just because something is documented doesn't mean that you should makje it difficult.
You're probably an outsourced developer, I hear that they don't have any "rules". They will happily make changes in a production environment.quote:
Originally posted by nrSounds like you aren't handling many transactions so this might not be an issue but the round trip time isn't the main impact on the transaction length when you move the control outside the database.
We're handling many transaction.quote:
Originally posted by nr>> string sql = "SELECT * FROM [car] WHERE id = @id;" <-- this is what dba writeslol - he writes a select *?This is another issue - this (and all client embedded sql) will cause more data to be transferred from the database so using up bandwidth and more importantly server memory which is the most important resource for sql server.
Yeah, 500kb of extra data will bring down our Cisco enterprise routers. Based on your response, it sounds like their outsourcing trolls now.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.[/quote] |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-22 : 12:30:36
|
quote:
Good point, but invalid. You said the third party company would like to use their own software? Well then they better plan to rewrite their software to be compatible with your data structure OR your SP API. Same applies if they are using my application framework, they have to make their software compatible. This would be solved by a bridge interface (middle-tier) between the two frameworks (regardless of SPs or not).The application layer makes even more sense in these situations. They just drag and drop our interface into their software and it will provide all the documentation and intellisense they need (if their using a high level language).
Yes -- I agree with you - in a perfect world where the third party make good, well considered decisions and are happy not to use their own legacy crap then yes -- perfect.However, from personal painful experience that sometimes doesn't happen. Sometimes some third party comes along that your management (for whatever reason) are *unable* to say no to and then bang -- things have changed.Speaking as a database guy who gets pulled into development to correct / advise on SQL, I've seen different applications set up to use our database. Once when we acquired a competitor and recently when we were bought out (cycle of life huh?). The first time was relatively painless but the second was hideous -- they already had their own distributed bus to handle data transfer to and from their many and varied departments / applications and had no desire to change or adapt it.I think Spirit1 got it exactly right -- if you are designing a system from the ground up that you can completely control then your object oriented approach sounds great (as long as you don't let automatic tools make *all* the important decisions). Your developers are then free to quickly and correctly add functionality.I'll add one caveat -- I hate hibernate (only one I've so far been exposed to) but that's just a personal thing arising from the extreme difficulty I've had correcting errors and my own lack of development experience with it. Tracking down crazy sql that some awful hybrid of java programmer and automated development tool has created was a million times harder than debugging a stored procedure.Basically -- If your developers have a good strong understanding of SQL then your approach is great. When you have a big team of developers some of who's SQL skills are not so good then you get problems.Its horses for courses. I write fairly good SQL (or at least I like to think so) -- If I were to write .net / java / whatever, you'd be justified in pointing and laughing at me.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 12:31:32
|
quote:
Originally posted by Peso
quote:
Originally posted by whitefang
Maybe you never worked in a enterprise environment but nobody is allowed to make ANY changes to ANY of the applications or databases without a CMI request where everything is document down to the last detail.
I assume this is true for triggers too?
E 12°55'05.63"N 56°04'39.26"
Yep. |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-22 : 12:34:24
|
quote:
Originally posted by Transact Charlie
quote:
Good point, but invalid. You said the third party company would like to use their own software? Well then they better plan to rewrite their software to be compatible with your data structure OR your SP API. Same applies if they are using my application framework, they have to make their software compatible. This would be solved by a bridge interface (middle-tier) between the two frameworks (regardless of SPs or not).The application layer makes even more sense in these situations. They just drag and drop our interface into their software and it will provide all the documentation and intellisense they need (if their using a high level language).
Yes -- I agree with you - in a perfect world where the third party make good, well considered decisions and are happy not to use their own legacy crap then yes -- perfect.However, from personal painful experience that sometimes doesn't happen. Sometimes some third party comes along that your management (for whatever reason) are *unable* to say no to and then bang -- things have changed.Speaking as a database guy who gets pulled into development to correct / advise on SQL, I've seen different applications set up to use our database. Once when we acquired a competitor and recently when we were bought out (cycle of life huh?). The first time was relatively painless but the second was hideous -- they already had their own distributed bus to handle data transfer to and from their many and varied departments / applications and had no desire to change or adapt it.I think Spirit1 got it exactly right -- if you are designing a system from the ground up that you can completely control then your object oriented approach sounds great (as long as you don't let automatic tools make *all* the important decisions). Your developers are then free to quickly and correctly add functionality.I'll add one caveat -- I hate hibernate (only one I've so far been exposed to) but that's just a personal thing arising from the extreme difficulty I've had correcting errors and my own lack of development experience with it. Tracking down crazy sql that some awful hybrid of java programmer and automated development tool has created was a million times harder than debugging a stored procedure.Basically -- If your developers have a good strong understanding of SQL then your approach is great. When you have a big team of developers some of who's SQL skills are not so good then you get problems.Its horses for courses. I write fairly good SQL (or at least I like to think so) -- If I were to write .net / java / whatever, you'd be justified in pointing and laughing at me.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
I agree, my approach works perfect for the companies I worked with. Also, in a proper architecture, there is no place for ORMs. ORMs are usually for RAD apps and lazy developers.Also, as I said, our developers don't need to be experts in SQL. That's why we have DBAs. They work with our developer team to write the queries. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-22 : 12:37:13
|
| Yep - a Troll>> Your inexperience shows. If you think making a quick change in a production environment is professional, you need to go find a new job.Where have I said it's ok to make a quick change. Do you think it's a good idea to make system easier or more difficult to change.>> You're probably an outsourced developer, I hear that they don't have any "rules". They will happily make changes in a production environment.Lol - where do you get that from? I just said I want to make systems flexible and maintainable. I don't allow embedded sql in client apps and people who can't follow rules (not mentioning names) aren''t allowed access.>> We're handling many transaction.>> Yeah, 500kb of extra data will bring down our Cisco enterprise routers. How much extra data per transaction, how many transactions. Multiply them and you will get an indication of how much extra traffic you have to handle - and it's per time unit not a number of kb. And are the routers the onl;y things involved?Your approach may be fine for your situation but I think you might be a bit more open to other ideas.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Previous Page&nsp;
Next Page
|
Tell me why my design approach is bad