Forum: Ruby on Rails Oracle and Rails seems really slow.... In development

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
60e38de043848f82392062088f191213?d=identicon&s=25 Hogan, Brian P. (Guest)
on 2006-01-11 20:28
(Received via mailing list)
I'm just looking into how to connect to Oracle using Rails.  I've got
everything connecting and working as it should.

I have a Users table in Oracle:

  create_table "users", :force => true do |t|
    t.column "username", :string
    t.column "created_on", :datetime
    t.column "email", :string
    t.column "note", :text
  end

And everything works fine, but in development, it has to run this query
for each view

select column_name, data_type, data_default, nullable,
 decode(data_type, 'NUMBER', data_precision,
 'VARCHAR2', data_length,
 null) as length,
 decode(data_type, 'NUMBER', data_scale, null) as scale
 from user_catalog cat, user_synonyms syn, all_tab_columns col
 where cat.table_name = 'USERS'
 and syn.synonym_name (+)= cat.table_name
 and col.table_name = nvl(syn.table_name, cat.table_name)
 and col.owner = nvl(syn.table_owner, user)

I am assuming that this is because development mode can't cache the
models... in production mode it seems to take about 8 seconds the first
time but then it's really fast.

To test, I've placed this into SQLPlus and it takes about 6 seconds to
execute there as well.

Is this something that others are experiencing? I'm seeing 6-7 seconds
delay between requests because of this query running on all of my view
pages. Is there something that can be done or is the price of doing
business with Oracle? :)


Brian Hogan
Web Development
Learning & Technology Services
Schofield 3-B
University of Wisconsin-Eau Claire
715 836 3585
hoganbp@uwec.edu
25e11a00a89683f7e01e425a1a6e305c?d=identicon&s=25 Wilson Bilkovich (Guest)
on 2006-01-13 20:49
(Received via mailing list)
On 1/11/06, Hogan, Brian P. <HOGANBP@uwec.edu> wrote:
>     t.column "email", :string
>  decode(data_type, 'NUMBER', data_scale, null) as scale
> To test, I've placed this into SQLPlus and it takes about 6 seconds to
> execute there as well.
>
> Is this something that others are experiencing? I'm seeing 6-7 seconds delay
> between requests because of this query running on all of my view pages. Is
> there something that can be done or is the price of doing business with
> Oracle? :)
>

The default query is very, very slow, partly because it has to account
for all the different possible ways you might have configured your
tables.
In my case, I just make a user synonym on the user Rails is logging in
as, in development.
Then, in my development environment.rb, include something like this,
which overrides the query.

module ActiveRecord
  module ConnectionAdapters
    class OCIAdapter < AbstractAdapter
      def columns(table_name, name = nil)
          table_cols = <<-SQL_CODE
            select column_name, data_type, data_default, nullable,
              decode(data_type, 'NUMBER', data_precision, 'VARCHAR2',
              data_length, null) as length,
              decode(data_type, 'NUMBER', data_scale, null) as scale
             from user_synonyms syn
             join all_tab_columns col on syn.table_name = col.table_name
             where syn.table_name = #{table_name.to_s.upcase}
             and col.owner = nvl(syn.table_owner, user) }
          SQL_CODE

          select_all(table_cols, name).map do |row|
            row['data_default'].sub!(/^'(.*)'\s*$/, '\1') if
row['data_default']
            OCIColumn.new(
              oci_downcase(row['column_name']),
              row['data_default'],
              row['data_type'],
              row['length'],
              row['scale'],
              row['nullable'] == 'Y')
          end
      end
    end
  end
end
This topic is locked and can not be replied to.