February 14, 2011 at 2:42 pm
I'm sure this has been discussed immensely so any links to general discussions are welcomed ....
I work in a developer group where we do a variety of different web-based database applications for our company. We do not have a DBA, per se ... we have a guy who administers the SQL Cluster but we don't have a gatekeeper for all things that go in and out of the SQL servers like some places may have.
As such, we (the programmers) are tasked with designing the backend structure for all our applications. We're currently discussing what standards we should put in place when it comes to this sort of stuff and are odds on one part and that is how much separation there should be between code and the physical tables.
I'm on the slightly hardcore side of saying that no code should ever contact a database table directly under any circumstances. Having that direct connect from application to table means that no changes can be made whatsoever on the database structure without forcing changes in the application's code. And the even worse part than updating the code where you find that table name is the handling of the issues for the instance of that table name that you didn't find.
I have gotten to the point where every query I write is based on a view ... even if the view is just a direct copy of the structure of the table (with a _V_ instead of a _T_ for the name). Lately, more and more of these queries I write are ending up as Stored Procedures. My thinking - it increases reusability (if I had to get this information in this application then where else might I need the exact same data?) and also flexibility to do changes to the data structure as they are requested and being then able to run a simple query and find all the affected procedures, update them and then just test them over a couple times.
Now, this ends up creating a large number of SProcs and I don't simply overlook that can be an irritating by itself but what other pitfalls and issues should be come into this conversation?
TIA for all your help.
February 14, 2011 at 3:05 pm
You should be a DBA :-D.
I agree with you that direct table access is evil. Most developers I have worked with or interact with now, will tell you that is what your DAL is for, a place to setup your data access code so if you make schema changes you only have to change your DAL, oh and your DAL should be a separate DLL so you can incorporate into multiple projects as needed. Change it once, etc...
I love stored procedures, who cares how many you have, they don't cost you anything and if you use schemas you can do some organization of them so you can find them easily enough. You also have better control of security using sp's or views (not a big view guy). Sp's also makes tuning queries easier. I actually have a webcast coming up on using stored procedures over ORM tools/direct table access next month over on MSSQLTips (I doubt Steve minds my mention of a competitor). Here are some links on using SP's:
Mladen Prajdic – Decoupling the Database - http://tinyurl.com/DecouplingPrajdic
Paul Neilsen – Why Use Stored Procedures – http://tinyurl.com/NielsenWhySps
The Pros and Cons of Stored Procedures – Tech Ed Online - http://tinyurl.com/ProsConsOfSps
Grant Fritchey - Pre-compiled Stored Procedures: Fact or Myth -http://tinyurl.com/PreCompiledSps
Grant Fritchey - Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact - http://tinyurl.com/AdHocExecPlans
Microsoft – SDL Quick Security Reference - SQL Injection- http://tinyurl.com/SDLSqlInjection
Jeremiah Peschka - O/R-M’s: Panacea or Polio Braces - http://tinyurl.com/OR-MsPanacea
Bob Beauchemin’s series in his Data Access Topic - http://tinyurl.com/BeaucheminDataAccess
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 15, 2011 at 6:47 am
You're absolutely on the road to being a dba. Don't worry. It doesn't hurt and you frequently make a little more money than developers. You just won't have as many friends. But that's OK too. You can come around here.
I've worked on systems that had thousands of stored procedures and hundreds of views in support of a hundred tables. Storage of procedures in the system is extremely cheap. You do have to sweat procedure cache if you have lots and lots of procedures that are doing almost exactly the same job, they might be kicking each other out of memory, but other than that, don't sweat using procs.
There has been a very strong push by lots of development teams to use ORM tools like nHibernate for development so that they can get rid of TSQL becuase it's messy and hard compared to most development languages. I think the pendulum is starting to swing away from these things as many of them have hit production and encountered huge performance problems and maintenance nightmares. As long as you're using an relational database engine for storage, the best way to access the data is through set based operatoins using the language of choice for that storage engine. In this case, it's TSQL. You're doing the right thing.
Now, you can start working on making sure you have a valid clustered index on every table and make it part of the design process to pick this out. The default is for the cluster to go on the primary key, but that's not necessarily the best place for it. You should also work on enforcing referential constraints because they do help performance as well as protect data. Also, time to pick up on the rules of normalization. yes, no one follows them 100% of the time, but you need to know what the rules are and break them knowledgeably, not blindly.
Sounds like you're doing well to me. Have fun.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 16, 2011 at 8:49 am
Kind of in the same boat by me. I am a web developer but my niche as of late has been TSQL. Our latest projects have been using Net MVC and way too much LINQ to SQL in my eyes.
What I have been able to get going though is naming conventions and a lot more seperation of concerns, on the premise that calling a complex query inside of a procedure is more efficient than calling the same code across an ADO connection
I too would like to see articles/discussions that would help bolster my position
February 16, 2011 at 9:09 am
Jack Corbett (2/14/2011)
You should be a DBA :-D.I agree with you that direct table access is evil. Most developers I have worked with or interact with now, will tell you that is what your DAL is for, a place to setup your data access code so if you make schema changes you only have to change your DAL...
I love stored procedures, who cares how many you have, they don't cost you anything and if you use schemas you can do some organization of them so you can find them easily enough.
At my work we combine the two and have a DAL that is built on top of stored procedures. Stored procedures are a must for performance reasons when you end up working with any significant amounts of data that need to be manipulated and returned back to a user(reporting in my case).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply