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

Solution 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

Solution 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.

Solution 3

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

Try removing unnecessary indexes.

Comments

[Admin]

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

[ypercubeᵀᴹ]

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

[Tobias Koller]

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

[Tobias Koller]

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

[Tobias Koller]

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 ;)

[Grzegorz Gierlik]

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.

[Adir D]

@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.

[Tobias Koller]

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

[Ashish Kumar Jaryal]

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