Hi @Ajit Kurane 

According to my understanding Static tables typically have fewer records and are less likely to grow significantly over time. By joining smaller tables first, you can reduce the overall result set early in the query. This means that subsequent joins with larger tables will operate on a smaller intermediate result set, which can reduce the amount of data processed and improve performance.

Regards ,

Rajat

There are lots of factor like what kind of join you are using.

In case of static table and dynamic table it don't make sense as you always have want data from dynamic table. If there will be null value in dynamic table for the static value then what you want to do ? 

exclude those rows ? or include those rows ?

Hi @Ajit Kurane, the sequence of your join conditions is very important but not in the sense that you should use smaller or larger tables first rather as what data is important for you. Let me explain with an example, suppose you have two tables A and B with B having reference attribute of A and there are 20 rows in A and 10 rows in B. Now if you apply A with or without B then you will get 20 rows, but if you apply B with or without A then you will only get 10 rows. So you need to decide the join condition and sequence based on your requirement, taking the previous example, if data of A table is important for you then you apply A with or without B, but if data of B table is important for you then you apply B with or without A which will query less rows making it more optimized. For more details refer to this SQL Joins.

One other optimization factor that OutSystems provide is it does not fetch data of columns that are not being used, refer to this old post of yours for understanding, Aggregate on the dashboard page, but keep in mind when I say not being used it is not limited to UI use, even if you are using an attribute in the backend it will get fetched as it is being used.

Hope this helps.

Thanks
Gitansh Anand

Hi Ajit,

Yes, When you start with smaller tables (which have fewer rows) the database engine can quickly filter and reduce the result set and once that set is smaller, it's easier to join with larger and more dynamic tables because the data that needs to be matched is already reduced. This can result in less processing time and better overall performance.

hope it helps

Hi,

Joining static tables (e.g., lookup/reference tables) first can help filter unnecessary data before joining larger dynamic tables. This can improve performance by reducing the number of records processed in later joins.

Hi Ajit,
You are right. Order of tables in joins matters in terms of performance especially for large dataset. It is recommended to use most selective table (The table with the most restrictive conditions) first in the join order then we should go with ascending order based on the business need and joins.

Thanks,
Sant

• Yes, joining smaller static tables first can potentially optimize query performance, especially if it reduces the number of rows processed in subsequent joins.
• However, the actual performance gain will depend on various factors, including the database system, the specific query, and the data distribution.
• Always analyze the execution plan and test different approaches to find the most efficient solution for your specific scenario.