question

If data belongs together in a single table, I would put it in a single table, rather than spliting it up. It is not slower to use bigger tables, unless you are searching through the table. Then using more tables would be better. But usually, there is a way to avoid doing a search, allowing you to keep your data together with no performance impact.

Let's look at the following line of code:

Variant value = Table("MyTable")[3][4];

This line can be split as follows with no performance impact:

// Part 1
var table = Table("MyTable"); 

// Part 2
var row = table[3];

// Part 3
Variant value = row[4];

Part 1 is the most expensive part for performance. This part searches the Tools/GlobalTables folder using Model.find(). This searches by checking the name of every node until it finds a match. The more tables you have, the longer this search will take.

I say more expensive, but if there are less than 1000 tables, I doubt you'll notice any performance issue with this part, especially if the most frequently used tables are near the top of the list.

Part 2 is very cheap. It is an index lookup. You can think of this as instant. It doesn't matter how many rows there are, either. This operation will never take longer.

Part 3 is the same cost as part 2. It doesn't matter how many columns there are; accessing the nth column is very quick, because it is also an index lookup.

Let's also look at the following line of code:

Variant value = Table("MyTable")["Row3"]["Col4"];

This line is much, much slower than the first example. That is because when passing text instead of a number, you use a search instead of an index lookup. Starting at the first row, the code will each row header for "Row3" until it finds it. For big tables that are accessed frequently, this can have a major performance impact. The same is true for the column lookup; instead of using an index, this line uses the column name. There usually aren't that many column names, so the search isn't bad, but it is still faster to use an index value.

A very common case is to look up a row by a value, like a SKU. In that case, you should use the following code:

Table("MyTable").getRowByKey(keyValue)

By default, this also does a search, looking in the key column (column 1 by default) of every row until it finds the key value. So that is slow, unless you also do two things:

1. Toggle the table as a bundle (also great for memory use) and
2. Right-click the column and choose Index this column (unordered)

This option tells the bundle to memorize which row has which value. Then, when you use getRowByKey(), it's approximately the same speed as using a row number.

In conclusion, the size of the table matters much less than how you access data. Searching is slow, and gets slower the bigger the table gets. Using a number index or creating a value index is very fast, and will be fast regardless of table size.