[thelist] case statement catch-all? MSSQL

[Brian Cummiskey]

Ken Schaefer wrote:

> : >
> : Sure, it can be done on the scripting side, but IMO, it would be much
> : more time consuming, especially since i'm stuck with classic asp.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> You are joking me, surely? 
> 
> You think that evaluating CASE statements inside a sproc and generating a sum
> is more efficient that adding three numeric values in ASP? The former (using
> a sproc) is an order of magnitude (at least) more costly.

I'm sure the CASE takes more than val + val + val in asp, but inorder to 
get those values, i'm going to have to tripple the length of my query in 
the first place.


> Surely your ASP code would look something like:
> 
> <%
> With Response
> 	.Write(firstValue)
> 	.Write(secondValue)
> 	.Write(thirdValue)
> 	.Write(firstValue + secondValue + thirdValue)
> End With
> %>

something like that...  but it will be a huge generic loop like:

for x = 0 to rst.Fields.count - 1
	Response.Write "<td>" & trim(rst.Fields(x).Value) & "</td>" & vbcrlf
next 		


> 
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : the code is horible, but, it works...  and we can 
> : always buy more ram :D
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 
> RAM doesn't help you with lock contention, and all the other things that
> start to happen when you do things "because you can"
> 
> But hey, what do I know? 

Probably a LOT more than I do...  asp/iis/mssql are not my native 
tools... i'm a LAMP guy, trying to hack it in the corporate world, 
working with M$ products.

> 
> However, if you're free next Thursday and in Australia you can come to my MS
> Tech.Ed 2005 session on debugging crashes, hangs and performance issues in
> IIS 
> :-)

I'm only 5000 miles away...  i'll take the train :p


> In all seriousness, it's your app, and you know the environment best. But
> given that it's taken a day to get this working, and it's not very efficient,
> and the alternative involves next to no code, I'm struggling to think why
> this is a good idea.

I'm re-thinking the approach, but i just don't see it being any better 
having asp be the workhorse on a pIII 512mb ram iis server, vs sql being 
the work horse on a dual xenon 3gb ram machine.  perhaps knowing that 
may sway your results some?


I agree, if it was simple 1+2+3 math, i would in fact do it with the 
asp.  but since i need to grab totals, do division, round, and all that 
good stuff, no to mention, i need to use a temp table in order to get it 
all in one recordset, i just don't see doing it with asp being the 
better approach.