http://mivo.truxoft.com
MIVO!
miva beyond limits

MIVA^®  RESOURCES:  Database Performance Tips

by Ivo Truxa, 10/25/2000
updated 11/09/2000: Fast loops with MvFILTER
updated 11/10/2000: MvFIND+MvWHILE versus MvFILTER
note 01/08/2001: check also art0036.htm - Database Performance Notes

NOTE: the following information is based on experience and measuring with Miva engines up to version 3.72. Some database functions and their speed may change (and I hope so) in future versions. Other tips are general and even a new Miva architecture would not change their meaning.

1. Use databases without indexes whenever you can
2. Use recno instead of indexing
3. Never open a database with more than a single index when not changing it
4. Keep your database as small as possible
5. Use MvFIND instead of MvGO
6. Fast loops with MvFILTER
7. Try to avoid MvSKIPping of big number of rows
8. MvFIND+MvWHILE versus MvFILTER
9. Trim your indexes to reduce their size improving the performance
10. User Comments

top

MvFILTER may be several ten times faster on a database opened without any index than on a indexed one. It means that, for example, instead of a delay of about a minute, the filtering on a big database would take just very few seconds. Also other database access functions (MvGO and MvSKIP) profit from a non-indexed database. You will see the performance difference immediately especially if you use them in a bigger loop.

"Good tip, but how to avoid using indexes when I need the database sorted?"

Indeed, working with an un-indexed database may be almost impossible if you permanently add/edit/remove such records that have influence on the sorting (e.g. user database with new user records each few minutes). On the other hand, in each application, there are usually several databases and some of them change quite seldom (e.g. product database, country database, etc.) or you just change some of the fields that have no influence on the default sorting (e.g. changing a price or a description at products sorted by the code or the name).

Once you have a database that does not change permanently, but just time-to-time or even regularly (e.g. once a year or once an hour), you can run a simple function that reorganizes the database to be correctly sorted even without any index open.

Have a look at the source of the function and feel free to use it.

Example:

• You have a product database and you list it by default alphabetically sorted by the name (or category, code or whatever else). You have also a search box for listing products containing the search expression and use MvFILTER for displaying them. You will get the results 10-50 times faster if you integrate the above function in the administration interface, re-sort the database after each database editing and open it by default without an index. It still lets you the possibility to open it with an index if you want to offer users the possibility to sort the products by different criteria (e.g. by price, by color, etc.). It will be as slow as originally in such case, but the most used default setting will be much faster.

Note: due to delays during loading the page, starting the Miva engine, opening databases, etc., the difference in performance would no be important at small databases (hundreds of records)

top

In some cases, it may be possible to access a database record directly. This may be interesting in the case that the recno can be directly derived from a database field (see the examples below). This method would be of much bigger advantage if the MvGO function were properly designed. Unfortunately it is not the case at Miva engines at least up to the version 3.72. Even with no index open, MvGO is just slightly faster than MvFIND on an indexed medium sized database. Normally MvGO should be flashing fast and this access solution would be the fastest possible.

You should note that deleting a record and consequent packing of the database could break your association of records to recno.

Examples:

• Database of ZIP codes containing all US ZIP codes. You can create a database with all 100,000 records (ZIPs from 00000 to 99999) even if currently there are much less of them (I have seen a database with 30,000 US ZIP codes). With such database you can assume recno = ZIP and access the ZIP record, containing other information about the ZIP code, with simple MvGO ROW="zipcode".
  
  
• You have 1000 products in a database with order codes 700500, 700505, 700510, etc. You can access the records with a simple formula MvGO ROW="{(prodCode-700500)/5}" if the products are properly sorted.

top

As I mentioned, the fastest are databases without any opened index. However, often you need to use an index anyway. Databases in common applications use to have several indexes and as I often see, people use to open all of them when accessing a database. This is a fault caused by the fact that you need to open all indexes to have them updated at adding, editing or deleting a database. Many programmers use the same routines in both cases and opening so databases with all indexes even if the current page has no possibility to modify the database.

