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?

Perhaps can you help us help you and do a minimal analysis why is going so slow. A query plan, a statistics IO, something. SQL is a declarative language, you can never say by looking at its text if is slow or fast. What matters is the actual execution plan, and is impossible for us to guess a plan w/o knowing your schema or without seeing some execution statistics. — Remus Rusanu
– Remus Rusanu, Commented Jul 13, 2009 at 16:28
Isolate an UPDATE on one of the @Tables that is going slow, run it in SSMS and extract the plan. The post here the execution plan. — Remus Rusanu
– Remus Rusanu, Commented Jul 13, 2009 at 16:29

marc_s · Accepted Answer · 2009-07-13 17:26:15Z

3

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.

edited Jul 13, 2009 at 17:26

answered Jul 13, 2009 at 15:50

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

John Over a year ago

Actually in above code cursor is must because one by one i m checking whether particular number with table that cursor table contain only 15 records which run against 1500000 rows

John Nicholas Over a year ago

unknown: that is not a very clear explanation. It also doesn't sound like one would be necessary. I do appreciate that English is probably not your first language but in order to help more information is needed.

John Over a year ago

can we modify above Query with subquery

marc_s Over a year ago

Well, if you have to have that cursor, I don't think there's much you can do, then :-( You might be able to tweak your JOIN conditions a bit and introduce an index here or there, but with the CURSOR still in place, that won't really help much.....

marc_s Over a year ago

Is that 2 minutes instead of 60 ? That wouldn't be so bad, right?

|

Brian Spencer · Accepted Answer · 2009-07-13 19:38:03Z

2

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.

answered Jul 13, 2009 at 19:38

Brian Spencer

2042 silver badges7 bronze badges

4 Comments

marc_s Over a year ago

+1 yep, exactly ! passing the table name in and using dynamic SQL is one of the two killer elements in this query...

Remus Rusanu Over a year ago

Dynamic SQL being slow is kinda urban legend nowdays. The problem is not that is dynamic, but that is not parametrized. It should use sp_executesql, format the sql text with params and pass in @PrefixNo,@PrefixLen,@AfterPrefixLen. If you do that, the dynamic overhead is next to 0 (on cache lookup based on input text hash and you got the plan). Even so, the query is likely auto-parametrized and the plan alternates exploration phase is skipped. And of course, use SYSNAME as the table name type and QUOTENAME in the sql building, but that's a different subject.

Remus Rusanu Over a year ago

The proper way to diagnose is, of course, measure. Does the compilation of dynamic SQL shows up as significant in SATISTICS TIME or in perf counters?

marc_s Over a year ago

Yes, absolutely - only measuring will REALLY tell. But dynamic SQL usually IS a good candidate to look at - often it will yield significant performance improvements. Not always - but often.

HLGEM · Accepted Answer · 2009-07-13 16:57:02Z

1

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.

answered Jul 13, 2009 at 16:57

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

Optimize SQL Query

3 Answers 3

16 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

16 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related