Stepping Through 1k+ records smoothly ?

Classic Servoy
1

Hi Folks - perhaps someone can help me with understanding the concept of scrolling through thousands of records?

Past incarnations of our app (MS Access + MS VS C#) offer the user the ability to scroll through any number of records he has selected through a filter - or indeed the entire Db if he chooses. These can be in the order of 20k Records. In most cases even after filtering to a selection he will liklely have 3 - 400 records through which he may need / want to step record by record, or at least scroll and select individual records to work on / edit etc.

Problem I have right now is when we scroll though records (by selecting them from a table one by one) we reach a point (and I’m assuming its at 200 records) where the record selection slows down and in fact it takes (pretty much exactly) 6 second for the record to be selected.

So, for example - if I scroll through a table (not touching each record) and select say 560th record, it will take 6 seconds for that row to show selected, and stepping back or forward a row takes another 6 seconds etc. Its only when I scroll back in to the first 200 records I can select smoothly again.

I’ve tried this in a test solution with nothing but a bare table being scrolled / selected, and its the same as in my dev-solution so I’m pretty sure its a product of the 200 record foundset. Works the same even with just 1 column in the form.

I like the idea of the limited foundset as a bandwidth saver, but its causing me significant challenges in this solution, since the user MUST be able to scroll and select easly and quickly.

Q. Is there a technique we can employ to speed-up these selections, or something we could do to trigget the correct block of 200 records when one is selected?

I’d appreciate feedback on this to help me understand this concept! I guess others must have solutions that demand the user scroll through many records?

BTW: I have quite a few stored and unstored calculations on the table in question, though since they appear not to interfere with the record selection in the first 200 or so rows - I can’t believe they are causing the selection slowdown elsewhere in the foundset!

2

This is a question that comes up frequently, perhaps with variations … and one that i’ll be contemplating soon in a solution i’m building.

TIP: in a Google search field type:
“200” site:www.servoy.com/forum/

Anyhow, often the response to this question is another question, asking why users would want to scroll through 20,000 rows. “Because that’s how they had to do it in our old application” doesn’t count as an answer – we’re talking usability here, not familiar pain ;-)

I think about the times I might find myself scrolling through anything more than 200 rows of data (in FMP for example), and identify why. It comes down to two answers:

  1. As a developer or advanced knowledge worker, I am doing some sort of data analysis. For example, I select all rows that have been created during a 6-month period in order to assess how the size of the table’s data might be growing. Then I might sort the rows by some column and scroll quickly, sort of “eyeballing” for values like “createdBy” or “status”. In this instance, certainly using a SQL browsing tool is preferable and something like Servoy Client is not the appropriate tool at all.

  2. As a user, I construct a very loose query that results in a large record set – say, all Orders ever placed for a particular product. Why would I ever need to see all orders for that product? If what I, as a user, needed was an overall count, that should be made available by the developer as a “# of Units sold” figure in the product list. If, on the other hand, I really wanted to see only the orders for that product sold to a particular customer in the past 3 months, scrolling through a huge list of all sales of that product to all customers in all years is the absolutely least efficient way to go about this.

The answer to this scenario #2 is to rethink the interaction design. You may well have a real business case for displaying that many rows at once (and I’d love to hear it), in which case there are ways to handle it (see Google search above), but chances are if you start with a “clear slate” and think about large data stores you interact with (Amazon, for instance) you’ll find a completely different approach you prefer anyhow. For example, have the user first select a Category of some sort, then a Subcategory, then enter the final criteria. “Customer X, Product Y, sales for last 3 months”.

There is very little benefit in scrolling up and down through even 1K records, never mind 20K, in an attempt to locate something. As a former FMP developer, I am beginning to understand that the only reason I used to present users with such long lists to scroll through was because (1) this used to be the way users interacted with data and having come to computers in 1983 my approach could use a little updating, lol, and (2) I learned to present such UIs more because of the limitations of the tools I was using than because it was any kind of preferable design choice. After enough years of doing this it seemed “normal” to want to be able to scroll around in big lists. But when I think of my dozens of successful data interactions every day on various Web sites, I never ever ever need to scroll around through large lists and I almost always find exactly what I need.

