Shulou(Shulou.com)06/02 Report--

This article will explain in detail the example analysis of the choice of CLR integrated performance design. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Compilation process

When compiling an SQL expression, if you encounter a reference to a managed routine, a Microsoft Intermediate language (MSIL) stub is generated. The stub contains code to marshal routine parameters from SQL Server to CLR, call functions, and return results. The "stick" code is based on the parameter type and parameter direction (inward, outward, or referenced). The glue code supports type-specific optimizations and ensures that SQL Server semantics are effectively enforced, such as Null, constraint aspects, value-by-value, and standard exception handling. By generating code for parameters of the exact type, you can avoid the overhead of type enforcement or wrapper object creation across call boundaries (called "boxing"). The generated stubs are then compiled into native code using CLR's JIT compilation service and optimized for the specific hardware architecture on which the SQL Server execution is located. The JIT service is invoked at the method level and allows the SQL Server hosting environment to create a compilation unit that executes across SQL Server and CLR. After the stub is compiled, the generated function pointer becomes the run-time implementation of the function. This code generation method ensures that other call overhead associated with run-time reflection or metadata access does not occur.

Fast conversion between SQL Server and CLR

Function pointers generated during compilation can be called from native code at run time. For scalar-valued user-defined functions, this function can be called on a per-line basis. To * reduce the cost of converting between SQL Server and CLR, statements that include any managed calls have a startup step that identifies the target application domain. The identification step reduces the conversion cost per row.

two。 Performance consideration

The following provides an overview of considerations specific to CLR integration performance in SQL Server. For more details, see "Using CLR Integration in SQL Server 2005" on the MSDN website (using CLR integration in SQL Server 2005). For general information about the performance of managed code, see "Improving .NET Application Performance and Scalability" on the MSDN Web site (improving performance and scalability of .NET applications).

3. User-defined function

Compared to Transact-SQL user-defined functions, CLR functions can benefit from a faster call path. In addition, managed code has a decisive performance advantage over Transact-SQL in terms of procedure code, computation, and string manipulation. CLR functions that require a lot of computation and do not perform data access work better when written in managed code. But Transact-SQL functions do perform data access more efficiently than CLR integration performance.

4. User-defined aggregation

Managed code performs much better than cursor-based aggregations. The execution of managed code is usually slightly slower than that of the built-in SQL Server aggregate function. If there is a native built-in aggregate function, it is recommended that you use it. For cases where the required aggregations are not natively supported, consider using CLR user-defined aggregations instead of a cursor-based implementation for performance reasons.

5. Flow table-valued function

Applications usually need to return a table as the result of calling a function. Examples include reading tabular data from a file as part of an import operation and converting comma-separated values into relational representations. Typically, you can do this by materializing and populating the result table before the caller uses it. The integration of CLR and SQL Server introduces a new extensibility mechanism called streaming table-valued function (STVF). The performance of managed STVF is better than that of comparable extended stored procedure implementations. STVF is a managed function that returns the IEnumerable interface. IEnumerable has a way to navigate the result set returned by STVF. When STVF is called, the returned IEnumerable connects directly to the query plan. The query plan calls the IEnumerable method when the row needs to be extracted. With this iterative model, the results can be used after the * rows are generated, rather than waiting for the entire table to be filled. You can also greatly reduce the memory consumed by calling the function.

6. Arrays and cursors

Using managed code can significantly improve performance when Transact-SQL cursors must traverse data that is more easily represented as an array.

7. String data

SQL Server character data, such as varchar, can be of type SqlString or SqlChars in managed functions. The SqlString variable creates an instance of the entire value into memory. The SqlChars variable provides a streaming interface that can be used for better performance and scalability without having to create an instance of the entire value into memory. This is especially important for large object (LOB) data. In addition, server XML data can be accessed through the streaming interface returned by SqlXml.CreateReader ().

8.CLR and extended stored procedures

The Microsoft.SqlServer.Server Application programming Interface (API), which allows managed procedures to post result sets back to clients, performs better than the Open data Services (ODS) API used by extended stored procedures. In addition, System.Data.SqlServer API supports data types introduced in SQL Server 2005 such as xml, varchar (max), nvarchar (max), and varbinary (max), but ODS API has not been extended to support new data types.

Through managed code, SQL Server manages the use of resources such as memory, threads, and synchronization. This is because the managed API that exposes these resources is implemented for SQL Server Explorer. In contrast, SQL Server cannot view or control the resource usage of extended stored procedures. For example, if an extended stored procedure consumes too much CPU or memory resources, the situation cannot be detected or controlled through SQL Server. However, SQL Server can use managed code to detect that a given thread has not generated a result for a long time and force the task to generate to schedule other work. Therefore, the use of managed code can improve scalability and improve system resource usage. Managed code can incur additional overhead in maintaining the execution environment and performing security checks. For example, this may occur when running within SQL Server and you need to perform a large number of conversions from managed code to native code (because SQL Server requires additional maintenance of thread-specific settings when converting back and forth between managed code and native code). Therefore, in the case of frequent conversions between managed and native code, the performance of extended stored procedures is much better than that of managed code running within SQL Server.

Note:

It is recommended that you do not develop new extended stored procedures because this feature is no longer recommended.

9. Native serialization of user-defined types

User-defined types (UDT) are designed as an extensibility mechanism for scalar type systems. SQL Server implements an UDT serialization format called Format.Native. During compilation, check the structure of the type to generate a MSIL that is customized for that particular type definition. Native serialization is the default implementation for SQL Server. User-defined serialization calls methods defined by the type author to perform serialization. Format.Native serialization should be used whenever possible to achieve * performance.

10. Standardization of comparable UDT

Relational operations, such as sorting and comparing UDT, are performed directly against the binary representation of values. You can do this by storing a normalized (binary sorted) representation of the UDT state on disk. Normalization has two advantages: avoiding the overhead of constructing type instances and method calls, which greatly reduces the cost of comparing operations; and creating binary fields for UDT that support the construction of histograms, indexes, and histograms of values of that type. Therefore, the performance profile of the canonical UDT is similar to the native built-in type performance profile for operations that do not involve method calls.

11. Scalable memory usage

To perform and tune managed garbage collection well in SQL Server, avoid using large single allocations. Allocations larger than 88 kilobytes (KB) will be placed on the large object heap, which will cause garbage collection performance and tuning results to be much lower than those of multiple smaller allocations. For example, if you need to allocate a large multidimensional array, * allocate an interlaced (scattered) array.

This is the end of this article on "sample analysis of CLR integrated performance design choices". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.