Thanks Jim for the insight on the use of a SCROLL cursor. If I read this carefully, if the target table is going to be processed one row at a time and the ability to move forward or backward (NEXT/PRIOR) then a simple cursor should be declared for very large conversions. This should reduce the amount of overhead in the processing. What is interesting is that in the processing of these 120,000 rows of data, if I use an update command to record the conversion status (CONV = Y/N) then I run into a virtual memory problem about 75% into the conversion (12-15 hrs). What I have been doing is to create a conversion status table and use an insert command with the key of the target conversion record with the status. It runs more effeciently than the update and I do not have a problem with the virtual memory. What this leads me to believe is that an update used in the manipulation of a cursor does not clear all of the memory used and eventually "gags" itself. Phil
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of James (Jim) Bentley Sent: Sunday, January 20, 2002 8:03 AM To: [EMAIL PROTECTED] Subject: RE: table generation memory problem Phil, To the best of my knowledge you only loose the ability to dynamically position the cursor. Check out the definiton for "FETCH" at www.rsyntax.com. You can't use "NEXT", "PRIOR", "FIRST", "LAST", "REL n", "ABS n" clauses which can only be used when you have defined a scrolling cursor. Esentially with a non-scrolling cursor you access rows defined in the CURSOR definition only in the order defined. You are reading the data from top to bottom. Most of the time we don't need to dynamically positon the cursor using any of the previous clauses. With scrolling or non-scrolling cursors you can update the row you have fetch by using the following clause in an UPDATE command: "WHERE CURRENT OF cursor Specifies a cursor that refers to a specific row to be affected by the UPDATE command. With this option, you must specify tblview." Checkout the first option in the UPDATE syntax diagram. You mentioned that this application was a conversion. My experience has been that a lot of times when we convert an application from a prior version (especially a much older version) we tend to minimize changes to working applications. Thus we don't take advantages of enhancements to existing commands that the new version offers. -- Jim Bentley American Celiac Society [EMAIL PROTECTED] - email (973) 776-3900 x5029 - voicemail/fax ---- "Phil Nolette" <[EMAIL PROTECTED]> wrote: > Jim, > I am very interested in this post. I just finished a very > large conversion > and had used a Scroll Cursor in my cursor statement. When > I was processing > every fetched record (over 100,000), I wanted to update > a flag column in the > table after I did the insert. Everything went well except > that after > processing about 75,000 of these records, my system reported > vitual memory > was low. It eventually locked up. I removed the update > statement and it > processed all of them. > My question then becomes, if I remove the scroll statement > in the Declare > Cursor what do I lose? I have used it only as a matter > of policy and > apparently do not understand the power of it or the cost > of it. > > Phil > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of James (Jim) Bentley > Sent: Friday, January 18, 2002 7:40 AM > To: [EMAIL PROTECTED] > Subject: Re: table generation memory problem > > > David, > > Try declaring your cursor without the "SCROLL" keyword, > i.e. > DECLARE c1 CURSOR FOR SELECT catalog_id,artist,title FROM > + catalog > > You are not using any of the positioning phrases in you > fetch > command so there is no need for the "SCROLL" keyword. > In > my experience there is a performance hit when you use the > "SCROLL" Keyword. > -- > Jim Bentley > American Celiac Society > [EMAIL PROTECTED] - email > (973) 776-3900 x5029 - voicemail/fax > > > > ---- David Torre <[EMAIL PROTECTED]> wrote: > > Hello, > > > > Thanks for helping with my primary key problem. I was > > able to start work > > on a routine that generates the keyword table i will > use > > for searching. I > > don't see anything wrong with the table itself, but the > > following routine > > eats up memory at an exponential rate. Anybody know > of > > some methods I can > > rewrite some of this code to make it so it's not holding > > on to memory? > > > > > > --Routine to generate keywords table based on all artists > > in the catalog. > > --David Torre > > --10-16-2001 > > --last update 11-1-2001 > > > > DISCONNECT CDSTORE > > CONNECT CDSTORE > > SET FEEDBACK ON > > SET ECHO OFF > > SET MESS OFF > > SET ERR MESS OFF > > > > WRI 'Now creating artist_keywords > > table ' AT 1 1 > > > > DROP TABLE artistkeys_ln NOCHECK > > DROP TABLE artist_keywords NOCHECK > > > > SET FASTFK ON > > CREATE TABLE artist_keywords+ > > (+ > > artistkeys_id INTEGER NOT NULL UNIQUE,+ > > keyword TEXT 50 NOT NULL UNIQUE,+ > > PRIMARY KEY (artistkeys_id) > > ) > > > > CREATE TABLE artistkeys_ln+ > > (+ > > catalog_id INTEGER,+ > > artistkeys_id INTEGER,+ > > FOREIGN INDEX (catalog_id) REFERENCES catalog (catalog_id),+ > > FOREIGN INDEX (artistkeys_id) REFERENCES artist_keywords > > (artistkeys_id)+ > > ) > > > > SET V vartistkeys_count INTEGER=1 > > SET V vartistkeys_id INTEGER=NULL > > SET V vkeywordgrab TEXT=NULL > > SET V vartist TEXT > > SET V vtitle TEXT > > SET V vkeywords TEXT > > SET V vsingle_keyword TEXT > > COMPUTE vcatalog_total AS ROWS FROM catalog > > SET V vcatalog_total_txt TEXT=(' '+(CTXT(.vcatalog_total))+' > > items') > > > > DROP CURSOR c1 > > DECLARE c1 SCROLL CURSOR FOR SELECT catalog_id,artist,title > > FROM catalog > > OPEN c1 > > > > LABEL GetRowsFromCatlg > > > > FETCH c1 INTO vcatalog_id INDICATOR vind1, vartist > INDICATOR > > vind2,+ > > vtitle INDICATOR vind3 > > > > IF SQLCODE=100 THEN --If there's no more rows in > the > > catalog > > GOTO CleanUp --Stop Adding Rows to the artist_keywords > > table > > ENDIF > > > > --Feedback > > SET V vcatalog_id_txt TEXT=('Status: '+(CTXT(.vcatalog_id))+' > > of') > > > > WRI .vcatalog_id_txt AT 2 1 > > WRI .vcatalog_total_txt AT 3 1 > > > > > > --make a string that contains all the keywords for > this > > item > > SET V vkeywords TEXT=(ULC(SRPL((.vartist),' ',',',0))) > > > > --Grab each keyword one by one and shove it in the > artist_keywords > > table > > LABEL AddKeywordsToTable > > > > IF vkeywords IS NULL THEN > > SET V vsingle_keyword=NULL > > GOTO GetRowsFromCatlg > > ENDIF > > > > --extract a keyword from the keywords string > > SET V vsingle_keyword TEXT=(SSUB(.vkeywords,1)) > > > > --now remove what was just extracted from the keywords > > string > > SET V vkeywords_len=(SLEN(.vkeywords)) > > SET V vkeywords=(LJS((SRPL(.vkeywords,(.vsingle_keyword),' > > ',1)),+ > > .vkeywords_len)) > > > > SET V vfirst_char=(SGET(.vkeywords,1,1)) > > SET V vkeywords_len=(SLEN(.vkeywords)) > > SET V vsingle_keywd_len=(SLEN(.vsingle_keyword)) > > SET V vkeywords_isalpha=(ISALPHA(.vkeywords)) > > SET V vkeywords_isdigit=(ISDIGIT(.vkeywords)) > > > > IF vfirst_char=',' THEN > > SET v vkeywords=(SGET(.vkeywords,(SLEN(.vkeywords)),2)) > > ENDIF > > > > --When the last word is stripped from the string, > I'm > > always left with a > > --single character that looks like a space but isn't > > one. The only way > > --I could remove it was to check if the string was > one > > character and the > > --character wasn't a letter or number. That is what > > the following > > --statement does. > > IF ((vkeywords_len=1) AND (vkeywords_isalpha=0 OR > vkeywords_isdigit=0))+ > > THEN > > SET V vkeywords=NULL > > ENDIF > > > > --Take care of zero length characters (these end up > > in artist_keywords table > > --when there are duplicate keywords in the artist > and/or > > title) > > IF (vsingle_keywd_len=0) THEN > > SET V vsingle_keyword=NULL > > ENDIF > > > > IF vsingle_keyword IS NOT NULL THEN > > > > SET V vkeywordgrab=keyword from artist_keywords where > > > > keyword=.vsingle_keyword > > SET V vartistkeys_id=artistkeys_id from artist_keywords > > where > > keyword=.vsingle_keyword > > > > IF vkeywordgrab IS NULL THEN --keyword doesn't appear > > elsewhere in the > > keywords table > > > > INSERT INTO artist_keywords (artistkeys_id,keyword)+ > > VALUES (.vartistkeys_count,.vsingle_keyword) > > > > INSERT INTO artistkeys_ln (catalog_id,artistkeys_id)+ > > VALUES (.vcatalog_id,.vartistkeys_count) > > > > vartistkeys_count=vartistkeys_count+1 > > ENDIF > > > > IF vkeywordgrab IS NOT NULL THEN --keyword does appear > > in the keywords > > table > > > > INSERT INTO artistkeys_ln (catalog_id,artistkeys_id)+ > > VALUES (.vcatalog_id,.vartistkeys_id) > > ENDIF > > > > SET V vartistkeys_id=NULL > > SET V vkeywordgrab=NULL > > ENDIF > > > > GOTO AddKeywordsToTable > > > > GOTO GetRowsFromCatlg > > > > > > LABEL CleanUp > > WRI 'Finished creating artist_keywords > > table ' AT 1 1 > > DROP CURSOR c1 > > CLEAR VARIABLES vartist,vtitle,vkeywords,vsingle_keyword,vcatalog_total,+ > > vcatalog_total_txt,vcatalog_id,vind1,vind2,vind3,vcatalog_id_txt,+ > > vkeywords_len,vfirst_char,vsingle_keywd_len,vkeywords_isalpha,+ > > vkeywords_isdigit,vartistkeys_count,vartistkeys_id,vind4,vind5,+ > > vkeywordgrab > > > > RETURN > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE > rbase-l > > > > __________________________________________________ > FREE voicemail, email, and fax...all in one place. > Sign Up Now! http://www.onebox.com > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > __________________________________________________ FREE voicemail, email, and fax...all in one place. Sign Up Now! http://www.onebox.com ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l
