0 Replies Latest reply on Dec 9, 2008 12:08 PM by 645350

    performance of will_paginate + stored programs

      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

      "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.


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