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
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On Sep 25, 10:34 pm, chinsz songyu <uahs...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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]> 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.