Hogan, Brian P.
2006-Jan-11 19:28 UTC
[Rails] Oracle and Rails seems really slow.... In development
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 -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060111/1a1afea9/attachment.html
Wilson Bilkovich
2006-Jan-13 19:49 UTC
[Rails] Oracle and Rails seems really slow.... In development
On 1/11/06, Hogan, Brian P. <HOGANBP@uwec.edu> wrote:> > > 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? :) >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
Hogan, Brian P.
2006-Jan-13 20:15 UTC
[Rails] Oracle and Rails seems really slow.... In development
Neat!>From my findings, though.... This is what I noticed:The original code: table_cols = %Q{ 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 #{scope}_catalog cat, #{scope}_synonyms syn, all_tab_columns col where cat.table_name = #{table} 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, #{(scope ="all" ? "cat.owner" : "user")}) } My version table_cols = %Q{ 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 #{scope}_catalog cat, #{scope}_synonyms syn, all_tab_columns col where col.table_name = #{table} 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, #{(scope ="all" ? "cat.owner" : "user")}) } Runs no problem. I just changed where cat.table_name = #{table} To where col.table_name = #{table} Since the ALL_TAB_COLUMNS has the table name, this works great.... And this fix makes it just fly! -----Original Message----- From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Wilson Bilkovich Sent: Friday, January 13, 2006 1:49 PM To: rails@lists.rubyonrails.org Subject: Re: [Rails] Oracle and Rails seems really slow.... In development On 1/11/06, Hogan, Brian P. <HOGANBP@uwec.edu> wrote:> > > 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 thefirst> 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? :) >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
Leon Leslie
2006-Jan-13 21:28 UTC
[Rails] Oracle and Rails seems really slow.... In development
Is this a patch? On 1/13/06, Hogan, Brian P. <HOGANBP@uwec.edu> wrote:> > > Neat! > > >From my findings, though.... This is what I noticed: > > The original code: > > table_cols = %Q{ > 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 #{scope}_catalog cat, #{scope}_synonyms syn, > all_tab_columns col > where cat.table_name = #{table} > 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, #{(scope => "all" ? "cat.owner" : "user")}) } > > > > My version > > > > table_cols = %Q{ > 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 #{scope}_catalog cat, #{scope}_synonyms syn, > all_tab_columns col > where col.table_name = #{table} > 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, #{(scope => "all" ? "cat.owner" : "user")}) } > > > > Runs no problem. > > I just changed > > where cat.table_name = #{table} > > To > where col.table_name = #{table} > > > Since the ALL_TAB_COLUMNS has the table name, this works great.... And > this fix makes it just fly! > > > -----Original Message----- > From: rails-bounces@lists.rubyonrails.org > [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Wilson > Bilkovich > Sent: Friday, January 13, 2006 1:49 PM > To: rails@lists.rubyonrails.org > Subject: Re: [Rails] Oracle and Rails seems really slow.... In > development > > > On 1/11/06, Hogan, Brian P. <HOGANBP@uwec.edu> wrote: > > > > > > 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? :) > > > > 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 > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- First they laugh at you, then they ignore you, then they fight you. Then you win. -- Mahatma Karamchand Gandhi -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060113/cb37bd62/attachment.html
Hogan, Brian P.
2006-Jan-13 21:49 UTC
[Rails] Oracle and Rails seems really slow.... In development
No patch yet because I just don''t have the time to make patches (never learned how to do the diff stuff) but I did contact the current maintainer and did hear back from him a couple of days ago so we''ll see what happens next. Right now I just made a plugin to patch it for my apps. I''ll gladly post the plugin as a zip on my server if anyone cares. -----Original Message----- From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Leon Leslie Sent: Friday, January 13, 2006 3:28 PM To: rails@lists.rubyonrails.org Subject: Re: [Rails] Oracle and Rails seems really slow.... In development Is this a patch? On 1/13/06, Hogan, Brian P. <HOGANBP@uwec.edu> wrote: Neat! >From my findings, though.... This is what I noticed: The original code: table_cols = %Q{ 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 #{scope}_catalog cat, #{scope}_synonyms syn, all_tab_columns col where cat.table_name = #{table} 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, #{(scope = "all" ? "cat.owner" : "user")}) } My version table_cols = %Q{ 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 #{scope}_catalog cat, #{scope}_synonyms syn, all_tab_columns col where col.table_name = #{table} 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, #{(scope = "all" ? "cat.owner" : "user")}) } Runs no problem. I just changed where cat.table_name = #{table} To where col.table_name = #{table} Since the ALL_TAB_COLUMNS has the table name, this works great.... And this fix makes it just fly! -----Original Message----- From: rails-bounces@lists.rubyonrails.org [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Wilson Bilkovich Sent: Friday, January 13, 2006 1:49 PM To: rails@lists.rubyonrails.org Subject: Re: [Rails] Oracle and Rails seems really slow.... In development On 1/11/06, Hogan, Brian P. < HOGANBP@uwec.edu <mailto:HOGANBP@uwec.edu> > wrote: > > > 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? :) > 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 _______________________________________________ Rails mailing list Rails@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails -- First they laugh at you, then they ignore you, then they fight you. Then you win. -- Mahatma Karamchand Gandhi -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060113/e474cc03/attachment-0001.html
Lori Olson
2006-Jan-14 00:22 UTC
[Rails] Oracle and Rails seems really slow.... In development
On Jan 13, 2006, at 2:49 PM, Hogan, Brian P. wrote:> No patch yet because I just don''t have the time to make patches > (never learned how to do the diff stuff) but I did contact the > current maintainer and did hear back from him a couple of days ago > so we''ll see what happens next. Right now I just made a plugin to > patch it for my apps. > > I''ll gladly post the plugin as a zip on my server if anyone cares. > >We care! We care!
Hi, I''m uploading a raw image into a blob column and noticed that unless I overwrote the model attribute writer that Rails would try to load a ruby object for the image instead of just the raw data. So in the model I did this: def picture=(picture_field) write_attribute(:picture, picture_field.read) end and things work fine (though I don''t really know what I''m doing) ... except if the user fails to select an image to upload, in which cases I get an error: undefined method "read" for string "" Any ideas for avoiding this error message in these cases? It seems like some kind of conditional wrapped around the write_attribute command should work, probably some IO method that will test for the ability to read a stream (am I working with a stream here?) ... or maybe there''s a better way to upload a raw image. Thanks, russ -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060114/c900c409/attachment.html
put the images on the filesystem and use file_column. Tons of threads on it in here (a few by me thats for sure). http://www.kanthak.net/opensource/file_column/ On 1/13/06, Russ McBride <Russ@psyex.com> wrote:> > > > Hi, > > > I''m uploading a raw image into a blob column and noticed that unless I > overwrote the model attribute writer that Rails would try to load a ruby > object for the image instead of just the raw data. So in the model I did > this: > > > def picture=(picture_field) > write_attribute(:picture, picture_field.read) > end > > > and things work fine (though I don''t really know what I''m doing) ... except > if the user fails to select an image to upload, in which cases I get an > error: > > > undefined method "read" for string "" > > > Any ideas for avoiding this error message in these cases? It seems like > some kind of conditional wrapped around the write_attribute command should > work, probably some IO method that will test for the ability to read a > stream (am I working with a stream here?) ... or maybe there''s a better way > to upload a raw image. > > > Thanks, > russ > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >
Michael Schoen
2006-Jan-23 06:06 UTC
[Rails] Oracle and Rails seems really slow.... In development
Hogan, Brian P. wrote:> No patch yet because I just don''t have the time to make patches (never > learned how to do the diff stuff) but I did contact the current > maintainer and did hear back from him a couple of days ago so we''ll see > what happens next. Right now I just made a plugin to patch it for my apps.It''s on my list, should be able to get a patch submitted for this by the end of the week. Note that I''m still concerned about the revised query. But I''ve been sent a few different options, and one of them will work out.