InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 01:52:28 GMT20http://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxHi,I'm a little confused... :ermm:[b][url=http://www.sqlservercentral.com/Forums/FindPost751591.aspx] your first [/url] [/b]and [b][url=http://www.sqlservercentral.com/Forums/FindPost751635.aspx] your second [/url][/b] post both describe a rather large update statement.However, your [b][url=http://www.sqlservercentral.com/Forums/FindPost751811.aspx] third post[/url][/b] is regarding an insert statement and in the [b][url=http://www.sqlservercentral.com/Forums/FindPost751827.aspx] last post [/url][/b] 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...Mon, 13 Jul 2009 10:46:29 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxHi, Even when i execute below Simple Query its take more than 20 minutesupdate Auditdata set tatcalltype = 'null' where tatcalltype is nulltatcalltype datatype is varchari put the index on tatcalltype columnMon, 13 Jul 2009 01:40:21 GMTmaxyogesh2002http://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxDear Friends Thanks for your reply if i run below query its take more than 35 minutesinsert 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 Mon, 13 Jul 2009 00:26:17 GMTmaxyogesh2002http://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxIt'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.Sat, 11 Jul 2009 14:09:13 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxDear 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 AdvanceSat, 11 Jul 2009 13:48:03 GMTmaxyogesh2002http://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxHi,please check if the following code will help you to get the expected results.[b]IMPORTANT NOTE:[/b]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.[code]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=1inner 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 nspON 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)[/code]Sat, 11 Jul 2009 13:25:49 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxDear Friends, Thanks for your Reply... Actually the above code is my Store Procedure i m passing value to @Table and @AuditMasterID through the front endi 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))asBEGINDeclare @SQLStatement varchar(2000)Declare @PrefixNo varchar(20)Declare @PrefixLen varchar(20)Declare @AfterPrefixLen varchar(20)DECLARE Cur_Prefix CURSORFORSELECT PrefixNo,PrefixLen,AfterPrefixLen FROM NoSeriesMaster_Prefix WHERE PrefixType='SMS' order by IDOPEN Cur_PrefixFETCH NEXT FROM Cur_Prefix INTO @PrefixNo,@PrefixLen,@AfterPrefixLenWHILE @@FETCH_STATUS = 0BEGIN 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,@AfterPrefixLenENDCLOSE Cur_PrefixDEALLOCATE Cur_Prefixendplz help mewhat should i do.....Sat, 11 Jul 2009 12:49:34 GMTmaxyogesh2002http://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxHi,the following part of your code is a little confusing to me:[code]... 'update '+@TABLE+' set AuditData.TATCallType=''12'', AuditData.TATCallUnit=''1'' ' ...[/code]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 errorMsg 4104, Level 16, State 1, Line 1The 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: 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...Sat, 11 Jul 2009 10:07:30 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxHello,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 MarshSat, 11 Jul 2009 09:42:02 GMTJohn H Marsh http://www.sqlservercentral.com/Forums/Topic751591-8-1.aspxDear 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...Sat, 11 Jul 2009 08:58:49 GMTmaxyogesh2002