.NET 응용 프로그램에서는 SQL 쿼리가 느리지 만 SQL Server Management Studio에서는 즉각적입니다.

.NET 응용 프로그램에서는 SQL 쿼리가 느리지 만 SQL Server Management Studio에서는 즉각적입니다.

---

다음은 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'
)

기본적으로 Users 테이블, TrustAccount 테이블 및 TrustAccountLog 테이블이 있습니다.
사용자 : 사용자 및 세부 정보를 포함합니다.
TrustAccount : 사용자는 여러 TrustAccount를 가질 수 있습니다.
TrustAccountLog : 모든 TrustAccount "이동"에 대한 감사를 포함합니다.
TrustAccount 여러 TrustAccountLog 항목과 연관되어 있습니다. 이제이 쿼리는 SQL Server Management Studio 내에서 밀리 초 내에 실행되지만 이상한 이유로 내 C # 앱에서 영원히 걸리고 때로는 시간 초과 (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;

---

이것이 매개 변수 스니핑이라면 option(recompile)쿼리 끝에 추가해보십시오 . 보다 관리하기 쉬운 방식으로 논리를 캡슐화하는 저장 프로 시저를 만드는 것이 좋습니다. 또한 동의합니다. 예제로 판단하면 3 개만 필요한데 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 일이 아니라 1 월 3 일입니다. 이것 좀 봐:

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'형식 yyyymmdd hh : mm : ss를 사용하는 것입니다.

select convert(datetime, '20100301 00:00:00') --midnight 00, noon 12

---

내 경험상 쿼리가 SSMS에서는 빠르게 실행되지만 .NET에서는 느린 일반적인 이유는 연결의 SET-tings 의 차이 때문 입니다. SSMS 또는 SqlConnection에서 연결이 열리면 SET실행 환경을 설정하기 위해 여러 명령이 자동으로 실행됩니다. 불행히도 SSMS와 SqlConnection다른 SET기본값이 있습니다.

일반적인 차이점은 SET ARITHABORT. SET ARITHABORT ON.NET 코드에서 첫 번째 명령으로 실행 해보 십시오.

SQL 프로필러를 사용하여 SETSSMS와 .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.
}

---

테스트 환경에서 동일한 문제가 있었지만 라이브 시스템 (동일한 SQL 서버에서)이 제대로 실행되고있었습니다. OPTION (RECOMPILE) 및 OPTION (OPTIMIZE FOR (@ p1 UNKNOWN))을 추가해도 도움이되지 않았습니다.

나는 SQL 프로파일 러를 사용하여 .net 클라이언트가 보내는 정확한 쿼리를 잡았고 이것이 래핑 exec sp_executesql N'select ...되었고 매개 변수가 nvarchars로 선언되었음을 발견했습니다 . 비교되는 열은 단순한 varchar입니다.

캡처 된 쿼리 텍스트를 SSMS에 넣는 것은 .net 클라이언트 에서처럼 느리게 실행된다는 것을 확인했습니다.

매개 변수 유형을 AnsiText로 변경하면 문제가 해결되었음을 알았습니다.

p = cm.CreateParameter() p.ParameterName = "@company" p.Value = company p.DbType = DbType.AnsiString cm.Parameters.Add(p)

테스트 환경과 라이브 환경이 성능에 큰 차이가있는 이유를 설명 할 수 없었습니다.

---

이전 게시물이므로 특정 문제가 지금까지 해결되기를 바랍니다.

다음 SET옵션은 계획 재사용에 영향을 미칠 수 있습니다 (마지막에 전체 목록).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ARITHABORT ON
GO

다음 두 문은 msdn-SET ARITHABORT 에서 가져온 것입니다.

ARITHABORT를 OFF로 설정하면 쿼리 최적화에 부정적인 영향을 주어 성능 문제가 발생할 수 있습니다.

SQL Server Management Studio의 기본 ARITHABORT 설정은 ON입니다. ARITHABORT를 OFF로 설정하는 클라이언트 응용 프로그램은 서로 다른 쿼리 계획을 수신하여 성능이 낮은 쿼리 문제를 해결하기 어렵습니다. 즉, 동일한 쿼리가 Management Studio에서는 빠르게 실행되지만 응용 프로그램에서는 느리게 실행될 수 있습니다.

이해해야 할 또 다른 흥미로운 주제 는 응용 프로그램Parameter Sniffing 에서 느리게, SSMS에서 빠르게? 성능 미스터리 이해-Erland Sommarskog 저

Still another possibility is with conversion (internally) of VARCHAR columns into NVARCHAR while using Unicode input parameter as outlined in Troubleshooting SQL index performance on varchar columns - by Jimmy Bogard

OPTIMIZE FOR UNKNOWN

In SQL Server 2008 and above, consider OPTIMIZE FOR UNKNOWN . UNKNOWN: Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.

