sql server executing update it takes more time

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

---

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.

---

Dumb question, why are you trying to do an update based on a [userType] value ?

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

Don't you want to update this value on one specific [UserProfile] based on its ID (which is a Primary Key, so would be much faster)

UPDATE [UserProfile] 
SET [messageClient]='010383772' 
WHERE id_profile=2;

Perhaps the performance problem is due to your UPDATE attempting to update all of your [UserProfile] records with this particular UserType value...?

Or I'm missing the point of what you're trying to do (and how many records you're attempting to update).

---

Maybe you have alredy started a transaction (BEGIN TRANSACTION) on the table in another process (maybe another query editor page) and until you don't stop that transaction the table would not be available for updates. Check the variable select @@trancount, or try do rollback the updates you have already made (ROLLBACK TRANSACTION). Also check if other tables can be update without issues.

链接地址: http://www.djcxy.com/p/15134.html

上一篇: 内部连接表和视图时性能显着降低

下一篇: SQL Server执行更新需要更多时间