问题描述
我的数据库中有个字段是NTEXT类型的。里面的数据长度大概是20万以上。然后我再循环另一个表中的某个字段替换与这个字段相匹配的值。速度超过了7秒。能不能提速啊。我在网上找了一些方法。好些都在七秒以上。需求大概跟下面的一样。usemastergoifobject_id('sp_replaceTextCol')isnotnulldropprocsp_replaceTextColgocreateproceduresp_replaceTextCol@tableNamesysname,@colNamesysname,@oldStrnvarchar(512),@newStrnvarchar(512),@whereStrnvarchar(200)=''/*万一昨天有收集我这个过程的同志注意了：存在严重bug，就是第一个字母不能是查找内容原来的替换语句是直接抄的（本来就是因为网上搜来的代码无法工作我才自己写，能少写几个单词我就必定抄），因为可以用所以就将就用了，结果早上睡醒忽然想到一个严重bug，于是起来改掉了上面说的内容谴责下自己抄别人代码的时候不注意阅读，也谴责下到处乱贴代码不做检查的网友然后这个修改还是有严重bug：查找内容不能是替换内容的一部分……使用charindex代替patindex可以修正此bug，不过会导致另一个bug：第4000个以后的文本不被处理使用substring可以完美解决此问题，不过需要注意substring不要把搜索字符串截断掉随便找种语言都基本上是不超过10行代码搞定的事情，在t-sql里却要多写那么多代码，于是懒得再写了。*//*作用：为一个表内的text做统一替换的存储过程版本:1.0缺点：慢，事实上还没办法支持任意表bug：查找内容不能是替换内容的一部分因为varchar的长度有限，当然还可以扩展，不过在sql2005里已经没这个问题了，懒得再写。realgz2008-05-06*/asbeginsetnocountondeclare@cursornvarchar(4000),@fetchnvarchar(4000),@insertnvarchar(4000),@wherenvarchar(4000)declare@replaceExecnvarchar(4000),@checkExecnvarchar(4000)declare@rpPtrvarbinary(16),@rpPostionint,@rpLenintifobject_id('tempdb..#key')isnotnulldroptable#key--获得主键列表selectsc.namekeyNameinto#keyfromsysobjectssojoinsysindexesidxonso.parent_obj=idx.idjoinsysindexkeysidkonso.parent_obj=idk.idandidx.indid=idk.indidjoinsyscolumnssconso.parent_obj=sc.idandidk.colid=sc.colidwhereso.xtype='PK'andso.parent_obj=object_id(@tableName)andidx.status&0x800>0if@@rowcount<1beginraiserror('表不符合要求或者没有这个表',12,1)returnend--增加键值列altertable#keyaddkeyValuesql_variantaltertable#keyaddprimarykey(keyName)--替换的长度select@rpLen=len(@oldStr),@oldStr='%'+@oldStr+'%'--游标声明语句select@cursor='declare#ccursorstaticforselecttextptr('+@colname+'),PATINDEX('''+replace(@oldStr,'''','''''')+''','+@colname+')'select@cursor=@cursor+',['+keyName+']'from#keyselect@cursor=@cursor+'from'+@tablename+''+@whereStr--为游标的提取生成语句select@fetch='',@insert='',@where=''select@fetch=@fetch+'declare@'+keyName+'sql_variant'+char(13)from#keyselect@fetch=@fetch+'fetchnextfrom#cinto@rpPtr,@rpPostion'select@fetch=@fetch+',@'+keyName+char(13)from#key--把游标提取出来的值放到临时表的语句select@insert=@insert+'insertinto#key(keyName,keyValue)'select@insert=@insert+'select'''+replace(keyName,'''','''''')+''',@'+keyName+'unionall'+char(13)from#keyselect@insert=left(@insert,len(@insert)-10)select@fetch=@fetch+@insert--每次获得游标行对应text指针的语句select@where='where1=1'select@where=@where+'and['+keyName+']=(selectkeyValuefrom#keywherekeyName='''+replace(keyName,'''','''''')+''')'from#key--改写的sqlselect@replaceExec='updatetext'+@tablename+'.'+@colname+'@rpPtr@rpPostion@rpLen@NewStr'--检查是否存在目标的sqlselect@checkExec='select@rpPostion=PATINDEX('''+replace(@oldStr,'''','''''')+''','+@colname+')from'+@tablename+@where--声明游标execsp_executesql@cursortruncatetable#keyopen#c--提取第一行execsp_executesql@fetch,N'@rpPtrvarbinary(16)output,@rpPostionintoutput',@rpPtroutput,@rpPostionoutput--print@fetchwhile@@fetch_status=0begin--只要@rpPostion>0证明还需要替换while@rpPostion>0begin--替换select@rpPostion=@rpPostion-1execsp_executesql@replaceExec,N'@rpPtrvarbinary(16),@rpPostionint,@rpLenint,@newStrnvarchar(512)',@rpPtr,@rpPostion,@rpLen,@NewStr--重新判断是否需要替换execsp_executesql@checkExec,N'@rpPostionintoutput',@rpPostionoutputendtruncatetable#key--提取下一行execsp_executesql@fetch,N'@rpPtrvarbinary(16)output,@rpPostionintoutput',@rpPtroutput,@rpPostionoutputendclose#cdeallocate#cifobject_id('tempdb..#key')isnotnulldroptable#keysetnocountoffendgousetempdbgocreatetabletest(idintidentity,bntextprimarykey(id))godeclare@xintselect@x=1while@x<1600begininsertintotest(b)selectreplicate(newid(),1000)select@x=@x+1endgoselect*fromtestgoexecsp_replaceTextCol'test','b','1','$$'goselect*fromtestgodroptabletestgo
解决方案
解决方案二：
解决方案三：
建立适当的索引，比如给主键建立索引用了游标的话，本来速度就会受影响
解决方案四：
应该是你列数据太多原因1.建索引，主键索引全文索引2.查询优化排序3.增加缓存设置4.临时表5增加内存（硬件方面的了）
解决方案五：
NTEXT是不适合建索引的建议考虑全文检索