streamline DB: 128341ms to below DB:200

rubyonrails-talk
1

Hello everyone,

My case will be I assume I have a big table that contains several records about 300 lines of records in 1 excel table. I encountered the time it took for retrieving the records is too long about 128341ms which stated in my shell prompt in my linux ubuntu 10.10 terminal.

Well... my question is, anyway or anyhow to streamline the retrival time so that the "DB " stated below can below 200, like "DB: 190" somewhere there? And, I provide further info for you,

the terminal show as follows:-

Sending data order_receipt_report-26-09-2011.csv Completed in 128341ms (View: 4, DB: 127022) | 200 OK [http://127.0.0.1/ report_masters/order_receipt_report?]

Plus, hereby is my sql script to retrieve the records:-

select * from            (                select supplier_masters.descr AS Supplier_Name,                       pos.po_no AS Order_Number,                       pos.created_at AS Order_Date,                       -- '' AS 'Item_#',                       item_masters.code AS Item_Code,                       po_lines.descr AS Item_Description,                       po_lines.supplier_part_no AS Part_No,                       prs.pr_no AS Requisition_No,                       prs.subject AS PR_Title,                       grns.grn_no AS GRN_No,                       grns.updater_id AS Received_By,                       grns.date_received AS Received_On,                       grn_lines.amt_accepted AS Accepted_Qty,                       grn_lines.amt_rejected AS Rejected_Qty,

(item_masters.latest_uprice*grn_lines.amt_accepted) AS Accepted_Amt,

(item_masters.latest_uprice*grn_lines.amt_rejected) AS Rejected_Amt,                       grn_lines.open_qty AS Open_Qty,                       '' AS Open_Amt,                       po_lines.qty AS PO_Original_Qty,                       po_lines.local_total AS PO_Original_Amount,                       item_masters.latest_uprice AS Unit_Price,                       currencies.code AS Currency,                       grns.do_no AS DO_No,                       uoms.code AS UOM,                       companies.descr AS Company,                       cost_centers.code AS Cost_Center,                       grn_lines.serial_no AS Asset_Serial_Number,                       grn_lines.tag_no AS Asset_Tag_Number,                       grn_lines.location AS Asset_Location,                       grn_lines.part_no AS Asset_Part_No,                       '' AS ERP_Receipt_No,                       pos.company_id AS company_id,                       pos.id AS po_id,                       po_lines.id AS po_line_id,                       pos.cost_center_id AS cost_center_id,                       pos.erp_po_no AS ERP_PO_No,                       grns.reject_reason AS Comments,                       grns.closed_order AS Closed_Order,                       --statuses.code AS Status                       grns.status_id AS Status                     from                       grns,                       grn_lines,                       pos,                       --company_item_suppliers,                       po_lines,                       prs,                       companies,                       cost_centers,                       currencies,                       uoms,                       statuses,                       --ad_hoc_suppliers,                       supplier_masters,                       item_masters,                       (select * from users) preparers,                       (select * from users) requesters                     where                       grns.id = grn_lines.grn_id                       --AND grns.id = 10742                       AND pos.id = grns.po_id                       AND prs.id = pos.pr_id                       AND pos.company_id = companies.id                       AND pos.cost_center_id = cost_centers.id                       AND po_lines.po_id = pos.id                       AND po_lines.currency_id = currencies.id                       AND po_lines.uom_id = uoms.id                       AND grns.status_id = statuses.id                       --AND po_lines.supplier_master_id = ad_hoc_suppliers.id                       AND po_lines.supplier_master_id = supplier_masters.id                       AND po_lines.item_master_id = item_masters.id                       AND grn_lines.po_line_id = po_lines.id                       AND po_lines.is_adhoc = 0                       AND po_lines.ad_hoc_supplier_id is null                       --AND grns.created_at = '01-Nov-10'

              UNION                select supplier_masters.descr AS Supplier_Name,                      pos.po_no AS Order_Number,                      pos.created_at AS Order_Date,                      -- '' AS 'Item_#',                      item_masters.code AS Item_Code,                      po_lines.descr AS Item_Description,                      po_lines.supplier_part_no AS Part_No,                      prs.pr_no AS Requisition_No,                      prs.subject AS PR_Title,                      grns.grn_no AS GRN_No,                      grns.updater_id AS Received_By,                      grns.date_received AS Received_On,                      grn_lines.amt_accepted AS Accepted_Qty,                      grn_lines.amt_rejected AS Rejected_Qty,

(item_masters.latest_uprice*grn_lines.amt_accepted) AS Accepted_Amt,

(item_masters.latest_uprice*grn_lines.amt_rejected) AS Rejected_Amt,                      grn_lines.open_qty AS Open_Qty,                      '' AS Open_Amt,                      po_lines.qty AS PO_Original_Qty,                      po_lines.local_total AS PO_Original_Amount,                      item_masters.latest_uprice AS Unit_Price,                      currencies.code AS Currency,                      grns.do_no AS DO_No,                      uoms.code AS UOM,                      companies.descr AS Company,                      cost_centers.code AS Cost_Center,                      grn_lines.serial_no AS Asset_Serial_Number,                      grn_lines.tag_no AS Asset_Tag_Number,                      grn_lines.location AS Asset_Location,                      grn_lines.part_no AS Asset_Part_No,                      '' AS ERP_Receipt_No,                      pos.company_id AS company_id,                      pos.id AS po_id,                      po_lines.id AS po_line_id,                      pos.cost_center_id AS cost_center_id,                      pos.erp_po_no AS ERP_PO_No,                      grns.reject_reason AS Comments,                      grns.closed_order AS Closed_Order,                      --statuses.code AS Status                      grns.status_id AS Status                    from                      grns,                      grn_lines,                      pos,                      (select * from users) preparers,                      (select * from users) requesters,                      --company_item_suppliers,                      po_lines,                      prs,                      companies,                      cost_centers,                      currencies,                      uoms,                      statuses,                      --ad_hoc_suppliers,                      supplier_masters,                      item_masters                    where                      grns.id = grn_lines.grn_id                      --AND grns.id = 10742                      AND pos.id = grns.po_id                      AND prs.id = pos.pr_id                      AND pos.company_id = companies.id                      AND pos.cost_center_id = cost_centers.id                      AND po_lines.po_id = pos.id                      AND po_lines.currency_id = currencies.id                      AND po_lines.uom_id = uoms.id                      AND grns.status_id = statuses.id                      --AND po_lines.supplier_master_id = ad_hoc_suppliers.id                      AND po_lines.supplier_master_id = supplier_masters.id                      AND po_lines.item_master_id = item_masters.id                      AND grn_lines.po_line_id = po_lines.id                      AND po_lines.is_adhoc = 0                      AND po_lines.ad_hoc_supplier_id is null                      --AND grns.created_at = '01-Nov-10'

              UNION                select supplier_masters.descr AS Supplier_Name,                      pos.po_no AS Order_Number,                      pos.created_at AS Order_Date,                      -- '' AS 'Item_#',                      item_masters.code AS Item_Code,                      po_lines.descr AS Item_Description,                      po_lines.supplier_part_no AS Part_No,                      prs.pr_no AS Requisition_No,                      prs.subject AS PR_Title,                      grns.grn_no AS GRN_No,                      grns.updater_id AS Received_By,                      grns.date_received AS Received_On,                      grn_lines.amt_accepted AS Accepted_Qty,                      grn_lines.amt_rejected AS Rejected_Qty,

(item_masters.latest_uprice*grn_lines.amt_accepted) AS Accepted_Amt,

(item_masters.latest_uprice*grn_lines.amt_rejected) AS Rejected_Amt,                      grn_lines.open_qty AS Open_Qty,                      '' AS Open_Amt,                      po_lines.qty AS PO_Original_Qty,                      po_lines.local_total AS PO_Original_Amount,                      item_masters.latest_uprice AS Unit_Price,                      currencies.code AS Currency,                      grns.do_no AS DO_No,                      uoms.code AS UOM,                      companies.descr AS Company,                      cost_centers.code AS Cost_Center,                      grn_lines.serial_no AS Asset_Serial_Number,                      grn_lines.tag_no AS Asset_Tag_Number,                      grn_lines.location AS Asset_Location,                      grn_lines.part_no AS Asset_Part_No,                      '' AS ERP_Receipt_No,                      pos.company_id AS company_id,                      pos.id AS po_id,                      po_lines.id AS po_line_id,                      pos.cost_center_id AS cost_center_id,                      pos.erp_po_no AS ERP_PO_No,                      grns.reject_reason AS Comments,                      grns.closed_order AS Closed_Order,                      --statuses.code AS Status                      grns.status_id AS Status                    from                      grns,                      grn_lines,                      pos,                      po_lines,                      prs,                      (select * from users) preparers,                      (select * from users) requesters,                      companies,                      cost_centers,                      currencies,                      uoms,                      statuses,                      ad_hoc_suppliers                      supplier_masters,                      item_masters                    where                      grns.id = grn_lines.grn_id                      --AND ad_hoc_suppliers.id = 10040                      AND pos.id = grns.po_id                      AND prs.id = pos.pr_id                      AND pos.company_id = companies.id                      AND pos.cost_center_id = cost_centers.id                      AND po_lines.po_id = pos.id                      AND po_lines.currency_id = currencies.id                      AND po_lines.uom_id = uoms.id                      AND grns.status_id = statuses.id                      --AND po_lines.supplier_master_id = supplier_masters.id                      --AND po_lines.ad_hoc_supplier_id = ad_hoc_suppliers.id                      AND po_lines.ad_hoc_supplier_id = supplier_masters.id                      AND po_lines.item_master_id = item_masters.id                      AND grn_lines.po_line_id = po_lines.id                      AND po_lines.is_adhoc = 1                      AND po_lines.ad_hoc_supplier_id is not null                      --AND grns.created_at = '01-Nov-10'

           )            order by Status

is it recommended to create views??? I am a total newbie to oracle so please excuse my naivety, hope to get your reply soon...

thanks

2

Hello everyone,

My case will be I assume I have a big table that contains several records about 300 lines of records in 1 excel table. I encountered the time it took for retrieving the records is too long about 128341ms which stated in my shell prompt in my linux ubuntu 10.10 terminal.

Well... my question is, anyway or anyhow to streamline the retrival time so that the "DB " stated below can below 200, like "DB: 190" somewhere there? And, I provide further info for you,

That's quite a tall order - the query below is certainly inefficient, but even then I'd consider it unlikely that it could be sped up by 1000x...

the terminal show as follows:-

Sending data order_receipt_report-26-09-2011.csv Completed in 128341ms (View: 4, DB: 127022) | 200 OK [http://127.0.0.1/ report_masters/order_receipt_report?]

Plus, hereby is my sql script to retrieve the records:-

[snip]

                from
                  grns,
                  grn\_lines,
                  pos,
                  \-\-company\_item\_suppliers,
                  po\_lines,
                  prs,
                  companies,
                  cost\_centers,
                  currencies,
                  uoms,
                  statuses,
                  \-\-ad\_hoc\_suppliers,
                  supplier\_masters,
                  item\_masters,
                  \(select \* from users\) preparers,
                  \(select \* from users\) requesters
                where
                  grns\.id = grn\_lines\.grn\_id
                  \-\-AND grns\.id = 10742
                  AND pos\.id = grns\.po\_id
                  AND prs\.id = pos\.pr\_id
                  AND pos\.company\_id = companies\.id
                  AND pos\.cost\_center\_id = cost\_centers\.id
                  AND po\_lines\.po\_id = pos\.id
                  AND po\_lines\.currency\_id = currencies\.id
                  AND po\_lines\.uom\_id = uoms\.id
                  AND grns\.status\_id = statuses\.id
                  \-\-AND po\_lines\.supplier\_master\_id =

ad_hoc_suppliers.id AND po_lines.supplier_master_id = supplier_masters.id AND po_lines.item_master_id = item_masters.id AND grn_lines.po_line_id = po_lines.id AND po_lines.is_adhoc = 0 AND po_lines.ad_hoc_supplier_id is null --AND grns.created_at = '01-Nov-10'

Several thoughts on this:

- explicit joins may help the query optimizer figure out what you mean

- at very least, stop including subselects that you don't even USE; 'preparers' and 'requesters' shouldn't need subselects AND they aren't used at all. Unless your users table is very small, this creates a MASSIVE number of rows to select against. The query optimizer may figure out these aren't used, but why include them at all?

- verify that the database has the required indexes. You'll want to use Oracle's 'EXPLAIN PLAN' functionality to see what's actually going on:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231

--Matt Jones

3

In addition to Matt's suggestions, I'd suggest you learn how to formulate a where clause that includes AND and OR clauses. The UNION appears to be completely unnecessary and is tripling what appears to be a completely inefficient query to start with.

UNION does have it's uses, but querying the same fields from the same tables with relatively simple conditions isn't one of them.

Jim