which is better? aggregate function or calculated in program?

Any one has comment on this? SELECT SUM(col) from table or select col from table then calculate sum value in 4gl program. Comparing these by performance , memory usage,or else. Which way is better?

roger@star2000.com.tw wrote: > Any one has comment on this? > SELECT SUM(col) from table > or > select col from table then calculate sum value in 4gl program.> > Comparing these by performance , memory usage,or else. > Which way is better? > Select sum(col), for sure. If it isn't, there's something seriously wrong with your database server. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

Depends. ;-) Usually, almost always, it will be faster to do the sum in the database and then get the single value back. (The caveat is that there may be some strange reason why the sum() may take longer ...) (We don't need to talk about network traffic, speed of local machine vs database server, etc, etc, etc...) > From: roger@star2000.com.tw > Subject: which is better? aggregate function or calculated in program? > Date: Wed, 20 Aug 2008 17:02:37 -0700 > To: informix-list@iiug.org > > Any one has comment on this? > SELECT SUM(col) from table > or > select col from table then calculate sum value in 4gl program.> > Comparing these by performance , memory usage,or else. > Which way is better? > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Get ideas on sharing photos from people like you. Find new ways to share. http://www.windowslive.com/explore/photogallery/posts?ocid=TXT_TAGLM_WL_Photo_Gallery_082008

Ian Michael Gumby wrote: > Depends. ;-) ...on what? > Usually, almost always, it will be faster to do the sum in the database > and then get the single value back. > (The caveat is that there may be some strange reason why the sum() may > take longer ...) ...what strange reason are you thinking of? Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

> ...what strange reason are you thinking of? My bet is he won't be able to tell you for NDA reasons. But he did it years ago, and you are being naive.

Mark Townsend wrote: >> ...what strange reason are you thinking of? >> > > My bet is he won't be able to tell you for NDA reasons. But he did it > years ago, and you are being naive. > You're funny for an Australian, you are. :o) -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

On Aug 20, 9:51 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > Ian Michael Gumby wrote: > > Depends. ;-) > > ...on what? > > > Usually, almost always, it will be faster to do the sum in the database > > and then get the single value back. > > (The caveat is that there may be some strange reason why the sum() may > > take longer ...) > > ...what strange reason are you thinking of? > Serge, There is always the unknown and when someone says "a simple sum(col) on table A", you don't know what's behind it. I can think of some complex calcs and then you want to take the sum as a roll up. Calcs you can't do in the database unless you want to write your own "datablade" or maybe purchase NAG's stuff which I'm not sure how its going to be supported since IBM and NAG didn't negotiate a new deal. Or suppose you want to take the sum of some number that is part of a geospatial query where you have to account for overlapping extents and you have a large number of extents? So that's why you say, it depends... there's always a fringe example that you can't always count on working as predicted.

On Aug 21, 6:05 am, Mark Townsend <markbtowns...@sbcglobal.net> wrote: > > ...what strange reason are you thinking of? > > My bet is he won't be able to tell you for NDA reasons. But he did it > years ago, and you are being naive. No, I just answered it. Of course you really don't want me to pick on Oracle's spatial stuff, do you.. ;-) Aren't your temp tables bad enough? The point is Mark, Serge, et al, is that if you read my response, you will see that I said almost always, and as OTC points out, unless there's something wrong with your server... There is always a caveat. And yeah, I'm under NDAs all the time, aren't you?

On Aug 21, 7:33 am, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > On Aug 21, 6:05 am, Mark Townsend <markbtowns...@sbcglobal.net> wrote: > > > > ...what strange reason are you thinking of? > > > My bet is he won't be able to tell you for NDA reasons. But he did it > > years ago, and you are being naive. > > No, > I just answered it. > > Of course you really don't want me to pick on Oracle's spatial stuff, > do you.. ;-) Aren't your temp tables bad enough? > We could talk about Oracle's 9i r-tree bug. Now most people wouldn't care that Oracle put a kludge in their r-tree indexing to handle a little nasty sync problem. (Its been fixed in 10g and so far works, which is why I can talk about it.) The alternative was using Q-Tree or quad tree indexes. I mean yeah, I could talk more about Oracle's spatial, and I'd even talk about IBM's spatial too, but what players in the Geospatial community are really using it? Not talking about ESRI which is a tools manufacurer but about Tele- Atlas, Nokia, Garmin, MapQuest, Google, Microsoft, car manufacturers, etc ... Yeah there's a lot I can talk about and there's a lot that I can't. But getting back on track... I just had to write a little script that if it was dealing with latin-1 characters, it would have been fine. But no, the user didn't tell me that there were unicode strings and that there was some bad data. Naw that would be too easy. So a simple script just went wrong. So yeah, I preface it with a "it depends". ;-)

