optimizing SQL UPDATE with LIKE

Question

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%'

Which RDBMS are you using, and which version? – Yuck Jun 23 '11 at 16:34 — Yuck, Jun 23 '11 at 16:34

score 0 · Answer 1 · edited Jun 23 '11 at 17:07

0

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%'

edited Jun 23 '11 at 17:07

Tom H

46,766
14
87
128

answered Jun 23 '11 at 16:39

Ovais Khatri

3,201
16
14

score 0 · Answer 2 · answered Jun 23 '11 at 16:39

0

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.

answered Jun 23 '11 at 16:39

Gidon Wise

1,896
1
11
11

score 0 · Accepted Answer · answered Jun 23 '11 at 17:13

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.

optimizing SQL UPDATE with LIKE

3 Answers3