RiCo技術農場

1避免具名參數

具名參數效能問題老早就存在，但我沒想到native compiled sp居然也一樣，

下面我簡單測試

create proc usp_InsertEmailnative(@size int,@name varchar(20), @email nvarchar(100),@emailid uniqueidentifier OUTPUT)
with native_compilation,schemabinding
as 
begin atomic
with(transaction isolation level=snapshot,language='english')
  declare @id uniqueidentifier=newid();
  insert into [dbo].[myEmail_mem](c1,c2,c3,c4)
  values(@id,@size,@name,@email)
  set @emailid=@id;
end

 

如果變數命名可讀性高的話，一般執行SP我也都不會使用具名參數

--unnamed_parameters
declare @p1 int=100,@p2 varchar(20)='rico',@p3 nvarchar(100)='ricoisme@sql.com',@p4 uniqueidentifier
exec dbo.usp_InsertEmailnative @p1,@p2,@p3,@p4 output
GO 30000

--named_parameters
declare @p1 int=100,@p2 varchar(20)='rico',@p3 nvarchar(100)='ricoisme@sql.com',@p4 uniqueidentifier
exec dbo.usp_InsertEmailnative @size=@p1,@name=@p2,@email=@p3,@emailid=@p4 output
GO 30000

 

我們可以透過extended event觀察natively_compiled_proc_slow_parameter_passing

CREATE EVENT SESSION [XTP_Parameter_Events] ON SERVER 
ADD EVENT sqlserver.natively_compiled_proc_slow_parameter_passing
(
    ACTION(sqlserver.sql_text)
) 
ADD TARGET package0.event_file(SET filename=N'D:\sqlevent\XTPParams.xel');
GO
ALTER EVENT SESSION [XTP_Parameter_Events] ON SERVER STATE = START;  --STOP

;WITH x([timestamp], db, [object_id], reason, batch)
AS
(
  SELECT 
    xe.d.value(N'(event/@timestamp)[1]',N'datetime2(0)'),
    DB_NAME(xe.d.value(N'(event/data[@name="database_id"]/value)[1]',N'int')),
    xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
    xe.d.value(N'(event/data[@name="reason"]/text)[1]',N'sysname'),
    xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
  FROM 
    sys.fn_xe_file_target_read_file(N'D:\sqlevent\XTPParams*.xel',NULL,NULL,NULL) AS ft
    CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT [timestamp], db, [object_id], reason, batch FROM x;

執行30000次比較結果

 

2 execute as caller效能較佳

預設預設使用execute as caller(SQL2014以前不支援)，因為要支援原生內建函數，

如沒有特殊權限需求，則無須改為owner或self

create proc usp_InsertEmailwithCallernative(@size int,@name varchar(20), @email nvarchar(100),@emailid uniqueidentifier OUTPUT)
with native_compilation,schemabinding,execute as caller
as 
begin atomic
with(transaction isolation level=snapshot,language='english')
  declare @id uniqueidentifier=newid();
  insert into [dbo].[myEmail_mem](c1,c2,c3,c4)
  values(@id,@size,@name,@email)
  set @emailid=@id;
end

declare @p1 int=100,@p2 varchar(20)='rico',@p3 nvarchar(100)='ricoisme@sql.com',@p4 uniqueidentifier
exec dbo.usp_InsertEmailwithCallernative @p1,@p2,@p3,@p4 output
GO 30000

執行30000次和owner比較