11 Replies Latest reply on May 8, 2017 11:16 AM by Vincent_L

    ExecuteSQL not equal <> or equal operator is terribly slow

    Vincent_L

      Hi,

       

      I needed to compare 2 tables to seem to find the records where the supplier_id is different between the 2 tables :

       

      So here's the query

       

      SELECT P.product_id FROM product P

      JOIN files_index F ON P.product_id=F.Product_ID

      WHERE P.supplier_id<>F.Supplier_id

       

      The P table contains 60K records, the F tables 3.6 Millions records. Normally there should be only 60K tests.

      I left that query run for hours and had to kill it.

       

      So I tried this

       

      SELECT P.product_id,P.supplier_id,F.Supplier_id FROM product P

      JOIN files_index F ON P.product_id=F.Product_ID

       

      This took 83 seconds ! (of course then I need to pot process the result)

       

       

      So just testing with <> is order of magnitudes slower. I knew that > or < were very slow, but the I though the not equal sign didn't had this issue

       

       

      But = sign seems also to be affected, I didn't had the patience to wait for hours, so I killed it after 12 minutes

       

       

      So I tried this, as luckily the compared values are numbers

       

       

      SELECT P.product_id FROM product P

      JOIN files_index F ON P.product_id=F.Product_ID

      WHERE (P.supplier_id-F.Supplier_id)<>0

       

       

      and it worked in 93s

       

       

      So I don't think it's normal to have an equal or not equal operation being order of magnitude slower than a mathematical one.

      The performance is so low that it practically doesn't work. This add a lot of complexity because doing a query by the book leads to that performance bug.  And of course equal and not equal could be even faster than this mathematical operation