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"
Go to Top of Page

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:24:41
Every columns used in the inner join and where clause defined index.
Go to Top of Page

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'


Go to Top of Page

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"
Go to Top of Page

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)



Go to Top of Page

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-16 : 05:52:27
is any alternate i can use for that concanate values
Go to Top of Page
   

- Advertisement -