时间戳

首页 > 解决方案 > SQL查询优化更新

sql - SQL查询优化更新

问题描述

此查询需要很长时间才能更新我很想优化此查询，我们将不胜感激。

DECLARE @text1 NVARCHAR(50) = N'test1'
DECLARE @text2 NVARCHAR(50) = N'test2'
DECLARE @text3 NVARCHAR(50) = N'test3'
DECLARE @text4 NVARCHAR(50) = N'test4'
DECLARE @text5 NVARCHAR(50) = N'test5'

UPDATE dbo.Session SET
text1 = CASE
WHEN text1 IS NOT NULL THEN @text1
ELSE text1 END  ,
text2 = CASE
WHEN text2 IS NOT NULL THEN @text2
ELSE text2 END,
text3 = CASE
WHEN text3 IS NOT NULL THEN @text3
ELSE text3 END,
text4 = CASE
WHEN text4 IS NOT NULL THEN @text4
ELSE text4 END,
text5 = CASE
WHEN text5 IS NOT NULL THEN @text5
ELSE text5 END

标签： sqlsql-serverperformancequery-optimizationtun

解决方案

创建新表通常比更新现有表更优化（时间上）。

create table dbo.Session_1 nologging as 
select /*Insert the other columns from the old table that you want to keep here*/
    decode(text1, null, null, 'test1') as text1,
    decode(text2, null, null, 'test1') as text2,
    decode(text3, null, null, 'test1') as text3,
    decode(text4, null, null, 'test1') as text4,
    decode(text5, null, null, 'test1') as text5
from dbo.Session;

然后您可以删除旧表并重命名新表：

drop table dbo.Session;
alter table dbo.Session_1
rename to dbo.Session;

推荐阅读