Oracle and Rails seems really slow.... In development

Rails
1

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? :slight_smile:

Brian H.
Web D.
Learning & Technology Services
Schofield 3-B
University of Wisconsin-Eau Claire
715 836 3585
[email protected]

2

On 1/11/06, Hogan, Brian P. [email protected] 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? :slight_smile:

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