Troy: I just had to share my results. I finally got around to re-doing my program along the lines that you suggested and the results were phenomenal. If I ran my original program in stand alone mode it ran in about 3 minutes. If it ran at the end of all my other programs in the wee hours of the morning it was taking upwards of 40 minutes. I re-did it using the index and count function and it now runs in 40 SECONDS. I did a double take when I looked at the results. I wish I understood why there is such a difference in the results (using 2 cursors vs. 1 cursor) but whatever the reason I like the results.
Thanks for your suggestion. Mike Ramsour -----Original Message----- From: Troy Sosamon [mailto:[EMAIL PROTECTED] Sent: Friday, March 12, 2004 10:46 AM To: [EMAIL PROTECTED] Subject: [RBASE-L] - Re: Updating with WHERE CURRENT of cursor Mike, I assume you have an index on csr. I don't like using cursors unless absolutely necessary. I would try getting rid of the second cursor and do your update using the index and the count function. Something like this: Set var vSec_loop int = 1 Label top_loop if vSec_loop <= 4 then SET VAR vcmd TEXT = ('UPDATE csr_step_rdct SET ' + + .vmill_col + ' = .vcs_cst_cntr, ' + + .voper_col + ' = .vcs_opertype, ' + + .vgage_col + ' = .vcs_gauge WHERE csr = .vcsr and step = .vSec_loop') Set var Vsec_loop = (.vsec_loop + 1) Goto top_loop -- I also don't like using while loops Troy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ramsour Mike Sent: Friday, March 12, 2004 8:29 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Updating with WHERE CURRENT of cursor That's how I have been doing it but I was trying to find some way to make it run faster. It really bugs me that if this particular program is run in sequence with others it takes so much longer to run than if it is run by itself. The difference is 4 minutes vs. 35 minutes. I just don't understand what would make the difference. I have tried to be very careful about having each program "clean up after itself" as far as dropping any tables and views it created and clearing any variables. This one just has me stumped. I have also tried setting it up to run after different points in my program list. It makes no difference. Thanks for reviewing this Bill. Mike -----Original Message----- From: Bill Downall [mailto:[EMAIL PROTECTED] Sent: Friday, March 12, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: [RBASE-L] - Re: Updating with WHERE CURRENT of cursor Mike, Can you just use a primary key value with a traditional WHERE clause in your UPDATE command, rathen than using the CURRENT OF syntax? Bill At 10:03 AM 3/12/2004, you wrote: >Good morning: > >I have an application that has two cursors defined. The first cursor is the >primary cursor and the second cursor is based on the first cursor. I am >trying to design the application so that as the second cursor loops through >from 1 to 5 rows it updates appropriate columns for the first cursor table >using a "WHERE CURRENT OF cursor" approach -- the update will happen from 1 >to 5 times based on the results of the second cursor. The problem I am >encountering is that after the inside (second) cursor loops more than one >time my "UPDATE table WHERE CURRENT OF cursor" throws an error message >saying "The cursor is not positioned in a valid row". I have not fetched >the first cursor to the next row at the point in time that this error occurs >so I was assuming that "WHERE CURRENT OF cursor" should continue to point to >the same row as the second cursor does its looping. > >Here's my code >-- >-- This is the first or primary cursor. The important value is CSR which is >used in >-- the second cursor. Each row in the csr_step_rdct table has a unique CSR >value. >-- >DECLARE vcsr_cursor CURSOR FOR SELECT csr,oper1,mill1,gauge1,oper2,mill2, + >gauge2,oper3,mill3,gauge3,oper4,mill4,gauge4,oper5,mill5,gauge5 + >FROM csr_step_rdct WHERE mill1 IS NULL >-- >-- This is the second cursor. Which is based on the CSR column from the >-- the first cursor. It will have from 1 to 5 rows per CSR. >-- >DECLARE vcsr_rdct_step CURSOR FOR SELECT csr,step,cst_cntr,opertype,gauge + >FROM rdct_data WHERE csr = .vcsr ORDER BY csr,step >-- >-- This opens the first cursor and fetches the values >-- >OPEN vcsr_cursor >-- >FETCH vcsr_cursor INTO vcsr INDICATOR vind, + >voper1 INDICATOR vind, vmill1 INDICATOR vind, vgauge1 INDICATOR vind, + >voper2 INDICATOR vind, vmill2 INDICATOR vind, vgauge2 INDICATOR vind, + >voper3 INDICATOR vind, vmill3 INDICATOR vind, vgauge3 INDICATOR vind, + >voper4 INDICATOR vind, vmill4 INDICATOR vind, vgauge4 INDICATOR vind, + >voper5 INDICATOR vind, vmill5 INDICATOR vind, vgauge5 INDICATOR vind >-- >SET VAR verrtrap = .verrcode >-- >WHILE verrtrap = 0 THEN > -- > SET VAR vcntr INTEGER = 1 > -- > -- This opens the second cursor for processing and fetches it into >variables > -- > OPEN vcsr_rdct_step > -- > FETCH vcsr_rdct_step INTO vcs_csr, vcs_step, vcs_cst_cntr, vcs_opertype, + > vcs_gauge > -- > SET VAR verrtrap = .verrcode > -- > WHILE verrtrap = 0 THEN > -- > -- The next few lines set variables to use macro substitution for column >names > -- > SET VAR vmill_col TEXT = ('MILL' + (CTXT(.vcntr))) > -- > SET VAR vgage_col TEXT = ('GAUGE' + (CTXT(.vcntr))) > -- > SET VAR voper_col TEXT = ('OPER' + (CTXT(.vcntr))) > -- > -- This creates an update command variable and then executes it with the >&vcmd > -- > SET VAR vcmd TEXT = ('UPDATE csr_step_rdct SET ' + + > .vmill_col + ' = .vcs_cst_cntr, ' + + > .voper_col + ' = .vcs_opertype, ' + + > .vgage_col + ' = .vcs_gauge WHERE CURRENT OF vcsr_cursor') > -- > &vcmd > -- > SET VAR vcntr INTEGER = (.vcntr + 1) > -- > FETCH vcsr_rdct_step INTO vcs_csr, vcs_step, vcs_cst_cntr, vcs_opertype, >+ > vcs_gauge > -- > SET VAR verrtrap = .verrcode > -- > ENDWHILE -- it's after this loop processes more than one time that I get >an error > -- > CLOSE vcsr_rdct_step > -- > CLS > -- > FETCH vcsr_cursor INTO vcsr > -- > SET VAR verrtrap = .verrcode > -- >ENDWHILE >-- >DROP CURSOR vcsr_rdct_step >-- >DROP CURSOR vcsr_cursor >-- >RETURN > >Any thoughts, insights or help will be appreciated. My ultimate goal here >is to optimize this program. If this program is run in sequence with >several others it takes over a half an hour to run but if I run it by itself >it only takes less than 10 minutes. I'm trying to take a different approach >to see if I can speed it up when it's run in sequence with other programs. > >Thanks > >Mike Ramsour >AK Steel > >740-829-4340 > >--- RBASE-L >================================================ >TO POST A MESSAGE TO ALL MEMBERS: >Send a plain text email to [EMAIL PROTECTED] > >(Don't use any of these words as your Subject: >INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, >REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message SUBJECT, put just one word: INTRO >================================================ >TO UNSUBSCRIBE: >Send a plain text email to [EMAIL PROTECTED] >In the message SUBJECT, put just one word: UNSUBSCRIBE >================================================ >TO SEARCH ARCHIVES: >Send a plain text email to [EMAIL PROTECTED] >In the message SUBJECT, put just one word: SEARCH-n >(where n is the number of days). In the message body, >place any >text to search for. >================================================
