A connection spends most of its life waiting for the next question. From release 27.0 onward that waiting time is used: while nothing is being asked, the platform fetches data the session is likely to want next and puts it in its caches. The first click of a session then answers from what is already there instead of from a platform on the other side of the internet. This matters most where the wait is longest. A newly connected administration has nothing in cache at all, so the first report of a first-time user is the slowest one they will ever run. Fetching ahead moves that wait to a moment when nobody is looking. ## Where It Is On Fetching ahead is on by default in the products which a person works in directly: - Invantive Query Tool, both the Windows and the multi-platform edition, - Invantive Data Loader, - Invantive Runtime, - Invantive Business for Windows, - Invantive Control for Excel, - Invantive Composition, - Invantive Data Hub, - Invantive UniversalSQL Server, where it serves databases of its own installation. Everywhere else it is off unless it is switched on. A server carries the sessions of many people at once, so what it fetches ahead is spent from somebody else's allowance and that is a decision for whoever runs the server: on a database in Invantive Cloud it is a setting of the database, and on any connection the setting `preemptive-loading` in the connection string, in a provider file or in a `set` statement decides. A job, a webhook or a scheduled script reads exactly what it came for and is left alone. Invantive UniversalSQL Server sits on both sides of that line, and follows the database it serves. An installation which serves its own databases is one organisation's own machine spending its own allowance, so it fetches ahead like a product on a desk. Where the same server serves a database of Invantive Cloud, the setting of that database decides and the default gives way to it. ## What It Never Does Fetching ahead is bounded, and every bound exists to keep it out of the way of the person using the connection: - it starts only after the session has been idle for a few seconds, and stops the moment the session asks for anything itself, at the first page boundary; - it spends at most three quarters of the request rate a platform allows per minute, so a question asked while it is busy waits at most moments; - it never spends the last part of the daily allowance of a platform, which is kept for questions actually asked; - it stops after two hours in which nobody did anything, and starts again when the user returns; - it stays inside a disk budget, and stops rather than filling the disk of a server. Nothing it fetches changes what a statement returns. It fills the same caches an ordinary question fills, so a question answered from a table fetched ahead answers exactly as it would have done otherwise, only sooner. ## What Is Fetched Each platform states what is worth fetching ahead of time and what it is worth relative to everything else. For Exact Online those are the incremental tables, of which the transaction lines are refreshed on every round because they are what changes and what is asked for; document attachments are fetched only when there is capacity to spare. Which tables come first depends on the session: - on a connection which has no history, a preview of the tables the platform marks for first use, one thousand rows each, so that opening a table shows something at once; - on a connection which has been used before, what this user actually read around this time of day, taken from the history of the session and weighed so that yesterday at this hour counts for more than last week at another. A statement may also say what it is about to read, which changes the order in which things are fetched. See [[dbms_preload]]. ## Questions Answered Without A Call A table which has been fetched ahead whole is answered from the caches of the session, filter and all. A question such as `select * from projects where code like '%1%'` is then answered without asking the platform to apply that condition, since everything the condition could select is already here; the platform is left alone entirely and the answer is the same answer. This happens only where the answer cannot differ: every administration the question covers has to have been fetched whole, the data has to be young enough for what the session accepts, and the table has to be small enough that reading it here is cheaper than a call which would return a part of it. A question which asks for a limited number of rows is always left to the platform. The setting `preemptive-loading-superset` governs it and is on by default, and `preemptive-loading-superset-max-rows` states the size above which the platform is asked anyway. Neither does anything on a connection which does not fetch ahead in the first place. ## Following What It Does Three views of the Data Dictionary report it, and all three answer on any connection, whether anything is being fetched ahead or not: - `SystemPreemptiveLoadCandidates@DataDictionary` lists what each data container offers, what it is expected to cost and why it ranks where it does; - `SystemPreemptiveLoadingPartitions@DataDictionary` reports the progress per partition, with a reference back to the candidate; - `SystemPreemptiveLoadHints@DataDictionary` lists the announcements a statement made, what was last done with each one, the message code of that action and when it happened. A connection on which fetching ahead is off says exactly that here, so an announcement which led to nothing explains itself. Every call made this way is recorded as such: the session I/O of the call states that its origin is fetching ahead rather than a question of the user. Nothing fetched ahead is counted as work the user asked for, wherever the traffic of a session is reported. ## Switching It Off A connection which should never fetch ahead sets `preemptive-loading` to `false`, in the connection string, in a provider file or with a `set` statement. The change takes effect within seconds rather than at the next log on, and a fetch which is under way at that moment stops at the first page boundary. The bounds themselves are settings too, so an installation which wants to be more careful than the defaults can be. They are listed with every other setting of the platform in `SystemDataContainerAttributes@DataDictionary`, where their names all begin with `preemptive-loading`.