使用 LIKE 优化 SQL UPDATE

发布于 2024-11-16 21:26:35 字数 448 浏览 2 评论 0原文

我尝试在大约 25k 行的表上运行以下 UPDATE，它已经运行了超过 24 小时，但尚未完成。我只需要运行一次更新，所以时间不是太大的问题，但任何那么长的时间对我来说都是行不通的。 UPDATE 语句确实有效，因为我尝试仅在几条记录上单独运行它，但是当您将它应用于整个表时，它就会陷入困境。

我确信 LIKE 会导致速度变慢，但我不知道如何使其变得更简单或更快，任何线索将不胜感激：

UPDATE INVENTORY i2

SET i2.BVRTLPRICE01 =

(SELECT i1.BVRTLPRICE01 FROM INVENTORY i1

WHERE 

i1.CODE = REPLACE(LEFT(i2.CODE,1), 'S', 'C') + SUBSTRING(i2.CODE,2,LENGTH(i2.CODE)))        

WHERE 

i2.CODE like 'S%'

原文

I have tried running the following UPDATE on a table with about 25k rows and its been running for over 24hours and it has not completed. I only need to run this UPDATE once so time is not too much of an issue, but anything that long is just not going to work for me. The UPDATE statement does work as I have tried running it in isolation on just a few records, but when you apply it over the full table is when it bogs down.

Im certain that the LIKE is causing the slow down, but I do not have any idea how to make this any simpler or faster, any clues would be appreciated:

UPDATE INVENTORY i2

SET i2.BVRTLPRICE01 =

(SELECT i1.BVRTLPRICE01 FROM INVENTORY i1

WHERE 

i1.CODE = REPLACE(LEFT(i2.CODE,1), 'S', 'C') + SUBSTRING(i2.CODE,2,LENGTH(i2.CODE)))        

WHERE 

i2.CODE like 'S%'

分享到QQ

分享到微博

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

发布评论

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

剪不断理还乱 2024-11-23 21:26:35

我只使用过 Pervasive 一两次，但我无法想象这个引擎会如此糟糕，以至于问题会像您所拥有的那样简单 LIKE 。问题更有可能是子查询。

我会尝试这样做：

UPDATE
    I2
SET
    BVRTLPRICE01 = I1.BVRTLPRICE01
FROM
    INVENTORY I2
INNER JOIN INVENTORY I1 ON
    I1.CODE = REPLACE(LEFT(I2.CODE, 1), 'S', 'C') +
              SUBSTRING(I2.CODE, 2, LENGTH(I2.CODE)))
WHERE
    I2.CODE LIKE 'S%'

另外，请确保您只加入 I1 中的一行，并且不会为 I2 中的每一行获取很多行。

I've only worked with Pervasive once or twice, but I can't imagine that the engine is that horrible that the problem would be a simple LIKE as you have it. The issue is more likely the subquery.

I would try this instead:

UPDATE
    I2
SET
    BVRTLPRICE01 = I1.BVRTLPRICE01
FROM
    INVENTORY I2
INNER JOIN INVENTORY I1 ON
    I1.CODE = REPLACE(LEFT(I2.CODE, 1), 'S', 'C') +
              SUBSTRING(I2.CODE, 2, LENGTH(I2.CODE)))
WHERE
    I2.CODE LIKE 'S%'

Also, make sure that you're only joining on one row from I1 and not getting many rows for each row in I2.

回复收藏 0 原文

无风消散 2024-11-23 21:26:35

我有一个小小的预感。可能是错误的，但基于疯狂的运行时间和小数据集。尝试添加

LIMIT 1

到您的子查询中。

i have a small hunch. might be wrong but based upon the crazy runtime and small dataset. try to add

LIMIT 1

to your sub query.

回复收藏 0 原文

兲鉂ぱ嘚淚 2024-11-23 21:26:35

使用正确的连接而不是子查询

UPDATE INVENTORY i2
SET i2.BVRTLPRICE01 = i1.BVRTLPRICE01 
FROM INVENTORY i1,INVENTORY i2
WHERE i1.CODE = REPLACE(LEFT(i2.CODE,1), 'S', 'C') + SUBSTRING(i2.CODE,2,LENGTH(i2.CODE)))  
AND i2.CODE like 'S%'

use proper join instead of subquery

UPDATE INVENTORY i2
SET i2.BVRTLPRICE01 = i1.BVRTLPRICE01 
FROM INVENTORY i1,INVENTORY i2
WHERE i1.CODE = REPLACE(LEFT(i2.CODE,1), 'S', 'C') + SUBSTRING(i2.CODE,2,LENGTH(i2.CODE)))  
AND i2.CODE like 'S%'

回复收藏 0 原文

~没有更多了~