🚀 go-pugleaf

Isaac wrote:

> What should I do to speed up a stored procedure , it contains many
> select with sum function and also 2 updates

If someone asked you to improve the performance of code you have never
seen could you do it?

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> If someone asked you to improve the performance of code you have never
> seen could you do it?

LOL!! sorry but I was asking for head lines I mean what in general I 
should do, and I did not want to trouble you.

I use 3 triggers (after insert , after update and after delete) to 
execute a procedure

Trigger for after update :
   execute procedure PROC_AVGCOST_QTYBAL new.item_code, new.inv_code,
     old.item_code, old.inv_code, 'update';


procedure:
================================= Start

if (:p_operation = 'update') then begin
     if ((:p_new_inv_code <> :p_old_inv_code) OR (:p_new_item_code <> 
:p_old_item_code)) then BEGIN

 
/*---------------------------Purchase--------------------------------------*/
       select sum(QTY), sum(QTY * COST_PRICE) from purchase_inv_item
         left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
         where (purchase_inv.purch_return <> 1)
           and (PURCHASE_INV_ITEM.item_code = :p_old_item_code)
           and (PURCHASE_INV_ITEM.inv_code = :p_old_inv_code)
           into :pur_sum_qty, :pur_sum_cost;

       select sum(QTY) from purchase_inv_item
         left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
         where (purchase_inv.purch_return = 1)
           and (PURCHASE_INV_ITEM.item_code = :p_old_item_code)
           and (PURCHASE_INV_ITEM.inv_code = :p_old_inv_code)
           into :pur_ret_sum_qty;

 
/*---------------------------SALES--------------------------------------*/

       select sum(qty) from invoice_item
         left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
         where (invoice.sales_return <> 1)
           and (invoice_item.item_code = :p_old_item_code)
           and (invoice_item.inv_code = :p_old_inv_code)
           into :sale_sum_qty;

       select sum(qty) from invoice_item
         left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
         where (invoice.sales_return = 1)
           and (invoice_item.item_code = :p_old_item_code)
           and (invoice_item.inv_code = :p_old_inv_code)
           into :sale_ret_sum_qty;

       /*--------- >>>>>> Daily Sales */
       select sum(qty) from daily_sales
         where (daily_sales.SALES_RETURN <> 1)
           and (daily_sales.item_code = :p_old_item_code)
           and (daily_sales.inv_code = :p_old_inv_code)
           into :daily_sale_sum_qty;

       select sum(qty) from daily_sales
         where (daily_sales.SALES_RETURN = 1)
           and (daily_sales.item_code = :p_old_item_code)
           and (daily_sales.inv_code = :p_old_inv_code)
           into :daily_sale_ret_sum_qty;

       /*-------------------Update inventory------------------------*/
       select ob_qty from inventory_items
         where (inventory_items.item_code = :p_old_item_code)
         and (inventory_items.inv_code = :p_old_inv_code) into :ob_bal_qty;

       if (ob_bal_qty is null) then ob_bal_qty = 0;
       if (pur_sum_qty is null) then pur_sum_qty = 0;
       if (pur_ret_sum_qty is null) then pur_ret_sum_qty = 0;
       if (sale_ret_sum_qty is null) then sale_ret_sum_qty = 0;
       if (sale_sum_qty is null) then sale_sum_qty = 0;
       if (daily_sale_sum_qty is null) then daily_sale_sum_qty = 0;
       if (daily_sale_ret_sum_qty is null) then daily_sale_ret_sum_qty = 0;

       cur_bal_qty = :ob_bal_qty + :pur_sum_qty + :sale_ret_sum_qty
         - :pur_ret_sum_qty - :sale_sum_qty - daily_sale_sum_qty + 
daily_sale_ret_sum_qty;


       if (cur_bal_qty < 0) then exception EXCP_BAL_IS_UNDER_ZERO;

       update inventory_items set
         UNIT_TOTAL_QTY = (inventory_items.OB_QTY + :pur_sum_qty),
         UNIT_TOTAL_COST = :pur_sum_cost, cur_qty = :cur_bal_qty
         where (inventory_items.item_code = :p_old_item_code)
           and (inventory_items.inv_code = :p_old_inv_code);

     END /* new <> old */
   END /*if ((:p_operation = 'update') then begin*/
   /*  ----------------------- NEW ITEM --------------------------*/
   /* --------------- purchase for new record ---------------------*/
   select sum(QTY), sum(QTY * COST_PRICE) from purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return <> 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_sum_qty, :pur_sum_cost;

   select sum(QTY) from purchase_inv_item
     left outer join purchase_inv on (purchase_inv_item.pur_code = 
purchase_inv.pur_code)
     where (purchase_inv.purch_return = 1)
       and (PURCHASE_INV_ITEM.item_code = :p_new_item_code)
       and (PURCHASE_INV_ITEM.inv_code = :p_new_inv_code)
       into :pur_ret_sum_qty;
 