Ian Michael Gumby wrote: > I mean yeah, I could talk more about Oracle's spatial, and I'd even > talk about IBM's spatial too, but what players in the Geospatial > community are really using it? No one I can think of other than every municipality tracking locations of streets, highways, telephone polls, cellular phone towers, traffic signals, cameras, pipelines, and other infrastructure items. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

Ian Michael Gumby wrote: > Of course you really don't want me to pick on Oracle's spatial stuff, > do you.. ;-) Aren't your temp tables bad enough? If you've got an issue with Oracle's global temporary tables why don't you say it. And while doing so demonstrate your command of the topic by posting DDL and DML that demonstrates your point. > And yeah, I'm under NDAs all the time, aren't you? I'm not buying what you're selling. If all you know is what is covered by an NDA you have remarkably limited experience. Some of us actually have computers at home. <g> -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

Huh? Can you name any major player in the geospatial community using DB2's spatial solution? Man, you jump in the middle of a conversation and you don't even get it. > Date: Thu, 21 Aug 2008 15:52:33 -0700> From: damorgan@psoug.org> Subject: Re: which is better? aggregate function or calculated in program?> To: informix-list@iiug.org> > Ian Michael Gumby wrote:> > > I mean yeah, I could talk more about Oracle's spatial, and I'd even> > talk about IBM's spatial too, but what players in the Geospatial> > community are really using it?> > No one I can think of other than every municipality tracking locations> of streets, highways, telephone polls, cellular phone towers, traffic> signals, cameras, pipelines, and other infrastructure items.> -- > Daniel A. Morgan> University of Washington> damorgan@x.washington.edu (replace x with u to respond)> _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Get ideas on sharing photos from people like you. Find new ways to share. http://www.windowslive.com/explore/photogallery/posts?ocid=TXT_TAGLM_WL_Photo_Gallery_082008

Daniel, We've been down this road before. In oracle, the global temporary tables aren't really temporary now are they? And you can't index them unless the table is empty since the definition is shared by everyone. You want a good example of temp tables, look at how they are implemented in IDS. > Date: Thu, 21 Aug 2008 16:01:04 -0700> From: damorgan@psoug.org> Subject: Re: which is better? aggregate function or calculated in program?> To: informix-list@iiug.org> > Ian Michael Gumby wrote:> > > Of course you really don't want me to pick on Oracle's spatial stuff,> > do you.. ;-) Aren't your temp tables bad enough?> > If you've got an issue with Oracle's global temporary tables why> don't you say it. And while doing so demonstrate your command of> the topic by posting DDL and DML that demonstrates your point.> > > And yeah, I'm under NDAs all the time, aren't you?> > I'm not buying what you're selling. If all you know is what is> covered by an NDA you have remarkably limited experience. Some> of us actually have computers at home. <g>> -- > Daniel A. Morgan> University of Washington> damorgan@x.washington.edu (replace x with u to respond)> _______________________________________________> Informix-list mailing list> Informix-list@iiug.org> http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ See what people are saying about Windows Live. Check out featured posts. http://www.windowslive.com/connect?ocid=TXT_TAGLM_WL_connect2_082008

Ian Michael Gumby wrote: > Huh? > > Can you name any major player in the geospatial community using DB2's > spatial solution? > > Man, you jump in the middle of a conversation and you don't even get it. I doubt Daniel meant to push DB2...interesting how many twists this innocent thread takes.. Anyway: http://tinyurl.com/5peh4l http://tinyurl.com/6nlsrd All depends on how you define "major player" I suppose. But perhaps we should get back on the topic on how a simple sum() can be better executed in the app than the server. IIRC Michael claimed that the sum may be operating on spatial data. Now I still do not see how the server could do such a function slower than than the tiem added by piping complex spatial data through DRDA to client, bind it out to whatever format is used in the client and then do that same sum there. Maybe after his NDA with Navteq expires Michael can tell us that elusive exception to the "group by pushdown" rewrite rule that every RDBMS I know of applies quite unconditionally... > > Ian Michael Gumby wrote: > > > I mean yeah, I could talk more about Oracle's spatial, and I'd even > > > talk about IBM's spatial too, but what players in the Geospatial > > > community are really using it? > > > > No one I can think of other than every municipality tracking locations > > of streets, highways, telephone polls, cellular phone towers, traffic > > signals, cameras, pipelines, and other infrastructure items. -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

