Ruby on Rails

On Sep 25, 10:34 pm, chinsz songyu <uahs...@gmail.com> 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@googlegroups.com.
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.