更新 NText 导致长时间延迟/超时

发布于 2024-07-26 12:47:06 字数 954 浏览 3 评论 0原文

我正在尝试使用经典 ASP 更新 SQL 2000 中的 NText 字段。这是我用来执行此操作的代码。任何人都可以指导如何加快速度吗？或者我坚持下去了。

set Cnn = server.CreateObject("ADODB.connection")
Cnn.ConnectionString = Application("Cnn_ConnectionString")
Cnn.open 
set rs = server.CreateObject("ADODB.Recordset")

rs.CursorType = adoOpenDynamic
rs.LockType = adLockOptimistic      
conChunkSize = 100   
rs.Open "MyTable",Cnn, , , adCmdTable 
rs.Find "MyDataId=" & request("DataId"),,adSearchForward,1     

lngOffset = 0   
lngLogoSize = len(request("txtMyEntry"))*2
Do while lngOffset < lngLogoSize
    varChunk = LeftB(RightB(request("txtMyEntry"), lngLogoSize - _
               lngOffset), conChunkSize)
        rs("MyDataField").AppendChunk varChunk
        lngOffset = lngOffset + conChunkSize
    Loop

rs.Update
rs.Close

哦，这段代码几乎是逐字来自 MSDN 站点。

原文

I'm trying to update an NText field in SQL 2000 using Classic ASP. Here is the code I'm using to do it. Any one have any pointers on how to maybe speed it up? Or am I stuck with it.

set Cnn = server.CreateObject("ADODB.connection")
Cnn.ConnectionString = Application("Cnn_ConnectionString")
Cnn.open 
set rs = server.CreateObject("ADODB.Recordset")

rs.CursorType = adoOpenDynamic
rs.LockType = adLockOptimistic      
conChunkSize = 100   
rs.Open "MyTable",Cnn, , , adCmdTable 
rs.Find "MyDataId=" & request("DataId"),,adSearchForward,1     

lngOffset = 0   
lngLogoSize = len(request("txtMyEntry"))*2
Do while lngOffset < lngLogoSize
    varChunk = LeftB(RightB(request("txtMyEntry"), lngLogoSize - _
               lngOffset), conChunkSize)
        rs("MyDataField").AppendChunk varChunk
        lngOffset = lngOffset + conChunkSize
    Loop

rs.Update
rs.Close

Oh and this code is almost verbatim from the MSDN site.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尛丟丟 2024-08-02 12:47:06

首先我会消除 90 年代的分块。

然后是：-

rs.Open "MyTable",Cnn, , , adCmdTable 
rs.Find "MyDataId=" & request("DataId"),,adSearchForward,1

哎呀！您可能认为 ADO 智能地要求 SQL Server 根据索引的 MyDataId 字段查找该记录，但我打赌它不会。它很可能会拉动表的全部内容，直到到达记录。

这确实应该通过 UPDATE T-SQL 语句和 ADODB.Command 对象来完成。

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandType = 1 '' // adCmdText
cmd.CommandText = "UPDATE MyTable SET MyDataField = ? WHERE MyDataId = ?"
cmd.Parameters.Append cmd.CreateParameter("dataField", 203, 1, Len(txtMyEntry), txtMyEntry)  '' // 203 = asLongVarWChar, 1 = adParamInput
cmd.Parameters.Append cmd.CreateParameter("id", 3, 1, , CInt(DataID)) '' // 3 = adInteger
cmd.Execute

First I would eliminate the chunking which is so 90's.

Then there is:-

rs.Open "MyTable",Cnn, , , adCmdTable 
rs.Find "MyDataId=" & request("DataId"),,adSearchForward,1

Yikes! You'd like to think that ADO intelligently asked SQL server to find that record based on the indexed MyDataId field but bet it doesn't. Its most likely pulling the entire contents of the table across until the record is arrived at.

This really should be done with an UPDATE T-SQL statement and an ADODB.Command object.

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cnn
cmd.CommandType = 1 '' // adCmdText
cmd.CommandText = "UPDATE MyTable SET MyDataField = ? WHERE MyDataId = ?"
cmd.Parameters.Append cmd.CreateParameter("dataField", 203, 1, Len(txtMyEntry), txtMyEntry)  '' // 203 = asLongVarWChar, 1 = adParamInput
cmd.Parameters.Append cmd.CreateParameter("id", 3, 1, , CInt(DataID)) '' // 3 = adInteger
cmd.Execute

回复收藏 0 原文

~没有更多了~