Prefetching records from databases
But you can ask our SQL functions to do prefetching and get more records in advance. For example you can use a prefetch size of 100 records. On the first record query, you get the network request to get 100 records. Than your 2nd to 99th query return immediately with data in practically no time.
The picture on the right side shows how long record fetching takes in milliseconds on a test application. The batch size is 10, so getting 10th, 20th and 30th record takes each 70ms, which all the records between take only a small amount of time.
In Xojo with SQLDatabaseMBS, SQLCommandMBS or SQLConnectionMBS class:
dim nBulkSize as Integer = 1000
c.Option(SQLCommandMBS.kOptionPreFetchRows) = str(nBulkSize)
Please note that this is a setting which applied on the database connection will propagate to all commands running on that connection.
In Xojo with JDBC using JavaDatabaseMBS class, you can use the JavaResultSetMBS.FetchSize or JavaStatementMBS.FetchSize properties to enable the same batch loading.
In FileMaker with MBS SQL functions, you can call
MBS( "SQL.SetConnectionOption"; $Connection; "PreFetchRows"; "100" )
or
MBS( "SQL.SetCommandOption"; $Command; "PreFetchRows"; "100" )
to do the same.
I hope this helps you for your network based database access. If you use SQLite, please do not forget to use a big cache.