mybatisspring-mybatis

Mybatis cursor query more than 100k records

Using Mybatis, I am querying a huge data from database (about 50k records) but a problem with limited memory and the application restart again. I am currently using List<>, maybe this is the problem.

I am planning Cursor<>, can it solve the problem? If the records grow to above 100k?

Solution

  • Adding a cursor could solve your problem. Another option is batching your data. Is there a field like an id on which you could apply batching?

    SELECT TOP(1000) * FROM yourTable WHERE id > {record.id} ORDER BY id

    This way in a loop you can retrieve a dataset in the size you want, use it for what you want, save the last record.id and call this query again. This way your application will never run out of memory, even if the number of records in the database increases.