May 7, 2008 at 10:10 pm
I have a question. Our DBA is pushing for a new structure for our databases. First I have to say that we have a lot of different databases and many tables with a lot of data that we have to join together for reports on a daily basis. With 3 different developers writing procedures for these databases you can imagine the jumbled mess we have. The idea our DBA has is to have one database that holds nothing but stored procedures that connect to the another database or (if necessary) databases for the data. A naming convention would be enabled so that by looking at the name of the stored procedure you would know what it does and potentially where it gets the data.
I, being simply a database and web programmer think this sounds like a bad idea, but I have no real evidence to say that it is or isn't. I am more of the opinion that we should have the databases split out by client and write procedures, tables, views, etc that are specific to the client; only branching out if we need to. This way certainly gives you alot more databases to manage, but it also seems structurally more sound and more along the way I believe MS SQL was intended to be structured.
I am NOT looking for opinions to back up my own opinion... I just want to be clear on that... I'm simply looking for information on one way or the other so I can help make the most intelligent decision. Our DBA is really a brilliant and innovative guy; and ultimately knows more about databases then I do but the way he does certain things just seems to be an organizational nightmare. Like having 1 stored procedure that does the insert, updates and deletes and just passing parameters to it to tell the stored procedure which action to perform. I've always learned that you should break each action (within reason) out into a seperate stored procedure. Again, I have no evidence saying one way is better then the other so I'm just trying to gleam some information from other experts like yourselves! Any help, opinions, etc. would be appreciated.
May 8, 2008 at 12:21 pm
I am not a big fan of linked servers or cross-server stored procedures because they tend to promote bad practices and end up with poor performance. They can be managed, but my experience has taught me that it takes a great deal of understanding and if you have several developers working on a few projects each there will be a lot of room for error.
I am also not a big fan of a single stored procedure that has conditional logic in it to handle an insert vs. and update. To do this and get the benefit of the procedure caching it's execution plan, you would need to use sub-procedures for each of the actions anyway. Otherwise, having the conditional in the procedure is going to mean that the first execution of the procedure (say it is an insert) will cache a plan for an insert and for any updates the procedure may perform very poorly.
Ok, now back to the one "procedure" database. This may make sense if you have a lot of databases and you need the same code-base on all of them. A procedure to pull back customer names, 10 different organization databases with different customers, and the context of the particular user determines which database you want to query. Then, you could ensure you have the same logic for all queries. Other than something like this, I think you are going to end up with a great deal of overhead with linked servers that is going to really impact performance.
So I guess, I think there are some situations where this may be a good idea but I would tend to believe it is a bad idea in most cases.
May 8, 2008 at 12:38 pm
Why don't you ask the DBA to explain in detail why this is a good idea, and post what he says on this thread?
May 8, 2008 at 2:07 pm
I would definitely NOT recommend either of the practices (all procs in one place, one-proc-to-rule-them-all).
I have seen an advantage to having all the procs for a specific application in one database, and procs for another application in a different database, and the tables in one or the other of those two, where both applications were using the same base data. But that was simply to handle an overly complex development environment that didn't have any source control in place for procs, and inadequate isolation between dev/test/qa/prod. (Really should have been separate domains in the same database, but that's another story.)
One of the reasons I'd recommend against that is that you end up with about a 5%-6% speed penalty on any proc that has to get data from a different database than the one the proc is in, even if they're on the same server. Put all your procs in one database, and all your data in other databases, and you just threw away about 6% of your server's power.
The goal of separating them out is encapsulation. Separate the data from the code. Lots of OO developers think that way. It does have certain advantages, in terms of things like backup speed (your procs-only database will be tiny and will back up fast and to very small files), and in terms of knowing where to look when there's a bug in a proc (in that they're all in the same place).
But it will wreck havok on your ability to scale out.
What happens when your databases grow a bit and need to be separated onto multiple servers? Instead of moving a database and all its procs, and rebuilding the connection string in the apps that connect to that database, you have to go into every single proc and set it up for distributed processing.
On the point of placing multiple actions into a single proc and determining whether it's an update/insert/delete by use of an input parameter, you're losing out on that one too because of solution caching. Your whole system is slower, just because of that idea.
So, unless you own stock in the company that sells the hardware your employer buys, don't use either of these ideas. If you do, convince them that the slowness can be handled by purchasing vast amounts of expensive hardware. VAST amounts. Otherwise, just don't use either of these ideas.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2008 at 7:52 am
There are a number of reasons that are thrown out to justify having any/all stored procedures in one database and all other objects (views, tables, functions, etc) in the other databases. One is from an organization standpoint and code reuse. He is big on code reuse. He looks at things like this... if it does the same "action" then put it in an existing stored procedure. The problem is, it may be the same action, but the data is coming from different tables and/or views. So instead of having an SP per client to grab certain data, we have one stored procedure that we pass the clientid to and then if statements in the SP to say if clientid is XYZ then grab data from these tables, if the clientid is ABC then grab data from a different set of tables... etc.
Again, I've always learned that you should seperate actions. You make stored procedures that pull data from like tables not write stored procedures that perform like actions agains different tables.
Plus, somewhat in his defense... he has a lot of people over the years tell him that one way or another is bad... bad performance issues, etc. But I have to admit, our procedures are fast, faster then I ever thought they could be considering the way they are written. I just think it's better to seperate when possible. It may make more objects, but I know what each one does and who it's for.
Any other opinions? These have been helpful so far!
May 12, 2008 at 9:24 am
Primary reason to not do it? DR
If you have stored procedures in one place and data in another you have two separate systems to get back running if there's a disaster. You also are depending on them being in synch. What if the data comes back for yesterday but the stored procedures don't? And you made changes yesterday (or any other day). Then you might really be up the creek.
A database is a contained unit. It can be problematic enough getting jobs and logins squared away, but I'd be close to calling the separation of data and stored procedures into separate databases, a negligent idea.
The reasons given above are good as well.
Honestly I don't see a single reason to do it.
May 12, 2008 at 1:07 pm
Don't push the performance button on this one.
The biggest concern I'd have with this is still the ability to move a database to another server when size, traffic, etc., justify that. If the database is self-contained, you just move it. If the data is in one database, and the procs in another, a simple move is completely out of the question, and every proc that touches that data has to be re-written.
If the DBA objects to that point, it will almost certainly be in terms of, "our current server is good enough for years!" Just make sure management understands that they aren't allowed to expand the business significantly so long as this DBA insists on this architecture. That'll go over like a lead balloon.
Or, alternately, don't worry about it. If the databases truly work well enough for the expected expansion of the business, and it's not harming you in any way, let the DBA take responsibility for the databases. It's his job, let him fly or die with it.
But I still maintain it's a bad idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply