On Thursday, January 24, 2013 11:40:59 AM UTC-8, Charles Monteiro wrote:
>
> hi, forgive my newbiness
>
> So I started playing with pagination as strategy to port cursor using
> stored procs.
>
> I started to explore this by a simple test:
>
> database = Sequel.connect(active_oracle_url)
> cnt = 0
> table = database[:account]
> cursor = table.paginate 1,1000
> cursor.each { |ea|
> puts ea[:name]
> cnt += 1
> }
>
> puts "Count = #{cnt}"
>
> Now, the "account" table has over 4000 rows
>
> but count will show in the console as 1000
>
> i.e. contrary to my expectations what I have setup above does not load
> 1000 rows into memory at a time but rather loads 1000 rows from the offset
> of 1 only.
>
> What I need is something that will load all records into memory 1000 at a
> time i.e the underlying engine knows its finished with the current set and
> then issues the next query to get the next set of 1000.
>
> Please pray tell me what I have missed.
>
You have apparently neglected to read the documentation for
Dataset#paginate. :) You probably want to use each_page instead of
paginate. Now, each_page yields a paginated dataset for each page of
results, so it isn't a transparent addition:
table.each_page(1000) do |ds|
ds.each do |ea|
puts ea[:name]
cnt += 1
end
end
As I mentioned in the other email, I don't really recommend that approach,
but it should work fine. You should probably wrap that code in a
transaction if there are going to be any concurrent modifications to the
table.
> The objective is to be able to return a dataset that behaves that way i.e.
> that the result of "paginate" or whatever the right method is will return a
> dataset that will provide for iteration that will know when it needs to
> retrieve the next set of rows that meet the criteria of the dataset or in
> the case above non-filtered.
>
Other than the use_cursor method specific to the postgres adapter, Sequel
does not manually use cursors at all. Sequel sends a query, and iterates
over the results one at a time as the driver yields them, stopping when the
driver finishes yielding rows. It's up to the driver whether all results
are buffered in memory before yielding (e.g. typical PostgreSQL behavior),
whether they are yielded one-at-a-time as the come over the wire (e.g.
sequel_pg streaming behavior), or whether the driver uses cursors
internally to fetch X numbers of rows at a time (e.g. some JDBC drivers?).
I don't have a problem applying patches for use_cursor like methods to
other adapters, assuming they come with specs and don't cause other
problems. Similarly, I don't have a problem applying a patch for
setFetchSize support to the jdbc adapter.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
Visit this group at http://groups.google.com/group/sequel-talk?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
