5
\$\begingroup\$

This function code works very slowly. How can I speed it up?

CREATE OR REPLACE FUNCTION bill."ReportIngredients"(
    _from date,
    _to date,
    _beginning_date date,
    _has_inventory boolean,
    _inventory_id uuid,
    _restaurant_id uuid,
    _stock_id uuid,
    _ingredientIds uuid [],
    _sort_by character varying,
    _limit integer,
    _offset integer

  )
  RETURNS TABLE(
    json json
  ) AS
$BODY$
declare
  ingredientFilter character varying = '';
  ingredient_id uuid;
  ss_date date;
begin
  if ( _ingredientIds is not null ) then
    ingredientFilter = 'and i.id IN (';
    FOREACH ingredient_id in array _ingredientIds loop
      ingredientFilter := ingredientFilter || '''' ||  ingredient_id ||  ''',';
    end loop;
    Select trim(trailing ',' from ingredientFilter) into ingredientFilter;
    ingredientFilter := ingredientFilter || ') ';
  end if;

  if ( _has_inventory ) then
  return query execute
    'select array_to_json(array_agg(row_to_json(t)))
      From (
        Select i.id, i.title,
          (
            (
              SELECT coalesce(sum(ii.delta_count), 0)
              FROM inventory_ingredients ii
              Inner Join inventories inven On inven.id = ii.inventory_id
              WHERE ii.ingredient_id = i.id
                And inven.is_active = true
                And inven.stock_id = ''' || _stock_id || '''
                And inven.id = ''' || _inventory_id || '''
            ) + (
              SELECT coalesce(sum(ii.count), 0)
              FROM invoice_ingredients ii
              Inner Join invoices invo On invo.id = ii.invoice_id
              WHERE ii.is_active = true
                And ii.ingredient_id = i.id
                And invo.is_active = true
                And invo.restaurant_id = ''' || _restaurant_id || '''
                And invo.receiver_id = ''' || _stock_id || '''
                And invo.date >= ''' || _beginning_date || '''
                And invo.date < ''' || _from || '''
            ) + (
              SELECT coalesce(sum(ri.count), 0)
              FROM relocation_ingredients ri
              Inner Join relocations r On r.id = ri.relocation_id
              WHERE ri.ingredient_id = i.id
                And r.is_active = true
                And r.restaurant_id = ''' || _restaurant_id || '''
                And r.receiver_stock_id = ''' || _stock_id || '''
                And r.date >= ''' || _beginning_date || '''
                And r.date < ''' || _from || '''
            ) - (
              SELECT coalesce(sum(wi.count), 0)
              FROM write_off_ingredients wi
              Inner Join write_offs w On w.id = wi.write_off_id
              WHERE wi.ingredient_id = i.id
                And w.is_active = true
                And w.stock_id = ''' || _stock_id || '''
                And w.date >= ''' || _beginning_date || '''
                And w.date < ''' || _from || '''
            ) - (
              SELECT coalesce(sum(ri.count), 0)
              FROM relocation_ingredients ri
              Inner Join relocations r On r.id = ri.relocation_id
              WHERE ri.ingredient_id = i.id
                And r.is_active = true
                And r.restaurant_id = ''' || _restaurant_id || '''
                And r.sender_stock_id = ''' || _stock_id || '''
                And r.date >= ''' || _beginning_date || '''
                And r.date < ''' || _from || '''
            ) - (
                Select ((
                  SELECT coalesce(sum(bc.count), 0)
                  FROM bill_calculations bc
                  Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
                  Inner Join bill.bills b on b.id = bcs.bill_id
                  WHERE bc.ingredient_id = i.id
                    And bc.stock_id = ''' || _stock_id || '''
                    And bc.date >= ''' || _beginning_date || '''
                    And bc.date < ''' || _from || '''
                    And bc.calculate_type = ''subtract''
                    And b.bill_type <> 5
                )  - (
                  SELECT coalesce(sum(bc.count), 0)
                  FROM bill_calculations bc
                  Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
                  Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
                  Inner Join bill.bills b on b.id = bcs.bill_id
                  WHERE bc.ingredient_id = i.id
                    And bc.stock_id = ''' || _stock_id || '''
                    And bc.date >= ''' || _beginning_date || '''
                    And bc.date < ''' || _from || '''
                    And bc.calculate_type = ''add''
                    And b.bill_type <> 5
                )) AS sum
            )
          ) AS start_count,
          (
            SELECT coalesce(sum(ii.count), 0)
            FROM invoice_ingredients ii
            Inner Join invoices invo On invo.id = ii.invoice_id
            WHERE ii.is_active = true
              And ii.ingredient_id = i.id
              And invo.is_active = true
              And invo.restaurant_id = ''' || _restaurant_id || '''
              And invo.receiver_id = ''' || _stock_id || '''
              And invo.date >= ''' || _from || '''
              And invo.date <= ''' || _to || '''
          ) AS invoice_count,
          (
            SELECT coalesce(sum(ri.count), 0)
            FROM relocation_ingredients ri
            Inner Join relocations r On r.id = ri.relocation_id
            WHERE ri.ingredient_id = i.id
              And r.is_active = true
              And r.restaurant_id = ''' || _restaurant_id || '''
              And r.receiver_stock_id = ''' || _stock_id || '''
              And r.date >= ''' || _from || '''
              And r.date <= ''' || _to || '''
          ) AS relocation_in_count,

          (
            SELECT coalesce(sum(wi.count), 0)
            FROM write_off_ingredients wi
            Inner Join write_offs w On w.id = wi.write_off_id
            WHERE wi.ingredient_id = i.id
              And w.is_active = true
              And w.stock_id = ''' || _stock_id || '''
              And w.date >= ''' || _from || '''
              And w.date <= ''' || _to || '''
          ) AS write_off_count,
          (
            SELECT coalesce(sum(ri.count), 0)
            FROM relocation_ingredients ri
            Inner Join relocations r On r.id = ri.relocation_id
            WHERE ri.ingredient_id = i.id
              And r.is_active = true
              And r.restaurant_id = ''' || _restaurant_id || '''
              And r.sender_stock_id = ''' || _stock_id || '''
              And r.date >= ''' || _from || '''
              And r.date <= ''' || _to || '''
          ) AS relocation_out_count,
          (
              Select ((
                SELECT coalesce(sum(bc.count), 0)
                FROM bill_calculations bc
                Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
                Inner Join bill.bills b on b.id = bcs.bill_id
                WHERE bc.ingredient_id = i.id
                  And bc.stock_id = ''' || _stock_id || '''
                  And bc.date >= ''' || _from || '''
                  And bc.date <= ''' || _to || '''
                  And bc.calculate_type = ''subtract''
                  And b.bill_type <> 5
              )  - (
                SELECT coalesce(sum(bc.count), 0)
                FROM bill_calculations bc
                Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
                Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
                Inner Join bill.bills b on b.id = bcs.bill_id
                WHERE bc.ingredient_id = i.id
                  And bc.stock_id = ''' || _stock_id || '''
                  And bc.date >= ''' || _from || '''
                  And bc.date <= ''' || _to || '''
                  And bc.calculate_type = ''add''
                  And b.bill_type <> 5
              )) AS sum
          ) AS solds_count,
          (
            SELECT coalesce(sum(bc.count), 0)
            FROM bill_calculations bc
            Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
            Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
            Inner Join bill.bills b on b.id = bcs.bill_id
            WHERE bc.ingredient_id = i.id
              And bc.stock_id = ''' || _stock_id || '''
              And bc.date >= ''' || _from || '''
              And bc.date <= ''' || _to || '''
              And bc.calculate_type = ''add''
              And b.bill_type <> 5
          ) AS resign_count
        From ingredients i
        Where i.is_active = true
          And i.restaurant_id = ''' || _restaurant_id || '''
          ' || ingredientFilter || '
        Group by i.id
        order by ' || _sort_by || '
        limit ' || _limit || '
        offset ' || _offset || '
    ) t';
  else
    return query execute
      'select array_to_json(array_agg(row_to_json(t)))
        From (
          Select i.id, i.title,
            (
              (
                SELECT coalesce(sum(ii.count), 0)
                FROM invoice_ingredients ii
                Inner Join invoices invo On invo.id = ii.invoice_id
                WHERE ii.is_active = true
                  And ii.ingredient_id = i.id
                  And invo.is_active = true
                  And invo.restaurant_id = ''' || _restaurant_id || '''
                  And invo.receiver_id = ''' || _stock_id || '''
                  And invo.date >= ''' || _beginning_date || '''
                  And invo.date < ''' || _from || '''
              ) + (
                SELECT coalesce(sum(ri.count), 0)
                FROM relocation_ingredients ri
                Inner Join relocations r On r.id = ri.relocation_id
                WHERE ri.ingredient_id = i.id
                  And r.is_active = true
                  And r.restaurant_id = ''' || _restaurant_id || '''
                  And r.receiver_stock_id = ''' || _stock_id || '''
                  And r.date >= ''' || _beginning_date || '''
                  And r.date < ''' || _from || '''
              ) - (
                SELECT coalesce(sum(wi.count), 0)
                FROM write_off_ingredients wi
                Inner Join write_offs w On w.id = wi.write_off_id
                WHERE wi.ingredient_id = i.id
                  And w.is_active = true
                  And w.stock_id = ''' || _stock_id || '''
                  And w.date >= ''' || _beginning_date || '''
                  And w.date < ''' || _from || '''
              ) - (
                SELECT coalesce(sum(ri.count), 0)
                FROM relocation_ingredients ri
                Inner Join relocations r On r.id = ri.relocation_id
                WHERE ri.ingredient_id = i.id
                  And r.is_active = true
                  And r.restaurant_id = ''' || _restaurant_id || '''
                  And r.sender_stock_id = ''' || _stock_id || '''
                  And r.date >= ''' || _beginning_date || '''
                  And r.date < ''' || _from || '''
              ) - (
                  Select ((
                    SELECT coalesce(sum(bc.count), 0)
                    FROM bill_calculations bc
                    Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
                    Inner Join bill.bills b on b.id = bcs.bill_id
                    WHERE bc.ingredient_id = i.id
                      And bc.stock_id = ''' || _stock_id || '''
                      And bc.date >= ''' || _beginning_date || '''
                      And bc.date < ''' || _from || '''
                      And bc.calculate_type = ''subtract''
                      And b.bill_type <> 5
                  )  - (
                    SELECT coalesce(sum(bc.count), 0)
                    FROM bill_calculations bc
                    Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
                    Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
                    Inner Join bill.bills b on b.id = bcs.bill_id
                    WHERE bc.ingredient_id = i.id
                      And bc.stock_id = ''' || _stock_id || '''
                      And bc.date >= ''' || _beginning_date || '''
                      And bc.date < ''' || _from || '''
                      And bc.calculate_type = ''add''
                      And b.bill_type <> 5
                  )) AS sum
              )
            ) AS start_count,
            (
              SELECT coalesce(sum(ii.count), 0)
              FROM invoice_ingredients ii
              Inner Join invoices invo On invo.id = ii.invoice_id
              WHERE ii.is_active = true
                And ii.ingredient_id = i.id
                And invo.is_active = true
                And invo.restaurant_id = ''' || _restaurant_id || '''
                And invo.receiver_id = ''' || _stock_id || '''
                And invo.date >= ''' || _from || '''
                And invo.date <= ''' || _to || '''
            ) AS invoice_count,
            (
              SELECT coalesce(sum(ri.count), 0)
              FROM relocation_ingredients ri
              Inner Join relocations r On r.id = ri.relocation_id
              WHERE ri.ingredient_id = i.id
                And r.is_active = true
                And r.restaurant_id = ''' || _restaurant_id || '''
                And r.receiver_stock_id = ''' || _stock_id || '''
                And r.date >= ''' || _from || '''
                And r.date <= ''' || _to || '''
            ) AS relocation_in_count,
            (
              SELECT coalesce(sum(wi.count), 0)
              FROM write_off_ingredients wi
              Inner Join write_offs w On w.id = wi.write_off_id
              WHERE wi.ingredient_id = i.id
                And w.is_active = true
                And w.stock_id = ''' || _stock_id || '''
                And w.date >= ''' || _from || '''
                And w.date <= ''' || _to || '''
            ) AS write_off_count,
            (
              SELECT coalesce(sum(ri.count), 0)
              FROM relocation_ingredients ri
              Inner Join relocations r On r.id = ri.relocation_id
              WHERE ri.ingredient_id = i.id
                And r.is_active = true
                And r.restaurant_id = ''' || _restaurant_id || '''
                And r.sender_stock_id = ''' || _stock_id || '''
                And r.date >= ''' || _from || '''
                And r.date <= ''' || _to || '''
            ) AS relocation_out_count,
            (
                Select ((
                  SELECT coalesce(sum(bc.count), 0)
                  FROM bill_calculations bc
                  Inner Join bill.bill_course_solds bcs on bcs.id = bc.object_id
                  Inner Join bill.bills b on b.id = bcs.bill_id
                  WHERE bc.ingredient_id = i.id
                    And bc.stock_id = ''' || _stock_id || '''
                    And bc.date >= ''' || _from || '''
                    And bc.date <= ''' || _to || '''
                    And bc.calculate_type = ''subtract''
                    And b.bill_type <> 5
                )  - (
                  SELECT coalesce(sum(bc.count), 0)
                  FROM bill_calculations bc
                  Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
                  Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
                  Inner Join bill.bills b on b.id = bcs.bill_id
                  WHERE bc.ingredient_id = i.id
                    And bc.stock_id = ''' || _stock_id || '''
                    And bc.date >= ''' || _from || '''
                    And bc.date <= ''' || _to || '''
                    And bc.calculate_type = ''add''
                    And b.bill_type <> 5
                )) AS sum
            ) AS solds_count,
            (
              SELECT coalesce(sum(bc.count), 0)
              FROM bill_calculations bc
              Inner Join bill.bill_course_resigns bcr on bcr.id = bc.object_id
              Inner Join bill.bill_course_solds bcs on bcs.id = bcr.bill_course_sold_id
              Inner Join bill.bills b on b.id = bcs.bill_id
              WHERE bc.ingredient_id = i.id
                And bc.stock_id = ''' || _stock_id || '''
                And bc.date >= ''' || _from || '''
                And bc.date <= ''' || _to || '''
                And bc.calculate_type = ''add''
                And b.bill_type <> 5
            ) AS resign_count
          From ingredients i
          Where i.is_active = true
            And i.restaurant_id = ''' || _restaurant_id || '''
            ' || ingredientFilter || '
          Group by i.id
          order by ' || _sort_by || '
          limit ' || _limit || '
          offset ' || _offset || '
      ) t';
    end if;
