Customize SQL Query(Please Help)

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

SQL Server Development (2000)

Customize SQL Query(Please Help)

Author

Topic

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-11 : 15:18:55

Dear Friends
i m using the following which Execute against 1500000 rows.

the below Query takes 60 minutes to run against 1500000 rows

is any customization require in above query

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

please help me its urgent

thanx in advance...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-11 : 19:52:26

Why are you using Cursor with dynamic SQL inside? They are killers. Use Set-based solutions instead for update.

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-13 : 02:12:52

Dear Friends,
thanks for your reply..
Please give me some example of how can i modify above SQL Query..

maxyogesh2002
Starting Member

15 Posts

Posted - 2009-07-13 : 03:12:17

even when i run below all SQL Query simulataneously against 1500000 Rows its take
10 minutes

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,0))='' and len(AuditData.CallTo)=10

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,1))='0' and len(AuditData.CallTo)=11

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,2))='00' and len(AuditData.CallTo)=12

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,2))='91' and len(AuditData.CallTo)=12

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='0091' and len(AuditData.CallTo)=14

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,3))='910' and len(AuditData.CallTo)=13

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='9191' and len(AuditData.CallTo)=14

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='9100' and len(AuditData.CallTo)=14

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,4))='0091' and len(AuditData.CallTo)=15

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,2))='91' and len(AuditData.CallTo)=13

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,6))='009191' and len(AuditData.CallTo)=16

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,8))='00500003
' and len(AuditData.CallTo)=18

select ID,AuditMaster_ID,CallTo,CallTypeTag into Auditdata_callto from auditdata where AuditData.TATCallType is null and substring(AuditData.CallTo,1,convert(int,8))='00500004
' and len(AuditData.CallTo)=18

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources