Comparing 2 large ResultSets

[daniel_el]

Hi,

I need to compare 2 large ResultSets (may contain up to 100,000 records each), and output and differences between them. My current code is far too slow (normally around an hour before I terminate it), so I'm wondering if there's a more sophisticated way to do this comparison (perhaps using ArrayLists or HashMaps?) Anyway, here's the code I'm using - any ideas would be really appreciated. Thanks,

Daniel

DatabaseA_ResultSet.beforeFirst();
while (DatabaseA_ResultSet.next()) {

ComparisonStringA = DatabaseA_ResultSet.getString(1) + "-" + DatabaseA_ResultSet.getString(2);

foundMatch = "no";

DatabaseB_ResultSet.beforeFirst();
while (DatabaseB_ResultSet.next()) {

ComparisonStringB = DatabaseB_ResultSet.getString(1) + "-" + DatabaseB_ResultSet.getString(2);


if (ComparisonStringA.equalsIgnoreCase(ComparisonStri ngB)) {
foundMatch = "yes";
break;
}
}
if (foundMatch.equalsIgnoreCase("no")){
System.out.println("didn't find " + ComparisonStringA + " in entire resultSet");
}
}

[copeg]

Moved to http://www.javaprogrammingforums.com/jdbc-databases/

For future reference, please wrap your code in the code tags.

Use a Set - it allows only unique elements. Place one dataset within, then check whether that set contains values from the second dataset. Or better yet, you could almost surely optimize your query and let the database do this for you (through joins, intersects, minus/except, etc...)