mardi 5 mai 2015

rails add ordered, index or full to speed up query

So, my rails code generates a huge join query which starts as following:

SELECT "CREDITRSP"."ML_TRAN_ID"       AS t0_r0,
...
...
...

I noticed that this takes about 5 minutes when i run this in sql developer.

The following alternates were tried which returned results within 3-10 secs:

 1.SELECT /*+ FULL("CREDITRSP") */ "CREDITRSP"."ML_TRAN_ID" AS t0_r0,
    ...
    ...


2. SELECT /*+ INDEX("CREDITRSP" IDX2_CREDITRSP) */ "CREDITRSP"."ML_TRAN_ID" AS t0_r0, 
...
...



3. SELECT /*+ ORDERED */ "CREDITRSP"."ML_TRAN_ID" AS t0_r0, 
...
...

can either of the above 3 options be implemented in rails?

NOTE: I do not have control to modify, update, table schema migration. This application has only read-only access to those tables.

this is part of the existing model code.

class Search

  def search_by_date_range
    get_transactions(FILTER_TO_SEARCH[self.filter])
  end

  FILTER_TO_SEARCH = {
    Filter::AllTran => {
      :filter => "all_tran.between_dates(self.start_date, self.end_date)",
      :models => [
        "credit_responses"
      ]
    }
  }
end

I am using rails 3 and 'activerecord-oracle_enhanced-adapter', '1.4.2' gem.

Aucun commentaire:

Enregistrer un commentaire