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

More Resources

Keep up to date with Rails on Twitter and This Week in Rails

Policies: Conduct, License, Maintenance, Security, Trademarks