On Sep 25, 10:34 pm, 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] > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
