I'm using SQL2008 and .NET 4.
The following UPDATE query causes timeout errors.
Table Pages has 600,000 records.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
UPDATE Pages SET Checked = 1 , LastChecked = GETDATE() OUTPUT Inserted.ID, Inserted.Title INTO @Updated WHERE ID in (SELECT TOP (@Count) ID FROM Pages WHERE Checked = 0 and FileID = @FileID ORDER BY ID) SELECT * FROM @Updated END
On a different thread I Insert records to
Pages table, this runs continually (a page is added every 1 sec or less).
INSERT INTO Pages (Title ,.......) VALUES (@Title , .......)
Why do I get the timeout error and how can I solve it?
Doesn't SQL wait indefinitely on blocking situation ?
It's your command/client that's timing out, rather than SQL Server itself.
Perhaps adjust your
SqlCommand.CommandTimeout to a higher value?
myCmd.CommandTimeout = 360; //seconds
Are you sure your 1 second
UPDATE statement is the one being blocked/timing out? Perhaps there's something else executing just previous?
Have you looked at SSMS to determine what is being blocked/blocking? Right click your server, select 'Activity Monitor'.
What does SQL Profiler show you as being executed?
Updating 600,000 rows is a lot of work for SQL.
WHERE IN is a fairly inefficient way to do this clause, I would use:
UPDATE TOP(@Count) Pages SET Checked = 1, LastChecked = GETDATE() OUTPUT Inserted.ID, Inserted.Title INTO @Updated WHERE Checked = 0 and FileID = @FileID
Make sure that @Count is fairly small, between 2000 and 10,000
As (+1) p.campbell said, the timeout is coming from .NET, not from SQL.
What indexing do you have in place? Without suitable coverage of columns "Checked" and/or "ID", your subquery (SELECT TOP...) will have to read every page in the table, to first get all where Checked = 0 and then sort those by ID. If it's large and being regularly updated that could make it Delay Central.
If they don't have to be ordered by Id, taking out that ORDER BY might cause the query to only read rows until it gets @Count of them.
If you have SQL 2008, a filtered query (...WHERE Checked = 0) might speed things up.
User contributions licensed under CC BY-SA 3.0