Streamline DB: 128341ms to below DB:200

Rails
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

On Sep 25, 10:34pm, chinsz songyu [email protected] wrote:

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]
uoms,
AND prs.id = pos.pr_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 J.

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