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 «««23456»»»

Always Abstract Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 5:43 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 8,571, Visits: 9,076
RonKyle (8/21/2013)
I favour moving as much application business logic into stored procedures as is possible, thereby abstracting out the UI layer from the business logic


I think I can help with wondering why it's not in wider practice--this is not a good idea. The database is a chokepoint, and you want it processing as few business rules as possible. The enforcement of business rules really belongs in the middle tier, whatever that might be for the application. Let the code in the intermediate levels do their thing and then send the result to the database.


What, so you think we should maybe have tables that conform to 1NF, but normalisation to 2NF or higher is always a mistake, because only business rules can tell us what dependencies there are between different parts of the data and normalisation is all about ensuring that those dependencies are enforced by the database, and enforcing business rules in the database is anathema?

I thought that that attitude to business logic had become utterly unpopular about 40 years ago and no-one held it today, but you are showing me that I was absolutely wrong about that.


Tom
Post #1487006
Posted Wednesday, August 21, 2013 9:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 7,121, Visits: 15,024
RonKyle (8/21/2013)
As a VB Programmer, I developed a lot of processes using disconnected recordsets. I used stored procedures to retrieve the initial sets. However, the additions and changes made by the recordset object required direct access to the tables. For that reason alone, I couldn't have used views. I've seen this technique recommended before, but personally have found very little use for views in an OLTP environment. I've used them to some extent in an OLAP environment to allow me to pull a subset or higher level from a dimension table (e.g. create a quarter hour view from a dimension table that has all the minutes, a month view from a date dimension that has days or a sub list of inspectors from technicians). In one of the Kimball books I read recently views were recommended for all the tables so that the auditing information that was of no use to the end users could be shielded. This is also intriguing, but you can't set the referetial integrity among the views in the same manner as tables.


I too got a lot of mileage out of recordsets. One thing I will say is that while the default behavior of the recordsets was to go for direct access to the tables, it's actually very straightforward to switch them over to using nothing but stored procedures. Sure it adds a few minutes to the dev process - but I thought the extra work was worth every penny. The extra abstraction saved my bacon a few times, and allowed us to make changes on the DB side (such as - intercepting and validating certain changes) without redeploying the program.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1487030
Posted Wednesday, August 21, 2013 11:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:21 PM
Points: 17, Visits: 46
I am a Computer Science Uni graduate, working as an in-house programmer for a mid-sized business, using MS-Access as a front end, and SQL Server as the database backend. Putting aside the general IT cultural bias of loving to hate MS-Access, this combination often results in may real world / economical compromises and shortcuts as mentioned in the original post.
Long ago when I stared out I inherited a few MS-Access frontend apps with mdb backends. I cleaned them up, rewrote code, added indexes (there were none), added caching, etc.. when it became time to migrate to SQL Server, while I generally knew what I was doing, I still did my googling/research, read other peoples tips and various 'best practices' and thought about how I wanted to structure my programs and data, what problems I had already had, and how I may avoid them in the future, and how to best leverage the features and power of the SQL Server.
I read the tip about always doing Views on Tables, then linking to the View from the Front-End. This just made obvious sense to me ( me a lowly VBA code monkey :) and I have always, always been doing it ever since. This layer of 'independence' between my front-end app and the actual data tables has allowed me to do some very nifty on the fly conversions, data cleanups, feature migration, and backward compatibility for users that dont need to or forget to upgrade, while pushing out new versions to my power user beta testers and then onto most regular users. I suppose the technique is like a poor-mans AppServer, it gains me some of the advantages of a 3-tier architecture, even though I am using a 2-tier/Client-Sever approach.
Basically the 'View of Tables' technique has has saved me time, struggle and effort, over and over again. I have not found performance to be a problem, as the View is almost always just a straight mapping (or very simple query manipulation) onto a Table. Most of my more complicated queries have ever only involved 3 or 4 tables, and these 'complicated' queries are Front-End data manipulation that usually only need small sub-sets of the tables data, and not SQL Server Views on Tables as a layer of independence.
Whenever I have coded a front-end query that ended up being slow, I have had many other techniques to fall back on (eg Local caching of just the relevant sub-set of data in the front end app, then running my query on the local cached data, Pass Thru Queries that could even use the real sql server table and not the views, Stored Procedures, Creating a static cached result set from a View/Query each night for slow running View/Queries that are based on large but infrequently changing tables)
Admittedly I work with smaller databases, and so do not really tax a decent SQL Server, nor have to face the issues that a DBA with large databases and transactional load may routinely face, but still I read your original post and was suprized to think that everyone was not already using this technique. Mr Codd who laided the foundations for relational databases back in the early 70's wrote of using Views for these reasons and more way back then.

I dont think I have ever regretted nor questioned my use of SQL Server Views-On-Tables.

