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

MIVA^®  RESOURCES:  Miva Databases Surprises

by Ivo Truxa, 10/24/2000

1. Method of the performance testing
2. Results
3. MvGO slower than MvFIND!
4. Why is MvFILTER so slow?
5. What about MvSKIP?
6. Other database commands
7. Conclusion
8. Links
9. User Comments

As I mentioned in the previous article, I made some performance testing of several Miva database commands to better understand the way Miva implements databases and indexing. I used loops with up to 20000 passes. At each testing I deduced the time of an empty loop. I tested on a local dual PII/350 machine with Windows NT4 and Miva Mia 3.72. I tested with mid-size database of 30,000 records. The results would, for sure, differ on other platforms, with different size of databases and in multi-user environment, but already these results tell us a lot about the way Miva handles databases. Of course, some live testing with a really huge database with several parallel processes running would be interesting too, but I did not find time enough to do it.

In fact I begun to test the performance to see what influence has the changing of index parameters (keys, nodes, etc. - see art0027), but was rather surprised that the number of keys per node has relatively few influence on the performance. Theoretically it should be more interesting at huge databases. As I wrote in the last article and as you can see in the chart bellow, much bigger influence on the performance had the reducing of the index size.

Though the manipulating of the binary tree architecture was not especially successful, thanks to the testing I discovered some very interesting facts about Miva database commands. One of the most surprising was the discovery that MvGO is slower than MvFIND!

top

Influence of the index structure on the performance

Explanation:

• Index type - structure of the index: number of keys per node / size of a node in bytes / size of a key in bytes. Default is 8/1024/108 (second column)
• Index size - total size of the index file resulting from the index structure
• Tag results - average time of a single command execution (in miliseconds or seconds).

MvGO is about 3.5 times faster on databases without any opened index, but still just about as slow as MvSKIP that is just slightly faster than on an indexed database. This is quite surprising fact. MvGO should be, on my mind about 100-1000 times faster than it is regardless if an index is opened or not. MvSKIP on a plain database should be also flashing fast - the small difference to MvSKIP on an indexed database is surprising too. Only MvFILTER seems to take more advantage of the plain database (35 times faster), but in fact it is also surprising - MvFILTER should perform approximately same in all three cases. See the article about patching the Miva engine for the confirmation of this theory.

top

On the first look it is indeed a very surprising fact. MvGO, is a direct access command that accesses (better told should access) a record in a database in a very simple and fast manner - direct file access to an offset equal to the record number (recno) multiplied the record size. Normally it should be flashing fast, but it is not the case of MvGO - it is up to 50% slower than MvFIND at the database I tested (30,000 records). The difference would be bigger at smaller databases and vice versa. The reason is that when executing a MvGO command, Miva not only sets the file pointer to the desired offset, but it also automatically executes the MvFIND command afterwards. Miva does it to update the pointer showing the current position of the record in the index file(s). The index position is necessary only if you want to use MvSKIP afterwards and even in such case it could be avoided if properly programmed.

MvGO is about three times faster with the same database if you do not open any index and it could be much quicker if the MvGO routine was optimized.

top

I removed this paragraph, because it is better explained in another article. I also realized that some of the previous facts I claimed here are not exactly as I originally thought. See the explanation in Speeding up MvFILTER.

top

MvSKIP has also great reserves. If skipping a single record with MvSKIP ROW=1 is still about 3 times faster than MvFIND, it gets proportionally slower with the number of rows to skip. At the test database MvSKIP ROW=1 took in average about 0.4 millisecond, MvSKIP ROW=20000 was longer than 2 seconds. It looks that MvSKIP does not use at all the advantage of horizontal traversing of the binary tree. This advantage is the higher the higher is the number of keys per node. And because I did not see any important influence of the tree structure on the performance I assume that Miva, when skipping from one record to the next one, does not try to reach the neighbor record with simple horizontal shift within the node, but it restarts a search from the super ordered (or even root) node. Changing the algorithm or even better the whole index architecture could increase the speed dramatically.

top

The slowest database commands are MvPACK, MvMAKEINDEX and MvREINDEX. Reindex of the test database with default index parameters took about 30 seconds. The other two commands would be approximately as slow. Change to modern index architecture would be the best solution in this case. MvFIND is relatively fast, but just because there is nothing to compare with - thanks to a wrong design, MvGO is even slower. In usual cases, similarly as MvGO, MvFIND is rarely used in loops and therefore its speed would be sufficient as long as the database is not too big. Changing the index architecture would significantly increase the speed of MvFIND as it would do for all other database functions.

top

The performance of most Miva database functions could be increased in some cases up to 50-100 times with simple changes and code optimizing. Another performance boost could be achieved with better index architecture. Hopefully the design will be improved at the announced version 4.0. For the moment there are at least some hints for faster scripts: avoiding indexes wherever it is possible or trimming the index size.

top

top

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