July 11, 2009 at 8:58 am
Dear Friends
i m using the following which Execute against 1500000 rows
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
the above Query takes 60 minutes to run against 1500000 rows
is any customization require in above query
please help me its urgent
thanx in advance...
July 11, 2009 at 9:42 am
Hello,
Obviously I don’t know the structure of your DB, but would it not be possible to somehow replace the Cursor and Dynamic SQL with a Join on the NoSeriesMaster_Prefix table?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 11, 2009 at 10:07 am
Hi,
the following part of your code is a little confusing to me:
... 'update '+@TABLE+' set AuditData.TATCallType=''12'', AuditData.TATCallUnit=''1'' ' ...
If I'm reading your code correctly you're referring to @table with the alias [AuditData]. But your update statement refers still to @table instead of the alias [AuditData].
I'd expect the statement would fail with the error
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "AuditData.TATCallType" could not be bound.
Could you please provide a sample of one value for @SQLStatement for verification?
To follow John's recommendation I would create one dynamic SQL per @TABLE variable using a join to NoSeriesMaster_Prefix instead of a cursor.
@john-2: As far as I can see the OP has a number of tables with identical structure he's referring to with the variable @TABLE to perform updates on the common columns TATCallType and TATCallUnit. Seems like some sort of "horizontal split table" I guess...
July 11, 2009 at 12:49 pm
Dear Friends,
Thanks for your Reply...
Actually the above code is my Store Procedure i m passing value to @Table and @AuditMasterID through the front end
i m passing value to the below SP through the Query Analyzer is follows:
exec USP_12 'Auditdata','24'
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
plz help me
what should i do.....
July 11, 2009 at 1:25 pm
Hi,
please check if the following code will help you to get the expected results.
IMPORTANT NOTE:
Since there are no table definitions nor any sample data the code below is completely untested and should be only used a something to start with on your test system. Therefore, it does not contain any data modifaction.
SET @SQLStatement =
'SELECT AuditData.*, ''CallType=12'', ''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
INNER JOIN NoSeriesMaster_Prefix nsp
ON nsp.PrefixNo = substring(AuditData.CallTo,1,convert(int,PrefixLen))
AND nsp.PrefixType=''SMS''
and len(AuditData.CallTo) = convert(varchar(10),convert(int,nsp.PrefixLen)+convert(int,nsp.AfterPrefixLen))
where AuditData.TATCallType is null
and nsp.PrefixNo + ns.NoSeries = Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,PrefixLen))
and AuditData.AuditMaster_ID=' +@AuditMasterID + ''
print(@SQLStatement)
July 11, 2009 at 1:48 pm
Dear Friends,
thanks for your Reply..
if u want to make any modfication in my Above SQL Query u can do that..
Because Currently i m testing the Query on Test Server.
Thanks in Advance
July 11, 2009 at 2:09 pm
It's hard to make any tested/verified modification to a query without having any table definition or data to test against.
If you'd like us to have a more detailed look at the query then I kindly ask you to provide table definition (including index definition), sample data and expected result as described in my signature.
Other than the answer I already gave I don't see much more room for improvement/modification without any more detailed information from your side.
You need to help us help you.
July 13, 2009 at 12:26 am
Dear Friends
Thanks for your reply
if i run below query its take more than 35 minutes
insert into sms_tatcalltype Select distinct AuditData.ID,'12','1','null' 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 ProviderMaster_CallTypeMaster pm_ctm on pm_ctm.ProviderMaster_ID=am.ProviderMaster_ID and pm_ctm.CallTypeTagValue=AuditData.CallTypeTag
where AuditData.TATCallType is null and pm_ctm.CallTypeMaster_ID=101 and substring(AuditData.CallTo,1,convert(int,2))='91' and len(AuditData.CallTo)=12 and '91'+ns.NoSeries=Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,2)) and AuditData.AuditMaster_ID=74
July 13, 2009 at 1:40 am
Hi,
Even when i execute below Simple Query its take more than 20 minutes
update Auditdata set tatcalltype = 'null' where tatcalltype is null
tatcalltype datatype is varchar
i put the index on tatcalltype column
July 13, 2009 at 10:46 am
Hi,
I'm a little confused... :ermm:
your first and your second post both describe a rather large update statement.
However, your third post is regarding an insert statement and in the last post you're talking about a completely different, but more simple update statement.
I would have expected data definition and sample data for the first case rather than two new issues leaving the first one unanswered at the same time...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply