Hi,

I thought with CTE I can speed up my procedure, but in reality it is much slower.

Can anybody tell me the facts why this happens. 

/* Case 1: Call DetailDataForSinglePRO for each PRO
     Because the are thousends of PRO and there is a big amount of detail-data this takes some time */
  for
    select PRO
      from PRO
     where PRO.PR = :PR
      into :PRO
  do begin

    select Amount
      from DetailDataForSinglePRO (:PRO)
      into :Amount

    suspend;
  end

  /* Case 2: Call DetailDataForAllPRO once in the with-clause */

  /*  I thought that this should be faster then "case 1" because
      DetailDataForAllPRO brings a dataset with data
      for all PRO and is called only once (I thought).
      In reality this statement is much slower because it seems that
      DetailDataForAllPRO is called for each PRO in the mainquery.

     Could anybody enlight me on that.
       - Is it "normal" that DetailDataForAllPRO from the with-clause is called so often?
       - Why does this happen?
  */

  for
    with ProCosts as (
      select PRO, sum(Amount) as Amount
        from DetailDataForAllPRO (:PR)
       group by PRO)

    select PRO.PRO, :Amount
      from PRO
           left outer join ProCosts on PRO.PRO = ProCosts.PRO   /* not each PRO has Detaildata */
     where PRO.PR = :PR
      into :PRO, :Amount
  do
    suspend;