Suppose that we have two tables, table A and table B, and suppose that A and B are both very large: table A consists of 500000 rows and 20 columns and table B consist of 1000000 rows and and 20 columns. Suppose furthermore that there is no unique index available for the rows.
Question: What is the fastest way to check the overlap between the two tables? Should I use some form of hashing? Would it be doable to compare the tables within a couple of minutes and if not; how long would it take? I guess that just comparing each row of A with each row of B would take a lot of computing time?
Thanks!
I suspect the fastest solution would be to create an index on one of the tables on some field that is well distributed, i.e. where there would be few cases of two records having the same value in that field. Then you could do a fast search with a join on that field.
Creating the index and then running the comparison will almost certainly be faster than running a comparison without an index.
Exactly how long it will take will depend on the size of the fields, how fast your server is, etc. But with a decent index, "a few minutes" is not an unreasonable expectation.
If there's some reason why you don't want an index, then delete it when you're done.
Comparing large databases