How Do I Reduce Database Access? (Magic xpa 4.x)
Magic xpa handles much of the database access automatically, so you do not have to code statements to tell Magic xpa when to fetch records. However, you do have control over the factors that affect how much access is being done. Using the database access properties in Magic xpa in a way that fits your application is one of the biggest influences on the response speed of your programs.
Hint: You can use the Debugger and the native SQL profiler tools to determine just how many times the database is being accessed: when tables are being opened and how. It is a good idea to be familiar with what is going on behind the scenes, to make sure your programs are efficiently using resources.
Some tables are used primarily for reference, and are not updated often, such as code tables. There is a special access property you can use for such tables, the Resident property. This is described in How Do I Minimize Database Access for Read-only Data?.
You also have a great deal of control over how many records are fetched from the database, and when. There are four main factors that affect this:
Array Size
When Magic xpa fetches records, it does not fetch them one at a time, but rather as a block of records. You can control how many records are fetched at one time by setting the Array Size property for the Data source.
If the Array size is set to zero, then the default setting for array size for the Database is used. If the array size on the database is also zero, then the Magic xpa default is used.
The Magic xpa default is 1200/record size. So if your record is 200 bytes long, 6 records will be fetched at a time. For SQL files, the actual record length will vary at runtime, depending on how many columns are fetched back.
Cache
You can use the Cache property to reduce database access also.
Cache can be used in online tasks for all tables. For batch tasks it can be used only on linked tables.
When there is a cache on the main table (position and data) Magic xpa will not re-read the record from the database when positioned on it. When there is cache on a linked table, Magic xpa will read it once from the database and reuse it if needed for a link from another record while in the same task. One can also keep the cache on a linked table even when leaving the task, by defining the task as resident and opening the linked table in one of the ancestor tasks, the cache will be kept until leaving the task opening the table.
Preload View
The Task Properties->Data->Preload View property has an effect on the number of fetches done before the window opens. If this is set to Yes, then the records are all fetched before the window is displayed. This allows scroll bars to be displayed accurately. However, if there is a lot of data that the user is not likely to scroll through, it will result in many more record fetches than would otherwise be necessary.
Use of Ranges and Direct SQL
You should also try to get the database server to do as much your filtering as you can, to reduce the number of records fetched.
For instance, if you want to print all the customer records where the Amount Overdue is greater than $12,000, you could do this by using a Condition on the Form Output operation. However, if you do this, the engine has to fetch every single customer record back, to test the condition. This could require hundreds of fetches. On the other hand, if you use a Range to filter the data, Magic xpa will format one SQL statement, and the server will only return the records where Amount Overdue is greater than $12,000, which hopefully will only be one fetch, depending on how many customers owe you money.
There are also instances where a Direct SQL statement can be the most efficient way to work with data. For more information about this, see How Do I Send My Own SQL Statements to the Database?.
