[thelist] case statement catch-all? MSSQL

Ken Schaefer Ken at adOpenStatic.com
Thu Aug 25 19:41:34 CDT 2005

: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Brian Cummiskey
: Subject: Re: [thelist] case statement catch-all? MSSQL
: Ken Schaefer wrote:
: > 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.

You already have the values. I'm just talking about not calculating the final
value (the sum of the previous individual values) in the sproc

: 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

If you change it to:

For i = rst.Fields.count - 1
	intTotal = intTotal + rst.Fields(x).Value
	Response.Write "<td>" & rst.Fields(x).Value & "</td>" & vbcrlf
Response.Write "<td>" & intTotal & "</td>"

You can do it in ASP with just two extra lines of code.

: > 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?

No. Adding numeric values together costs next to nothing in VBScript. You
could do that operation 1,000 times on a P3 box and it would probably take a
millisecond or two.
: 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.

Even if you wanted to do the rounding, divisions and so forth in the
database, you could still do so. I'm talking about eliminating the extra
query from your statement which collects the "aggregate" result. You're
having the DB recalculate that value even though it's merely the sum of three
values you've already calculated.

Additionally, SQL (and databases) aren't really the right tool to be using
for mathematical operations. But the cost of those ops in the DBMS probably
aren't all that great, so I'm not going to argue about them.


More information about the thelist mailing list