SQL-запрос, медленный в приложении.NET, но мгновенный в Studio управления SQL Server
Вот SQL
SELECT tal.TrustAccountValue
FROM TrustAccountLog AS tal
INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
INNER JOIN Users usr ON usr.UserID = ta.UserID
WHERE usr.UserID = 70402 AND
ta.TrustAccountID = 117249 AND
tal.trustaccountlogid =
(
SELECT MAX (tal.trustaccountlogid)
FROM TrustAccountLog AS tal
INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
INNER JOIN Users usr ON usr.UserID = ta.UserID
WHERE usr.UserID = 70402 AND
ta.TrustAccountID = 117249 AND
tal.TrustAccountLogDate < '3/1/2010 12:00:00 AM'
)
Basicaly там является таблицей Users таблица TrustAccount и таблица TrustAccountLog.
Пользователи: Содержит пользователей и их детали
TrustAccount: у Пользователя может быть несколько TrustAccounts.
TrustAccountLog: Содержит аудит всего TrustAccount "перемещения". A
TrustAccount связан с несколькими записями TrustAccountLog. Теперь этот запрос выполняется в миллисекундах в Studio управления SQL Server, но по некоторой странной причине он берет навсегда в моем приложении C# и даже timesout (120 с) иногда.
Вот код вкратце. Это называют многократно в цикле, и оператор подготовлен.
cmd.CommandTimeout = Configuration.DBTimeout;
cmd.CommandText = "SELECT tal.TrustAccountValue FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID1 AND ta.TrustAccountID = @TrustAccountID1 AND tal.trustaccountlogid = (SELECT MAX (tal.trustaccountlogid) FROM TrustAccountLog AS tal INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID INNER JOIN Users usr ON usr.UserID = ta.UserID WHERE usr.UserID = @UserID2 AND ta.TrustAccountID = @TrustAccountID2 AND tal.TrustAccountLogDate < @TrustAccountLogDate2 ))";
cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountLogDate2", SqlDbType.DateTime).Value =TrustAccountLogDate;
// And then...
reader = cmd.ExecuteReader();
if (reader.Read())
{
double value = (double)reader.GetValue(0);
if (System.Double.IsNaN(value))
return 0;
else
return value;
}
else
return 0;
8 ответов
Если это анализ параметров, попробуйте добавить параметр (перекомпилировать) в конец запроса.
Я бы порекомендовал создать хранимую процедуру для более управляемой инкапсуляции логики. Также согласен - зачем вы передаете 5 параметров, если вам нужно только три, судя по примеру?
Можете ли вы использовать вместо этого этот запрос?
select TrustAccountValue from
(
SELECT MAX (tal.trustaccountlogid), tal.TrustAccountValue
FROM TrustAccountLog AS tal
INNER JOIN TrustAccount ta ON ta.TrustAccountID = tal.TrustAccountID
INNER JOIN Users usr ON usr.UserID = ta.UserID
WHERE usr.UserID = 70402 AND
ta.TrustAccountID = 117249 AND
tal.TrustAccountLogDate < '3/1/2010 12:00:00 AM'
group by tal.TrustAccountValue
) q
И, чего бы это ни стоило, вы используете неоднозначный формат даты, в зависимости от о языковых настройках пользователя, выполняющего запрос. Для меня, например, это 3 января, а не 1 марта. Проверьте это:
set language us_english
go
select @@language --us_english
select convert(datetime, '3/1/2010 12:00:00 AM')
go
set language british
go
select @@language --british
select convert(datetime, '3/1/2010 12:00:00 AM')
Рекомендуемый подход - использовать формат 'ISO' ггггммдд чч: мм: сс
select convert(datetime, '20100301 00:00:00') --midnight 00, noon 12
Похоже, вы не закрываете свой считыватель данных - это может начать накапливаться за несколько итераций ...
{{1 }} По моему опыту, обычная причина, по которой запрос выполняется быстро в SSMS, но медленно из .NET, связана с различиями в параметрах SET соединения. Когда соединение открывается либо SSMS, либо SqlConnection , автоматически запускается набор команд SET для настройки среды выполнения. К сожалению, SSMS и SqlConnection имеют разные значения по умолчанию SET .
Одно общее различие - SET ARITHABORT . Попробуйте ввести SET ARITHABORT ON в качестве первой команды из вашего кода .NET.
SQL Profiler можно использовать для отслеживания того, какие команды SET выдаются как SSMS, так и .NET, чтобы вы могли найти другие различия.
Следующий код демонстрирует, как выполнить команду SET , но обратите внимание, что этот код не был протестирован.
using (SqlConnection conn = new SqlConnection("<CONNECTION_STRING>")) {
conn.Open();
using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", conn)) {
comm.ExecuteNonQuery();
}
// Do your own stuff here but you must use the same connection object
// The SET command applies to the connection. Any other connections will not
// be affected, nor will any new connections opened. If you want this applied
// to every connection, you must do it every time one is opened.
}
Поскольку вы, кажется, всегда возвращаете значение только из одной строки из одного столбца, вы можете использовать ExecuteScalar ( ) вместо этого в объекте команды, что должно быть более эффективным:
object value = cmd.ExecuteScalar();
if (value == null)
return 0;
else
return (double)value;
Возможно, проблема связана с преобразованием типов. Действительно ли все идентификаторы SqlDbType.Int на уровне данных?
Кроме того, почему есть 4 параметра, а 2 подойдут?
cmd.Parameters.Add("@TrustAccountID1", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID1", SqlDbType.Int).Value = userId;
cmd.Parameters.Add("@TrustAccountID2", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID2", SqlDbType.Int).Value = userId;
Может быть
cmd.Parameters.Add("@TrustAccountID", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userId;
Поскольку им обоим назначена одна и та же переменная.
(Это могло привести к тому, что сервер разработал другой план, поскольку он ожидает, что четыре разные переменные будут использоваться в качестве оп. К. 4 константам, то есть две переменные могут иметь значение для оптимизации сервера.)
{{1} }Я предлагаю вам создать хранимую процедуру, которую можно скомпилировать и кэшировать с помощью Sql Server и, таким образом, повысить производительность
Скорее всего, проблема заключается в критерии
tal.TrustAccountLogDate < @TrustAccountLogDate2
Оптимальный план выполнения будет сильно зависеть от значения параметра, передача 1910-01-01 (который не возвращает строк) наверняка вызовет другой план, чем 2100 -12-31 (возвращает все строки).
Когда значение указано в запросе как литерал, SQL-сервер знает, какое значение использовать при создании плана. Когда используется параметр, SQL-сервер сгенерирует план только один раз, а затем повторно его использует, и если значение при последующем выполнении будет слишком сильно отличаться от исходного, план не будет оптимальным.
Чтобы исправить ситуацию, вы можете указать в запросе OPTION (RECOMPILE) . Добавление запроса к хранимой процедуре не поможет вам с этой конкретной проблемой, если только
вы не создадите процедуру WITH RECOMPILE.
Другие уже упоминали об этом («анализ параметров»), но я подумал, что простое объяснение концепции не повредит.
Возможно связано с анализом параметров? Вы пытались точно захватить то, что клиентский код отправляет на SQL Server (используйте профилировщик, чтобы поймать точный оператор), а затем запустить это в Management Studio?
Анализ параметров: Низкая производительность плана выполнения хранимой процедуры SQL - анализ параметров
] Я раньше не видел этого в коде, только в процедурах, но на это стоит взглянуть.