August 12, 2008 at 2:48 pm
Well I have a a few tables I need results from. To put it in an abstact form:
I need value1 from table1
I need value 2 from table2
I need value 3 from table3 if value 4 from table 4 is true
Now I can execute a stored procedure where I get value 4 from table 4 to see if it is true.
Then afterwards I if it is true I can select the other values from the other tables with joins.
But I can also put this logic inside one stored procedure where I check in the SP if value 4 from table 4 is true and decide in the SP to select the other values if value 4 is true.
I was thinking this method would be quicker because I only need to contact the SQL server once.
Ofcourse I need a much more complicated scenario but is it wise to handle such logic inside the SP instead of in my code file?
Any tips and pointers appreciated.
As you may see I'm new on this area and used to execute multiple select queries from within my code but I want to suppres my traffic between the webserver and the sql server.
August 13, 2008 at 7:05 am
Whether or not business logic should be inside a query is a HUGE debate. There are extremists on both sides. I'm pretty much in the middle. If it makes sense inside your database, put it there. If it doesn't, don't. There are cases where the database is absolutely the correct place for certain behaviors and there are cases where it is utterly the wrong place. Each situation is different which is why I wouldn't make a blanket statement in either direction.
That said, what you describe sure sounds like a place where the logic should be contained within the database. I say this for one reason. A very sound operating principal when working with databases is to only get the data you need and only when you need it. Meaning, if you don't need all the other data that doesn't equal value 4 from table 4, why on earth would you want to marshal it all, pass it over your network and then into your application server or app to have to be dealt with there? Why not simply filter it at the source and return only what is actually needed?
From the sounds of things, you have a set of JOINS and a WHERE clause to build within your procedure. Not at all a problem within SQL Server.
"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
August 13, 2008 at 8:24 am
I would go along with Grant on this one. If you are doing a lot of calculations or string manipulation then an SP may not be the right place to put your business logic, but when the logic is based on selecting data then the SP is the place to do it. Another area where an SP is not necessarily the best place to put business logic is when you are doing something that is a key to differentiating your product since, even with encryption, smart DBA's developers will be able to see your logic.
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
August 13, 2008 at 10:39 am
I'm curious to see how people deal with source control issues with sprocs if you're putting your business logic in the database. For example, lets say you create software that has a small number of clients (~50) and they're all on different versions (the same product, just taking releases at different times). You can get support calls from any of the 50 clients at any time, but you do not have access to the client's databases and you cannot store a copy of the client's database on your servers. How can you guarantee the database you are using has the same sprocs that the client has? You can do this with C# code very simply by labelling the code at release in team system/source safe and just extracting/building that specific code base when you get a call.
Similarly, how do you mitigate the chance that some rouge know-it-all DBA didn't decide to "optimize" your sproc at the client's site? It is not difficult to extract encrypted sprocs - but changing compiled obfuscated .net is far from trivial. Also, maybe it is just personal preference, but I find that debugging very complex C# code using visual studio is considerably easier than debugging an sproc (haven't had much time to play around with the new debugger in SSMS 2008 yet mind you).
Finally, how do you isolate developers during the development process? For example, lets say I have a C# code base under team system and a centralised development database that all developers connect to and develop from. If I have all my business logic in my C# code and I'm working on something, I can check out all code I need to work on and completely isolate myself from the rest of the team until which time I'm finished. You can guarantee stability of the checked in built at both ends of my development (besides of course having a developer checking in poor code). But if you're using the database, when I modify an sproc, it is modified for everyone. If my unit of work involves enhancing multiple sprocs, when I change one, I'm going to start interrupting everyone else and when someone else starts getting spurious results, the easy out is to blame my changes rather than investigating - you can't do that with checked in code. Granted everyone could have their own database, but in my situation there are frequent schema changes and this becomes a nightmare where different changes were made on different local databases. It takes no time to get to the point where every developer has a different database.
August 13, 2008 at 11:18 am
I can't help you on the rogue DBA topic. Hunt 'em down & hurt 'em may be your only option.
As to the rest, why not manage the database code in exactly the same way as the C# code, using almost the exact same tools? Visual Studio Team Edition for Databases does exactly this and more. It's how we support development at my company. Because it builds databases straight from source control, you can work off the exact same labels as the C# code and everything else.
Now, you will have to be able to support multiple databases in some fashion, whether on multiple servers, in virtuals, through instances, or through local installs. There's no getting around the fact that two different copies of the database can't occupy the same space. But if you're working on a branch of the code, you can't deply on top of the main stream of development either. Same issue.
Again, VSDB, especially the GDR release that's coming out shortly in support of 2008, works off multiple environments like this very well.
"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
August 13, 2008 at 2:00 pm
Thanks alot for your views.
I don't want to start a "HUGE debate" about this but I am always interested in more insight, deciding factors on when certainly not to include business logic in a SP.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply