SQL Update is really slow (about 20-50sec), Select takes less than 1 second

Question

I have a SQL Tabe "Document" which contains a lot of rows (up to a few millions).

When I'm executing an Select-Statement it takes about 0.5seconds. But when I'm executing an Update with the very same WHERE-clause it takes about 20 to 50 seconds, depending on the amount of affected rows.

Here are my Statments.

//Select

SELECT * FROM Document 
WHERE (State=20 OR State=23) AND 
LetterClosed IS NOT NULL AND 
TYPE=0 AND
SendLetter=1

//Update

UPDATE Document set State=32 
WHERE (State=20 OR State=23) AND 
LetterClosed IS NOT NULL AND 
TYPE=0 AND
SendLetter=1

The OR-Mapper internally send this update-statement as followed to the database:

exec sp_executesql N'Update
Document
SET
    State=@p4
WHERE
(
  (
    (
      (Document.State = @p0 OR Document.State = @p1) 
      AND Document.LetterClosed IS NOT NULL
    ) 
    AND Document.Type = @p2
  ) 
  AND Document.SendLetter = @p3
)'
,N'@p0 int,@p1 int,@p2 int,@p3 bit,@p4 int',@p0=20,@p1=23,@p2=0,@p3=1,@p4=32

The problem is, that I get an Timeout-Exception after 30 seconds from my LightSpeed(Database OR-Mapper in c#).

Could anyone help me here?

Edit:

And this are our indexes automatically created by SQL-Server:

CREATE NONCLUSTERED INDEX [_dta_index_Document_9_133575514__K42_1_2_3_4_5_6_7_8_9_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_32_33_34_] ON [Document] 

(
    [State] ASC
)
INCLUDE ( 
[Id],[DocumentId],[SendLetter],[SendFax],[Archive],[Crm],[Validation],[CreationDate],[PageCount],
[InformationLetter],[TermsOfDelivery],[DeliveryTypeNo],[SeparateDelivery],[FormName],[FormDescription],[TemplateFileName],[RecipientType],
[HealthInsuranceNo],[FamilyHealthInsuranceNo],[PensionInsuranceNo],[EmployerCompanyNo],[RecipientName1],[RecipientName2],[RecipientName3],
[RecipientStreet],[RecipientCountryCode],[RecipientZipCode],[RecipientCity],[RecipientFaxNo],[AuthorId],
[AuthorName],[AuthorEmailAddress],[CostcenterDepartment],[CostcenterDescription],[MandatorNo],[MandatorName],[ControllerId],
[ControllerName],[EditorId],[EditorName],[StateFax],[Editable],[LetterClosedDate],[JobId],[DeliveryId],[DocumentIdExternal],[JobGroupIdExternal],
[GcosyInformed]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_dta_index_Document_9_133575514__K2_1_46] ON [Document] 
(
    [DocumentId] ASC
)
INCLUDE ( [Id],
[JobId]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_dta_index_Document_9_133575514__K46_K2] ON [Document] 
(
    [JobId] ASC,
    [DocumentId] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go



CREATE NONCLUSTERED INDEX [Document_State_Id] ON [Document] 
(
    [State] ASC,
    [Id] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [Document_State_CreationDate] ON [Document] 
(
    [State] ASC,
    [CreationDate] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

Edit 2: Now I have an graphical execution-plan : Execution-plan: https://skydrive.live.com/redir?resid=597F6CF1AB696567!444&authkey=!ABq72SAWXOoAXfI

Execution-plan Index Update details: https://skydrive.live.com/?cid=597f6cf1ab696567&id=597F6CF1AB696567%21445&sff=1&authkey=!ADDPWvxB2JLLvWo

This SQL-Update took about 35 seconds to execute. Usually this Update only takes 0,3 seconds. It seems that another process blocked this one. I saw some other selects which started in the middle of this update and waited till the update was finished until they finished there select-execution.

So it seems that the index itself is correct (usually 0,3 sec execution). All selects (from java/jtds, php, .net) are isolation level read-committed (default). Would it help me here to change all the selects to read uncommitted to avoid this blocking during index-update?

Thanks Tobi

Answer 1

I had this problem once on SQL Server 2008 and SQL Server 2014 linked servers. A workaround for me was to store the "Select" results into a temporary table and use this to do the update rather doing the complex querying and the update at once.

In your case this would be:

--Select

SELECT * FROM Document
into #temp 
WHERE (State=20 OR State=23) AND 
LetterClosed IS NOT NULL AND 
TYPE=0 AND
SendLetter=1

--Update

UPDATE Document set State=32 
from #temp
WHERE #temp.id = Document.id 
--assuming that id is your PK

Answer 2

Without execution plan we can only guess what happens.

I would start from:

1. Check how many indexes document table has (but it's hard to believe that updating indexes takes such a time).
2. Check if any triggers are executed on update.

All of these should be visible on execution plan.

Another reason could be that SQL engine has one execution plan for SELECT query and different one for UPDATE query...

UPDATE

After looking into indexes.

In my opinion index _dta_index_Document_9_133575514__K42_1_2_3_4_5_6_7_8_9_11_12_13_14_15_16_17_18_19_20_21_22_23_24_25_26_27_28_29_30_31_32_33_34_ is completely wrong.

It include a lot of columns which could make updating slow.

Try to remove it or replace it with CLUSTERED index on state column. CLUSTERED index* include* (has direct access) to all columns of record without extra reads.

Probably it should be combined with one of other indexes started with state column -- I assume state has just a few values.

Unfortunately I am not able to interpret execution plan in text format.

Answer 3

Probably you have indexes on table Document. Indexes make selects faster but slow update/inset/delete operation.

Try removing unnecessary indexes.