A problem with a simple join taking too long to finish

I´m having trouble identifying who to fix this simple join.

SELECT A.CUSER,
       COUNT(*) AS QTD
FROM BO_PRODUCTCONFIG A WITH (NOLOCK)
    INNER JOIN BO_PRICER001 B WITH (NOLOCK)
        ON (
               A.CIDBRANCH = B.CIDBRANCH
               OR B.CIDBRANCH IS NULL
           )
           AND (
                A.CIDPRODUCT = B.CIDPRODUCT
                OR B.CIDPRODUCT IS NULL
           )

My problem is, after adding the column B.CIDPRODUCT IS NULL this query takes hours to finish.

table BO_PRICER001 rows 286537 table BO_PRODUCTCONFIG rows 7934844

on the table BO_PRICER001 exists 15329 rows with null

with B.CIDPRODUCT IS NULL the live query show this plan:

enter image description here

and without this column the plan is:

enter image description here

I already try to force an index and switch to a subquery but the problem continues.

if possible, I really appreciate a help.

Sample of data from PRICER001

CIDPRICE    CIDCOMPANY  CIDBRANCH   CIDTABLEPRICE   CIDCOMMISSIONGROUP  CIDPRODUCT  CIDPERIODPAYMENT    DENDDATEVALIDITY    DSTARTDATEVALIDITY  NINCREASEPERCENT    LACTIVE DLASTUPDATE NINCREASEPERCENTORIG
XPTO0319    XPTO    NULL    NULL    NULL    NULL    319 9999-12-31 00:00:00.000 2018-08-27 00:00:00.000 2.100   1   2020-10-07 05:07:37.917 21.000
XPTO0321    XPTO    NULL    NULL    NULL    NULL    321 9999-12-31 00:00:00.000 2014-08-06 00:00:00.000 2.100   1   2020-10-07 05:07:37.917 21.000
XPTO0326    XPTO    NULL    NULL    NULL    NULL    326 9999-12-31 00:00:00.000 2018-08-27 00:00:00.000 3.150   1   2020-10-07 05:07:37.917 31.500
XPTO0328    XPTO    NULL    NULL    NULL    NULL    328 9999-12-31 00:00:00.000 2014-08-06 00:00:00.000 3.150   1   2020-10-07 05:07:37.917 31.500
XPTO0345    XPTO    NULL    NULL    NULL    NULL    345 9999-12-31 00:00:00.000 2020-05-29 00:00:00.000 4.530   1   2020-10-07 05:07:37.917 45.300
XPTO0390    XPTO    NULL    NULL    NULL    NULL    390 9999-12-31 00:00:00.000 2020-05-29 00:00:00.000 9.130   1   2020-10-07 05:07:37.917 91.300
XPTO0412    XPTO    NULL    NULL    NULL    NULL    412 9999-12-31 00:00:00.000 2020-05-29 00:00:00.000 11.500  1   2020-10-07 05:07:37.917 115.000
XPTO0428    XPTO    NULL    NULL    NULL    NULL    428 9999-12-31 00:00:00.000 2020-05-29 00:00:00.000 2.640   1   2020-10-07 05:07:37.917 26.400
XPTO0460    XPTO    NULL    NULL    NULL    NULL    460 9999-12-31 00:00:00.000 2020-05-29 00:00:00.000 5.690   1   2020-10-07 05:07:37.917 56.900
XPTO0515    XPTO    NULL    NULL    NULL    NULL    515 9999-12-31 00:00:00.000 2020-05-29 00:00:00.000 13.900  1   2020-10-07 05:07:37.917 139.000

Sample of data from PRODUCTCONFIG

CIDPRODUCTCONFIG    CIDPRODUCT  CIDPRODUCTMARKET    CIDPRODUCTBUSINESS  CIDPRODUCTLINE  CIDPRODUCTGROUP CIDPRODUCTSUBGROUP  CIDREGION   CIDBRANCH   CIDTEAM CIDTERRITORY    LACTIVE LFLAG   DLASTUPDATE CUSER   CTPDCA
5840992036  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2036    1   NULL    2020-10-07 00:49:15.837 2036    1
5840992037  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2037    1   NULL    2020-10-07 00:49:15.837 2037    1
5840992038  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2038    1   NULL    2020-10-07 00:49:15.837 2038    1
5840992039  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2039    1   NULL    2020-10-07 00:49:15.837 2039    1
5840992040  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2040    1   NULL    2020-10-07 00:49:15.837 2040    1
5840992041  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2041    1   NULL    2020-10-07 00:49:15.837 2041    1
5840992042  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1402    NULL    2042    1   NULL    2020-10-07 00:18:20.783 2042    1
5840992044  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2044    1   NULL    2020-10-07 00:49:15.837 2044    1
5840992045  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1685    NULL    2045    1   NULL    2020-10-07 00:49:15.837 2045    1
5840992048  584099  XPTO    XPTO    XPTO    ACM FFK NULL    1686    NULL    2048    1   NULL    2020-10-07 00:50:20.223 2048    1

Answer

OR kills performance. A typical workaround are multiple LEFT JOIN. Perhaps:

I´m having trouble identifying who to fix this simple join.

SELECT pc.CUSER,
       COUNT(*) AS QTD
FROM BO_PRODUCTCONFIG pc LEFT JOIN
     BO_PRICER001 prbp
     ON pc.CIDBRANCH = prbp.CIDBRANCH AND
        pc.CIDPRODUCT = prbp.CIDPRODUCT LEFT JOIN
     BO_PRICER001 prb
     ON pc.CIDBRANCH = prb.CIDBRANCH AND
        prbp.CIDPRODUCT IS NULL LEFT JOIN
     BO_PRICER001 prp
     ON prp.CIDBRANCH IS NULL AND
        prp.CIDPRODUCT = pc.CIDPRODUCT LEFT JOIN
     BO_PRICER001 pr
     ON prp.CIDBRANCH IS NULL AND
        prp.CIDPRODUCT IS NULL
WHERE prbp.CIDBRANCH is not null OR prb.CIDBRANCH is not null OR pr.CIDPRODUCT is not null or pr.<some other column> is not null;

Advertisement

Answer