On Aug 21, 7:38 pm, Serge Rielau <srie...@ca.ibm.com> wrote: Gee Serge, you shouldn't have gone there. For a band 10 manager with no reports, you should have some more discretion. Somehow I don't think Bob P is worrying about you taking his job anytime soon.... I guess the lack of customers jumping up and demanding DB2 for geospatial solutions is staggering. I mean, heck, where would ESRI be if not for all those DB2 customers? As to SUM() I gave you an example but you seemed to be dense. Try this one. Suppose I want to calculate the sum of a bunch of call options. I need to do a sum of a bunch of Black-Scholes results. Try doing that in a database. You're better off getting the info and doing the calculations on your workstation. Or if you've got a high end nvidia card, you could use cuda to do the calculations on your pc.. If you don't like Black- Scholes, you can substitute any other complex math function that may be part of the query. Note: The point is that we don't know the where clause so it could be that there is a complex equation that could kill performance. (Databases doing diff eqs?) Now if you had IDS and NAG, it may be a different story. I think you get the idea. There are some things you can't easily do in the database. As to geospatial stuff, yes, that too can cause problems. Geospatial queries are expensive. But then again you'd probably say that zip codes are geospatial data. (Actually had an IBMer say that in a presentation.) -G

Have you tried UPDATE STATISTICS ? -ID- Ian Michael Gumby wrote: > On Aug 21, 7:38 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > > > Gee Serge, you shouldn't have gone there. > > For a band 10 manager with no reports, you should have some more > discretion. Somehow I don't think Bob P is worrying about you taking > his job anytime soon.... > > I guess the lack of customers jumping up and demanding DB2 for > geospatial solutions is staggering. > > I mean, heck, where would ESRI be if not for all those DB2 customers? > > As to SUM() > I gave you an example but you seemed to be dense. > > Try this one. > Suppose I want to calculate the sum of a bunch of call options. > I need to do a sum of a bunch of Black-Scholes results. > Try doing that in a database. > You're better off getting the info and doing the calculations on your > workstation. Or if you've got a high end nvidia card, you could use > cuda to do the calculations on your pc.. If you don't like Black- > Scholes, you can substitute any other complex math function that may > be part of the query. > > Note: The point is that we don't know the where clause so it could be > that there is a complex equation that could kill performance. > (Databases doing diff eqs?) > > Now if you had IDS and NAG, it may be a different story. > > I think you get the idea. > > There are some things you can't easily do in the database. > > As to geospatial stuff, yes, that too can cause problems. Geospatial > queries are expensive. > But then again you'd probably say that zip codes are geospatial data. > (Actually had an IBMer say that in a presentation.) > > -G

Ian Michael Gumby wrote: > On Aug 21, 7:38 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > > > Gee Serge, you shouldn't have gone there. > > For a band 10 manager with no reports, you should have some more > discretion. Somehow I don't think Bob P is worrying about you taking > his job anytime soon.... > > I guess the lack of customers jumping up and demanding DB2 for > geospatial solutions is staggering. > > I mean, heck, where would ESRI be if not for all those DB2 customers? > > As to SUM() > I gave you an example but you seemed to be dense. > > Try this one. > Suppose I want to calculate the sum of a bunch of call options. > I need to do a sum of a bunch of Black-Scholes results. > Try doing that in a database. > You're better off getting the info and doing the calculations on your > workstation. Or if you've got a high end nvidia card, you could use > cuda to do the calculations on your pc.. If you don't like Black- > Scholes, you can substitute any other complex math function that may > be part of the query. > Yeah, but this was select sum and 4GL. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