So I’m curious to know, in the scenario where you have a user scrolling through 1K or 20K records, what you guess their success rate is in finding (effciently) what they are looking for, and whether perhaps it would be better to provide them with a completely different UI approach to get at what they really need to see (and as a nice side effect boost the responsiveness of the application you give them).

So far for me, I’ve seen that, in every scenario in which I used to leave users in a long long list of records, designing a nice query UI or filter mechanism that supports the user in finding what they actually need to see, is the preferable approach, not a compromise of any kind.

kazar

3

kazar:
This is a question that comes up frequently, perhaps with variations … and one that i’ll be contemplating soon in a solution i’m building.

So far for me, I’ve seen that, in every scenario in which I used to leave users in a long long list of records, designing a nice query UI or filter mechanism that supports the user in finding what they actually need to see, is the preferable approach, not a compromise of any kind.

kazar

I take your point Kazar - but you really do need to take into account the way YOUR users interact with your app. It’s all well and good designing a nice UI from your perspective - but the user is the ultimate arbiter of what works and what does not! Believe me we have been there - this app has been in the field for more than 20 years - with many attempts to make the UI slicker and more user friendly. To the point where we now ‘force’ the user to adopt a filtering process, which as I mentioned, cuts down returned records to perhaps between 1 and 300. He has to bypass the filter process overtly to see the entire Db.

There are many times when the user needs to step through those records one at a time and either view the record detail / edit it or simply scroll through the foundset and quickly grab the particular record he’s looking for.

Here’s just two simple scenario’s where this might happen:

User filters to all of the records relating to 2" pipework, on a specific Plant Structure, at elevation 22000 and made from Stainless Steel. Say 400 records. User will raise a table of these records and quickly scroll down looking for pattern matching in a number of the fields. This is a regular error check that Engineers need to carry out as a QC process where Junior Engineers have updated the records whilst inspecting.

Another scroll or ‘scan’ takes place when an Engineer has already filtered to a foundset - which will then be used as the basis for a Budget calculation. This needs to be super accurate so they will often want to scan the table looking for inconsistency in several columns at once.

Not all solutions need this and certainly all the Servoy sample solutions are really simplistic when it comes to this type of record viewing - and in the main may not benefit from it.

But - just on the offchance - do you have any suggestion on how to allow smooth viewing of 200+ records in a table as I suggested.

I mentioned I have a good number of calculations on the table in question and I’m seriously considering moving them out of the table and into code - I’m more and more suspecting that the calcs are causing this slow-down.

4

I don’t have the experience to suggest how to smooth out the scroll, although perhaps hints in previous threads on this topic might provide you with some clues.

You do have a business case for users being able to see more than 200 records in a list. I wonder whether they really need to see them as records or whether it might be much more efficient to display the FS in the form of an html table containing the entire FS? Just a thought. In your scenario #1 you could provide a “Flag” link in one column of the table so that Engineers could flag (and then retrieve as a small FS) the records whose pattern indicates some potential problem or error. In your scenario #2, provide the entire FS listed inside an html table, and a couple of buttons for Proceed (i.e., to perform budgetary calcs) and Cancel… I don’t know whether this idea specifically would work, but just opening up the possiblity of allowing them to see what they need to see in some form other than as a list view of records.

With regard to the delay in loading, moving the unstored calcs out might help (“might” means “I really don’t know”) but easy enough to test by creating a copy of the table in question without any calcs and scrolling through a large FS. Let us know how it goes? thanks. Please understand I would not presume to say I know “the answer” to all this. I just happen to be in the same process of working out new modes of user/data interaction.

