调用本机编译存储过程的最佳做法Best Practices for Calling Natively Compiled Stored Procedures

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本机编译存储过程:Natively compiled stored procedures are:

  • 通常用于应用程序中性能至关重要的部分。Used typically in performance-critical parts of an application.

  • 频繁执行。Frequently executed.

  • 操作速度快。Expected to be very fast.

使用本机编译存储过程所得的性能优势随行数和该过程所处理的逻辑数的上升而增加。The performance benefit of using a natively compiled stored procedure increases with the number of rows and the amount of logic that is processed by the procedure. 例如,如果本机编译存储过程使用以下一项或多项操作,将获得更好的性能:For example, a natively compiled stored procedure will exhibit better performance if it uses one or more of the following:

  • 聚合。Aggregation.

  • 嵌套循环联接。Nested-loops joins.

  • 多语句选择、插入、更新和删除操作。Multi-statement select, insert, update, and delete operations.

  • 复杂表达式。Complex expressions.

  • 程序逻辑,如条件语句和循环。Procedural logic, such as conditional statements and loops.

如果只需处理一行,则使用本机编译存储过程可能没有任何性能优势。If you need to process only a single row, using a natively compiled stored procedure may not provide a performance benefit.

避免服务器映射参数名称和转换类型:To avoid the server having to map parameter names and convert types:

  • 使传递给过程的参数类型与过程定义中的类型相匹配。Match the types of the parameters passed to the procedure with the types in the procedure definition.

  • 在调用本机编译的存储过程时使用序数(无名称)参数。Use ordinal (nameless) parameters when calling natively compiled stored procedures. 要实现最高效的执行,请勿使用命名参数。For the most efficient execution, do not use named parameters.

可通过 XEvent natively_compiled_proc_slow_parameter_passing 检测本机编译的存储过程的参数是否低效:Inefficiencies in parameters with natively compiled stored procedures can be detected through the XEvent natively_compiled_proc_slow_parameter_passing:

  • 不匹配的类型:reason=parameter_conversionMismatched types: reason=parameter_conversion
  • 命名参数:reason=named_parametersNamed parameters: reason=named_parameters
  • 默认值:reason=defaultDEFAULT values: reason=default

另请参阅See Also

本机编译的存储过程Natively Compiled Stored Procedures