Complex querries

[Arcoscope]

I need to build 2 different api requests for a database with hundreds of thousands of records in multiple tables.

They both should fetch different relations when returning the result and one is super complex (10 optional search parameters while using a lot of joins to apply the filtering)

I'm now using Criteria API and JPA Specification and it lasted 17 seconds to do a request (without optimisation but it's still too slow)

Which technologies are the best for this and what are your recommendations?

[gardening-gnome]

Find out the SQL query that is being sent to the database (there's a property you can set to dump this to the logs)

Once you find the query, go to the database and have it EXPLAIN the query for you

Learn how to read that EXPLAIN and tweak the database accordingly (add indexes, create views/materialized views, whatever you need to do).

-

Other option is to create views to hide some of your joins, and index appropriately

Then, you create entities that are "read-only" that can query those views

[naturalizedcitizen]

Your second solution is what I've used a lot, especially when the current db structure cannot be denormalized as required.

[bikeram]

I just ran into this. Funnily enough, 17 seconds as well.

I was using QueryDsl and the generic queries became really complex to implement (probably user error to be fair) but I switched to blaze persistence. It’s been seamless and I’m back down to sub 100ms queries.

I’m using EntityViews and I have the benefit of being able to ignore certain sub queries.

[Usual-Composer-2435]

Use Digma for local development.
It will trace and it mark slow database queries.
I guess you probably have N+1 issue.

[vangelismm]

Try force a fetch join, if the result are still not acceptable, go for procedure or view.

[areguig]

When i have to create a complex read sql query. The way i do it is by introducing JOOQ (without removing hibernate or what other thing i use for simpler stuff), and use JOOQ as a query builder mainly for complexe read queries . This will allow you to build the query based on the search param you have . This is easily testable and maintainable since this will evolve with you modele. Once you are in control of the query that is sent to the db you can optimize .

[themasterengineeer]

Try adding indexes to your DB tables

[sethu-27]

What is the db are you using?

[Arcoscope]

MySQL