1

I have a table that contains information about orders made by users and their phone numbers. The table looks like this one:

table Orders (
    OrderID int,
    UserName varchar(50),
    PhoneNumber1 varchar(50),
    PhoneNumber2 varchar(50),
    PhoneNumber3 varchar(50)
)

Problem: Fixed a user u, count the number of different users that share at least one phone number with u. In other words, count the number of distinct users v that satisfy one or more of the following conditions:

  1. u.PhoneNumber1 = v.PhoneNumber1
  2. u.PhoneNumber2 = v.PhoneNumber2
  3. u.PhoneNumber3 = v.PhoneNumber3

My solution (OrderID is the primary key, I added the other columns in the group by clause just to display them in the result):

select o1.OrderID, 
       o1.PhoneNumber1, 
       o1.PhoneNumber2, 
       o1.PhoneNumber3, 
       count(distinct o2.UserName)
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    or o1.PhoneNumber2 = o2.PhoneNumber2
    or o1.PhoneNumber3 = o2.PhoneNumber3
group by o1.OrderID, o1.PhoneNumber1, o1.PhoneNumber2, o1.PhoneNumber3

The Orders table contains about 300K records, and my query requires too much time to complete (my estimate is about 3 hours, but I didn't let it finish completely).

What strategies can I adopt to speed up the computation? Is there, for example, an equivalent statement that yields the same result?

Note: I tried to create three non-clustered indexes on the PhoneNumber columns but didn't notice significant improvements.

4
  • 1
    Would o1.PhoneNumber1 = o2.PhoneNumber2 and such not also meet the criterion of "users that share at least one phone number with u"?
    – mustaccio
    Sep 26, 2019 at 13:53
  • @mustaccio True, thanks for pointing it out. I actually have to rethink about the join condition. I guess the main problem resides precisely on that, because if I simplify the disjunction by removing the two ors the query runs in just 2 seconds.
    – leqo
    Sep 26, 2019 at 14:48
  • It might be worth it to rethink your data model and normalize phone numbers if possible. Also, why are phone numbers attributes of orders, and not people placing the orders?
    – mustaccio
    Sep 26, 2019 at 14:51
  • @mustaccio that table is actually a sort of view with a lot of columns. I've just copied the relevant columns here and called it Orders for simplicity.
    – leqo
    Sep 26, 2019 at 15:24

1 Answer 1

2

The below creates 300000 dummy orders with 3 random "phone" numbers and then finds the matches, should get you started.

use tempdb
GO

drop table if EXISTS Orders 
GO


create table Orders (
    OrderID int primary key,
    UserName varchar(50),
    PhoneNumber1 varchar(50),
    PhoneNumber2 varchar(50),
    PhoneNumber3 varchar(50)
)

-- generate 300000 with randon "phone" numbers

;WITH TallyTable AS (
SELECT TOP 300000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [N]
  FROM dbo.syscolumns tb1,dbo.syscolumns tb2 
)
insert into Orders
select n, 'user' + cast(n as varchar(10)), cast(CRYPT_GEN_RANDOM(3) as int), cast(CRYPT_GEN_RANDOM(3) as int), cast(CRYPT_GEN_RANDOM(3) as int)
FROM TallyTable;

/*

-- original query, takes a long time

select o1.OrderID, 
       o1.PhoneNumber1, 
       o1.PhoneNumber2, 
       o1.PhoneNumber3, 
       count(distinct o2.UserName)
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    or o1.PhoneNumber2 = o2.PhoneNumber2
    or o1.PhoneNumber3 = o2.PhoneNumber3
group by o1.OrderID, o1.PhoneNumber1, o1.PhoneNumber2, o1.PhoneNumber3
*/

-- which users share the same phonenumber1
select o1.UserName as username, 'phonenumber1' as phonenumber, o2.UserName as sharedwith
from Orders o1
inner join Orders o2
    on o1.PhoneNumber1 = o2.PhoneNumber1
    and o1.UserName <> o2.username -- make sure it's a different user
union ALL
-- which orders share the same phonenumber2
select o1.UserName, 'phonenumber2',  o2.UserName
from Orders o1
inner join Orders o2
    on o1.PhoneNumber2 = o2.PhoneNumber2
    and o1.UserName <> o2.username -- make sure it's a different user
union ALL
-- which orders share the same phonenumber3
select o1.UserName, 'phonenumber3',  o2.UserName
from Orders o1
inner join Orders o2
    on o1.PhoneNumber3 = o2.PhoneNumber3
    and o1.UserName <> o2.username -- make sure it's a different user
order by username asc


-- use the above as a cte to do some counting
; with matches as (
    select o1.UserName as username, 'phonenumber1' as phonenumber, o2.UserName as sharedwith
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber1 = o2.PhoneNumber1
        and o1.UserName <> o2.username -- make sure it's a different user
    union ALL
    -- which orders share the same phonenumber2
    select o1.UserName, 'phonenumber2',  o2.UserName
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber2 = o2.PhoneNumber2
        and o1.UserName <> o2.username -- make sure it's a different user
    union ALL
    -- which orders share the same phonenumber3
    select o1.UserName, 'phonenumber3',  o2.UserName
    from Orders o1
    inner join Orders o2
        on o1.PhoneNumber3 = o2.PhoneNumber3
        and o1.UserName <> o2.username -- make sure it's a different user
)
SELECT matches.username, COUNT(*) AS matches
from matches
group by username
order by 2 desc

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.