stored procedure optimisation...

["Anton Melser" <melser.anton@xxxxxxxxx>]

Hi,
I need to analyse some html to get some links out, and with only 25
lines in exports_tmp_links (and text_to_parse no more than around
10KB) this function has taken 10 minutes and counting. Something
horribly wrong is going on here! Can someone give me any pointers?
Cheers
Anton

delete from tmp_links_all;
analyze exports_tmp_links;

FOR tempRow IN SELECT * FROM exports_tmp_links LOOP
	startString := tempRow.text_to_parse;
	LOOP
		
		linkString := substring(startString, '(linkadministration.*=[0-9]+)');
		IF linkString is null THEN
			EXIT;
		END IF;
		
		newlinkid := substring(linkString,'([0-9]+)');
		INSERT INTO tmp_links_all
(link_id,content_section,item_id,item_name,location_of_link)
		values (newlinkid,tempRow.content_section,tempRow.item_id,
tempRow.item_name, tempRow.location_of_link);
		
		startString := substring(startString from position(newlinkid in startString));

	END LOOP;
END LOOP;

analyze tmp_links_all;