kazar

5

if you want smooth scrolling (i guess that you mean with that that you really want the scrollbars to be filled with X (way larger then 200) records)?

what you can do is after search just touch 200 and then 400…
(i really wont go that far to be in the 10K or more because that really seems a waste to me)

6

JOhan,

I think Kahuna has an other problem than you discribe:

So, for example - if I scroll through a table (not touching each record) and select say 560th record, it will take 6 seconds for that row to show selected, and stepping back or forward a row takes another 6 seconds etc. Its only when I scroll back in to the first 200 records I can select smoothly again.

So in Kahuna case, the next 200-400 records are allready loaded, it’s just that when Kahuna selects the 560th record, this takes up 6 seconds.

7

jcompagner:
if you want smooth scrolling (i guess that you mean with that that you really want the scrollbars to be filled with X (way larger then 200) records)?

what you can do is after search just touch 200 and then 400…
(i really wont go that far to be in the 10K or more because that really seems a waste to me)

Johan - I can already fill the scroll bars - probably a bad description on my part. I can scroll down to any record using the bars, but when I select a record outside the 200th then it takes 6 seconds to go to that record.

I have tested with the same table without the calculations and this is not then an issue - its obviously the calcs that are causing the delay.

Why would the calcs be updated just by selecting a record when no data has changed, and why would they be OK in the first 200 records and not others? Is there a way to have table calcs update ONLY when data has changed (I know I can code to do that but like the auto mechanism of table calcs)?

It seems that if they create such a performance hit (they are very simple calcs - though there are 25 of them) then they are pretty much unusable for what they were designed for - or have I misconstrewed their use?

8

if you already have touched 200 and then 400 (to have at least 560 also loaded)

and then you set the selection on 1 to begin

Then there is NO difference in servoy what so ever if you then select record 160 or 360 or 560

they are all the same.

because if you just touched 200 and 400 then only records around that are completely loaded and 160 or 360 or 560 arent

so jumping to them does exactly the same thing. loading of a bunch of records around that selection you selected.

calcs are not threated any different for the index they are on. Maybe they are touching different relations ? and those relations are also all loaded in when you jump to 560 ?

Just monitor the performance tab in the admin pages. Clear everything and then what happens if you jump to 160 or if you jump to 560

9

jcompagner:
if you already have touched 200 and then 400 (to have at least 560 also loaded)

and then you set the selection on 1 to begin

Then there is NO difference in servoy what so ever if you then select record 160 or 360 or 560

they are all the same.

because if you just touched 200 and 400 then only records around that are completely loaded and 160 or 360 or 560 arent

so jumping to them does exactly the same thing. loading of a bunch of records around that selection you selected.

calcs are not threated any different for the index they are on. Maybe they are touching different relations ? and those relations are also all loaded in when you jump to 560 ?

Just monitor the performance tab in the admin pages. Clear everything and then what happens if you jump to 160 or if you jump to 560

Some of this languaging confuses me Johan - you say “if you already have touched 200 and then 400 (to have at least 560 also loaded)” but I have not actually touched thjose records - simply rather loaded them in a table view form - is that touching them? Any in my test case there are 12k of those records in that table view.

That table view loads instantly and I can scroll all the way down the table to the last record very quickly.

Its when I attemp to ‘select’ a record (which was what I understood the term touching the record to mean) that I get the delay if its greater than the 200 bouandary.

Checking the performance data - and stepping directly to a record + 200 I get the visible delay on the form selection, but the query performance does not seem to show whats happening - e.g. it takes 6+ seconds to select the record but in the query performance the total time involved is less than 900 ms. And it appears that the update query for the calcs is 00ms. There are 3 relations involved in queries.

This is even more confusing to be frank Johan!

10

Johna -taking your advice and attempting to analyse the performance data I noticed something really strange.

  1. I had 2 relations being queried in the original data, and after deleting the dataprovider on the main form which used this relation (just a direct PK to FK) I discovered that the 6 second record look-up dropped to 3 seconds - still too slow of course.

  2. Now - reviweing performance data I see that (after clearing the data and setting the scroll bar down to say 400th record) when that record is selected the follwoing query is run 6 times:

Total Time (mm:ss:ms) Count Avg Time (mm:ss:ms) Type Action
00:00:265 6 00:00:044 Find select cd_id, fc_id from component_detail order by cd_id asc, fc_id asc limit ?

I cant see why this should be the case for 1 record selection? Its the first query in the performance data table. All other queries are run once in the same performance test?

The calcs take 00ms so I’m more convinced now that they are not causing the slow process.

  1. Once I have selected a record (or say selected 5 or 6 consequtively) I can reselect back and forth easily and quickly between them. The process is not slowed?

I really would appreciate your feedback on this Johan as its a real show stopper for us at the moment. I’m sure its my lack of understanding but as you know figuring out how and when Servoy does stuff is not a simple task!

11

now i am bit confused. so my first post to this thread was correct.

if you just do a find on a foundset and nothing more

servoy does 2 queries first a pk query for the first 200 pks.

then a data query for the first 30 records.

so after that what you have in memory are 200 pks and for 30 records (1 to 30) the data.

if you then start scrolling to 31 we do the data query again to get the next 30 rows (the data)
this goes on until the 200

then if you hit 201 we do first a pk query for the next 201 → 400 rows.

then we have in mem 400 pks and some row data, if your selection is then 201 we do have 400 pks in mem but not all the 230 records, we clean rows to preserve memory, so if you go back to row 1 it could be that the data query comes again.

My suggestion for smooth scrolling in the first post was

after the search select first row 200 (then we will query the next 200 pks so you have 400) then select 400 (then you have 600 in mem)
then select the first again

Now in the tableview your scrollbar is already smaller (because we already know that your foundset has at least 600 rows in mem)

then if you do your selection, until 600, the only thing for that foundset would be data queries for 30 rows. around your selection.

How do you do your selection? How do you select 560? just by scrolling to it in the ui? or in the code?

if you really scroll through the tableview so you really go over all the records 1 by one then yes that data query is run at least 6 times if you go to 400
I would even expect if you are completely clean and you start at row 1 and you go to row 400 that in total that data query is done 400/30 = +/-13 times…

12

jcompagner:
now i am bit confused. so my first post to this thread was correct.

Perhaps it was Johan - perhaps I’m being a bit dumb today - but none of this explanation indicates why it should take 3 seconds to highlight a row in a table form? I scroll to say record 350 using the scroll bars and then select it using a mouse. From the point of clicking the mouse on the record there is a delay of at least 3 seconds to the row being selected and highlighted in the UI and this is similar if I step through the table using the ‘next’ or ‘down’ button. This is all done in the UI not in code.

The idea that Servoy selects the PKs for the first 200 records and only brings down the data for 30 is news to me I have to say! I was under the impression that the 200 records including data were loaded in memory!

jcompagner:
if you really scroll through the tableview so you really go over all the records 1 by one then yes that data query is run at least 6 times if you go to 400
I would even expect if you are completely clean and you start at row 1 and you go to row 400 that in total that data query is done 400/30 = +/-13 times…

It seems that if I just go directly to any record greater than say 200 and select it with the mouse that query is run 6 times!

Can you suggest Johan - where I might look to figure out why just selecting these records should take 3+ seconds using this UI technique?

13

is the data already there?
do you display calculations or related data in that row that is not yet displayed?

do you use a bgcolor calc to color the rows?

14

jcompagner:
is the data already there?
do you display calculations or related data in that row that is not yet displayed?

do you use a bgcolor calc to color the rows?

Johan - thanks for taking the time.

jcompagner:
is the data already there?

Yes all the data is in the table - though its displaying only 2 columns itself - and in related Tab Panels the remainder of the row is displayed.

