How to speed up eager loading of foreign collections?

Question

I have a database table mapped with ORMlite, it contains some data (18 columns) and it also contains ForeignCollectionField(eager = true).

Problem is when loading all data from this table ... ORMlite is creating query for every item instead using joins. Which is resulting in 67124 queries and taking forever to load all objects from this table.

This could be however done in right join query under few seconds? Why to generate thousands of queries instead?

How can I speed it up? Do I have to write raw query and then RawRowMapper , which makes using ORM pointless..

How to deal with loading eager collections in ormlite? Because queryForAll is not way..

Answer 1

Problem is when loading all data from this table ... ORMlite is creating query for every item instead using joins. Which is resulting in 67124 queries and taking forever to load all objects from this table.

It's ORM_Lite_ for a reason. Lot of people have asked for the join support on foreign collections but I've not gotten to it yet. It's not easy.

If you still want to use ORMLite then I'd recommend not using eager = true and doing 2 queries instead. One query for your main item and then another query using the DAO associated with the collection entity using IN. Something like:

qb = accountDao.queryBuilder();
qb.where()...;
List<Account> accounts = qb.query();

// build a list of account-ids
List<Long> accountIds = new ArrayList<>();
for (Account account : accounts) {
    accountIds.add(account.getId());
}

// now use this list of ids to get your other entities
List<Order> orders = orderDao.queryBuilder().where().in("accountId", accountIds).query();
// now you have a list of orders for all of your ids
// you will need to associate each order with its account

Hope this helps.