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
