Deployment, debugging, and performance of managed stored procedures

Recently, I am trying to make an image search experiment. I need to store a large amount of image feature data in the database, most of which are stored in binary format, for this kind of data will certainly not use the where in the T-SQL... like and other limited syntax to search, and the database client only needs to retrieve the results, rather than those large amounts of feature data, for feature data often requires more complex operations (using T-SQL to write those operations, it is estimated that the first big .. and I am not familiar with T-SQL), in order to reduce the client and database so thought of SQL Server 2005 CLR integration features, would like to try to use managed stored procedures, for the first time, I encountered some problems with this feature, which were solved by Google and msdn. The results showed that this feature was quite useful.

 

1. About deployment

In fact, in the database project of Visual Studio 2005, the deployment and debugging functions are automated, which can be seen in relevant articles and documents (but for some reason, I never used it again ..), however, the following describes manual deployment and subsequent manual configuration debugging.

 

The CLR integration feature of SQL Server has high security requirements. It strictly reviews the code of the deployed Assembly. For the Assembly, there are three permission sets: Safe, external access and unsafe, because the Assembly I want to deploy has some "insecure" code that cannot be deployed as a safe assembly, directly execute the following T-SQL code

Create Assembly icanseeclr
From 'e: \ Adrian. Icansee. Data. dll'
With permission_set = unsafe

Failed. The error message is

Create assembly for assembly 'adrian. icansee. data 'failed because assemblie' Adrian. icansee. data 'is not authorized for permission_set = unsafe. the Assembly is authorized when either of the following is true: the database owner (DBO) has unsafe Assembly permission and the database has the trustworthy database property on; or the assembly is signed with a certificate or an asypolicric key that has a corresponding login with unsafe Assembly permission.

It can be seen that the deployment of an assembly as unsafe must meet one of the following two conditions:

1. The database owner (DBO) has unsafe Assembly permission and the database has the trustworthy database property on;

2. The Assembly is signed with a certificate or an asypolicric key that has a corresponding login with unsafe Assembly permission.

The first condition seems a lot simpler, so I chose the first one. Through Visual operations of SQL Server Management studio, permissions are easily set, of course, people are getting increasingly reluctant to remember those T-SQL statements. then use

Alter database icanseedb set trustworthy on

Set the trustworthy attribute.

Execute create Assembly again, run successfully. but did not see the Stored Procedure added. From the set register stored procedure has to write T-SQL:

Create procedure retrieve
@ Histogram binary (288 ),
@ Avgx binary (288 ),
@ Avgy binary (288 ),
@ Count int
As
External name icanseeclr. [Adrian. Icansee. Data. sqlclr]. Retrieve

Stored procedure parameters correspond to the parameter types in the C # method:

[Sqlprocedure]
Public static void retrieve (sqlbinary histogram, sqlbinary avgx, sqlbinary avgy, sqlint32 count)

External name is followed by <SQL Server Assembly Name>. <Class Name>. <Method Name>

Here, a stored procedure is deployed. Calling it is the same as calling a common stored procedure.

2. About debugging stored procedures

Debugging the managed storage process also requires several prerequisites. For more information, see the SQL server documentation. However, after installing SQL Server 2005 Enterprise, you can directly debug it using the following method without any settings:

The method used to debug the stored procedure is to use Visual Studio 2005 attach processto sqlservr.exe, but you must upload the debugging information file (. PDB) for the manually deployed assembly ):

Alter Assembly icanseeclr
Add file from 'e: \ Adrian. Icansee. Data. pdb'

Set the breakpoint and attach process in the source file. If there are no other problems, any requests that successfully enter the stored procedure can be captured.

If you need to debug both the client program and the stored procedure, you have to open another Visual Studio to debug it.

3. Performance

By running the computing logic in the managed storage process and in the database client separately, I found that the performance between them was almost the same. the stored procedure hosting solution only improves the performance by up to 15%. however, I only perform local tests. Both the database and client are on my own, and the data volume is not large, I don't see the performance advantages of reducing data transmission in my initial managed storage process, but in a distributed system with a large amount of data, maybe .. it may bring some performance advantages. more information about CLR integration performance is described in this document.

But the biggest advantage of managed code is its powerful function, T-SQL is incomparable powerful, since the performance will not be poor, in the appropriate circumstances choose it is good.