По какой причине SQL сервер может не отвечать?

Question

[Дмитрий Гавриленко]

Всем привет. Есть небольшой кусочек кода обновления поля в таблице

private void SetResponsibleInSQL(long id, long resp)
		{
			using (SqlConnection con = new SqlConnection(@"Data Source=SERVER;Initial Catalog=DB;User ID=su;Password=123123;Timeout=300;"))
			{
				con.Open();
				using (SqlCommand comm = new SqlCommand()) {
					comm.Connection = con;
					comm.CommandTimeout = 300;
					comm.CommandText = String.Format("update Contractor set Responsible = {0} where Id = {1}", resp, id);
					comm.ExecuteNonQuery();
					comm.Dispose();
					comm.Cancel();
				}
				con.Close();
			}
		}

Выполняется данный метод в цикле. Каждая итерация, без вызова этой функции, занимает около 3х секунд. Это не единственный запрос к базе данных в итерации, но именно этот уходит в таймаут. Проходит то 8, то 10, то 3 итераций из 25 возможных. Я даже таймауты повысил на 300, но все равно не помогает.

Ошибка:
[System.Data.SqlClient.SqlException: Истекло время ожидания (Timeout). Время ожидания истекло до завершения операции или сервер не отвечает.
Выполнение данной инструкции было прервано.]

в System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
в System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
в System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
в System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
в System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
в System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Машина на которой выполняется код: i3-4130 3.4GHz, 8Gb RAM
Сервер SQL: i5-2500 3.6GHz, 12Gb RAM

UPD1: По рекомендациям Дмитрия Ковальского обновил метод

_con.Open();
SetResponsibleInSQL();
_con.Close();
//---------------------------
private void SetResponsibleInSQL()
{
	using (SqlCommand comm = new SqlCommand())
	{
		comm.Connection = _con;
		comm.CommandTimeout = 30;
		comm.CommandText = "BEGIN TRANSACTION;\n" + sqlQuery + "\nCOMMIT TRANSACTION;";
		Console.WriteLine(comm.CommandText);
		comm.ExecuteNonQuery();
	}
}

Теперь запрос выполняется 1 раз и в нем содержится N-ое кол-во update.
Пример CommandText:
BEGIN TRANSACTION;
update C SET C.Responsible = U.Id FROM [Contractor] as C, [User] as U where C.KodV1S = '000000014' and U.UserName = 'Янгуразова' and C.IsDeleted = 0;update C SET C.Responsible = U.Id FROM [Contractor] as C, [User] as U where C.KodV1S = '000000440' and U.UserName = 'Янгуразова' and C.IsDeleted = 0;update C SET C.Responsible = U.Id FROM [Contractor] as C, [User] as U where C.KodV1S = '000002351' and U.UserName = 'Бурмистрова' and C.IsDeleted = 0;update C SET C.Responsible = U.Id FROM [Contractor] as C, [User] as U where C.KodV1S = '000001702' and U.UserName = 'Пугач' and C.IsDeleted = 0;update C SET C.Responsible = U.Id FROM [Contractor] as C, [User] as U where C.KodV1S = '000002077' and U.UserName = 'Скоробогатова' and C.IsDeleted = 0;update C SET C.Responsible = U.Id FROM [Contractor] as C, [User] as U where C.KodV1S = '000001913' and U.UserName = 'Хромихина' and C.IsDeleted = 0;
COMMIT TRANSACTION;

Специально для Дмитрия резюмирую: даже такие действия не помогают решить проблему с TimeOut.

Answer 1

[Дмитрий Ковальский]

За циклическое выполнение такого кода наказывают рабочим местом в подвале без окошка. Уважаемый - старайтесь делать 1 скрипт, а не сотни. Выиграете тьму времени и сервак вздохнет с облегчением. Да, скрипт будет труднее и хитрее, но оптимизирование взаимодействия с базой - вообще дело не легкое, а вы тут одним клиентом создаете сотню соединений/разъединений хрен знает ради чего.

UPD: Вчитался немножко в код и не понял некоторых ваших телодвижений.
1) зачем делать using (SqlConnection con = new SqlConnection(),а затем con.Open(); и con.Close() на выходе? ну предположим что соединение не открыто при создании объекта, но при выходе из using и АВТОМАТИЧЕСКОМ вызове con.Dispose() соединение закроется.
2)
using (SqlCommand comm = new SqlCommand()) {
comm.Dispose();
comm.Cancel();
}
Во первых Dispose вызывается при выходе за границы using, а зачем вы вызываете Cancel вообще не представляю.

Comments

[Дмитрий Гавриленко]

Ок, да косяк с 25 дополнительными подключениями. Вынес. 1 подключение и 25 итераций с запросом. Результат 11 итераций из 25.

[Дмитрий Ковальский]

Дмитрий Гавриленко: Для всех скриптов или только для этого метода? вы говорили что там еще скрипты есть. Вообще я вам еще советовал придумать как сделать UPDATE одним запросом, а не 25. Для этого пишется хранимая процедурка, на вход которой подается таблица новых значений, а внутри делается 1 единственный скрипт вроде

UPDATE contractor
SET Responsible = t.newValue
FROM Contractor as C
JOIN @inputTable as t ON c.id = t.id

Ну и небольшой постскриптум - НИКОГДА не делайте последоватльное исполнение скриптов вызовом из серверного кода. Причина проста и банальна - есть такое понятия как лок таблицы и транзакции. Если этот ваш код на боевой системе рухнет - у вас база застрянет в полупозиции раком и хрен знает правильно это будет или нет, а уж откатить изменения сможете только случайно завалявшимся бэкапом. Если не умеете в хранимки и транзакции - возьмите Entity Framework. Он умеет накатывать изменения транзакцией.

[Дмитрий Гавриленко]

Дмитрий Ковальский: Поясню. Используется один из продуктов который основан на NHibernate. Я работаю с какими либо объектами, а при этом под капотом выполняются более 50 INSERT/UPDATE запросов в базу. Код огромен и все выполняется без проблем. Машина и сервер БД достаточно мощные. Применение Entity Framwork невозможно, а хранимая процедура все усложнит.

[Дмитрий Гавриленко]

Прочитайте пожалуйста UPD1