The speed of access functions is not really greatly influenced with the number of open indexes, but they cause important delays at opening. File operations are always the slowest ones and therefore the less files you handle with a program the better for you. Another good reason for keeping the indexes closed is saving the system resources. Each open file consumes resources (cache, memory, CPU, file handles, etc.) and because websites are multi-user environments and Miva does not share any memory with other instances of a script, each unnecessarily opened file eats the resources as many times as many users concurrently access the script.

top

For the same reason as I mentioned in the above paragraph, you should try to keep your databases as small as possible. Generally, the speed of the access functions falls with the number of records. MvGO should be equally fast regardless of the database size, but unfortunately, due to a design fault, it is not true. Regular removing or overwriting deleted or unused records is important. Also reducing the field sizes to necessary length brings advantages - not directly through the access speed, but because smaller records and files consume less resources. Who is not careful with resources, can, for example, reach the edge of the cache efficiency or bring the system to swapping RAM to disk and it means, of course, great performance degradation.

top

This is indeed a paradox, but unfortunately true with Miva at least up to 3.72. As soon as you have to use an index, MvFIND is much faster than MvGO. It is especially important to know if you are using these functions in a loop.

top

One of common tasks in database applications is looping through the entire database, for calculating some values (e.g. the sum) or manipulating fields. Common way to do it is as follows:

<MvWHILE EXPR="{NOT db.d.EOF}">
  ... code ...
  <MvSKIP NAME="db">
</MvWHILE>

In fact there is a much faster way do execute a loop - it is a hidden feature of MvFILTER. MvFILTER does not do anything else then the above loop, with the only difference that the engine does not need to parse the MvWHILE loop, because looping in a quicker way internally. The way to perform an operation on each database field (not only on the filtered ones) with MvFILTER is as follows:

<MvFUNCTION NAME="filterFn">
... code ...
</MvFUNCTION>

<MvFILTER NAME="db" FILTER="{filterFn()}">

The function should not return any value (it may return 0). Though the function call adds some additional parsing time, the loop with MvFILTER is still much faster then with MvWHILE. The percentage of gain would be much higher at longer code inside of the loop, because the lose caused by the function call overhead would be relatively smaller. At my test database of 30,000 records and a very short function (worst case) I could measure gain of 30%. In my test function, I changed a field in all records to uppercase: with MvFILTER it took in average 29s and MvWHILE (on a database opened without any index) needed 41s. Using an index slows down the loop even much more (in both cases - with MvFILTER as well as with MvWHILE).

Using MvFILTER for looping is, of course, interesting only if you loop through the entire database. It would not be the proper tool for looping through just a part of it (for example displaying a single page of records).

top

If MvSKIP ROW="1" is still faster then MvGO or MvFIND, then the speed of skipping multiple lines falls proportionally with the number of rows. Whenever you can use MvFIND or MvGO instead of MvSKIP over multiple lines, do it.

top

After I realized that I misinterpreted the function of MvFILTER, I had to rewrite this paragraph:

MvFILTER works, in very similar way as MvFIND in combination with MvWHILE loop, where you check the filtering condition. The difference is that in case of a filtered database, all the conditions verifying gets performed internally, without the necessity to parse any code in the script and therefore it is much faster. As I explain in the "Speeding up MvFILTER", if you use MvFILTER properly you will get much better results then with MvFIND + MvWHILE.

However, using indexed instead of filtered databases is always much faster. In the case you can loop through an indexed database just with MvFIND+MvWHILE+MvSKIP without filtering records manually with MvIF, the script runs incomparable quicker.

The best solution remains using a pre-sorted unfiltered and unindexed database and accessing it just with MvGO+MvWHILE+MvSKIP (see "Use databases without indexes whenever you can"). In some cases you may even consider creating of several copies of a single database pre-sorted by different criteria - in many cases it would take less space on the disk then multiple indexes (see "Why are the indexes so big?").

top

The performance of medium and big databases can profit from changed index structure. See more details in art0027 and art0028.

top

Miva and some other terms used on this page are registerd trademarks of the Miva Corporation
copyright  truXoft  © 1997-2010