/*----------------------------------------------------------------------------*/
   /* ************** sales for item current qty ***********************/
   select sum(qty) from invoice_item
     left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
     where (invoice.sales_return <> 1)
       and (invoice_item.item_code = :p_new_item_code)
       and (invoice_item.inv_code = :p_new_inv_code)
       into :sale_sum_qty;

   select sum(qty) from invoice_item
     left outer join invoice on (invoice_item.invoice_code = 
invoice.invoice_code)
     where (invoice.sales_return = 1)
       and (invoice_item.item_code = :p_new_item_code)
       and (invoice_item.inv_code = :p_new_inv_code)
       into :sale_ret_sum_qty;

   /*--------- >>>>>> Daily Sales */
   select sum(qty) from daily_sales
     where (daily_sales.SALES_RETURN <> 1)
       and (daily_sales.item_code = :p_old_item_code)
       and (daily_sales.inv_code = :p_old_inv_code)
       into :daily_sale_sum_qty;

   select sum(qty) from daily_sales
     where (daily_sales.SALES_RETURN = 1)
       and (daily_sales.item_code = :p_old_item_code)
       and (daily_sales.inv_code = :p_old_inv_code)
       into :daily_sale_ret_sum_qty;

   /*--------------Inventory update------------------------*/
   select OB_QTY from inventory_items
     where (inventory_items.item_code = :p_new_item_code)
     and (inventory_items.inv_code = :p_new_inv_code)
     into :ob_bal_qty;

   if (ob_bal_qty is null) then ob_bal_qty = 0;
   if (pur_sum_qty is null) then pur_sum_qty = 0;
   if (pur_ret_sum_qty is null) then pur_ret_sum_qty = 0;
   if (sale_ret_sum_qty is null) then sale_ret_sum_qty = 0;
   if (sale_sum_qty is null) then sale_sum_qty = 0;
   if (daily_sale_sum_qty is null) then daily_sale_sum_qty = 0;
   if (daily_sale_ret_sum_qty is null) then daily_sale_ret_sum_qty = 0;

   cur_bal_qty = :ob_bal_qty + :pur_sum_qty + :sale_ret_sum_qty
     - :pur_ret_sum_qty - :sale_sum_qty - daily_sale_sum_qty + 
daily_sale_ret_sum_qty;

   if (cur_bal_qty < 0) then exception EXCP_BAL_IS_UNDER_ZERO;

   update inventory_items set
     UNIT_TOTAL_QTY = (inventory_items.OB_QTY + :pur_sum_qty),
     UNIT_TOTAL_COST = :pur_sum_cost,
     CUR_QTY = :cur_bal_qty
   where (inventory_items.item_code = :p_new_item_code)
     and (inventory_items.inv_code = :p_new_inv_code);

end

================================== End
Thanks

Since the procedure is a long series of SELECTs the only way to improve
performance is to improve the performance of the individual SELECT
statements. Download a copy of InterBase Planalyzer (free). Read the
help file then analyze the performance of each SELECT and see if you
can improve it using the information in the help file and the
information provided by IB Planalyzer.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> Since the procedure is a long series of SELECTs the only way to improve
> performance is to improve the performance of the individual SELECT
> statements. Download a copy of InterBase Planalyzer (free). Read the
> help file then analyze the performance of each SELECT and see if you
> can improve it using the information in the help file and the
> information provided by IB Planalyzer.
> 

very informative thanks alot

about SET STATISTICS how often I should use it ?
is here a way to make it done automatically in interBase ?

Thanks

Isaac wrote:

> about SET STATISTICS how often I should use it ?
> is here a way to make it done automatically in interBase ?

Only when you do something that dramatically changes the selectivity of
the index, that is, when you do something that changes the ratio of the
number of unique values to the total number of rows.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
  > Only when you do something that dramatically changes the selectivity of
> the index, that is, when you do something that changes the ratio of the
> number of unique values to the total number of rows.
> 
ok Thanks a lot