Thread: stored procedure optimisation...

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;

Anton Melser wrote:
> 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?

I bet that you are running into an endless loop there.

Try adding a few RAISE statements to find out what is happening,
something like

RAISE NOTICE 'I am here, and myvar = % and thatvar = %', myvar, thatvar;

Yours,
Laurenz Albe

> RAISE NOTICE 'I am here, and myvar = % and thatvar = %', myvar, thatvar;

Thanks... it is indeed a gem that little instruction!!!
Cheers
Anton