Datatable navigation becomes very slow on last page of big database

Webix JS
1

Using a datatable with serverside dynamic loading, I have noted that on the last page the navigation become very slow with keyboard arrows keys.
The Oracle table has over 2.000.000 of records. I have investigated with Chrome debugger and I think the problem is on the db store logic. For your information, the web page does not send any data request to the server when I meet the strange behavior

2

The problem may be on the DB size. Which kind of SQL you are using to fetch the records? One of effective solution for Oracle will be something like next

SELECT * FROM ( select /*+ FIRST_ROWS(".$count.")*/temp_table.*, 
  ROWNUM rnum FROM (".$sql.") temp_table where ROWNUM <= ".($count+$start)." ) 
where rnum >".$start;

Without optimization, DB engine will need to fetch all 2 000 000 rows to select last 10 rows.

P.S. There may be a better solution, I’m not the Oracle expert, but the above line must be effective enough.

3

Yes, you have read my mind, I always use the Oracle db pagination logic you have described since Tom Kyte wrote his article on AskTom (http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html) and I can tell you it’s GREAT (only 10 ms to pick up 100 rows each request on a remote server). It’s very strange, if I start navigation from the first page the datatable view renders into the 10ms but if I start from the last and I click on the last item and try to use the keyboard arrows key o pgup, the rendering is very slow. I’m using the profile tool of Chrome to understand what is going on.

4

Here are the most cpu consuming function since I move selectionwith arrows key

808.0 ms 90.88 % 808.0 ms 90.88 % webix.js:24 webix.PowerArray.find

…

12.2 ms 1.38 % 820.2 ms 92.25 % webix.js:403 webix.DataStore.getIndexById

…

0.3 ms 0.04 % 825.6 ms 92.86 % webix.js:130 webix.UIManager.qb

…

0.2 ms 0.03 % 824.6 ms 92.74 % webix.js:876 webix.extend.moveSelection

5

Hi,

Yep, it seems the problem is in the client side code. It seems just getting the ID of the row takes a lot of time in your case.

I will send you the possible patch by PM, later today.

6

Perfect! Maksim, your patch works like a charms.
Thank you very much.