How to iterate over a huge amount of records with scala sorm

Question

I want to iterate over a lot of records from a table with sorm, but I want to do it in a memory efficient way.

Today I use this code:

Db.query[Items].whereEqual("title", someTitle).fetch.foreach { webCount =>
          //do something
}

The problem is that this code first loads all the records before proceeding to each item in the loop. Is there any way to stream records?

Link to the original English question

Answer 1

The methods Querier.fetch and Querier.fetchIds return streams, which doesn’t mean you can’t run out of memory if you have to work with all the returned objects at the same time.

The object Query (built by Querier) has properties limit and offset allowing pagination in the traditional sense of database:

val itemCount = Db.query[Items].whereEqual("title", someTitle).count()
// Consultas paginadas
Db.query[Items].whereEqual("title", someTitle).limit(10).offset(40).fetch // ...

Answer 2

According to a comment on the documentation, the fetch of the entities is done in two stages:

1. The query with all filters and sorts is executed in the database, but recovering only the id (primary key) of each record.

2. When you really get to the item on Stream returned by query, then the second phase occurs where the other fields are read.

So since the API doesn’t seem to provide any other way to iterate over the results, this would already be the most efficient way and would hardly burst the memory.

However, the comment is from 2 years ago and I don’t really know if it still applies to the latest versions.

---

Original comment:

Fetching entities in SORM Always Goes in two phases: first, all your Filters - no Matter how Intricate, - orderings and etc get Applied to a single multitable query which fetches just the ids of the matching entities; in the Second Phase SORM emits Multiple queries to Actually populate the Resulting entities Depending on Complexity of their Structure. Since all the selects of the Second Phase are by Primary Keys, they are very Cheap. But this area will Definitely become a Battlefield for all Kinds of optimizations in Future. Contibution is Much appreciated.

There Actually was a querier implementation which was Doing Everything in a single Phase: Both querying and Object Population Were being done in a single query in the version 0.1.0, - but then it turned out that, due to specifics of how Joining Tables Works, it could fetch a Million Rows for Certain multicollection entities, literally. So, downshifting to a Simpler Strategy turned out to be Inevitable.

The "Stream" Thing is there intentionally. It delays the Second Phase fetching queries and Objects Population until you Actually Reach them in the returned Stream. Although this Might be a Subject to changes in the Future.