Re: cursor performance question

In article <C1756349BCEAF2F2.B1982A41C08F74C0.8DFAC88CBDCBCA06@library- proxy.airnews.net>, STOPSPAM <danwright@bigfoot.com> writes >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. This should be the best option, just may sure any required indicies exist and that you have run an UPDATE STATISTICS afterwards. >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 will be slower since you open the cursor many times hence you a) engine validates table used the query still exist and have the required columns i.e. no=one has drop the table or columns in the table b) communicate with the engine (send open/close commands to the engine and receive status codes back). Hoever you must make sure indicies are still used ( use SET EXPLAIN ON). >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.] > >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! -- David Williams