end;

$BODY$
  LANGUAGE plpgsql STABLE
  COST 50
  ROWS 1000;
ALTER FUNCTION bill."ReportIngredients"(date, date, date, boolean, uuid, uuid, uuid, uuid[], character varying, integer, integer)
  OWNER TO developer;

EXPLAIN ANALYZE result

"Result (cost=0.00..5.13 rows=1000 width=0) (actual time=38859.253..38859.254 rows=1 loops=1)" "Total runtime: 38859.296 ms"

\$\endgroup\$
  • \$\begingroup\$ you have a lot of Nested Select Statements with multiple where statements, is there any way to reduce the Nested select statements? like Temp Tables, Table Variables, joins, etc? \$\endgroup\$ – Malachi Nov 15 '13 at 18:27
  • 1
    \$\begingroup\$ Ditto @Malachi; Temp tables or views may improve performance significantly, instead of nested SELECT statements. 39K ms seems excessive. \$\endgroup\$ – Phrancis May 21 '14 at 5:21
6
\$\begingroup\$

I will give you kudos on being very explicit in your programming. Here are my thoughts:

  1. Not performance related but still a factor, I would suggest that you remain consistent with your casing of key words. Sure SQL is not case-sensitive, but it makes the code easier to read especially in my opinion if SQL key words are all caps.

  2. Again likely not performance related, but I notice you reuse table aliases in multiple subqueries for different tables (e.g., ii forinventory_ingredients and invoice_ingredients both) this is not good practice as if you omitted a single parentheses you could get an unpredictable result set or an error from SQL that your table reference is ambiguous. These can be tricky to debug especially in a large script like this.

  3. Nested SELECT subqueries, avoid if possible especially for long-running scripts, as it can lock those pages/tables for other users throughout execution. I suggested breaking those into common table expressions or temporary tables (if infrequent execution) or views (if frequent). Not completely familiar with the PostgreSQL syntax for those but same principles apply to all SQL. Note, this would also make your function query much simpler to read while you do your arithmetic calculations.

  4. You can try to change the COST 50 to a different value. See section labeled execution_cost in this section of the manual for more details on how it works.

  5. You use this type of operator throughout: inven.stock_id = ''' || _stock_id || '''. This is ugly, why do you concatenate empty values instead of just inven.stock_id LIKE '%_stock_id%'? SQL engine might interpret this weirdly and may optimize better if you use the LIKE operator.

  6. Using comments within your script would help the next programmer understand your code better as to what it does in what order, etc.

I can't think of anything else but others are welcome to add/edit to this if warranted.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.