It’s not much data but way too many queries!
I would make a test and load all the records with the columns you need with a single query per table and put the data in arrays. Then build the listbox via processing the arrays. Or – instead of using arrays – use an in-memory SQLite database and when you build the listbox, you store the table name and row ID in RowTag/CellTag instead of filling the listbox with the real data. Display the data live via querying the in-memory database is very fast.
1 Like
1300 queries in sequence taking 20ms each = 26000ms = 26 seconds. 
1000 x in 1 s = 1x/ms
1300 queries in 5s = 3ms each.
2 Likes
ups, whatever i calculated it there. lol
because my wrong time calculation yes, i need to use a few sub select there or a temorary table in memory.
i will try to move through a rowset first to load data from instead of so much querys.
i have a solution in mind that should work fast.
i only need to create the service date objects and link it to the service object.
my first optimizing was to encapsulate this service dates functionality in a class.
using a rowset instead of querys was not that fast i thought but its better for the whole list display. (and still messy)
i consider to create more fields in the service table and set this via “trigger” or a method if the dates planned and done changed that i have all i need immediate.
it will make other circumstances for me but at least the search and display of data is very fast then,.
The aim should be to get the data in one single call to the DB. You’re using Postgres, right? So you got the right tool already. There is no such thing as “a query too complex for SQL”. SQL was designed to handle complex queries. You just need to get your head around all the join types, views and functions. Joins from 5 different tables are nothing special at all. Don’t handle this kind of stuff client side with many mini queries.
2 Likes
+1 … But Markus is a tiny bit resistant … well, he still is … ;-). I have to second that if we are talking about performance, minimizing queries is the wrong approach. In contrary, complex queries are where databases excel.
1 Like
The aim should be to get the data in one single call
yes, i added this needed data via a trigger and now the query is fast and convenient.
updating the calculated data if the dates in other tables change was a challenge because two date tables
dependent on each other. if me delete 20 dates i only need one trigger to calculate the resulting data.
There is no such thing as “a query too complex for SQL
if you not have the data which is need for display it get difficult.
as example the next service date planned come after a service date done, but if a service never was done its the first planned etc.
place of work can exists and need display but services can be empty in the same listbox row.
one filter is about a few common citys by checkbox which can be combined and a checkbox for all others without filter it by name. this make a worse and or construct.
one filter is a single input and it search most of all table fields for correlation.
the whole ui input is somehow optional and my query would need $100 arguments, looks fragile and is hardly comprehensible and understandable.
resistant
i appreciate all your comments.
because lack of time i choose a simplest solution with less effort and trustworthy data.
Fair comment. Knowing how to do things right is important, but doesn’t mean that we can always do it in the perfect way (even if we should). From your original post:
how do you map this hirachy from database into objects in a fast way?
i need some suggestions to handle this.
I concluded that fast is related to the most performant way to get the data from the database, not the fastest way to solve the challenges ;-).
You can even add IF THEN CLAUSES to SELECT Statements ;-). Granted that complex SQL statements are sometimes difficult to read, correct and document, but they are really powerful.
1 Like
my issue with a object tree was that the database was the origin of data and if me change somthing there
some objects need a update because i want to update also a single row in the listview.
currently i spend a day to rewrite some parts of the project for performance in a educational way.
i have also to consider that this project is a multiuser enviroment.
a relational database and a object hierarchy always mismatches 
sometimes difficult to read
longer sql statements above a kilobyte are no fun to debug if something not work as expected.
especially and or in many brackets.
1 Like