.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'
)

기본적으로 사용자 테이블, 신뢰 계정 테이블 및 신뢰 계정 로그 테이블이 있습니다.
의 세부 정보를 합니다.
: 개의 계정을 수 .사용자는 여러 개의 신뢰 계정을 가질 수 있습니다.
TrustAccountLog: 모든 TrustAccount "이동"에 대한 감사를 포함합니다.a
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;

되지만 .에제경따르 SSMS서쿼실의 입니다.NET에서는 느린 일반적인 이유는 연결의 차이 때문입니다.SET -tings 중 이 열렸을 때. SSMS는 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.
}

스니핑이라면 .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' 형식 yyymmdd hh:mm:ss를 사용하는 것입니다.

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

라이브 시스템(동일한 SQL 서버)이 정상적으로 실행되고 있음에도 불구하고 테스트 환경에서 동일한 문제가 발생했습니다. 추가하기OPTION (RECOMPILE)그리고 또한OPTION (OPTIMIZE FOR (@p1 UNKNOWN))도움이 되지 않았습니다.

SQL Profiler를 사용하여 .NET 클라이언트가 전송하는 정확한 쿼리를 검색한 결과 이 쿼리는 다음과 같이 처리되었습니다.exec sp_executesql N'select ...비교되는 열은 단순하지만 모수가 다음과 같이 선언되었습니다.

캡처한 쿼리 텍스트를 SSMS에 넣으면 .NET 클라이언트에서 실행하는 것처럼 느리게 실행된다는 것을 확인할 수 있습니다.

변수의 를 개매다유음변경로하것는발을다니견습했으로 했습니다.DbType.AnsiString문제 해결:

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로 설정한 클라이언트 응용 프로그램은 서로 다른 쿼리 계획을 수신할 수 있으므로 제대로 수행되지 않는 쿼리 문제를 해결하기가 어렵습니다.즉, 동일한 쿼리는 관리 스튜디오에서는 빠르게 실행되지만 애플리케이션에서는 느리게 실행됩니다.

할 수 있는 또 로운 주제는 해야할또흥주는제운로미다이입니다.Parameter Sniffing애플리케이션의 느린 속도, SSMS의 빠른 속도에 설명되어 있습니다. 공연 미스터리의 이해 - Erland Sommarskog 저자

Jimmy Bogard의 바하르 열 SQL 인덱스 성능 문제 해결에 설명된 대로 유니코드 입력 매개 변수를 사용하면서 내부적으로 VARCHAR 열을 NVARCHAR로 변환하는 것도 또 다른 가능성입니다.

알 수 없는 경우 최적화

SQL Server 2008 이상에서는 OPTIMIZE FOR UNKNOWN을 고려하십시오. UNKNOWN: 쿼리 최적화 중에 로컬 변수의 값을 결정하기 위해 쿼리 최적화 도구가 초기 값 대신 통계 데이터를 사용하도록 지정합니다.

옵션(재컴파일)

재컴파일이 유일한 해결책인 경우 "WITH RE컴파일" 대신 "OPTION(재컴파일)"을 사용합니다.매개 변수 임베딩 최적화에 도움이 됩니다.매개변수 스니핑, 임베딩 및 Recompile 옵션 읽기 - by Paul White

SET 옵션

SETmsdn - SQL Server 2008의 계획 캐싱에 따라 옵션이 계획 재사용에 영향을 미칠 수 있음

1. ANSI_NULL_DFLT_OFF 2. ANSI_NULL_DFLT_ON 3. ANSI_NULL_NULLS 4. ANSI_PADDING 5. ARSI_WARNings 6. ARSI BORT 7. CONCAT_NULL_8. 첫 번째 날짜 9. FORWARDATENT 11. BORDENULL_NULL_BROWARDENULL_DENULANULANULANGE 11. 숫자로 인용됨 11. 숫자 11. 숫자 11. BORD.

대부분의 경우 문제는 기준에 있습니다.

tal.TrustAccountLogDate < @TrustAccountLogDate2

최적의 실행 계획은 매개 변수 값에 따라 크게 달라지며 1910-01-01(행이 반환되지 않음)을 통과하면 2100-12-31(모든 행이 반환됨)과는 다른 계획이 발생할 가능성이 가장 확실합니다.

값이 쿼리에서 리터럴로 지정되면 SQL 서버는 계획 생성 중에 사용할 값을 알 수 있습니다.매개 변수를 사용하면 SQL Server에서 계획을 한 번만 생성한 다음 다시 사용합니다. 이후 실행의 값이 원래 값과 너무 많이 다를 경우 계획이 최적화되지 않습니다.

상황을 하기 위해 이상을해면다음같지이수있다니습을 지정할 수 .OPTION(RECOMPILE)조회 중에재컴파일로 프로시저를 만들지 않는 한 저장 프로시저에 쿼리를 추가하는 것은 이 특정 문제에 도움이 되지 않습니다.

다른 사람들은 이미 이것을 언급했지만("파라미터 스니핑"), 저는 개념에 대한 간단한 설명이 나쁘지 않을 것이라고 생각했습니다.

유형 변환 문제일 수 있습니다.로 ㅠㅠㅠㅠㅠㅠㅠㅠㅠSqlDbType.Int데이터 계층에서?

또한, 2가 작동하는 4개의 매개 변수가 있는 이유는 무엇입니까?

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개의 다른 변수를 4개의 상수로 예상하기 때문에 다른 계획을 세울 수 있습니다. 즉, 2개의 변수를 만들면 서버 최적화에 차이가 있을 수 있습니다.)

