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

MIVA^®  RESOURCES:  Speeding up MvFILTER

by Ivo Truxa, 10/24/2000 (updated 11/09/2000, 12/01/2001)

1. Introduction
2. MvFILTER Analysis
3. MvFILTER Example
4. The MvFILTER Patch
5. Recomendations
6. Links
7. User Comments

Filtering of huge databases and/or using complicated filter expressions with Miva may be rather long and even lead to timeouts.

I have to admit that originally I have totally misunderstood how MvFILTER works. MvFILTER in fact does just two things:

1. It stores your FILTER expression into an internal variable
2. It starts from the current record in the database and performs a single search of the first next matching record. As soon as it finds it, it stops.

It searches the next record in the same way that you would do it with a MvWHILE loop, just it is quicker, while processed internally. Still, you should keep on mind, that if you have an index open and the first matching record is not quite close to the current position, the looping through an indexed database will be much slower than on a unindexed database.

Any next database access function (like MvSKIP, MvGO or MvFIND) uses the the same technique: for example if you make MvSKIP ROWS="1", it skips through the database as long as it finds the next record matching the FILTER expression.

top

Originally I had the feeling that MvFILTER loops through the entire database when executed and therefore it is so slow. It was nonsense and there is no reason for doing it. Looping through the database and keeping an array of variables keeping the filtered records would be too resource intensive and slower then it is. In fact MvFILTER works much better then what I thought - just setting a FILTER expression that is used in all other database access functions until the next MvFILTER changes or removes it and looping to the next matching record only. Well, in the case there is no match or the next match is very far from the current position, it is virtually equal to looping through the entire database, but I am afraid there is no better way without having more complex indexing system.

Nothing changes on what I claimed sooner about indexes - all access functions, and MvFILTER of course too, are quicker when there is no index open, but you can speed up searches with MvFILTER in the way that you perform some operations without index, or you set an index, move your starting position through MvFIND, remove the index, apply FILTER and another index and continue with MvSKIP (or another MvFILTER). The exact sequence is always very dependent on the concrete task.

UPDATE (12/01/2001): The only problem is that you have to use a small trick to avoid MvFILTER looping through the database. You can do it for example with a global variable in the FILTER expression. Have look at another document for a working example: Databases Performance Notes. I forgot highlighting it earlier, because I was using a patched engine that did not require it.

top

Let's take the following example: you have a very huge database of customer names and need to list just names ending by 'of', but only those living in Texas and older than 30 years and displaying them 100 per page. Filtering the entire huge database against such a condition could be very long.

You could open the database indexed by state, MvFIND the first record in Texas and then loop with MvWHILE checking if the customer name of the respective record ends by 'of' and the age is higher than 30 years. If there the group of customers with age +30 is quite big, you will need again quite a lot of time to display just those names ending by 'of'. Well, you could create an index with a more complex expression, but if you need couple of such searches with different criteria, you would quickly finish with disk full of indexes and get performance problems updating them.

With MvFILTER you can do the following:

1. MvOPEN the customer database with an index sorted by state
2. MvFIND the first record in Texas
3. set a global variable to force the next FILTER expression to return TRUE
4. MvFILTER to find the first record matching the age and the name ending
5. reset the global variable to allow FILTER expression working
6. MvSKIP 100 times to display all 100 records on the page
7. Store the recno of the last record and put it as an argument into the URL calling the next page. You can use it with MvGO (without an index) when starting to display the next page

In this example, the displayed names would be sorted on the page according to the last applied 'state' index. You can set another index before MvSKIP, but you would loose the advantage of reducing the search range to smaller part of the database - if you change the state index to index sorted by name, the matching records (all of them are in Texas) could be suddenly quite far each from the other and the previous MvFIND would have no sense. If you really need the results sorted by another criteria, you would either need a more complex index expression (as I mentioned in the MvWHILE example) or to store all hits into a temporary database or to store the records in a variable array and resort the array (it could take a lot of memory if you have a lot of matches). In all cases, proper working with MvFIND+MvFILTER is faster then MvFIND+MvWHILE.

top

I removed the patch, because in fact it did not do almost anything else then what you can do directly in Miva. Sooner, I was not aware that you can, for example, open a databse set an index, perform a MvFIND, then remove the index and execute a MvFILTER, putting back the index again, etc. In fact, all this is possible without any problems and therefore the patch makes few sense.

top

Anytime you have a database that is static or you do not add/delete records too often, re-sort it after each change and use it without any index. A good example would be a database of products. Mostly you do not change it several times per day. In such a case you can easily write a function to re-create the database after each change with the desired default sorting. In such way you can open, search and filter it by default without any index and the operations will be up to several ten times faster.

top

top

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