Re: cursor performance question

STOPSPAM <danwright@bigfoot.com> wrote: :The answer may very well be "it depends" on the actual data, but I am :wondering which aproach to adding a table to a new table to a select :will be better. :Brief description of enhancement: :We need to modify a cursor to join with an additional table. There is a :one-to-many relationship between the original cursor (a 2 table join) :and the new table we need info from (I have no idea how many :"many-to-one" will actually be with our data, but I suspect we are :talking about increasing the number of rows returned by a factor of 10 :if we were to simply add the new table to the existing cursor....The :original cursor returns between 200 and 1000 rows (I think, not entirely :sure). :The 2 options I see are: :1: Just add the 3rd table to the "from clause" and add code to process :it within the existing loop. :OR :2: Add an inner loop (with a new cursor) and leave the existing cursor :alone. : :Option 1 is easier to implement, but then again, it's not a big deal to :add an inner loop, so that's not very significant. But, the kicker is :that the original cursor may now return 1000 rows instead of 100 (I'm :pulling these numbers out of the air, so if you believe it matters what :the actual numbers will be, please let me know...there are about 20 :fields selected in the existing cursor). :Option 2 means that we will have to open a new cursor for every :iteration of the existing cursor, but we will not have the overhead of :selecting the same data from the existing cursor over and over again. This is an in memory transfer only so it shouldn't matter that much compared to reading data from the disk. We did these kinds of inner loops for several reasons many years ago. On the newer engines we have allways found that anything you can leave to the engine is faster than doing it yourself in the program. Option 2 also adds a lot of overhead in the engine for opening the cursor so many times. :Obviously either option is going to decrease performance, but the :question is which one will affect performance the least. For empirical :reasons, let's assume that either way, we take full advantage of any :existing (or ones we may add) indexes. : :Interesting trade-off, no? :This happens to be an area where we really care about performance [My :gut instinct tells me we should abandon 4GL and write it in C, but then :again, we would have the same question since we are accessing the :database in either case.] Your gut instinct is wrong. Check your reasoning so your fealings (gut) eventualy conforms to reality. Unless you do an inordinate amount of prosessing in your code the reading from disk will allways swamp any such prosessing. The overhead of 4GL (at least the compiled version) to C is insignificant. The one and only time I have been able to even see any time component from processing data with 4GL was in a report with a very large number of "group sum... where..." statements. :Short of coding both possibilities and running benchmarks on them, does :anyone have any ideas????? : : :Thanks in advance, :Danny Wright :#include <StdDisclaimers.h> : :And remember Objective-C is more object oriented than C++, but still C :is a helluva lot better than 4GL! You may want to check your premises here as to 4GL compared to C. Object oriented languages are better again of course with the exception of C++ Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org