Help refactoring an SQL query

Question

Dear Friends.. Below is my SQL Query which takes more than 10 minutes and still running....

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 SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo       
    AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen))  
    AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT(INT, PD.PrefixLen))  
 where  AuditData.TATCallType is null   
    and  AuditData.AuditMaster_ID=74 
    and PrefixType='CALL'

evey column used in the inner join defied a index and columns used in where clause is also defined index...

is there any way to fast above query

Please help me...

Thanx

---

Dear Friends i m modified my SQL Query is as follow its still take lot of time to execute Against 15000000

modified SQL Query are as follows:

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, NoSeriesLen + PD.PrefixLen)
where AuditData.TATCallType is null and AuditData.AuditMaster_ID=74 and PrefixType='CALL'

Now what can i do??

Dear friend

my Query takes lot times because below part of code NoSeriesMaster Contain 4000 rows and Auditdata 15000000 rows with inner join every callto columns record in auditdata matched with the Noseriesmaster

INNER JOIN NoSeriesMaster_Prefix PD
ON SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo
AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen))
AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT (INT, PD.PrefixLen))
where AuditData.TATCallType is null and AuditData.AuditMaster_ID=74 and PrefixType='CALL'

Answer 1

It's difficult to say what is causing it, but the following may contribute:

• Performing transformations such as SUBSTRING, CONVERT, LEFT etc on values in joins will ruin performance, because it means SQL Server can't effectively use its indexes. You may want to look at extracting the columns you need to do this type of conversion on into separate columns and index them.

• Sub-optimal indexes - apart from the ones you can't use due to all the conversions, are the other columns you're joining on correctly indexed? Take a look at the index tuning wizard which may be able to help you out here.

Answer 2

What you can do is use the Execution Plan view inside SQL Server Management Studio when you execute this query. It will show you on which steps SQL spends the most time to process your query. From there you at least have an idea where the optimization needs to occur.

The Execution Plan is also featured in SQL Server Management Studio Express fyi.

Just open a new Query Window, click Query > Display Estimated Execution Plan and run your query. The execution plan will pop up once the query has completed.

Answer 3

And another thing - you can try to place some conditions from WHERE into JOIN:

from Auditdata AuditData 
inner join AuditMaster am 
    on am.ID=AuditData.AuditMaster_ID AND AuditData.TATCallType is null   
    and  AuditData.AuditMaster_ID=74

but I think Query Optimizer should do it.

anyway, you need to view execution plan first.

Answer 4

The execution plan would tell you exactly what's taking the most time.

It's likely the last join that is causing the most work. As you are comparing calculated values, the database can't use indexes for lookup.

I see that you do a CONVERT(VARCHAR(10), ...) on a numeric value, but then you compare it to a number. You should be able to just remove that conversion.

You convert the field PrefixLen to a number in several places. Is that field realy a text field, and if it is, can you convert it to a numerical field?

You are comparing the first part of AuditData to PrefixNo, then you are comparing a bit more of the field to PrefixNo + NoSeries. Unless there is an issue with values "bleeding" because a lack of separator (e.g. '01'+'23' = '0'+'123') you could just remove the first comparison.