1

I've table, example as

Id int primary key(clustered index)
Name varchar(255)not null (non-clustered index)
..
..
Other columns

I execute stored procedur as

update table1 set table1.Name=isnull(@Name,table1.Name) ,( updating other columns) where Id=@Id;

many times Name equals @Name; In execution plan i saw enter image description here

if i change stored procedure as

update table1 set ( updating other columns) where Id=@Id;

i saw Cost:15%

Question:

I need to change this procuder for two situation when

  1. table1.Name= @Name
  2. table1.Name <> Name.

In first case table1.Name doesn't change value or index doesn't update and clustered index update cost 15%;

in second case table1.Name changes value and clustered index update cost 50%.

Why index is updating when update a equal value?

i can't select "Name" and compare befor updatings

I know that create varchar column index isn't good idea

Update

update query with change Name column(execution CPU time 200ms) enter image description here

update query without change Name column(execution CPU time 70ms) enter image description here

Update

maybe anybody can help with trigger if it can help with this issue?

Update

Question is closed

0

how many record will updated at one go and how frequently ?

Why index is updating when update a equal value?

Because optmizer is not smart enough to detect if you are updating same value.

It is good that index is rebuild of its own,though it is time consuming.

If you disable index then you have to rebuild it later.

your situation is very serious ,1.one row, 30 000 per minute Hope you have handle the requirement correctly.

As by definition of index or disadvantage of index is that it slow down update statement if that table is so frequently updated.

Now you have to answer how frequently Name is use in predicate and how.is index helping you there.Depending upon all this it is better to drop index on Name.

Don't keep any non clustered index in these column which are use in this proc.

since @name 10% is null you need dynamic sql whether you drop index or not. it will improve your query on 90% occasion.

sp_executeSql example (just an example)

declare @Name varchar(250)
declare @othercol varchar(250)
declare @Id int=1

DECLARE @ParmDefinition nvarchar(500); 
set @Sql='update table1 set'

if (@Name is not null)
set @Sql=@Sql+' Name=@Name1 ,'


set @Sql=@Sql+' othercol=@othercol1 '


set @Sql=@Sql+' where Id=@LocalID '

SET @ParmDefinition = N'@LocalID int , @Name1 varchar(250) , @othercol1 varchar(250)';  

print @Sql

EXECUTE sp_executesql @Sql, @ParmDefinition,  
                      @LocalID = @Id,@Name1=@Name,@othercol1=@othercol ; 
  • 1.one row, 30 000 per minute 2. @name in 10% is null 3. Key look search by clustered index – KreminT Mar 23 '18 at 12:19
  • Do can i to teach optimizer to be more smarter? Maybe some triggers or other function in updating process – KreminT Mar 23 '18 at 12:25
  • variable @name only in 10% isn't equal with column value – KreminT Mar 23 '18 at 12:33
  • @KreminT, I have edited my answer also you need to reconsider "the way you are handling you requirement" apart from optimizing your query. – KumarHarsh Mar 23 '18 at 14:28
  • I can't delete this non-clustered index, it's very importent for other procedurs. Using sp_executesql will help me only in 90% from 10% of all query, i need more. other 80%, where @name is not null and equals NAME column value – KreminT Mar 23 '18 at 15:04
0

0 down vote unaccept

For fix my issue, I created a triger

CREATE TRIGGER [dbo].[trg_BeforeUpdateSubject] 
   ON  [dbo].[Subject] 
   INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
        declare @old_val  nvarchar(255),@new_val nvarchar(255) ;
        select
@old_val=[Name]   
from deleted;
select 
@new_val=[Name]
from inserted;
     if (@old_val=@new_val)  
    BEGIN

        UPDATE 
            Subject
        SET
            set new values for other colums,
            Subject.DLC             =   GETDATE()
        FROM 
            inserted
        WHERE
            Subject.Id = inserted.Id            
    END ELSE
    BEGIN
        UPDATE 
            Subject
        SET
            Subject.Name = inserted.Name,
            set new values for other colums,
            Subject.DLC             =   GETDATE()
        FROM 
            inserted
        WHERE
            Subject.Id = inserted.Id                
    END

END

It's work perfect. Average value for update query is 90ms(CPU), old value 200ms

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.