Performing "Optimized" set Operations on java.util.ArrayList or in SQL

This is the first time I need to ask question to get my query solved before today previous threads were able to solve my queries.

Scenario: I have two tables(lets say A & B) in a database with some same rows and some different rows. Poin开发者_JS百科t to note is that both tables have millions of rows or at least more than a million.

Now I have to perform set operations on them like no. of same rows (intersection) , no. of new rows(A-B) and no. of old rows (B-A).

I have two choices: 1) I can perform set operations as query it-self on the database.

2) Or I can fetch the records and perform operations on java.util.ArrayLIst in memory.

But in both the cases its taking too long around 5 to 10 mins what could be the best(OPTIMIZED) approach for this.

Please help.

First of all, union, minus, etc. are not operations on tables, but on select statement results. In your case you are scanning huge tables, create huge result sets and then compare each of the results. This is very slow. You have to optimize in a way that indices can be used.

Second, loading results from DB to Java, and then processing them is inherently slower then to process in the DB, and get ready result in Java.

Go for the database approach, because hundreds of man-years went into optimizing the query logic of the RDBMS. I am sure that you do not want to replicate this effort in your client. If using SQL is currently too slow, make sure that all required indices are in place and perform an analysis of the query execution plan (how to do that varies among different database management systems) to find any bottlenecks in your query (such as full table scans).

I would leave it up to the Database.

If the tables do not already have a Primary Key, you can create a column (indexed) in both tables that stores a hash of the column values, use that column to do easier comparisons, for instance:

SELECT COUNT(*)
FROM a JOIN b ON a.hash = b.hash
    AND a.ColA = b.ColA
    AND a.ColB = b.ColB