A query taking a lot of time to execute nearly 2 minutes 10 seconds Please help me to make this query fast

Question

The total Numbers of record in table gym_membercommon are 40352.

The total numbers of records for tenant 3 is 10250.

In the table gym_membercommon i need to find all the duplicate records that have any of the number common within that tenant.

create table #temp              
 (            
  meco_Commonid int,            
  meco_tenantid int,            
  meco_OfficeTelno varchar(30),            
  meco_HomeNo varchar(20),            
  meco_MobileNo varchar(20),            
  meco_Fax varchar(20)  
 )  

CREATE CLUSTERED INDEX idxCL_TEMP ON #temp(meco_Commonid)

CREATE NONCLUSTERED INDEX idxNC_TEMP ON #temp(meco_OfficeTelno,meco_HomeNo,meco_MobileNo,meco_Fax)

 insert into #temp            
 select 
    meco_Commonid,
    meco_tenantid,            
    meco_OfficeTelno,            
    meco_HomeNo,            
    meco_MobileNo,            
    meco_Fax
 from gym_membercommon a                   
 where             
    meco_tenantId = 1              
    And             
    lower(ltrim(rtrim(meco_status))) <> 'erroneous'              

 Select distinct  a.*            
 from #temp a                    
 inner join #temp b                   
 on             
 (                     
  (ltrim(rtrim(isnull(a.meco_officeTelno,''))) <>'' and  a.meco_officeTelno in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)) or                   
  (ltrim(rtrim(isnull(a.meco_HomeNo,''))) <>'' and a.meco_HomeNo in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)) or                  
  (ltrim(rtrim(isnull(a.meco_MobileNo,''))) <>'' and a.meco_MobileNo in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)) or                  
  (ltrim(rtrim(isnull(a.meco_Fax,''))) <>'' and a.meco_Fax in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax))                  
 )                  
 and a.meco_Commonid <> b.meco_commonid                  
 And a.meco_tenantId = 1

Awaiting for your reply

thanks in advance.

Dasharath Yadav
Fitness Force

Answer 1

Is it the time going in creating #temp or exeucting the query?

My guess is that

in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)

does not benefit from the index

INDEX idxNC_TEMP ON #temp(meco_OfficeTelno,meco_HomeNo,meco_MobileNo,meco_Fax)

Would a more normalised temporary table work?

meco_Commonid int,
meco_tenantid int,
meco_ANY_OLD_NO varchar(30)

Populate that with extracts of all 4 numbers (likely that this will be slower than your single extract) But then the query should be only a quite simple join which should exploit the index.

Answer 2

Your 'or's are creating horrible tablescans.

create table phonenumbers (
    commonid int,
    phonenumber varchar(30)
)

insert into phonenumbers select commonid, meco_HomeNo from gym_membercommon;
insert into phonenumbers select commonid, meco_OfficeTelno from gym_membercommon;
insert into phonenumbers select commonid, meco_MobileNo from gym_membercommon;
insert into phonenumbers select commonid, meco_Fax from gym_membercommon;

select distinct commonid, phonenumber from phonenumbers
where phonenumber in 
    (select phonenumber from phonenumbers
     group by phonenumber
     having count(*) >= 2)
order by phonenumber

This gives you everyone with shared phonenumbers.

Answer 3

How about:

with temp (
    meco_Commonid,
    meco_tenantid,
    meco_OfficeTelno,
    meco_HomeNo,
    meco_MobileNo,
    meco_Fax
    )
as (
    select meco_Commonid, meco_tenantid,
    meco_OfficeTelno,
    meco_HomeNo,
    meco_MobileNo,
    meco_Fax from gym_membercommon a
    where
    meco_tenantId = 1
    And
    lower(ltrim(rtrim(meco_status))) <> 'erroneous' 
    )
Select distinct a.*
    from temp a
    inner join temp b
    on
    (
    (ltrim(rtrim(isnull(a.meco_officeTelno,''))) <>'' and a.meco_officeTelno in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)) or
    (ltrim(rtrim(isnull(a.meco_HomeNo,''))) <>'' and a.meco_HomeNo in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)) or
    (ltrim(rtrim(isnull(a.meco_MobileNo,''))) <>'' and a.meco_MobileNo in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax)) or
    (ltrim(rtrim(isnull(a.meco_Fax,''))) <>'' and a.meco_Fax in (b.meco_OfficeTelno,b.meco_HomeNo,b.meco_MobileNo,b.meco_Fax))
    )
    and a.meco_Commonid <> b.meco_commonid
    And a.meco_tenantId = 1

But... try to get rid of the trims and lowers. That will stop indexes being used nicely.

And:

create index ixBlah on gym_membercommon (meco_tenantId, meco_status) include (meco_Commonid, meco_OfficeTelno, meco_HomeNo, meco_MobileNo, meco_Fax)

Rob