Huge increase in performance with Virtual Tables (aka Views)

[Dean D. Babic]

Hi all

First a bit of background. The VT (Virtual Tables) were
quite under the radar since used only sparingly. On Demo,

the VT is used to send Email to Customers. Which does
not really show the full VT potential. 

IMO, the VT can be used as DB Views. 

Let's say the DB view is only for SELECT SQL.

Hence, we can build the Server Module with any SQL we need, and pass that to VT
Client Module to show the table.
However, this JS is where the performance issue is.

First, I installed Visual Code for linux from here (thanks Ankit Mittal):
https://code.visualstudio.com/docs/setup/linux

to get the nice formatting, since Jam Ace is not great for c/p.

Consider this code which gets the data from Server Module:

function on_after_open(item) {

item.alert('Working!');

item.server('get_records', function(records) {

records.forEach(function(rec) {

item.append();

item.id.value = rec.product_id;

item.product_name.value = rec.product_name;

item.target_level.value = rec.target_level;

item.quantity_on_hold.value = rec.quantity_on_hold;

item.quantity_on_order.value = rec.quantity_on_order;

item.quantity_on_back_order.value = rec.quantity_on_back_order;

item.quantity_on_hand.value = rec.quantity_on_hand;

item.quantity_purchased.value = rec.quantity_purchased;

item.quantity_sold.value = rec.quantity_sold;

item.post();

});

});

}

Looks ok, right?

 

Wrong. The performance is just not there, it is super slow. 

One would opt for DataTables.js (DT), to get the performance up. But that

just complicates stuff, it is completely different technology, even tho it really  

works fabulously with Jam. 

Now consider this code, provided by Master Yoda himself (AY):

function on_after_open(item) {

item.alert('Working!');

item.server('get_records', function(records) {

item.disable_controls();

try {

records.forEach(function(rec) {

item.append();

item.id.value = rec.product_id;

item.product_name.value = rec.product_name;

item.target_level.value = rec.target_level;

item.quantity_on_hold.value = rec.quantity_on_hold;

item.quantity_on_order.value = rec.quantity_on_order;

item.quantity_on_back_order.value = rec.quantity_on_back_order;

item.quantity_on_hand.value = rec.quantity_on_hand;

item.quantity_purchased.value = rec.quantity_purchased;

item.quantity_sold.value = rec.quantity_sold;

item.post();

});

item.first();

}

finally {

item.enable_controls();

}

});

}

And that, my friends, is the Holly Grail of VT. Instant performance boost!

So lets see what the Master Yoda says about it:

The idea behind these changes is that the controls (DOM elements that display the data) in Jam.py are data-aware.

Every time a record is added, deleted or modified these changes are reflected by controls.

It makes the filling of a table by adding records ineffective, every change of every field and so on must be updated.

So in the code above I turned off this data-awareness by the disable_controls method and after records are added

turned it on with enable_controls.

The tables in Jam.py use virtual scrolling and display only data that is visible and are very fast.

I also executed the first method to move to the first record. 

I created a small example with two tables, one VT and one DT, showing the same select SQL:

https://drive.google.com/file/d/1uMz2i-2RcNYsJwLGcBblxjXrFcQfuq0h/view?usp=share_link

Replace the code in question for VT and see the performance boost.

It is really fast. 

Enjoy

D.

[Moh. Sarip Hidayat]

Hi Dean,

I am using this approach to fill in a VT. It is working as expected. However, I noticed that the resulting view form isn't scrollable.

Here is my client module:
function on_after_open(item) {
    item.disable_controls();
    let order_rows = item.server('get_order_rows');
    order_rows.forEach(function(v){
        item.append();
        item.id.value = v.id;
        item.master_rec_id.value = v.master_rec_id;
        item.sku.value = v.sku;
        item.title.value = v.title;
        item.colours.value = v.colours;
        item.post();
    });
    item.first();
    item.enable_controls();
}

And here is my server module:

def get_order_rows(item):
    rows = []
    recs = item.task.execute_select('''
        SELECT a.id, a.master_rec_id, b.sku, b.title, a.colours
        FROM order_ordertable a
        INNER JOIN order_products b
            ON a.sku = b.id
        INNER JOIN order_orders c
            ON a.master_rec_id = c.id
                AND c.completed = false
        WHERE a.deleted = 0 and a.start_date IS NULL and a.machine IS NULL;
    ''')
    for v in recs:
        rows.append({
            'id': v[0],
            'master_rec_id': v[1],
            'sku': v[2],
            'title': v[3],
            'colours': v[4]
        })
    return rows

If you can help to make the scroll works that would be great.

Thank you.

Best regards,

Moh. Sarip Hidayat

[Moh. Sarip Hidayat]

Hi Dean,

After checking your attached samples, I manage to make the scrolling works by adding this part to my client module:

function on_view_form_created(item) {
item.paginate = false;
}

I then realize what I should ask was how to make the pagination works as normal item would.

If that was possible, please let me know. Otherwise, I'll be content with just scrolling.

Thank you

[Dean D. Babic]

Good to hear.

AY never advised on scrolling, I think it's not possible with VT by the default.

Unless the SQL is accommodated with some pagination and buttons actions.

For your example, I would look into Pivot.js.

The Datatables are not bad for reporting too. They do provide paginating
and search.  Just a few ideas tho. 

Regards

D.