I have a database which describes the various phases of a product lifecycle - one table per phase, one row per product. This is accessed by an application (developed in Gambas3) which displays the entire lifecycle of each product in a grid - left to right. Each phase also has its own maintenance form. This works fine and does everything I need it to do.
My question regards the SQL I've used to populate the grid. I'm no SQL guru, so joining all the tables together in a single view using multiple nested left joins is probably beyond my comfort level. I have approached the problem by creating multiple views which join two tables together and then chaining them together e.g
vw1 joins t1 and t2, vw2 joins t3 to vw1, vw3 joins t4 to vw2 and so on. The final view returns all the data and is used to populate the grid.
While this gets the job done I'm wondering if it might be inefficient in performance terms, as I assume that sqlite functions have to resolve each view in turn to build up the query before delivering the result.
Would I be better off building a single complex query or does the ease of maintenance and relative simplicity of my current approach outweigh any performance deficits? At the moment I have around 350 product lines and the application performance is good, but this is expected to grow to about 5000 product lines.
> My question regards the SQL I've used to populate the grid. I'm no SQL guru, so joining all the tables together in a single view using multiple nested left joins is probably beyond my comfort level.
You don’t have to nest the joins. You can put many JOINs in the same SELECT:
CREATE VIEW AllPhases AS
SELECT *, p1.a, p2.b, p3.c FROM Products
JOIN p1 ON p1.productID = Products.id
JOIN p2 ON p2.productID = Products.id
JOIN p3 ON p3.productID = Products.id
With only 5000 products you probably won’t notice any difference in performance, but the above syntax will give you simpler code and if you lay it out as above it will be easier for other people to understand.