2
\$\begingroup\$

OK, I have a customer list that has approx 80k records. I need to search and compare looking for duplicates, but duplicates could be an exact match, or a similar match (only some data matches) so it gives a confidence score.

My first block of code takes 3 hours to process and returns 1.25 Billion results.

Does anyone see a way to streamline this a bit so it doesn't take quite so long?

INSERT INTO DataDupe
    SELECT
        'CUST',
        ranking AS score,
        Duplicate.data_uid,
        originalCustNo,
        NULL

      FROM (
            SELECT
                FirstCompany.data_uid AS originalCustNo, 
                Duplicate.*, 
                CASE WHEN FirstCompany.data_uid = Duplicate.data_uid THEN 2 ELSE 0 END 
/* Company */     + CASE WHEN FirstCompany.data1 = Duplicate.data1 THEN 2 ELSE 0 END 
/* First/Last */  + CASE WHEN FirstCompany.data2 = Duplicate.data2 AND FirstCompany.data3 = Duplicate.data3 THEN 2 ELSE 0 END 
/* Address1 */    + CASE WHEN FirstCompany.data4 = Duplicate.data4 THEN 2 ELSE 0 END
/* Address2 */    + CASE WHEN FirstCompany.data5 = Duplicate.data5 THEN 1 ELSE 0 END
/* City/State */  + CASE WHEN FirstCompany.data6 = Duplicate.data6 AND FirstCompany.data7 = Duplicate.data7 THEN 1 ELSE 0 END
/* Country */     + CASE WHEN FirstCompany.data8 = Duplicate.data8 THEN 1 ELSE 0 END
/* Phone1 */      + CASE WHEN FirstCompany.data9 = Duplicate.data9 THEN 1 ELSE 0 END
/* Phone2 */      + CASE WHEN FirstCompany.data10 = Duplicate.data10 THEN 1 ELSE 0 END
/* Email */       + CASE WHEN FirstCompany.data11 = Duplicate.data11 THEN 1 ELSE 0 END
/* Addr Type */   + CASE WHEN FirstCompany.data12 = Duplicate.data12 THEN 1 ELSE 0 END
                AS ranking
              FROM 
              (
                SELECT data_uid, data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12
                  FROM (
                        SELECT TOP (100) data_uid, data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12,
                            ROW_NUMBER() OVER (PARTITION BY data1 ORDER BY data_uid) AS ordering
                          FROM DataCheck
                       ) FC
                  WHERE ordering = 1
              ) AS  FirstCompany
                JOIN DataCheck Duplicate
                  ON Duplicate.data_uid >= FirstCompany.data_uid) Duplicate
                JOIN (VALUES 
                    (12, 'Original'),
                    (11, ''),
                    (10, ''),
                    (9, ''),
                    (8, ''),
                    (7, ''),
                    (6, 'Original'),
                    (5, 'Duplicate'),
                    (4, 'Duplicate'),
                    (3, 'Likely Dupe'),
                    (2, 'Possible Dupe'),
                    (1, 'Not Likely Dupe'),
                    (0, 'Not A Dupe')
      ) RankMapping(score, description)
        ON RankMapping.score = Duplicate.ranking
    ORDER BY Duplicate.originalCustNo, Duplicate.ranking DESC

My next Query I'm trying to only return the results that have possible matches. I've been staring at this too long. I'm not getting any results, but I should have like 20k. What am I missing?

SELECT
    dd.score,
    dd.data_original,
    CUST.CUSTNUM,
    CUST.COMPANY,
    CUST.FIRSTNAME,
    CUST.LASTNAME,
    CUST.ADDR,
    CUST.ADDR2,
    CUST.CITY,
    CUST.STATE,
    CUST.COUNTRY,
    CUST.PHONE,
    CUST.PHONE2,
    CUST.EMAIL,
    CUST.ADDR_TYPE

  FROM DataDupe dd
    LEFT JOIN MailOrderManager.dbo.CUST
      ON dd.data_uid = CUST.CUSTNUM
  WHERE score > 6
  GROUP BY dd.score, dd.data_original, CUST.CUSTNUM, CUST.COMPANY, CUST.FIRSTNAME, CUST.LASTNAME, CUST.ADDR, CUST.ADDR2, CUST.CITY, CUST.STATE, CUST.COUNTRY, CUST.PHONE, CUST.PHONE2, CUST.EMAIL, CUST.ADDR_TYPE
  HAVING COUNT(dd.data_original) > 1
  ORDER BY dd.data_original ASC, dd.score DESC

I am not sure using the straight score is the best way to get results. For example, if a record has first, last, phone1, and email it has a score of 6, if another record has the same first last and email, it has a score of 3. If you could null = null for the columns missing data it gets more complicated.

I'm thinking it may make more sense to compare the score to the original and use:

CASE
    WHEN dd.data_original = CUST.CUSTNUM THEN 0
    ELSE (SELECT score FROM DataDupe WHERE data_uid = dd.data_original) - dd.score
END AS conf_score,

SQL for the two data tables. Actual data is pretty generic company, name address info, but I can't give it for security reasons. I can say that not all columns have data for each record (ie Address 2, Phone 2, and Email are supplied on maybe 200 records, about 20% are for individuals not companies, so there is no company name, etc)

CREATE TABLE DataCheck (
    id          int             identity(1,1),
    data_table  varchar(60)     NOT NULL,
    data_field  varchar(60)     DEFAULT NULL,
    data_uid    varchar(60)     NOT NULL,
    data1       varchar(255)    NOT NULL,
    data2       varchar(255)    DEFAULT NULL,
    data3       varchar(255)    DEFAULT NULL,
    data4       varchar(255)    DEFAULT NULL,
    data5       varchar(255)    DEFAULT NULL,
    data6       varchar(255)    DEFAULT NULL,
    data7       varchar(255)    DEFAULT NULL,
    data8       varchar(255)    DEFAULT NULL,
    data9       varchar(255)    DEFAULT NULL,
    data10      varchar(255)    DEFAULT NULL,
    data11      varchar(255)    DEFAULT NULL,
    data12      varchar(255)    DEFAULT NULL,
    data_type   varchar(60)     NOT NULL
)

CREATE TABLE DataDupe (
    id              int             identity(1,1),
    data_table      varchar(60)     NOT NULL,
    score           int,
    data_uid        varchar(60)     NOT NULL,
    data_original   varchar(255)    NOT NULL,
    action          varchar(60)     DEFAULT NULL
)
\$\endgroup\$
  • \$\begingroup\$ Please also post the SQL to create the tables that are queried. \$\endgroup\$ – BCdotWEB Oct 19 '19 at 21:32
  • \$\begingroup\$ @BCdotWEB Done! \$\endgroup\$ – Dizzy49 Oct 20 '19 at 0:14
  • \$\begingroup\$ 3hrs on SELECT TOP (100) * FROM DataCheck even if it has billions of rows, you still getting only 100 rows, You must check the indexes and also test the query performance on the SELECT TOP (100) * FROM DataCheck alone, see how much it takes. \$\endgroup\$ – iSR5 Dec 13 '19 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.