매개 변수 스니핑과 관련이 있을 수 있습니까?클라이언트 코드가 SQL Server로 전송하는 내용을 정확히 캡처한 다음(정확한 문을 캡처하려면 profiler를 사용하십시오) Management Studio에서 실행해 보셨습니까?

매개변수 스니핑: SQL 저장 프로시저 실행 계획 성능 불량 - 매개변수 스니핑

저는 이것을 코드에서 본 적이 없고, 단지 절차에서만 본 적이 있지만, 볼 가치가 있습니다.

는 제 가 의나경문내엔프다사음쿼생리있다를 이었습니다.exec sp_executesql.

매개 변수가 유형에서 정확히 일치하지 않는 경우 실행 계획은 쿼리 자체에 변환을 적용하기로 결정하기 때문에 인덱스를 사용하지 않습니다.상상할 수 있듯이 성능이 훨씬 느립니다.

제 경우 열이 CHR(3)로 정의되고 엔티티 프레임워크가 nchar에서 char로 변환되는 쿼리에서 N'str'을 통과했습니다.다음과 같은 쿼리의 경우:

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

다음과 같은 SQL 쿼리를 생성했습니다.

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

제 경우 가장 쉬운 해결책은 열 유형을 변경하는 것이었습니다. 또는 코드와 씨름하여 올바른 유형을 처음부터 전달할 수 있습니다.

한 열에서 한 행의 값만 반환하는 것으로 보이므로 명령 개체에 대해 ExecuteScalar()를 대신 사용할 수 있습니다. 이 방법이 더 효율적입니다.

    object value = cmd.ExecuteScalar();

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

저는 오늘 이 문제를 겪었고 이것은 제 문제를 해결합니다: https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/

SP의 첫머리에 다음과 같이 입력했습니다.ARITHABORT 설정

이것 좀 도와주세요!

데이터 리더를 닫지 않는 것 같습니다. 여러 번 반복하면 데이터 리더가 닫히기 시작할 수 있습니다.

이 질문의 증상 제목과 정확히 일치하는 다른 근본 원인으로 문제가 발생했습니다.

저의 경우, 반환된 모든 레코드를 순환하고 데이터베이스에 대해 다른 세 개의 쿼리를 실행하는 동안 애플리케이션의 .NET 코드에 의해 결과 집합이 열린 상태로 유지된다는 것이 문제였습니다!수천 개가 넘는 행이 SQL Server의 타이밍 정보를 기반으로 원본 쿼리를 완료하는 데 시간이 걸리는 것처럼 잘못 표시했습니다.

따라서 각 행을 처리하는 동안 결과 집합이 열려 있지 않도록 호출을 하는 .NET 코드를 리팩터링하는 것이 수정되었습니다.

, 할 때 이 있다는 . OP가 저장 프로시저를 할 때 .OPTION(RECOMPILE)아무것도 하지 않는 것 같습니다.

절차에 명시된 변수에 모수를 복사하고 대신 사용하기만 하면 됩니다.

예:

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

저는 방금 이 정확한 문제를 겪었습니다.SSMS에서 하위 초 응답을 반환한 보기에 대해 실행 중인 선택입니다.하지만 sp_executesql을 실행하는 데 5~20초가 걸렸습니다. 왜죠?sp_executesql을 통해 실행할 때 쿼리 계획을 살펴보니 올바른 인덱스를 사용하지 않았기 때문입니다.또한 검색 대신 인덱스 스캔을 수행했습니다.저의 해결책은 전달된 매개 변수로 쿼리를 실행하는 단순한 sp를 만드는 것이었습니다.sp_executesql을 통해 실행할 때 올바른 인덱스를 사용하고 검색을 검색하지 않았습니다.더 개선하고 싶다면 명령어를 사용하십시오.명령 유형 = 명령 유형입니다.StoredProcedure는 sp_executesql을 사용하지 않고 EXEC만 사용하지만 결과는 ms만 삭제합니다.

이 코드는 수백만 개의 레코드와 함께 DB에서 1초 미만으로 실행되었습니다.

   public DataTable FindSeriesFiles(string StudyUID)
    {
        DataTable dt = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("VNA.CFIND_SERIES", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@StudyUID", StudyUID);
               using (SqlDataReader reader = command.ExecuteReader())
                {
                    dt.Load(reader);
                }
                return dt;
            }
        }
    }

저장 프로시저가 단순히 포함된 경우

CREATE PROCEDURE [VNA].[CFIND_SERIES]
    @StudyUID NVARCHAR(MAX)
AS BEGIN
    SET NOCOUNT ON
    SELECT * 
    FROM CFIND_SERIES_VIEW WITH (NOLOCK) 
    WHERE [StudyInstanceUID] = @StudyUID
    ORDER BY SeriesNumber
END

이 작업에는 5~20초가 걸렸습니다(그러나 선택 항목은 VNA.CFIND_SERIES 저장 프로시저의 내용과 정확히 동일합니다).

public DataTable FindSeriesFiles(string StudyUID)
    {
        DataTable dt = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText =" SELECT * FROM CFIND_SERIES_VIEW WITH (NOLOCK) WHERE StudyUID=@StudyUID ORDER BY SeriesNumber";
                command.Parameters.AddWithValue("@StudyUID", StudyUID);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    dt.Load(reader);
                }
                return dt;
            }
        }
    }

저장 프로시저를 만들어 보는 것이 좋습니다. 저장 프로시저는 SQL Server에서 컴파일하고 캐시할 수 있으므로 성능을 향상시킬 수 있습니다.

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