When is sql distinct faster than java programming 'distinct'

Question

If I have a sql query that uses 'distinct' (in oracle), would it be faster than retrieving the non-distinct then getting the unique results via java programming?

I heard somewhere that oracle sql distinct is heavy, but is it heavier than manual 'distinction' via java programming?

Thanks, Franz

Answer 1

The rule of the thumb is that the data is faster to process in the database than in your programming language.

The reason is that the data is already available in the database, and it saves the effort of going to your app:

• marshalling data for the driver from the database;
• network transfer;
• unmarshalling data from the driver to the application;

---

As for Oracle DISTINCT being heavy, what it can mean is to never simply throw a DISTINCT at a query just because it seems like a good idea - profile it with a realistic data set since it can have serious performance implications.

Answer 2

Two main aspects:

• If you have to transfer the data over to Java, there's all the overhead of doing that transfer. Doing the work in the database means you don't need to transfer data you don't need.
• The database will be able to use its indexes, caches etc to speed things up.

I'd be very surprised to find that fetching all the data and then doing a distinct operation in Java was faster than doing it in the database.