December 22, 2009 at 7:07 am
On the "where to process it" question, the keys to that are "what are you doing" and "what effect will it have on network utilization".
If, for example, you're going to things to the data that can be done by T-SQL in sets, then you're better off doing that on the server. If you're going to do things like format datetime data or other presentation issues, do that on the web server. I prefer to move things like dynamic pivots all the way to the client (usually in Excel) and take advantage of local processing and RAM. Of course, that's only if it's a small enough data set to make that viable.
If, for example, processing it on the database server would cut down the amount of data being sent over the network to the web server, then it's probably best to do it on the database server. If not, then it's a wash on that one.
- 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
December 22, 2009 at 4:26 pm
My 2c ...
I'm not a DBA. I come from a development background, moving in more recent years into a SQL role (database design, rather than the server management). I still develop some small, in-house apps that have SQL backends.
I used to just code SQL into the app, rather than use SPs, but in recent times I am using SPs more. I like that approach now as it means fewer changes to the application front-end ... with just me to develop/support them I need the most time-efficient option and I find SPs provide this.
There is no single, correct answer to the argument about whether this logic belongs in the app, the database, or some middle layer. It depends on so many things. Keep an open mind - try using SPs and see what works best for you. The best solution is likely to be a mixture of all the different tools and methods available.
Chris
December 23, 2009 at 7:29 am
Naked Ape (12/22/2009)
I used to just code SQL into the app, rather than use SPs, but in recent times I am using SPs more. I like that approach now as it means fewer changes to the application front-end ... with just me to develop/support them I need the most time-efficient option and I find SPs provide this.
Yeah, been there, done that, fodder for this thread. 🙂
As has been expressed through this thread like a broken record, "it depends." I will say that, because of the suggestions I've gotten (especially about security), combined with what I've learned about SQL in the past couple of years, I'd probably lean toward the SP direction.
But again, it depends!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 23, 2009 at 9:26 am
For me it is Stored Procedures every time, for most of the optimisation, efficiency, debugging and security reasons already mentioned. I think the IDEs I have used (Delphi and Visual Studio) invite you - in demonstrations and tutorials - to use inline SQL queries, so that they can show off their RAD speed and power. Once you've got the idea, you have to go away and do some Slow and properly-written and -tested development to make proper use of SPs.
Yes, they do put logic in the database layer, but that's the sort of practical implementation compromise you meet all the time. SQL Server is just too good at data not to give it all the data work you can.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply