如何优化更新查询

Question

我有一个包含2个字段的表：PIN和文件名

我使用存储过程读取共享文件夹并填充fileName字段，例如filexxx_PIN.pdf；然后我用光标浏览记录，提取PIN并更新记录；

DECLARE @PIN AS VARCHAR(100);
DECLARE @name AS VARCHAR(500);
DECLARE @start INT , @length INT;
DECLARE db_cursor CURSOR LOCAL FOR  SELECT FileName FROM [dbo].[pharmaCV];
SET @length = 11
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @PIN = RTRIM(@name)
SET @PIN = RIGHT(@PIN, CHARINDEX('_', REVERSE(@PIN) + '_') - 1)
SET @PIN = SUBSTRING (@PIN  , 0, @length ) 
UPDATE pharmaCV SET PIN = @PIN WHERE FileName LIKE '%'+@PIN +'%'
FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor  
DEALLOCATE db_cursor 

550条记录需要25秒；

有人能帮我们做一个UPDATE语句吗？

Answer 1

通常我们不使用Cursor进行更新。我你的情况下你可以简单地运行下面的查询

UPDATE [dbo].[pharmaCV]
SET PIN = SUBSTRING (RIGHT(RTRIM(FileName), CHARINDEX('_', REVERSE(RTRIM(FileName)) + '_') - 1)  , 0, 11 ) 
WHERE FileName LIKE '%'+SUBSTRING (RIGHT(RTRIM(@name), CHARINDEX('_', REVERSE(RTRIM(@name)) + '_') - 1)  , 0, 11 ) +'%'