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.
>================================================