Oh, and yes I would love the dbViewReader and dbViewWriter feature. I just assumed that there was a way to force a View to be read only (which is a slightly different thing to User security Role Membership) but I just had never got around to finding it

- Matt
Post #1487054
Posted Thursday, August 22, 2013 3:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:23 AM
Points: 1,627, Visits: 1,070
I've got an irrational hatred of views on tables from sorting out various endemic performance issues with systems which were essentially not very well designed. I acknowledge it was the bad design bit that was the problem but still harbour a strong dislike of excessive view deployment (of course views can be very helpful in some circumstances).

As to stored procedures, I find they quickly get overly long and impenetrable, and the business logic they contain can often be better (more clearly and elegantly) expressed in code e.g through EF. This is obviously for the types of systems I have written and with my knowledge set and experience of the other developers I have worked with and I accept it's not gospel. Clearly this does not preclude the use of procs where they are the best choice for the job.
Post #1487137
Posted Thursday, August 22, 2013 6:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 804, Visits: 1,990
One thing I will say is that while the default behavior of the recordsets was to go for direct access to the tables, it's actually very straightforward to switch them over to using nothing but stored procedures. Sure it adds a few minutes to the dev process - but I thought the extra work was worth every penny. The extra abstraction saved my bacon a few times, and allowed us to make changes on the DB side (such as - intercepting and validating certain changes) without redeploying the program.


If I had found a process that only added a few minutes, I would have been glad to use it. I did a lot of reading on the subject and never came across a good solution for it. I'm not sure how helpful it would be now in the .NET world (I was programming in VB6), but if it's a relavant topic for today, I'd be interested to see a sample solution.



Post #1487224
Posted Thursday, August 22, 2013 7:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 804, Visits: 1,990
no-one held it today, but you are showing me that I was absolutely wrong about that.


Well, then I'm glad. However, I didn't create that. As at least one other has pointed out, most business logic is more logically stated in code. Before I was taught to move the business logic to the middle tier, I remember writing some long and convoluted stored procs. When I learned that that wasn't the best way and rewrote the more complicated ones so that the logic was in the code, and the stored procedures just did the reading and the writing, not only were the rules easier to understand, but the database performance improved.



Post #1487229
Posted Thursday, August 22, 2013 7:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 804, Visits: 1,990
The database can be a choke point but it isn't always.


The application may be performing poorly, but it's not a chokepoint in the sense that the database is. In most setups, all the read and write requests coming from either all the applications or from the multiple servers end up going to the ONE database. That's was makes it the chokepoint. In a sense, it is the center of that application's universe. (I'm not sure I like the analogy, but for now I'll go with it).



Post #1487231
Posted Thursday, August 22, 2013 7:19 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 9:03 AM
Points: 758, Visits: 2,154
RonKyle (8/22/2013)
The database can be a choke point but it isn't always.


The application may be performing poorly, but it's not a chokepoint in the sense that the database is. In most setups, all the read and write requests coming from either all the applications or from the multiple servers end up going to the ONE database. That's was makes it the chokepoint. In a sense, it is the center of that application's universe. (I'm not sure I like the analogy, but for now I'll go with it).


In my specific case, it looks like the application is iterating one-by-one through the records in the database just to display them to the screen. I can get the same information in a second if I query the production database directly. Even if I'm the only one in the system, it shouldn't take a full minute for the application to retrieve 22 records from the database, when it's instantaneous querying the database directly.

To keep with your space analogy, troubleshooting data issues through the application is travelling sub-light speeds, while querying the database myself (without the application) is like stepping through a wormhole to my destination.




The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.
Post #1487240
Posted Thursday, August 22, 2013 7:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 7:03 AM
Points: 6,779, Visits: 1,866
I love abstraction, but I only use it when I get to the place where it's needed to solve the problem. I've seen the view for everything approach and it never seemed like it made much difference, still wind up with changes that change things!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #1487243
Posted Thursday, August 22, 2013 8:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 1,603, Visits: 4,593
Andy Warren (8/22/2013)
I love abstraction, but I only use it when I get to the place where it's needed to solve the problem. I've seen the view for everything approach and it never seemed like it made much difference, still wind up with changes that change things!

I've found that abstraction via views is most useful in a situation where the database is accessed by a several different applications or a lot of ad-hoc users. I can't trust everyone to write efficient SQL, joining tables the right way to produce correct and efficient results, so I write a manageable number of views or stored procedures to do it for them based on case usage requirements or requests. Of course, the app developers don't write the views, because that would defeat the purpose.

For a data mart that is accessed only by pre-built SSIS packages and Analysis Services, not tyically queried in an ad-hoc fashion by users, I don't see so much need for views. Preferably the end users don't even have a login there.
Post #1487290
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse