Optimize SQL Query

Question

I'm using the following which Execute against 1'500'000 rows

My SP is as follows:

CREATE Procedure USP_12(@AuditMasterID as varchar(10),@TABLE as Varchar(50))
as
BEGIN
Declare @SQLStatement varchar(2000)
Declare @PrefixNo varchar(20)
Declare @PrefixLen varchar(20)
Declare @AfterPrefixLen varchar(20)

DECLARE Cur_Prefix CURSOR
FOR
SELECT PrefixNo,PrefixLen,AfterPrefixLen FROM NoSeriesMaster_Prefix WHERE PrefixType='SMS' order by ID

OPEN Cur_Prefix
FETCH NEXT FROM Cur_Prefix INTO @PrefixNo,@PrefixLen,@AfterPrefixLen
WHILE @@FETCH_STATUS = 0
BEGIN 
SET @SQLStatement = 'update '+@TABLE+' set AuditData.TATCallType=''12'', AuditData.TATCallUnit=''1'' ' +
'from '+@TABLE+' 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 AuditTaggingMaster atm on atm.AuditMaster_ID=am.ID '+
'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 ProviderMaster_CallTypeMaster pm_ctm on pm_ctm.ProviderMaster_ID=am.ProviderMaster_ID and pm_ctm.CallTypeMaster_ID=101 and pm_ctm.CallTypeTagValue=AuditData.CallTypeTag '+
'where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,'+@PrefixLen+'))='''+ @PrefixNo + ''' and len(AuditData.CallTo)='+convert(varchar(10),convert(int,@PrefixLen)+convert(int,@AfterPrefixLen))+' and '''+@PrefixNo+'''+ns.NoSeries=Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,'+@PrefixLen+')) and AuditData.AuditMaster_ID='+@AuditMasterID+' '
print(@SQLStatement)
exec(@SQLStatement)
FETCH NEXT FROM Cur_Prefix INTO @PrefixNo,@PrefixLen,@AfterPrefixLen
END
CLOSE Cur_Prefix
DEALLOCATE Cur_Prefix
end

The above query takes 60 minutes to run against 1'500'000 rows

Is any optimization possible for that query?

Answer 1

Since you are passing @table it seems you would be better off to have one sProc for each table you need to run this on. At least the server would have a fighting chance with the cached plan.

Answer 2

No. 1 optimzation - get rid of the CURSOR ! :-) Do you execute this against a lot of different tables?? Can you get rid of the @table variable somehow??

Combining dynamic SQL with a cursor is a sure-fire way to kill off any optimizations SQL Server might have been able to use.......

Try running the core of your stored proc against that 1.5mio table, with the table name hardcoded:

update (your table name)
set AuditData.TATCallType='12', AuditData.TATCallUnit='1'
from (your table name) AuditData 
inner join AuditMaster am on am.ID=AuditData.AuditMaster_ID 
 ...... (and so forth)

How much time does this take on its own??

Can you post a bit more info? Table structures, what indices are available?

Marc

PS: I tried breaking up the huge SQL statement and try to avoid the CURSOR by means of a Common Table Expression. This however requires you to hardcode the @Table name into your statement - could this work for you??

Try it - what times do you get now?

UPDATE 
    (your table name)
SET 
    AuditData.TATCallType='12', AuditData.TATCallUnit='1'
FROM
    (your table name) 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
    AuditTaggingMaster atm ON atm.AuditMaster_ID = am.ID 
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 
    ProviderMaster_CallTypeMaster pm_ctm ON pm_ctm.ProviderMaster_ID = am.ProviderMaster_ID 
      AND pm_ctm.CallTypeMaster_ID = 101 
      AND pm_ctm.CallTypeTagValue = AuditData.CallTypeTag 
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 = @AuditMasterID
    AND PD.PrefixType = 'SMS'

IF this works, your next steps would be to check if you have indices for

• all your JOIN conditions, e.g.

  INNER JOIN AuditTaggingMaster atm ON atm.AuditMaster_ID = am.ID

  do you have indices on "atm.AuditMaster_ID" and "am.ID" ?

• your WHERE clauses (e.g. do you have an index on PD.PrefixType?)

Also, for each case, you need to consider just how selective the index would be. For instance, on your WHERE clause for PD.PrefixType = 'SMS' - does this select half of all entries in the "PD" table, or just 1-2% ? If the index is selective, then it will most likely be used - if it's on a "BIT" column which can have only two values and each value will select about half of the table, don't bother putting an index there, it won't help.

Answer 3

First thing I would check would be that I have defined indexes on all the foreign keys.

Blocking can also be serious issue when hitting audit tables. Read up on table hints to see if they can help with any delays caused by blocking.