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