views:

201

answers:

3

Dear Friends 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?

please help me its urgent

thanx in advance...

+3  A: 

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.

marc_s
Actually in above code cursor is must because one by one i m checking whether particular number with tablethat cursor table contain only 15 records which run against 1500000rows
John
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 Nicholas
can we modify above Query with subquery
John
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
ok let me check to run the core of my stored proc as marc said
John
Can you at least get rid of the dynamic SQL and hardcode the @table name into the SQL statement? That might help a bit....
marc_s
thanx marc for Query i m checking that.. get back to u
John
is with exits in SQL server 2000.
John
Ah, too bad - the CTE is only available in SQL 2005 and up :-(
marc_s
ohhhhhhhhh,,now other alternate exits
John
I tried to incorporate that extra table which you used in the CURSOR directly into the UPDATE statement. Try to verify if it updates the same set of data, and see how it performs.
marc_s
John
marc your above modified query takes more than 2 minutes
John
Is that 2 minutes instead of 60 ? That wouldn't be so bad, right?
marc_s
ya marc...thank you very much
John
marc,using your modified query i m taking Original table ID to the temp table its take around 3:02but after that i need to update tatcalltype,tatcallunit to original table using ID columns for that i m using below query.update Auditdataset tatcalltype=20,tatcallunit=1from auditdata auditdatainner join auditdata_sms a_smson auditdata.id=a_sms.idthe above query takes more than 4 minuteswhats wrong in above query even both table ID column datatype is integer
John
total both SQL Query takes 9 minutes to run
John
+1  A: 

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.

HLGEM
+2  A: 

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.

Brian Spencer
+1 yep, exactly ! passing the table name in and using dynamic SQL is one of the two killer elements in this query...
marc_s
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
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?
Remus Rusanu
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.
marc_s