왜 SQL 서버 스칼라 반환 함수는 느린받을 수 있나요?

왜 스칼라 반환 않는 기능은 사용하는 것이 누적 느린 연속으로 더 많은 시간을 실행하는 원인 쿼리에 보인다?

나는 제 3 자로부터 구입 한 데이터로 지어진이 테이블을 가지고있다.

이 게시물은 짧게 몇 가지 물건을 손질했습니다 ...하지만 너무 당신은 일을 설정 얼마나의 아이디어를 얻을.

CREATE TABLE [dbo].[GIS_Location](
        [ID] [int] IDENTITY(1,1) NOT NULL, --PK
        [Lat] [int] NOT NULL,
        [Lon] [int] NOT NULL,
        [Postal_Code] [varchar](7) NOT NULL,
        [State] [char](2) NOT NULL,
        [City] [varchar](30) NOT NULL,
        [Country] [char](3) NOT NULL,

CREATE TABLE [dbo].[Address_Location](
    [ID] [int] IDENTITY(1,1) NOT NULL, --PK
    [Address_Type_ID] [int] NULL,
    [Location] [varchar](100) NOT NULL,
    [State] [char](2) NOT NULL,
    [City] [varchar](30) NOT NULL,
    [Postal_Code] [varchar](10) NOT NULL,
    [Postal_Extension] [varchar](10) NULL,
    [Country_Code] [varchar](10) NULL,

그럼 난 LAT과 LON 찾아 두 가지 기능을 가지고있다.

CREATE FUNCTION [dbo].[usf_GIS_GET_LAT]
(
    @City VARCHAR(30),
    @State CHAR(2)
)
RETURNS INT 
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @LAT INT

    SET @LAT = (SELECT TOP 1 LAT FROM GIS_Location WITH(NOLOCK) WHERE [State] = @State AND [City] = @City)

RETURN @LAT
END


CREATE FUNCTION [dbo].[usf_GIS_GET_LON]
(
    @City VARCHAR(30),
    @State CHAR(2)
)
RETURNS INT 
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @LON INT

    SET @LON = (SELECT TOP 1 LON FROM GIS_Location WITH(NOLOCK) WHERE [State] = @State AND [City] = @City)

RETURN @LON
END

나는 다음을 실행하면 ...

SET STATISTICS TIME ON

SELECT
    dbo.usf_GIS_GET_LAT(City,[State]) AS Lat,
    dbo.usf_GIS_GET_LON(City,[State]) AS Lon
FROM
    Address_Location WITH(NOLOCK)
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)

SET STATISTICS TIME OFF

100 ~ = 8 ms 200 ~ = 32 ms, 400 ~ = 876 ms

--편집하다 죄송합니다 좀 더 명확해야합니다. 나는 조정 위의 쿼리에 찾는 게 아니에요. 이것은 실행 시간을 느리게 통해 대량으로 분석 더 많은 기록을 얻기를 보여 단지 샘플입니다. 실제 응용 프로그램에서 기능 절은 그 지역의 모든 기록을 포함하는 도시와 국가의 주위 반경을 구축하는의 한 부분으로 사용된다.

해결법

==============================
1.대부분의 경우, 그것은 그들이 기본적으로 필요가 모든 행에 대해 한 번으로 실행 될 및 쿼리 계획 엔진 최적화 할 수없는 블랙 박스 때문에 참조 테이블 (다른 사람과 같은 말) 것을 스칼라 값 함수를 방지하는 것이 가장 좋습니다. 따라서, 그들은 관련된 테이블 인덱스를 경우에도 선형 적으로 확장하는 경향이있다.

대부분의 경우, 그것은 그들이 기본적으로 필요가 모든 행에 대해 한 번으로 실행 될 및 쿼리 계획 엔진 최적화 할 수없는 블랙 박스 때문에 참조 테이블 (다른 사람과 같은 말) 것을 스칼라 값 함수를 방지하는 것이 가장 좋습니다. 따라서, 그들은 관련된 테이블 인덱스를 경우에도 선형 적으로 확장하는 경향이있다.

당신은 그들이 쿼리에 인라인으로 평가되기 때문에, 인라인 테이블 반환 함수 사용을 고려할 수 있으며, 최적화 할 수 있습니다. 당신은 당신이 원하는 캡슐화하지만, 바로 선택 문에서 식을 붙여의 성능을 얻을.

그들은 모든 절차 코드를 포함 할 수 인라인되는 부작용으로 (더 @variable 선언되지 = .. @variable 설정, 복귀). 그러나, 그들은 여러 행과 열을 반환 할 수 있습니다.

당신은 당신의 기능을 이런 식으로 뭔가를 다시 쓸 수있다 :
```
create function usf_GIS_GET_LAT(
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 lat
  from GIS_Location with (nolock) 
  where [State] = @State
    and [City] = @City
);

GO

create function usf_GIS_GET_LON (
    @City varchar (30),
    @State char (2)
)
returns table
as return (
  select top 1 LON
  from GIS_Location with (nolock)
  where [State] = @State
    and [City] = @City
);
```
를 사용하는 구문은 약간 다릅니다 :
```
select
    Lat.Lat,
    Lon.Lon
from
    Address_Location with (nolock)
    cross apply dbo.usf_GIS_GET_LAT(City,[State]) AS Lat
    cross apply dbo.usf_GIS_GET_LON(City,[State]) AS Lon
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)
```

==============================

2.그들은하지 않습니다.

그들은하지 않습니다.

에 대해 실행되는 스칼라 함수의 행의 수에 따라 기하 급수적으로 저하 성능을 야기 스칼라 함수에는 버그가 없습니다. 다시 테스트를 시도하고 SQL 프로파일 러를 살펴에서, CPU보고 READS 및 기간 열을 가지고있다. 초 2 초 5 초보다 오래 걸릴 테스트를 포함하는 당신에게 테스트의 크기를 늘리십시오.

CREATE FUNCTION dbo.slow
(
    @ignore int
)
RETURNS INT 
AS
BEGIN
    DECLARE @slow INT
    SET @slow = (select count(*) from sysobjects a 
        cross join sysobjects b 
        cross join sysobjects c 
        cross join sysobjects d 
        cross join sysobjects e 
        cross join sysobjects f
    where a.id = @ignore) 

    RETURN @slow
END
go
SET STATISTICS TIME ON

select top 1 dbo.slow(id)
from sysobjects
go
select top 5 dbo.slow(id)
from sysobjects
go
select top 10 dbo.slow(id)
from sysobjects
go
select top 20 dbo.slow(id)
from sysobjects
go
select top 40 dbo.slow(id)
from sysobjects

SET STATISTICS TIME OFF

산출

SQL Server Execution Times:
   CPU time = 203 ms,  elapsed time = 202 ms.


SQL Server Execution Times:
   CPU time = 889 ms,  elapsed time = 939 ms.

SQL Server Execution Times:
   CPU time = 1748 ms,  elapsed time = 1855 ms.

SQL Server Execution Times:
   CPU time = 3541 ms,  elapsed time = 3696 ms.


SQL Server Execution Times:
   CPU time = 7207 ms,  elapsed time = 7392 ms.

당신은 결과 집합의 행에 대한 스칼라 함수를 실행하는 경우, 스칼라 함수가 전역 최적화 당 행이 실행됩니다 있음을 유의하십시오.

==============================
3.당신은 인라인 TVF에 기능을 래핑 할 수 있습니다, 그것은 훨씬 더 빨리 될 것입니다 :

당신은 인라인 TVF에 기능을 래핑 할 수 있습니다, 그것은 훨씬 더 빨리 될 것입니다 :

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx
==============================
4.당신은 결과 집합의 모든 행에 대해 함수를 두 번합니다 (DB 두 가지 선택 안타)로 전화하십시오.

당신은 결과 집합의 모든 행에 대해 함수를 두 번합니다 (DB 두 가지 선택 안타)로 전화하십시오.

빠르게 쿼리를 만들 GIS_Location 바로 가입하고 기능을 건너 뛸 수 :
```
SELECT
    g.Lat,
    g.Lon
FROM
    Address_Location        l WITH(NOLOCK)
    INNER JOIN GIS_Location g WITH(NOLOCK) WHERE l.State = g.State AND l.City = g.City
WHERE
    ID IN (SELECT TOP 100 ID FROM Address_Location WITH(NOLOCK) ORDER BY ID DESC)
```
NOLOCK, 또는 미친 절, 난 그냥 질문에서 복사 이유를 잘 모르겠어요 ...
==============================
5.사용자 정의 함수와 SQL 식 그들없이 SQL 식에 비해 덜 효율적이기 때문에 간단히했습니다. 실행 로직은 최적화 될 수 없다; 과 (전화 프로토콜 포함) 기능의 오버 헤드는 모든 행에 발생해야합니다.

사용자 정의 함수와 SQL 식 그들없이 SQL 식에 비해 덜 효율적이기 때문에 간단히했습니다. 실행 로직은 최적화 될 수 없다; 과 (전화 프로토콜 포함) 기능의 오버 헤드는 모든 행에 발생해야합니다.

KMike의 조언이 좋다. IN (SELECT 무언가가) 아니다 WHERE .. 가능성 효율적인 패턴 일, 그리고 A는 가입과 함께이 경우 쉽게 교체 할 수 있습니다.

==============================

6.만약이 작품 더보기 ... 아니면 별개의 내부 조인?

만약이 작품 더보기 ... 아니면 별개의 내부 조인?

select a.*,
(select top 1 g.Lat from GIS_Location g where g.City = a.City and g.State = a.State) as Lat,
(select top 1 g.Lon from GIS_Location g where g.City = a.City and g.State = a.State) as Lon
from Address_Location a
where a.ID in (select top 100 ID from Address_Location order by ID desc)

스칼라 기능 성능에 관해서는, 나는 확실하지 않다.

==============================
7.일반적으로 스칼라 함수는 인라인 TVF 대응보다 훨씬 느립니다. 다행히 많은 시나리오가 변경됩니다.

일반적으로 스칼라 함수는 인라인 TVF 대응보다 훨씬 느립니다. 다행히 많은 시나리오가 변경됩니다.

SQL 서버 2019 스칼라 UDF 인라인을 소개합니다 :

기능이 inlinable 경우 확인 :
```
SELECT OBJECT_NAME([object_id]) AS name, is_inlineable
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('schema.function_name')
```
데이터베이스 수준에서 기능을 활성화 / 비활성화 :
```
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON/OFF;
```
마이크로 소프트 리서치 - 프로젝트 루아
==============================
8.죄송합니다 내가 늦게 파티에 합류,하지만 난 미래 프로파일 피해자에 대한 내 대답을 공유하고자합니다. 며칠 전, 한 프로덕션 서버 (SQL 서버 2012 SP4 기업)의 모든 스칼라 기능을 가지고 느리게, 보통 전체에 초 정도 걸릴 일부 저장 프로 시저, 그들은 한 경우, 분 시간을 실행하기 시작했다.

죄송합니다 내가 늦게 파티에 합류,하지만 난 미래 프로파일 피해자에 대한 내 대답을 공유하고자합니다. 며칠 전, 한 프로덕션 서버 (SQL 서버 2012 SP4 기업)의 모든 스칼라 기능을 가지고 느리게, 보통 전체에 초 정도 걸릴 일부 저장 프로 시저, 그들은 한 경우, 분 시간을 실행하기 시작했다.

마지막으로, 프로파일 러를 사용하여 만든 추적이의 근본 원인이었다. 추적이 시작되었다 그러나이 추적이 실행 된 노트북은 이전에 추적을 중지 밖으로 꺼졌습니다. 기적처럼 추적은 사용자 사에 의해 자동으로 중이 야했다 (기록을 위해, sa 계정이 사용 중지 및 이름) - "SQL 추적이 중지 추적 ID = '3'로그인 이름 = '사'..." 이 자동으로 성능 문제를 해결.

그래서, 프로파일 러 추적 또는 확장 이벤트가 느린 서버에서 실행 확인

이 도움말을 미래의 어떤 하나를 바랍니다.

from https://stackoverflow.com/questions/800017/why-do-sql-server-scalar-valued-functions-get-slower by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL에서 여러 테이블을 조인 (0)	2020.06.04
[SQL] 빌 &는 SQL 주입 - UTF8 POC (0)	2020.06.04
[SQL] JPA 기본 쿼리를 선택하고 캐스트 객체 (0)	2020.06.04
[SQL] 모든 열은 'X'가 들어 SQL 서버 SELECT (0)	2020.06.04
[SQL] 어떻게 PostgreSQL을 테이블에 행을 계산 속도를합니까? (0)	2020.06.04

복붙노트

[SQL] 왜 SQL 서버 스칼라 반환 함수는 느린받을 수 있나요?

왜 SQL 서버 스칼라 반환 함수는 느린받을 수 있나요?

해결법

2.그들은하지 않습니다.

3.당신은 인라인 TVF에 기능을 래핑 할 수 있습니다, 그것은 훨씬 더 빨리 될 것입니다 :

4.당신은 결과 집합의 모든 행에 대해 함수를 두 번합니다 (DB 두 가지 선택 안타)로 전화하십시오.

5.사용자 정의 함수와 SQL 식 그들없이 SQL 식에 비해 덜 효율적이기 때문에 간단히했습니다. 실행 로직은 최적화 될 수 없다; 과 (전화 프로토콜 포함) 기능의 오버 헤드는 모든 행에 발생해야합니다.

6.만약이 작품 더보기 ... 아니면 별개의 내부 조인?

7.일반적으로 스칼라 함수는 인라인 TVF 대응보다 훨씬 느립니다. 다행히 많은 시나리오가 변경됩니다.

'SQL' 카테고리의 다른 글

티스토리툴바