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

Cress

How many rows are returned by the SELECT? What DBMS are you using? (I guess SQL-Server from the sp_executesql) Can you show the CREATE TABLE statement (and existing indexes)? Can you add the execution plan? – Halt

We are using MSSQL-Server 2005. I will try to post the execution-plan and create-statement. – Cress

Also include any indexes on that table, you need to do that to get an accurate answer, it will be either too many indexes or too few, or you may just need to alter one slightly. When you get the execution plan in SSMS it may even suggest an index which will speed it up. – Faulty

Do you have the same execution times if you run queries from your app and from SSMS? – Marni

Usually people attach screenshot with plan, but it could not work for big plans. – Marni

see my links above. they point to the screenshots of my executionplan. – Cress

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

Decameter

It worked for me also. I don't know why? Can anyone please throw some light on this behaviour? – Ludwick

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.

Marni

here is my execution-plan. link had to put it in a txt.file – Cress

thanks for your answer. Which format of execution plan can you read? maybe i can change it to another format. I will try to remove all indexes and create new ones without trusting the MSSQL database optimizer created indexes ;) – Cress

Image is the easiests one, however it doesn't have hints. I saw that plan can be saved as XML file, but I am not sure I could interpret it better. Look at your plan, search for IO costs, number of records and bytes read, check if you don't have table scans (usually bad thing), index scans or RID lookups -- these are typical bottle necks. – Marni

Answer 3

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

Try removing unnecessary indexes.

Beilul

Possibly, but only a single index needs to be updated (State; if it is even indexed) .. – Cambrel

It's probably the lack of indexes that is causing this than the existence of a lot. – Halt

When i created the table I ran some tests, logged every statement with the profiler and used the SQLServer DatabaseOptimizer to create my Indexes. – Cress

@pst why "only a single index"? Are you assuming that only single column indexes exist? What if State is part of many indexes? Without the plan we don't know, but I don't think you can state that this will only affect one index. – Shamefaced

hi Aaron. I posted the XML-Executionplan already. Maybe you can have a look at it? its graphical and should be easier to read. here is the Link agail: Edit 2: Now I have an graphical execution-plan : Execution-plan: link Execution-plan Index Update details: link – Cress