Prefetching records from databases
When you do queries to a SQL database, you should know that in worst case, there ia a request over the network for fetching each record. So everytime you call MoveNext or FetchNext methods, the database driver may go and request the next record from the database server. This can be quite time consuming if you need 10000 rows and you do 10000 network roundtrips, each with a few milliseconds.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.
