Results 1 to 1 of 1
  1. #1
    Join Date
    May 2014

    Unanswered: Looping through query inside function running very slow


    I am new to postgre sql and i am trying to generate html return from a postgresql function. With small set of records about 1000 its working fine, but when it increased upto 20000 records it performs very slow and does not return any result even after 30 minutes. Please check the following code and please let me know where I am doing wrong. I have also attached a text file with complete defination in a attachment.

    qry :='select v.verifier as verifier_id,v.verifiercode,zon,upper(oprname) as oprname,sum(case when oprsendon < '''||frmdt||' 00:00:00'' and (doneon is null or doneon > '''||frmdt||' 00:00:00'') then 1 else 0 end) as backlog,sum(case when oprsendon >= '''||frmdt||' 00:00:00'' and (oprsendon <= '''||frmdt||' 15:00:00'' and oprsendon <= '''||frmdt||' '||totime||''') then 1 else 0 end) as am,sum(case when oprsendon >= '''||frmdt||' 15:00:01'' and oprsendon <= '''||frmdt||' '||totime||''' then 1 else 0 end) as pm,sum(case when oprsendon < '''||frmdt||' 00:00:00'' and doneon is not null and doneon > '''||frmdt||' 00:00:00'' and doneon <= '''||frmdt||' '||totime||''' then 1 else 0 end) as bksol, sum(case when oprsendon >= '''||frmdt||' 00:00:00'' and oprsendon < '''||frmdt||' 15:00:00'' and doneon > '''||frmdt||' 00:00:00'' and doneon <= '''||frmdt||' '||totime||''' then 1 else 0 end) as amsol,sum(case when oprsendon >= '''||frmdt||' 15:00:01'' and doneon > '''||frmdt||' 15:00:01'' and doneon <= '''||frmdt||' '||totime||''' then 1 else 0 end) as pmsol,sum(case when oprsendon < '''||frmdt||' 00:00:00'' and (doneon is null or doneon > '''||frmdt||' '||totime||''') then 1 else 0 end) as bkpen, sum(case when oprsendon >= '''||frmdt||' 00:00:00'' and oprsendon < '''||frmdt||' 15:00:00'' and (doneon is null or doneon > '''||frmdt||' '||totime||''') then 1 else 0 end) as ampen,sum(case when oprsendon >= '''||frmdt||' 15:00:01'' and (doneon is null or doneon > '''||frmdt||' '||totime||''') then 1 else 0 end) as pmpen from visit_mis v where branch_id='||branchid||' group by verifier,verifiercode,zon,oprname order by zon';
    FOR row_data IN execute qry LOOP
    diffhour = 0;
    diffday = 0;
    bksolperc = 0.00;
    display = '';
    select max(doneon) from visit_mis where verifier=row_data.verifier_id and doneon > (frmdt||' 00:00:00')::timestamp without time zone and doneon < (frmdt||' '||totime)::timestamp without time zone into lastvisit;
    IF lastvisit is not null THEN
    END IF;
    IF row_data.bksol !=0 and row_data.backlog !=0 THEN
    bksolperc = ((row_data.bksol*100)/row_data.backlog)::float;
    END IF;
    IF ( <= 0 THEN
    display = 'style="display:none"';
    END IF;
    retval =retval||'<tr '||display||' ><td align="left" class="detailbox" >'||totrec||'</td>';
    retval =retval||'<td class="detailbox" align="center" >'||(case when coalesce(row_data.verifiercode,'NI')='NAL' then '<font color=red><b>NAL</b></font>' else coalesce(row_data.verifiercode,'NI') end)||'</td>';
    retval =retval||'<td align="left" class="detailbox" >'||coalesce(upper(row_data.oprname),'NI')||'</td>';
    retval =retval||'<td align="left" class="groupcols" >'||coalesce(row_data.zon,'NI')||'</td>';
    retval =retval||'<td class="detailbox" align="center" bgcolor="#fdf9eb" '||(case when row_data.backlog > 30 then 'style="font-weight:bold;colorurple;background-color:yellow"' else '' end)||' id="numbk" >'||row_data.backlog||'</td>';
    retval =retval||'<td class="detailbox" align="center" bgcolor="#fdf9eb" id="numam" >'||||'</td>';
    retval =retval||'<td class="groupcols" align="center" bgcolor="#fdf9eb" id="numpm" >'||||'</td>';
    retval =retval||'<td class="detailbox" align="center" bgcolor="#ecfdeb" id="numsbk" >'||row_data.bksol||'</td>';
    retval =retval||'<td class="detailbox" align="center" bgcolor="#ecfdeb" id="numsam" >'||row_data.amsol||'</td>';
    retval =retval||'<td class="groupcols" align="center" bgcolor="#ecfdeb" id="numspm" >'||row_data.pmsol||'</td>';
    retval =retval||'<td class="detailbox" align="center" bgcolor="#ebf2fd" id="numpbk" >'||row_data.bkpen||'</td>';
    retval =retval||'<td class="detailbox" align="center" bgcolor="#ebf2fd" id="numpam" >'||row_data.ampen||'</td>';
    retval =retval||'<td class="groupcols" align="center" bgcolor="#ebf2fd" id="numppm" >'||row_data.pmpen||'</td>';
    retval =retval||'<td class="detailbox" align="center" '||(case when bksolperc >= 100 then 'bgcolor="#bade8f"' else '' end)||'>'||bksolperc||'</td>';
    retval =retval||'<td class="detailbox" align="center" >'||(case when row_data.amsol !=0 and !=0 then ((row_data.amsol*100)/ else 0 end)||'</td>';
    retval =retval||'<td class="groupcols" align="center" >'||(case when row_data.pmsol !=0 and !=0 then ((row_data.pmsol*100)/ else 0 end)||'</td>';
    retval =retval||'<td class="detailbox" align="center" '||(case when (diffhour > 0 and diffhour < 13 and diffday < 1) then 'bgcolor="#fd4801"' else '' end)||'>'||coalesce(lastvisit::text,'')||'</td></tr>';

    IF totrec=0 THEN
    retval =retval||'</table><hr><div style="font-size:13pt;font-weight:bold;color:#A3A8A8;font-style:italic;text-align:center">No Records Found</div>';
    retval =retval||'<tr><td align="left" colspan="4" class="groupcolssub" bgcolor="#9cbcaf"></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totbk" bgcolor="#ffd143"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totam" bgcolor="#ffd143"><b>0</b></td>';
    retval =retval||'<td class="groupcolssub" align="center" id="totpm" bgcolor="#ffd143"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totsbk" bgcolor="#bedf6f"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totsam" bgcolor="#bedf6f"><b>0</b></td>';
    retval =retval||'<td class="groupcolssub" align="center" id="totspm" bgcolor="#bedf6f"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totpbk" bgcolor="#a1c5fe"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totpam" bgcolor="#a1c5fe"><b>0</b></td>';
    retval =retval||'<td class="groupcolssub" align="center" id="totppm" bgcolor="#a1c5fe"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totprbk" bgcolor="#dfdfdf"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" id="totpram" bgcolor="#dfdfdf"><b>0</b></td>';
    retval =retval||'<td class="groupcolssub" align="center" id="totprpm" bgcolor="#dfdfdf"><b>0</b></td>';
    retval =retval||'<td class="subtotalbox" align="center" bgcolor="#9cbcaf"></td></tr>';
    retval =retval||'</table>';
    END IF;
    RETURN retval;
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts