Performance when using large tables

[Jenner]

Hi Ryan,

Please explain how to use lookup Field pointing to large datasets…

E.g: In an order table, there is a customer field, and my customer table has 3000 rows.

If there any tutorial or help about this issue, i d like to look before your live.

Thanks and sorry for my english.

Obrigado! Saudações do Brasil!

[p.colbert]

Good morning, @Jenner, and welcome to Anvil’s Community Forum!

For help (documentation), this might be a good starting point: Links Between Tables.

You can find many tutorials and examples here: Learn.

Also, feel free to search this Forum, and to create new questions in the Forum’s Q&A.

[Jenner]

I will take a good look again.
But my question is not about how to make parent-child relations…
Is about performance when using large tables (my customer table has 3000 records);
If I use Data Tables, all the records are uploaded at the beginning, right?
Using large amount of data, this spends minutes, instead of seconds, as desired.

Maybe this post reports the same issue, and I am not be able to find the solution.

Slow data loading from data table to app

I have over 20,000 rows in a data table. I need to be able to filter them based on user selected criteria, or display all of them if no criteria is selected. Using the to do list method of building a second form and displaying each row in a for loop takes 6 minutes to load. Any suggestions on how I can get instant display updates (or at least faster, like seconds rather than minutes)?

Thanks

[p.colbert]

@Jenner

explain how to use lookup Field

How to use a lookup (Link) column does not depend on the size of the table it is pointing to. The value in a column points to one, or to a limited number of rows. Because each link points directly to a row, retrieving the row will be about the same speed no matter how big or small the table it resides in.

@Jenner

If I use Data Tables, all the records are uploaded at the beginning, right?

No. Not the way I use my Data Tables. In my app, no one has to transfer large numbers of records from place to place to get things done. In my app:

• Records (table rows) are created on the Server, and stay there, for weeks, months, or years, as needed.
• When a User asks to see some of their records, the Server fetches only those specific rows from the database, and sends them to the Client. The table might have 10,000 rows, but if only 5 are needed, only 5 are found and copied.

You’re assuming a lot of details; a particular pattern of use. Can you tell us more about it?

1. “uploaded” from where?
2. To where?
3. “at the beginning” of what?

(Sorry about the “upload/to/from” questions, but I often see and hear “upload” and “download” confused for each other.)

[Jenner]

@p.colbert

Records (table rows) are created on the Server, and stay there, for weeks, months, or years, as needed.

Things have become clearer now.

Anyway, answering your questions

1. from database or anvil.server
2. to the browser
3. beginning of session (in order to populate the dropdown that refers to lookup fields)

One last question - I promise:
This approach is the same when using external databases?

[p.colbert]

There is no limit on the number of questions, Jenner! However, you should probably post each new topic in the “Q&A” section of the Forum.

@Jenner

This approach is the same when using external databases?

Yes. The approach is the same. For whatever task the browser will be doing, the browser should ask for only the data needed for that task.

For example, your Tables can have any number of columns. Any number of values per row. But a DropDown will have at most two values per row.

So, if the immediate task is only to fill a DropDown. then you don’t need to send all the columns of every row. You can write code, to run on the Server, to return only what the DropDown needs.

The general idea is, try to arrange things so that the browser can ask for small amounts of information. That way, the browser (user) spends less time waiting.

Of course, if any of that information will be needed in later tasks, the browser can keep a temporary copy. This will last until the session times out, or until the user closes the browser tab. Any information that needs to persist longer than that, should be sent back to the Server, and recorded in your database.

Not every problem can be solved this way. Some browser-side tasks may require more data. It depends on what you’re trying to accomplish, and how.

You can combine requests. If you know you will be asking for N pieces of information, you could ask for each separate value once, but it will be quicker to ask for them all at once.

It can take some practice, and some measurement, to find a good balance for your app.

[Tony.Nguyen]

Hi @Jenner,

Without code, it’s quite hard to find out why it takes minutes to show data.

There surely a way to make it seconds.