T-SQL stored procedure call from CLR stored procedure

Question

Very short background: We use CLR stored procedures to apply access control using Active Directory in the query results to limit what the end user can see. In a nutshell, this is done by removing rows from the data where the user does not meet the criteria for accessing the result (the document in this case).

This filtering was previously done on the client before displaying the results. SQL 2008 and a much more powerful server are the motivation to migrate this access filtering from the client.

I am wondering if there is any performance advantage from calling the original T-SQL ordinary stored procedure from an equivalent CLR equivalent procedure, instead of having inline T-SQL passed to the comand object (which in this case is only the original T- SQL that was created by the stored procedure)? I can't find anywhere where anyone mentioned this (partly, probably because it would be very confusing as an example of CLR SP, I think :-)). It seems to me that you could, since the T-SQL stored process is already optimized and compiled?

Can anyone confirm this for me?

I hope I was clear enough. Thank you very much,

Colm

Answer 1

If your SQL CLR stored procedure executes a specific query correctly (well parameterized) and executes it quite often, then this T-SQL query will simply be run once through the entire sequence "determine the optimal execution plan" and then stored in the plan cache of your SQL Server ( and didn’t exit it faster than a similar T-SQL stored procedure).

Thus, it will be the same as the “precompiled” one as the original T-SQL stored procedure. From this point of view, I see no benefit.

If you can configure the SQL statement from your SQL CLR procedure in such a way that it doesn’t even include these lines in the result set, which you throw out anyway, then your SQL-CLR will be saved by the procedure that executes the correctly parameterized T-SQL query , maybe even a little faster than having a standard T-SQL stored procedure, returning too much data from which you need to exclude some rows again.