Expensive where clause => bad performance over a large ResultSet?

[Isa Muqattash]

Hi everyone,

Perhaps someone has a quick answer to this. I've been looking for a few days and cannot find any answer online.

My situation: I have a pretty expensive and complex where clause. Given the current application design, the where clause itself is not easy to change. We already have indices on the involved table columns. But let's just take for granted that this can be somewhat slow (say 50 milliseconds).

The problem arises when I do a select that returns a lot of rows (say a few thousand).

The code reads like a standard Squeryl select: from(mytable)(row => where(INSERT_COMPLEX_CLAUSE_HERE) select(row.id))

As it turns out, I can get the underlying SQL statement created by Squeryl and run it directly under JDBC and iterate over the results in say 70 milliseconds.

However, if I let Squeryl do its lazy fetching by calling toList on the query object above, it takes several seconds to get the list.

Root cause: Turns out Squeryl is calling the where clause each time it advances its iterator to fetch the entire list? From the Squeryl documentation, "The select expression will be evaluated for every row returned by the query."

Does anyone know of a way to bypass calling the where clause only once at the start instead of on every time an iterator is advanced?

[Эрдэм Тумуров]

I think "where" clause is run only at constructing query, only "select expression" is evaluated on each row, which is "select(row.id)" in your code.

[Erdem Tumurov]

Perhaps toBuffer would be much faster. Could you test?

вторник, 2 июня 2020 г., 4:37:23 UTC+7 пользователь Erdem Tumurov написал:

[Erdem Tumurov]

or if you just iterate over and don't need a collection, use foreach instead of 'toList'

вторник, 2 июня 2020 г., 4:47:09 UTC+7 пользователь Erdem Tumurov написал: