Skip to content
Advertisement

Are these SQL queries equivalent? And which is better or is there a better option?

I am working on a Spring web application that utilizes hibernate to connect to a DB2 database. I am try to optimize a service method that gets called may times during a wed service call by reducing the number of DB queries.

So my question is whether or not this query

is equivalent/better then this query

or is there a better option?

Both queries seem to run about the same time (<50ms) but that may depend on the resulting data. I would need to test more to know for sure.

The point of these two queries is for one of them to replace three other queries where their resulting data is processed in Java to get the required data.

I will also have to be able to convert the SQL query to HQL. I was struggling to convert the first query.

I have a feeling that I maybe wasting my time since the java objects for tables B and C are a one-to-many relationship in the object for table A and they are load by hibernate anyway. Meaning I may not be saving anytime in the long run. Is my thinking here correct?

Thanks!

Advertisement

Answer

If I understand correctly, exists would be the best solution:

One big gain is just in removing the select distinct.

Then for performance, you want indexes on table_b(fk_id, request_id, type_id) and table_c(fk_id, request_id, type, DATA_RQST_ID) and table_d(id).

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement