« FileMaker Experience … | Home | Greetings from MBS Xo… »

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" )
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.
08 11 17 - 12:01
three comments

Never know it can be done before. Any example project to see how to implement it?
Worajedt Sitthidumrong - 08 11 17 - 18:35

This can be very interesting, however, what happens if one of the 100 records I prefetched changes in the database? I do get the old value or the updated one?
Massimo Valle - 09 11 17 - 12:36

Well, you get the data at the time it was read.
But I don’t keep around recordsets long, so for me they only live a second and there is no issue for us.

And when user clicks on item on the list, we load a fresh copy of the data for the text fields.
Christian Schmitz - 09 11 17 - 13:08

Remember personal info?

Emoticons / Textile

Hide email:

Small print: All html tags except <b> and <i> will be removed from your comment. You can make links by just typing the url or mail-address.