1

What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the unique key is a VARCHAR(60) field? (I know, a bad idea to have this column as unique key, but I can't change it).

Specifically:

  1. Would using a CHAR(60) versus VARCHAR(60) going to help?
  2. How about hashing the key to binary(20)? Do you expect a significant improvement in join?
4
  • Generally ’not exists'. What indexes do you have on the tables? what string comparison semantics do you need? What version and edition of SQL Server? Apr 25, 2016 at 15:03
  • SQL Server 2008 Enterprise Edition. There is a PK on both tables and comparisons are equality on PKs. Left join, NOT EXISTS, and EXCEPT, they all produce slightly different execution plans but with very similar costs in this scenario. Generally they all have to scan both tables and joining on a wide key is problematic. That's why I'm not looking for different syntaxes, but smarter way to achieve this. See my last comment below.
    – ODBC
    Apr 25, 2016 at 15:20
  • By string comparison semantics I mean case sensitivity, Unicode canonical equivalence etc. If you hash to some binary value you will lose that except if you convert to a canonical form first. What plan do you currently get? Merge anti semi join using the two indexes? How long does it take to execute? How have you determined that it is the predicate comparison responsible for the time? Rather than just IO of reading those tables for example. Apr 25, 2016 at 16:21
  • There's no case sensitivity or Unicode. If we chose to hash, everything would be casted to upper case. I get a simple plan, scan of two tables and hash joins and filtering the existing rows. The query takes 45 minutes to run on a decent server, about 20% of cost is in the hash joins.
    – ODBC
    Apr 30, 2016 at 4:33

1 Answer 1

1
SELECT tbl_A.id
FROM tbl_A
LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id
WHERE tbl_B.id IS NULL

This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations.

As for varchar and char: char is more efficient when using data that is all the same length.

Here is an SQLFiddle example http://sqlfiddle.com/#!9/bbbbc3/1

4
  • Really?!!! At least I would have expected you to say SELECT * FROM tbl_A LEFT OUTER JOIN tbl_B ON tbl_A.column=tbl_B.column WHERE tbl_B.column is null
    – ODBC
    Apr 25, 2016 at 3:26
  • I'm sorry, but your query is wrong... simply try this WITH tb AS (SELECT 1 AS x UNION SELECT 2 AS x) SELECT * FROM tb AS a LEFT JOIN tb AS b ON a.x <> b.x; as for varchar vs char, I never said they all have the same length.
    – ODBC
    Apr 25, 2016 at 3:37
  • Whoa! you are totally right. major lapse on my part. I made an edit that should work. Without a union. Also, you may want to check if your version of SQL allows the use of MINUS which is like UNION but more suited to your needs.
    – Jmaurier
    Apr 25, 2016 at 4:20
  • My question is not about query syntax. SQL Server does support EXCEPT that is similar to MINUS. The issue is having to hash / merge join millions of rows on a wide character keys only to throw most of them out and taking the delta. I'm looking for some advise on advanced optimization techniques for physical table/index storage and retrieval or any other smart way of achieving high performance.
    – ODBC
    Apr 25, 2016 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.