This discussion is archived
0 Replies Latest reply: Dec 9, 2008 4:08 AM by 645350 RSS

performance of will_paginate + stored programs

645350 Newbie
Currently Being Moderated
Hi all,

What your experience with performance of will_paginate with stored procedures/functions? I have a few pipelined functions following the procedure described at http://wiki.rubyonrails.org/rails/pages/OracleStoredProceduresAsDataSource and uses will_paginate for it.

As you know, a code such as

paginate_by_sql([
"select col1, col2, col3 from table(function(?,?))", "#{param1}", "#{param2}",
], options)

will automatically form:

select count(*) from (select col1, col2, col3 from table(function(param1,param2)))

to count the results. This looks like it will result in Oracle needlessly retrieving data for col1, col2, etc when all that is needed is the number of results. Or is there (hopefully) some sophistication/intelligence that determines otherwise?

And then to get for example results 76-90 will_paginate forms:

select * from
(select raw_sql_.*, rownum raw_rnum_ from
(select col1, col2, col3 from table(function(param1,param2))) raw_sql_
where rownum <= 90) where raw_rnum_ > 75

Again, does this mean the pipelined function will still read all the data for results 1-75 first to form the "table"? If so, that would be a waste. Does it look like it's not ideal to use will_paginate with stored programs? Would appreciate to know what you think.

Thank you very much.

Johann

Edited by: johanntagle on Dec 9, 2008 4:06 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points