How Do I Minimize Database Access for Read-only Data? (Magic xpa 3.x)
Database access tends to slow down programs, because each request for data has to go through the database server. Some kinds of access are slower than others; data that is locked takes longer than data that is read-only.
However, if you have data that is accessed a lot, and doesn’t change very often, such as lookup tables, you can load these tables just once when the application opens (or when the table is first accessed) and continue to access the same copy throughout the application.
This is accomplished by defining the table as resident.
To change the Resident setting:
-
Position the cursor on the table you want to change, in the Data repository.
-
Press Alt+Enter to access the Data source properties. The Advanced tab will be chosen by default.
-
Choose the option you want on the Resident field.
You have several choices here:
-
No is the default setting. The table will be opened every time it is opened by a task.
-
Immediate: The table will be opened once, when the application starts. Any tasks that use the table will get the same data that was in the table when it was first opened.
-
On Demand: The table will be opened once, when it is first used by a task. After that, other tasks will get the same copy of the data.
-
Immediate and on Browser: This refers only to Browser client tasks. It will cause the table to be downloaded to the client’s browser.
Once a table is defined as Resident, no program in the application can update it.
If you want to update that table within the same application, you need to make a copy of the table within the Data repository, and set the Resident flag to No for the copy. In this example, we have two copies of the State_Codes table. One is Resident, the other isn’t.
Now, we can create a program that updates table #5, and those changes will be stored in the State_Codes table. However, this will not change the copy of the “State_Codes” table that the other programs are using, which is the Resident copy, table #4.
So, to refresh the copy of the State_Codes table that is being used, we use a Magic xpa function called DBReload(). DBReload takes on parameter, the DSource number (and, optionally, name). After the DBReload() is executed, the copy in memory will be refreshed.
