Category: Valentina KERNEL

SPEED of DB Open for Database with 1000+ Tables: -20 sec

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.

SPEED of DB Open for Database with 1000+ Tables: -10 sec

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.

[NEW] REGEX_REPLACE() function.

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

  • SELECT EXPR, …
  • UPDATE TABLE T SET fld = EXPR

where EXPR is REGEX_REPLACE() function or contains it as a part.

Read details about this function here.

VCursor Now Can UpdateRecord() of JOINs !!!

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 !!!

Internal optimization for views and sub-queries.

In some cases we can avoid copying sub-query result to the temporary table. We can even use sub-query cursor “as is” – so it could be used as a result of the whole query. In other words there  is almost zero overhead against a simple query.

For now such optimization can be applied if a query:

  1. contains only “*” item in the select list clause
  2. has no “where” clause
  3. has no “group by” clause
  4. has no “having” clause
  5. has no “distinct” clause
  6. is not a part of some join operation
  7. has no “order by” clause
  8. has no binding values
  9. sub-query is not a “union/except/intersect” result

Examples:

SELECT * FROM (SELECT * FROM t1 WHERE f1 > 5);

SELECT * FROM (SELECT t1.f1, t2 f1 FROM t1 JOIN t2 ON l1 WHERE t1.f1 > 5 AND t2.f1 <3);

Hierarchical (Recursive) Queries in Valentina SQL

There are three new kind of queries specially designed  for recursive (hierarchical) tables.

Such kind of tables are widely used. It could be human relationships, threaded forums and anything which might be described as tree-like data.

There are few common SQL ways to operate with such structures but all of them are too far from perfect.

Valentina introduces special kind of query which looks much more clear, natural and readable.

Syntax:
vext_recursive_table         -- v4.1
    :   {ANCESTORS | BROTHERS | DESCENDANTS} OF vext_root_objects
        [{TO|ON} LEVEL UINT] USING link_name [WITH {ROOT | ROOTS}]

vext_root_objects
    :   UINT
    |   variable_name
    |   ( search_condition )

Examples:

ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1
BROTHERS OF 4 ON LEVEL 2 USING l1
SELECT * FROM (ANCESTORS OF (RecID IN(4,5)) ON LEVEL 1 USING l1)  WHERE f1 = 'Branch2'

See also:

* WIKI: Valentina SQL Reference: Hierarchical (Recursive) Queries for description of exact syntax of these commands and examples.

* WIKI: Article Hierarchical (Recursive) Queries in Valentina SQL

Using of “Instead-of” triggers

Such kind of trigger may be applied to views only. It could be used for insert, update and delete operations as usual.

Example:

CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t2 VALUES( 1 );
END ;

“trigger1” will be fired on attempt to insert record in the view “v1”.
Starting from v 4.1 you may use ‘NEW’ and ‘OLD’ descriptors within this kind of trigger.
Views are always read-only – so using the trigger is a nature way to overcome this restriction.

Example:

CREATE TABLE t1 (f1 LONG, f2 TEXT(1024));
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE TRIGGER trigger1
INSTEAD OF INSERT ON v1
FOR EACH ROW
BEGIN
INSERT INTO t1 VALUES( NEW.f1, NEW.f2 );
END ;

—
INSERT INTO v1 VALUES ( 1, ‘abc’);

SELECT * FROM v1;
————-
f1 f2
————-
1 abc
————-

Improved work with TIFF format for VPicture field.

On request of Marc Schmitt, Kirill have improve work with TIFF format.

1) V4MD 4.0 was the single ADK, which did not allow specify format of picture in the VPicture.WriteAs() method. This problem fixed and now its is possible specify format as #kJPG, #kTIFF

2) But Marc did want more: he want to have SEVERAL compression methods for TIFF. We have look around and have found that TIFF compression do not use CompressionRate (1..100) as do JPG, instead it can specify kind of compression.

To not change existed API, we have decide to use parameter “INT inCompressionRate” to send compression kind also for TIFF.

3) One more fix was made — in the KERNEL itself — to pass compression kind into PAINTLIB.

4) For all other ADKs e.g. V4RB now also possible to use TIFF with compression, it needs just specify kind of compression in the 3d parameter. See WIKI docs for numeric constants. We are lazy so far to provide these constants into each Valentina ADK. So it will be good idea if you in your code will make self symbolic constant around number. Future we will provide such constants also self.