jcompagner:
do you display calculations or related data in that row that is not yet displayed?

Yes - some of the tabs have the calculations as described above displayed (the tab is not necessarily shown when cursoring through the table though.

jcompagner:
do you use a bgcolor calc to color the rows?

Yes - is that a problem? I tried the form without bgcolor and there was no difference in performance.

15

ahh ok there are related stuff that you also show on that form that depends on the selection in the tableview?

if you for example scroll to the record you want to select in the tableview. and let is show (dont select it yet) then clear the performance data in the admin page
then select the row (that was already shown and loaded)

what queries are happening then?

bgcolor could be a problem on large tableviews (with loads of columns) and you do there something heavy (for example you base your color on a related value)

16

jcompagner:
if you for example scroll to the record you want to select in the tableview. and let is show (dont select it yet) then clear the performance data in the admin pagethen select the row (that was already shown and loaded)

what queries are happening then?)

Not sure your meaning Johan - if I cursor to the record in the table so the row is shown in the related form I’ve obviously already selected the row?? In that case there is no query fired if I then select the record again in the table???

If you mean just use the scroll bar to run down the table without selecting a record - (then obviously its not displaying the related data because no record is selected) then I get the attached queries fired.

But still the total time from selection to show is around 3 + seconds - the bgcolor is just basic alternate row highlight.

PerfData.jpg
PerfData.jpg1021×932 292 KB

17

what is now that output exactly?

is that the output that is just the selection of the record in the tableview and nothing more or not?

because for just the selection there are quite a lot of queries also the 6 component_detail queries i guess thats the tableview right?
i dont expect those in the output if you just select a record in that tableview. (that tableview shouldnt load any thing anymore thats should already be done)

18

jcompagner:
what is now that output exactly?

is that the output that is just the selection of the record in the tableview and nothing more or not?

because for just the selection there are quite a lot of queries also the 6 component_detail queries i guess thats the tableview right?
i dont expect those in the output if you just select a record in that tableview. (that tableview shouldnt load any thing anymore thats should already be done)

Let me clarify Johan.

I have scrolled down (not touching individul records - just using the scroll bar with a mouse - I cleared the performance data after the scroll) to show a record deep in the table. This is the performance data when I select a record with the mouse, the detail page also shows the record details after selection.

The 6 queries are the table view yes!

If I just scroll to a deep record - I’m not actually touching the records am I? So nothing will be queried at all until I select a record with the mouse or by using the down cursor button. So I’m confused when you say it should already be loaded?

19

no but your tableview is already loaded. You see the data right?

so i dont expect to see anything related to that tableview to be loaded (so that database table)

or if you have relations to itself and you show that in other stuff then that relation will load data.

So if the dump you made was only by 1 click and that is select a record from the tableview
then thats quite a bit of data. I dont know how many related stuff you show somewhere
What are all those distinct queries on the component_detail table? Doesnt look servoy stuff to me.

also i dont understand why there are 10 related qires for the comp_area_link_table (i guess this is 1 relation deep from the tableview record that you select)

So currently i dont know what happens exactly at your place. make a sample solution with data and create a case with it so that we can look at it

20

jcompagner:
no but your tableview is already loaded. You see the data right?

so i dont expect to see anything related to that tableview to be loaded (so that database table)

or if you have relations to itself and you show that in other stuff then that relation will load data.

So if the dump you made was only by 1 click and that is select a record from the tableview
then thats quite a bit of data. I dont know how many related stuff you show somewhere
What are all those distinct queries on the component_detail table? Doesnt look servoy stuff to me.

also i dont understand why there are 10 related qires for the comp_area_link_table (i guess this is 1 relation deep from the tableview record that you select)

So currently i dont know what happens exactly at your place. make a sample solution with data and create a case with it so that we can look at it

Thanks Johan - will do

Case submitted No 232410