OPTION (RECOMPILE)

Use "OPTION (RECOMPILE)" instead of "WITH RECOMPILE" if recompiliing is the only solution. It helps in Parameter Embedding Optimization. Read Parameter Sniffing, Embedding, and the RECOMPILE Options - by Paul White

SET Options

Following SET options can affect plan-reuse, based on msdn - Plan Caching in SQL Server 2008

1. ANSI_NULL_DFLT_OFF 2. ANSI_NULL_DFLT_ON 3. ANSI_NULLS 4. ANSI_PADDING 5. ANSI_WARNINGS 6. ARITHABORT 7. CONCAT_NULL_YIELDS_NUL 8. DATEFIRST 9. DATEFORMAT 10. FORCEPLAN 11. LANGUAGE 12. NO_BROWSETABLE 13. NUMERIC_ROUNDABORT 14. QUOTED_IDENTIFIER

---

Most likely the problem lies in the criterion

tal.TrustAccountLogDate < @TrustAccountLogDate2

The optimal execution plan will be highly dependent on the value of the parameter, passing 1910-01-01 (which returns no rows) will most certainly cause a different plan than 2100-12-31 (which returns all rows).

When the value is specified as a literal in the query, SQL server knows which value to use during plan generation. When a parameter is used, SQL server will generate the plan only once and then reuse it, and if the value in a subsequent execution differs too much from the original one, the plan will not be optimal.

To remedy the situation, you can specify OPTION(RECOMPILE) in the query. Adding the query to a stored procedure won't help you with this particular issue, unless you create the procedure WITH RECOMPILE.

Others have already mentioned this ("parameter sniffing"), but I thought a simple explanation of the concept won't hurt.

---

It might be type conversion issues. Are all the IDs really SqlDbType.Int on the data tier?

Also, why have 4 parameters where 2 will do?

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;

Could be

cmd.Parameters.Add("@TrustAccountID", SqlDbType.Int).Value = trustAccountId;
cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = userId;

Since they are both assigned the same variable.

(This might be causing the server to make a different plan since it expects four different variables as op. to. 4 constants - making it 2 variables could make a difference for the server optimization.)

---

Since you appear to only ever be returning the value from one row from one column then you can use ExecuteScalar() on the command object instead, which should be more efficient:

    object value = cmd.ExecuteScalar();

    if (value == null)
        return 0;
    else
        return (double)value;

---

Sounds possibly related to parameter sniffing? Have you tried capturing exactly what the client code sends to SQL Server (Use profiler to catch the exact statement) then run that in Management Studio?

Parameter sniffing: SQL poor stored procedure execution plan performance - parameter sniffing

I haven't seen this in code before, only in procedures, but it's worth a look.

---

In my case the problem was that my Entity Framework was generating queries that use exec sp_executesql.

When the parameters don't exactly match in type the execution plan does not use indexes because it decides to put the conversion into the query itself. As you can imagine this results in a much slower performance.

in my case the column was defined as CHR(3) and the Entity Framework was passing N'str' in the query which cause a conversion from nchar to char. So for a query that looks like this:

ctx.Events.Where(e => e.Status == "Snt")

It was generating an SQL query that looks something like this:

FROM [ExtEvents] AS [Extent1] ... WHERE (N''Snt'' = [Extent1].[Status]) ...

The easiest solution in my case was to change the column type, alternatively you can wrestle with your code to make it pass the right type in the first place.

---

I had this problem today and this solve my problem: https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/

I put on the begining of my SP this: Set ARITHABORT ON

Holp this help you!

---

You don't seem to be closing your data reader - this might start to add up over a number of iterations...

---

I had a problem with a different root cause that exactly matched the title of this question's symptoms.

In my case the problem was that the result set was held open by the application's .NET code while it looped through every returned record and executed another three queries against the database! Over several thousand rows this misleadingly made the original query look like it had been slow to complete based on timing information from SQL Server.

The fix was therefore to refactor the .NET code making the calls so that it doesn't hold the result set open while processing each row.

---

I realise the OP doesn't mention the use of stored procedures but there is an alternative solution to parameter sniffing issues when using stored procedures that is less elegant but has worked for me when OPTION(RECOMPILE) doesn't appear to do anything.

Simply copy your parameters to variables declared in the procedure and use those instead.

Example:

ALTER PROCEDURE [ExampleProcedure]
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN

--reassign to local variables to avoid parameter sniffing issues
DECLARE @MyStartDate datetime,
        @MyEndDate datetime

SELECT 
    @MyStartDate = @StartDate,
    @MyEndDate = @EndDate

--Rest of procedure goes here but refer to @MyStartDate and @MyEndDate
END

---

I suggest you try and create a stored procedure - which can be compiled and cached by Sql Server and thus improve performance

ReferenceURL : https://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-st