Help refactoring an SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help refactoring an SQL query

Author

Topic

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:10:16

Dear Friend
Below is my SQL Query


select DISTINCT Auditdata.ID,ns.ProviderMaster_ID as  CDRComment    from Auditdata AuditData             
      inner join AuditMaster am on am.ID=AuditData.AuditMaster_ID     
      inner join HomeCircleMaster hcm on  hcm.Ori_CircleMaster_ID=am.CircleMaster_ID  and hcm.Ori_ServiceTypeMaster_ID=1     and hcm.Dest_ServiceTypeMaster_ID=1             
     inner join NoSeriesMaster ns  on (ns.CircleMaster_ID=am.CircleMaster_ID or     ns.CircleMaster_ID=hcm.Dest_CircleMaster_ID) and ns.ProviderMaster_ID<>am.ProviderMaster_ID   and ns.ServiceTypeMaster_ID=1        
       INNER JOIN NoSeriesMaster_Prefix PD ON 
AuditData.CallTo like PD.PrefixNo + '%' AND 
AuditData.CallTolen =  PD.PrefixLen +  PD.AfterPrefixLen  AND PD.PrefixNo + ns.NoSeries = LEFT (AuditData.CallTo, ns.NoSerieslen + PD.PrefixLen)    
where  AuditData.TATCallType is null and  AuditData.AuditMaster_ID=74 and PrefixType='CALL'

from the above Query below part of query takes lot of time to run


  inner join NoSeriesMaster ns  on (ns.CircleMaster_ID=am.CircleMaster_ID or     ns.CircleMaster_ID=hcm.Dest_CircleMaster_ID) and ns.ProviderMaster_ID<>am.ProviderMaster_ID   and ns.ServiceTypeMaster_ID=1        
       INNER JOIN NoSeriesMaster_Prefix PD ON 
AuditData.CallTo like PD.PrefixNo + '%' AND 
AuditData.CallTolen =  PD.PrefixLen +  PD.AfterPrefixLen  AND PD.PrefixNo + ns.NoSeries = LEFT (AuditData.CallTo, ns.NoSerieslen + PD.PrefixLen)    
where  AuditData.TATCallType is null and  AuditData.AuditMaster_ID=74 and PrefixType='CALL'

what modification should be needed to fast that part of query
Every columns used in the inner join and where clause defined index..

please help me

thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 05:23:06

[code]SELECT DISTINCT Auditdata.ID,
ns.ProviderMaster_ID as CDRComment
FROM Auditdata
INNER JOIN AuditMaster AS am ON am.ID = AuditData.AuditMaster_ID
INNER JOIN HomeCircleMaster AS hcm ON hcm.Ori_CircleMaster_ID = am.CircleMaster_ID
AND hcm.Ori_ServiceTypeMaster_ID = 1
AND hcm.Dest_ServiceTypeMaster_ID = 1
INNER JOIN NoSeriesMaster AS ns ON ns.CircleMaster_ID IN (am.CircleMaster_ID, hcm.Dest_CircleMaster_ID)
AND ns.ProviderMaster_ID <> am.ProviderMaster_ID
AND ns.ServiceTypeMaster_ID = 1
INNER JOIN NoSeriesMaster_Prefix AS PD ON AuditData.CallTo LIKE PD.PrefixNo + '%'
AND AuditData.CallTolen = PD.PrefixLen + PD.AfterPrefixLen
AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, ns.NoSerieslen + PD.PrefixLen)
WHERE AuditData.TATCallType IS NULL
AND AuditData.AuditMaster_ID = 74
AND PrefixType = 'CALL'[/code]All parts in red cause your query to go slow. becuase of index no being usable.

N 56°04'39.26"
E 12°55'05.63"

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:24:41

Every columns used in the inner join and where clause defined index.

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:33:15

hi,
below query is slow because the NoSeriesMaster contain 4000 rows
and Main table Conatin 15000000 rows.
inner join i look up noseriesmaster noseries column with
Main table callto column
i m running the above query on 4 GB Test machine..


  inner join NoSeriesMaster ns  on (ns.CircleMaster_ID=am.CircleMaster_ID or     ns.CircleMaster_ID=hcm.Dest_CircleMaster_ID) and ns.ProviderMaster_ID<>am.ProviderMaster_ID   and ns.ServiceTypeMaster_ID=1        
       INNER JOIN NoSeriesMaster_Prefix PD ON 
AuditData.CallTo like PD.PrefixNo + '%' AND 
AuditData.CallTolen =  PD.PrefixLen +  PD.AfterPrefixLen  AND PD.PrefixNo + ns.NoSeries = LEFT (AuditData.CallTo, ns.NoSerieslen + PD.PrefixLen)    
where  AuditData.TATCallType is null and  AuditData.AuditMaster_ID=74 and PrefixType='CALL'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 05:35:27

Yes, but they can't be used.
And why? because the concatenated value is not indexed, only the underlying parts.

N 56°04'39.26"
E 12°55'05.63"

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:41:39

u means on the below part

can't use index


AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, ns.NoSerieslen + PD.PrefixLen)

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:52:27

is any alternate i can use for that concanate values

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources