We have discover that interface of VLink miss Count property to easy ask VLink how much pairs it contains.
Now this property is implemented in the vkernel. It works for all kinds of links (Binary, ObjectPtr and ForeignKey). It is implemented in effective way — only header of index is asked actually.
Adding of this property do minor extension of VSERVER protocol also.
We have realize that if to add simple header to Warnings.log that describes what this log contains, when it should be used, and how it can be disabled for RELEASE then this will help to developers and remove often questions about this.
There are many find-like methods which are able to employ indexes to speed up the searching (FindLike, FindRegEx, FindContains, and so on). Of course, the field must be indexed and search preference is set to kPreferIndexed.
But currently there are two issues when index search may bring a wrong result:
1.Index keeps only first 256 symbols of the value – so rest of the symbols are just ignored in such a search.
2. Index-By-Words index and multi-word searching pattern.
Some of the methods like FindRegEx don’t use an index-search but scan the table instead if any of the above occurred. The rest of the methods are less strict choosing the searching algorithm. Now you will get the warning in such cases and you can switch algorithm to not-index search (kPreferNotIndexed) if it is relevant (less speedy but more accurate). Also you should consider to change index type of such a lengthly fields to the index-by-words.
Warning examples:
“Index search in the first 256 symbols only : FindContains t1:f1”
“Index-By-Words search : FindContains t1:f1”
Today one of user have asked if Valentina supports partial index. Answer is YES OF COURSE, you can easy create a partial index using Table Methods.
Index fragmentation can cause problems with query performance. Indexes therefore need to be occasionally rebuilt. Read the full article in the “Valentina Performance Guide” document.
There is a new property – LogFilesLocation for both server and local.
It defines a folder where the logs should be placed in.
Default folder is “./vlogs”
ObjectPtr field contains RecID values of some “target” table. If you need to change ObjectPtr in order to point another table those values are not valid anymore – so default behavior is to nullify them. But sometime it looks to be too strong. For instance – changing field or link name or on-delete policy should not cause such data zeroing.
There are two ways to keep existed values altering ObjectPtr:
Ivan have implement today MD5() function, which can be used in EXPRESSION of Valentina SQL.
Bart have asked this function to be able produce UNIQUE INDEX for strings/texts bigger of 128 chars. This limit comes from fact that indexes of Valentina use pascal-like storage format for strings, e.g. 256 bytes maximum, 128 for UTF16.
44 Seconds for that 1023 tables DB still not looks good for me, and I have continue search for ways to improve it. And after one more day of work time have go down to 34 sec from original 54.
And this new optimization is even better! It is also made on INDEX-level and affects absolutely ANY index search operation!! Especially we will see effect for searches that return many records into Set.
I see yet two possible steps to optimize speed of open for database.
* [minor] – …
* [major] – start to cache data in the algorithms that read schema from recursive sysItem table.
Target is to make time few seconds only.
One developer have produce Valentina database with 1023 tables. And note us that it takes long time to open it. On my macbook it have take 54 sec.
After some profiling and optimization, time is now 44 sec.
Nice thing here is that optimization was made on INDEX-level for functions that return ArraySets. This means that all other index-based operations may benefit from this.
In 4.5.1 release we have add a new REGEX_REPLACE() function to Valentina Expresions, which can be used in Table Methods, SQL and therefore, in Reports.
Up to now, we was able to do searches with the help of REGEX operator:
… WHERE fld REGEX ‘ab.[cdf]*’
New function brings the awesome power of REGEX to manipulate by strings stored in the database! Now we can not only search using REGEX, but modify strings! You can do that in the
where EXPR is REGEX_REPLACE() function or contains it as a part.
Read details about this function here.
Kem Tekinay have found that FindContains is not fast enough. We was able speed up this place x10 times. Indexed search on table with 7 million rows have go down from 46 sec to 4.6 sec.
You can get this using
Valentina is a perfect choice to work with a big data. It employs vertical storage approach. It means – each column has own virtual file to be stored in. Most of db vendors use a horizontal storage – it stores a table data in record-by-record manner.
There are some vertical storage advantages:
It is well known that Valentina offers to developer a very reach ADK API (about 1000 methods) in contrast to poor API of most dbs (e.g. near to 25 in mySQL).
One of nice feature is the ability to use VCursor class methods to MODIFY records of ServerSide & ReadWrite cursor. But for years we did have limit here: cursor can be ReadWrite only for single table SELECT. In the release 4.5 we remove this limitation! Now you can do UpdateRecord() and DropRecord() for cursors built over INNER JOIN of N tables!
Continue reading VCursor Now Can UpdateRecord() of JOINs !!!
We are happy to introduce, in the release 4.5, the result of our 8 months efforts – Valentina Server 64 bit for 3 platforms (win, mac, linux)!