sql server executing update it takes more time

Question

I have two tables (UserTable and UserProfile) and the Structure:

create table userTable(
id_user int identity(1,1) primary key , 
Name varchar(300) not null , 
Email varchar(500) not null , 
PasswordUser varchar(700) not null,
userType int ,
constraint usertype_fk foreign key(userType) REFERENCES userType(id_type)
on delete set null
)

and userPtrofile:

    create table UserProfile(
    id_profile int identity(1,1) primary key , 
    ClientCmpName varchar(300) null,
    Clientaddress varchar(500) null,
    phone varchar(50) null,
    descriptionClient varchar(400) null,
    img image null,
    messageClient text , 
    fk_user int ,
    constraint fkuser foreign key(fk_user) references userTable(id_user) 
    on delete cascade  
    )

I am using SQL Server 2008. The problem is that when I update records the executing load without executing this is sample query:

update UserProfile set messageClient=N'010383772' where fk_user=2;

screenshot

I suspect part of the performance hit is that it's updating a text field which isn't stored inline with the rest of the row data and is an extra hop per record to update. However the documentation on this (technet.microsoft.com/en-us/library/…) says that as long as the data is small, it shouldn't have to do that extra hop. I don't know if this optimization was done for 2008 though. — DiskJunky

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-05-02T12:24:48

If your concern is performance for this query:

update UserProfile
    set messageClient = N'010383772'
    where fk_user = 2;

Then an index will be very helpful:

create index idx_UserProfile_fkuser on UserProfile(fk_user);

This should make the query almost instantaneous.

Note: indexes can slow down inserts and other operations. This is usually not a big issue, and having indexes on foreign key columns is common.

sql server executing update it takes more time

5 Answers