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
