Author: Ruslan Zasukhin

VP Engineering and New Technology Paradigma Software, Inc

[VStudio][mySQL] Speed improved A LOT! Reading for Constraints

Speed of opening  a mySQL database that has ForeignKeys improved a lot!

Few people have report that Valentina Studio 5.3 for them is very very slow on open of a mySQL database. After some investigation we have found that problem comes from simple, legal official commands to mySQL server, which return list of constraints. We need this to build the list of FK of a Table or even of a Database. mySQL is incredibly slow on this operation.

We have take a look on other tools and with wonder we have found that they all use next trick: They ask via “SHOW CREATE TABLE T” the SQL string of “CREATE TABLE” command for a Table T, then they parse it to extract info about constraints.

We even have start implement this way also, but few days later was found that mySQL loose huge time collecting statistic. We have disable flag ‘innodb_stats_on_metadata’ and bingo! Speed becomes as fast as it should be.

So we have add into Valentina Studio ability to disable this flag. Added checkbox into “Add Bookmark” and into “Connect To” dialogs. On default it is ON.

Do not worry, this flag and this statistic have no many sense. We do not understand, why it is enabled on default in mySQL.

You can right now download and test beta build with this improvement: http://www.valentina-db.com/download/beta/5.4b5 or newer.

New Index Optimization Makes v5.4 up to x3 times faster of v5.3

Description of Bench

Prepare Steps:

  • We have table T1 and field f1 of ULONG type: CREATE TABLE T1(f1 ULONG).
  • We insert N records with values [1 .. N].

Benches:

  • FORWARD – do N searches of values 1, 2, 3, … N
  • REVERSE – do N searches of values N, N-1, … 1
  • 2 Direction – do N searches of random values 1, N, 2, N-1, 3, N-2, … N/2

Each search returns 1 found record.

 

Bench Hardware

MacBook Pro 2.2 GHz Intel Core i7 (Early 2011),
RAM 8GB 1333 MHz,
HDD 500GB 7200 rpm, about 45-50 Mb/sec.

 

Bench using v5.3

=========================================================
Count = 10 000 000 Opt1 = 1 Opt2 = 0
=========================================================

ADD inCount Records = 5 450 ms = 5.45 sec
Build Index f1 = 1 877 ms

Count searches FORWARD = 30 310 ms
Count searches REVERSE   = 28 821 ms
Count searches 2 directions = 85 018 ms

 

Bench using v5.4

=========================================================
Count = 10 000 000 Opt1 = 0 Opt2 = 0
No optimizations. We see pure speed of BinarySearch.
=========================================================

ADD inCount Records = 4 543 ms
Build Index f1 = 1 922 ms

Count searches FORWARD = 124220 ms
Count searches REVERSE = 128918 ms
Count searches 2 directions = 134173 ms

 

=========================================================
Count = 10000000 Opt1 = 1 Opt2 = 0

Enabled Opt1, i.e. Optimisation using the current page.
We see that forward and reverse sequental searches becomes x6 times better.
Random search also becomes better almost x2 times. NOT CLEAR WHY.
TODO: find explain why using debug/profile. Set breakpoint and see when it works.
=========================================================

ADD inCount Records = 4 458 ms
Build Index f1 = 1 823 ms

Count searches FORWARD = 19 849 ms
Count searches REVERSE = 20 848 ms
Count searches 2 directions = 74 005 ms

 

=========================================================
Count = 10000000 Opt1 = 0 Opt2 = 1

This combination have no many sense. Just for interest.
We see that times for all 3 benches becomes good,
but forward and reverse are not so fast as with Opt1.
=========================================================

ADD inCount Records = 4508 ms
Build Index f1 = 1715 ms

Count searches FORWARD = 23 951 ms
Count searches REVERSE = 24 875 ms
Count searches 2 directions = 28 200 ms

 

=========================================================
Count = 10000000 Opt1 = 1 Opt2 = 1

ALL optimizations ENABLED.
We see that again forward and reverse have the best times AND
RANDOM search becomes about x3 times better comparing to Opt1=1 only case.
=========================================================

ADD inCount Records = 4 373 ms
Build Index f1 = 1 729 ms

Count searches FORWARD = 20 950 ms
Count searches REVERSE = 19 002 ms
Count searches 2 directions = 24 981 ms

 

[VStudio] DataEditor now works much faster with huge tables of mySQL and postgreSQL

Up to now there was trouble in Valentina Studio in Data Editor with huge tables working with mySQL or PostgreSQL, because DataEditor did try load all records at once via client-side cursor. For a Valentina Server database was used server-side cursor, for a local Valentina DB or SQLite also there was no troubles.

Interesting to note, that other manager tools as Navicat, Workbench, … implement own data editors, which work with some sub-set of records (e.g. 4000 records) and user must manually click buttons “Next subset”, “PrevSubset”.

In contrast, we have implement load of records subsets in absolutely transparent for user way. So there is no additional buttons and overall it looks more natural.

Available for download starting from 4.5b5: http://www.valentina-db.com/download/beta/4.5b5

[FIX] Valentina for RB/Xojo: Mac + Linux updated the work with pictures

In new Xojo was changed the work with pictures for Mac/Cocoa and Linux.

We have improve plugin “Valentina for REALbasic/Xojo” to support these new formats. This fix affects both normal pictures and preview pictures in Valentina Reports.

[Imp][VStudio] SQL Dump/Load now can work with 220 encodings

Up to now Valentina Studio was able to work only with UTF8/UTF16 dump files.

We have realize that we can easy plug ICU library into Valentina Studio itself, because Valentina engine and SQLite plugin already use ICU. In result, SQL Dump/Load dialogs now are able to work with dump files using any of supported by ICU encodings. This is about 220 encodings of the world.

Available in Valentina Studio v5.3

[NEW][VStudio] QueryBuilder and Diagrams now do Smart Prompting of related Tables/Links

One more absolutely innovative feature! You will not find such in any other GUI DB Manager! 🙂

When you adding existing tables to:

  • a diagram  (i.e. you doing reverse engineering), or
  • a diagram of Query Builder

usually it is hard remember, which Tables are related to just added table. Especially this is hard if you try understand not your own database schema.

Now Valentina Studio is able to help you with this problem. When you select one or even more tables on diagram, the list of tables shows immediately on the top tables related to selected.

Look on the following example. We have drag the first table ‘film’, and now on the left side you can see tables Related to: film.

vs_qb_related_to_1

 

Let’s drag the second table ‘film_actor’ and you see list of tables Related to: film_actor. Only one table in this list.

Very easy, yes?  🙂

Continue reading [NEW][VStudio] QueryBuilder and Diagrams now do Smart Prompting of related Tables/Links

[NEW][VStudio] Copy a Record as CSV/SQL text to clipboard

When you are in DataEditor or SQL Editor Result, you can now select a record and open contextual menu for it. Here you will see new commands

  • Copy Record as CSV
  • Copy Record as SQL

vs_records_copy_menu

Continue reading [NEW][VStudio] Copy a Record as CSV/SQL text to clipboard

[New][VStudio] QueryBuilder now supports VIEWs

We have add support of VIEWs into QueryBuilder.

On the left side now you can see one more tab-panel to see the list of existed views. You can drag and drop a VIEW exactly as Tables, then specify a link(s) between this view and other tables/views as usually. Note that a view have grey color.

 

vs_qb_views