Minimize querys

Databases
1

i have a database table hirachy like this
table a
—table b
------table c
---------table d
---------table e
and for each table i made a object, each object can load and save his data.
if me need to display all data into a single rows there are a lot of sql querys unfortunately.
a row in listview looks like table a, table b, table c, date from table d, date from table e.
each row from table c is a row in listview, if there is no data then table b is a row.
before i display the rows i filter the objects in a method.

how do you map this hirachy from database into objects in a fast way?
i need some suggestions to handle this. :slight_smile:

2

Without seeing your code…

Could you modify your queries to prefix the table name to the columns, then run the RecordSet (or RowSet) through a function that processes each row and creates the objects you need based on the column names?

1 Like
3

For example:

SELECT
  table_a.id AS table_a_id,
  table_a.f1 AS table_a_f1,
  table_b.id AS table_b_id,
  ...
1 Like
4

Stupid question: what do you mean with table hierarchy? If you mean table b is contained in table a then why the complex setup?

1 Like
5

I told this to mean JOINs but I’m sure Markus will clarify.

1 Like
6

the hirachy is
purchaser
—place of work
------piece of service
---------service dates planned
---------service dates done.
because i not want duplicate data.
each year get a new planning end execute dates in a interval from 7 to 30 days.

i guess it is fastest to fetch all data from each table and then build the object hirachy.
that would be 5 querys without any join.
after that i need a flat view into a single row in some way.

7

Is the table organisation one-to-one or one-to-many? Please show the ERM. Tables don’t have a hierarchy.

1 Like
8

most of all are one to many.
the hirachy is made with id fields.
so a row entry have a parent id.

1 Like
9

Views can help a lot, but all depends on your DB model.

1 Like
10

So JOINS or nested SELECTS are the better approach. They will end up in large SQL statements indeed, but performance-wise they are most often the better way, as the database (optimizer) will “see” how to optimize the query internally.

1 Like
11

somehow i have a conflict with OOP and overmuch SQL statements.
some complex SQL statements i like to solve easier via a method.

12

yes, its a serious consideration. i need a magic view.
i want get all data.
filter data from ui input.
display the remaining data + virtual calculated data into a listview.

update this listview row after one table field has changed is a other problem.

13

This is normal. The object model might be good for CRUD but not for viewing lots of data.

2 Likes
14

yes, i use the hugely useful object model for CRUD.
only the data for the listview display is tricky.

15

If you use SQLite, take a look at “The WITH Clause”: The WITH Clause

Scroll down to “3.4. Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER BY”. A very elegant and fast solution to read a tree.

2 Likes
16

THIS:

vs this:

I have experienced the difference firsthand while helping someone optimize a query. They wanted a monthly summary of a 10,000 row SQLite database that contained many events recorded per day.
Reading a batch of rows and summarizing took about 10 seconds for a month of data.
Setting up a SQL Query took 0.3 seconds to summarize the entire year.

2 Likes
17

Yup, when someone has ever worked on a larger ERP system, he will soon give up any adempts to bypass the beauty and help of the database itself ;-), you will always lose against the database.

18

the search filter i want is to complicated to solve via sql query.
not impossible but why should i make life difficult.
the bottle-neck is the count of (unnecessary/not ideal) querys and somehow also the network or database server speed, firewall or anti virus or network security video-recording cams.
at least it runs noticeable some times slower than my dev enviroment.
the database itself is very tiny.
as soon as possible i will make some tests.

19

A long long time ago some specialists made a some real WTF: a database with configurable fields. B.u.t.t.-ugly. Terrible to manage. Html was loaded with MBs of json. But the real problem for me as number designer was the reporting. The Americans wrote the basic queries manually. Pages and pages of SQL. Without tool.

20

today i made more tests
the count of data
purchaser 12
—place of work 126
------piece of service 432
---------service dates planned 3112
---------service dates done. 0

the time for fill a list with 2500 rows and 20 columns
plus 1300 sql querys are 5 seconds.

if a normal query took 20 ms (i remember from ms vb6 dao) that should use 260 ms.
this querys just search between from and to date in service dates planned and service dates done for each service.

for the database service dates i created indices.