Hello everyone, I am trying to convince my colleagues to convert to Ruby on Rails, however I am up against the following problem: Ruby on rails seems to be primarily designed a single database doing a small number of queries. While trying to process a webpage that does thousands of sql queries on Oracle, I get the message that I have used up too many cursors. In Java, I can use stmt.close() to close all the cursors on a sql statement. In Ruby on Rails the sql "statement" and the sql "result set" are hidden inside ActiveRecord''s methods. Therefore I can''t close the statement. As a work around I can close the database connection, however it takes too long to open another connection thousands of times. How can I solve this problem? Thanks - Jon -- Posted via http://www.ruby-forum.com/.
take a look to this link http://vsbabu.org/mt/archives/2005/07/19/oracle_with_active_record.html Jon Leung wrote:> Hello everyone, > > I am trying to convince my colleagues to convert to Ruby on Rails, > however I am up against the following problem: > > Ruby on rails seems to be primarily designed a single database doing a > small number of queries. While trying to process a webpage that does > thousands of sql queries on Oracle, I get the message that I have used > up too many cursors. > > In Java, I can use stmt.close() to close all the cursors on a sql > statement. In Ruby on Rails the sql "statement" and the sql "result set" > are hidden inside ActiveRecord''s methods. Therefore I can''t close the > statement. > > As a work around I can close the database connection, however it takes > too long to open another connection thousands of times. > > How can I solve this problem? > > Thanks > - Jon-- Posted via http://www.ruby-forum.com/.
If I wer your Oracle DBA, I would be very concerned that you''re
running an
app that has a web page which runs thousands of SQL queries. I would expect
you to run out of cursors. Are you able to take advantage of eager loading?
I don''t know if this is so much a Rails issue as an issue with your
app.
(Sounds like the n+1 problem)
A quick glance at the source for the Oracle connection adapter for
ActiveRecord shows that cursors are being created and closed. If you are
sure that this isn''t happening on your database then you might want to
consider filing a ticket.
C:\ruby\lib\ruby\gems\1.8\gems\activerecord-
1.14.4\lib\active_record\connection_adapters\oracle_adapter.rb line 472
def select(sql, name = nil)
cursor = execute(sql, name)
cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
rows = []
while row = cursor.fetch
hash = Hash.new
cols.each_with_index do |col, i|
hash[col] case row[i]
when OCI8::LOB
name == ''Writable Large Object'' ? row[i]:
row[i].read
when OraDate
(row[i].hour == 0 and row[i].minute == 0 and row[i].second
== 0) ?
row[i].to_date : row[i].to_time
else row[i]
end unless col == ''raw_rnum_''
end
rows << hash
end
rows
ensure
cursor.close if cursor
end
You could increase the number of open cursors at the server level too.
Check to make sure you are using eager-lading though... not doing that can
kill your app.
On 8/10/06, Jon Leung <ravenodyssey@gmail.com>
wrote:>
> Hello everyone,
>
> I am trying to convince my colleagues to convert to Ruby on Rails,
> however I am up against the following problem:
>
> Ruby on rails seems to be primarily designed a single database doing a
> small number of queries. While trying to process a webpage that does
> thousands of sql queries on Oracle, I get the message that I have used
> up too many cursors.
>
> In Java, I can use stmt.close() to close all the cursors on a sql
> statement. In Ruby on Rails the sql "statement" and the sql
"result set"
> are hidden inside ActiveRecord''s methods. Therefore I
can''t close the
> statement.
>
> As a work around I can close the database connection, however it takes
> too long to open another connection thousands of times.
>
> How can I solve this problem?
>
> Thanks
> - Jon
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060811/6f63401c/attachment.html
Jon Leung wrote:> Hello everyone, > > I am trying to convince my colleagues to convert to Ruby on Rails, > however I am up against the following problem: > > Ruby on rails seems to be primarily designed a single database doing a > small number of queries. While trying to process a webpage that does > thousands of sql queries on Oracle, I get the message that I have used > up too many cursors. > > In Java, I can use stmt.close() to close all the cursors on a sql > statement. In Ruby on Rails the sql "statement" and the sql "result set" > are hidden inside ActiveRecord''s methods. Therefore I can''t close the > statement. > > As a work around I can close the database connection, however it takes > too long to open another connection thousands of times. > > How can I solve this problem?Hi Jon, I don''t know where you''re seeing this. We''re using AR against Oracle and don''t have this issue at all. In the active_record/connection_adapters/oracle_adapter.rb, there''s this code, which closes the cursor: def select(sql, name = nil) cursor = execute(sql, name) cols = cursor.get_col_names.map { |x| oracle_downcase(x) } rows = [] while row = cursor.fetch ... ... ... rows << hash end rows ensure cursor.close if cursor end So it does close the cursor. Regards, Blair