When using the live Creator, what is the trade off of using just views over tables, when designing an API that utilises sub resources, from a performance point of view. i.e. views loose relationships (foreign keys) and indexes ... what impact does this have if any? Currently we experiencing very poor performances, but its due to how CA handles the multi level API data structures. In addition filtering is handled very poorly when filtering by a sub resource where the primary resource doesn't filter as well - so these all seem Independent from each other and results in multiple DB calls being made to create 1 JSON object, which is inefficient.
Live API Creator exposes views as endpoints because there are times data governance policies requires DBAs to expose tables as Views to mask columns. They can be hidden under Security, if not required.
In terms of nested table resources, Live API Creator actually optimizes the calls. If you are returning customers and their orders, Live API Creator actually makes one single query to get the orders for all the customers, and sprinkles the orders back to the respective customers as it prepares the JSON response. E.g. select * from orders where custId in (c1, c2, ..., c20) assuming Page Size is 20. See logs for how the queries are formed and called.
If a virtual relationship is being used to link the parent and child tables, you want to link them through foreign keys or columns with indexes.
As for the last comment on:"In addition filtering is handled very poorly when filtering by a sub resource where the primary resource doesn't filter as well" -- please provide a specific example so that we can provide further guidance.
Just to add a bit to Wai-Yin's response -- Live API Creator will never (by definition) be faster than the database, so if performance is critical, and you can retrieve the desired data using a view, that's always going to be the fastest possible way. In many cases, LAC may be able to get close to that level of performance, but it'll never be faster.
On the other hand, that also means that you will not get some of LAC's features, like fine-grained security. Also, most views are flat -- they will return a table of objects, not a complex document (unless the SQL schema is usually complex and includes embedded tables and/or objects).
It's ultimately a design choice -- LAC resources are very easy to change, and they take full advantage of rules and fine-grained security, but they can incur extra cost.