On Aug 22, 3:03 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > Ian Michael Gumby wrote: [SNIP] > > Try this one. > > Suppose I want to calculate the sum of a bunch of call options. > > I need to do a sum of a bunch of Black-Scholes results. > > Try doing that in a database. > > You're better off getting the info and doing the calculations on your > > workstation. Or if you've got a high end nvidia card, you could use > > cuda to do the calculations on your pc.. If you don't like Black- > > Scholes, you can substitute any other complex math function that may > > be part of the query. > > Yeah, but this was select sum and 4GL. > > -- > Cheers, > Obnoxio the Clown > > http://obotheclown.blogspot.com Huh? You can't call c functions from within 4GL? That's strange! I could have sworn I did this many moons ago, besides writing esql/c programs. Darn, what is this world coming to? When you take a very powerful language like 4GL and then bastardize its ability to enhance it by being able to make C calls. :-P Sure, I wouldn't normally thing of 4GL, but it is possible and it is doable. Of course had Serge bothered to read my original post I did say the following: "Usually, almost always, it will be faster to do the sum in the database and then get the single value back. (The caveat is that there may be some strange reason why the sum() may take longer ...) " RIF (That's reading is fundamental) Its a wonder how IBM survives when their band 10 employees can't take time to read a thread when they decide to post. Maybe that's a band 10 where 10 is in binary? ;-) But hey! What do I know?

Ian Michael Gumby wrote: > RIF (That's reading is fundamental) Its a wonder how IBM survives when > their band 10 employees can't take time to read a thread when they > decide to post. Maybe that's a band 10 where 10 is in binary? ;-) > > Any one has comment on this? > SELECT SUM(col) from table > or > select col from table then calculate sum value in 4gl program.> > Comparing these by performance , memory usage,or else. > Which way is better? Reading is, indeed, fundamental. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

On Aug 22, 6:25 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > Ian Michael Gumby wrote: > > RIF (That's reading is fundamental) Its a wonder how IBM survives when > > their band 10 employees can't take time to read a thread when they > > decide to post. Maybe that's a band 10 where 10 is in binary? ;-) > > > Any one has comment on this? > > SELECT SUM(col) from table > > or > > select col from table then calculate sum value in 4gl program.> > > Comparing these by performance , memory usage,or else. > > Which way is better? > > Reading is, indeed, fundamental. > Yes it is. And the OP didn't provide a WHERE CLAUSE or a table join, so its reasonable to assume that he was talking about generalities and not a specific query. Of course how many databases do you have a table named 'table' ? ;-) Which is why I answered the way I did. And of course, I made the assumption that the reader groks that 4GL can call C. But hey! What do I know? I also assumed that the OP understood about networking and that he'd have to pull the data across the network and then do the summation. Again the assumption on my part is that the OP wasn't talking about running the 4GL program on the same machine as his server. -G PS. Note that the OP didn't make idiot comments after the initial post. Just some "expurts" from Oracle and IBM who really didn't bother to read what I posted or think about the problem... But then again, what can you expect from some of these people. Heck I remember attending a dog and pony show at IBM where the speaker claimed zip codes (that's postal codes to you non-americans) were spatial. (Hint: They are not.)

Ian Michael Gumby wrote: > On Aug 22, 6:25 am, Obnoxio The Clown <obno...@serendipita.com> wrote: > >> Ian Michael Gumby wrote: >> >>> RIF (That's reading is fundamental) Its a wonder how IBM survives when >>> their band 10 employees can't take time to read a thread when they >>> decide to post. Maybe that's a band 10 where 10 is in binary? ;-) >>> >>> Any one has comment on this? >>> SELECT SUM(col) from table >>> or >>> select col from table then calculate sum value in 4gl program.>>> >>> Comparing these by performance , memory usage,or else. >>> Which way is better? >>> >> Reading is, indeed, fundamental. >> >> > > Yes it is. > > And the OP didn't provide a WHERE CLAUSE or a table join, so its > reasonable to assume that he was talking about generalities and not a > specific query. > Of course how many databases do you have a table named 'table' ? ;-) > > Which is why I answered the way I did. > And of course, I made the assumption that the reader groks that 4GL > can call C. > > But hey! What do I know? > > I also assumed that the OP understood about networking and that he'd > have to pull the data across the network and then do the summation. > Again the assumption on my part is that the OP wasn't talking about > running the 4GL program on the same machine as his server. > > -G > > PS. Note that the OP didn't make idiot comments after the initial > post. Just some "expurts" from Oracle and IBM who really didn't bother > to read what I posted or think about the problem... But then again, > what can you expect from some of these people. Heck I remember > attending a dog and pony show at IBM where the speaker claimed zip > codes (that's postal codes to you non-americans) were spatial. (Hint: > They are not.) > > I just read this on the intermong: "Never argue with an idiot. He'll drag you down to his level and then beat you with experience." I don't know quite why I'm sharing this with everybody. -- Cheers, Obnoxio the Clown http://obotheclown.blogspot.com

Ian Michael Gumby wrote: > On Aug 21, 7:38 pm, Serge Rielau <srie...@ca.ibm.com> wrote: > > Gee Serge, you shouldn't have gone there. > > As to SUM() > I gave you an example but you seemed to be dense. Vulgar (adj.) a. Deficient in taste, delicacy, or refinement. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

DA Morgan said: > Ian Michael Gumby wrote: >> On Aug 21, 7:38 pm, Serge Rielau <srie...@ca.ibm.com> wrote: >> >> Gee Serge, you shouldn't have gone there. >> >> As to SUM() >> I gave you an example but you seemed to be dense. > > Vulgar (adj.) > a. Deficient in taste, delicacy, or refinement. pompous (adj.) 1. Affectedly grand, solemn or self-important. -- Bye now, Obnoxio http://obotheclown.blogspot.com/