SQL query optimize for update

Question

This query take long time to update I would love to optimize this query, your help will be appreciated.

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

Your code actually looks pretty optimal already. Do you have some kind of performance problem? — Tim Biegeleisen, Oct 31 '19 at 08:20
add a `WHERE` clause checks for `text1` or `text2` etc `NOT NULL` — Squirrel, Oct 31 '19 at 08:34
@Ahmed . . . I think the logic in your query is backwards. Why would you want to replace *existing* data with a single value? — Gordon Linoff, Oct 31 '19 at 11:35

score 0 · Answer 1 · answered Oct 31 '19 at 08:35

You can try with this:

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


UPDATE dbo.Session
SET text1 = ISNULL(text1,@text1)  ,
text2 = ISNULL(text2,@text2),
text3 = ISNULL(text3,@text3),
text4 = ISNULL(text4,@text4),
text5 = ISNULL(text5,@text5)

score 0 · Answer 2 · answered Oct 31 '19 at 08:38

It is often more optimal (timewise) to create a new table than to update an existing one.

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;

Then you can drop the old table and rename the new one:

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

score 0 · Answer 3 · answered Oct 31 '19 at 11:32

First, I think your logic is backwards. Normally, the intent is to assign a value when the existing value is NULL -- not to replace valid values. However, that is not what your question is, so I'll proceed.

There are two things that you can do about a simple update query:

You can ensure that each row is updated only once and that no update is attempted on rows with no updates.
If a significant proportion of rows are being updated, then replacing the data is often more efficient (insert is faster than update for a given number of rows).

The first approach suggests a WHERE clause:

UPDATE dbo.Session    
    SET text1 = (CASE WHEN text1 IS NOT NULL THEN @text1
    END),
        text2 = (CASE WHEN text2 IS NOT NULL THEN @text2
    END),
        text3 = (CASE WHEN text3 IS NOT NULL THEN @text3
    END),
        text4 = (CASE WHEN text4 IS NOT NULL THEN @text4
    END),
        text5 = (CASE WHEN text5 IS NOT NULL THEN @text5 END)
    WHERE text1 IS NOT NULL OR
          text2 IS NOT NULL OR
          text3 IS NOT NULL OR
          text4 IS NOT NULL OR
          text5 IS NOT NULL;

SQL query optimize for update

3 Answers3