This sounds like a use case for external entities and Mendix data hub, https://docs.mendix.com/refguide/external-entities
Load and commit all data from sql into the local database before use. Database replication is an option for this. https://appstore.home.mendix.com/link/app/160/
Or create custom search and paging: https://docs.mendix.com/howto/logic-business-rules/server-side-paging
And there may be other options i did not think of.
I hope this helps.