4

Very brief background: We are making use of CLR stored procedures to apply access control, using Active Directory, on query results to restrict what the end user can see accordingly. In a nutshell, this is done by removing rows from a datatable where the user does not satisfy the criteria for access to the result (document in this case).

This filtering was previously done on the client before displaying the results. SQL 2008 and a much more powerful server is the motivation for moving this access filtering off the client.

What I am wondering is, is there any performance benefit to be had from calling the original regular T-SQL stored procedure from the CLR stored procedure equivalent, instead of having 'inline' T-SQL passed into the comand object (which in this case is just the original T-SQL that was made a stored procedure) ? I cannot find anywhere where someone has mentioned this (in part probably because it would be very confusing as an example of CLR SPs, I guess :-) ). It seems to me that you might, as the T-SQL stored proc has already been optimised and compiled ?

Is anyone able to confirm this for me ?

Hope I've been clear enough. Thanks very much,

Colm.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Colm
  • 43
  • 7

1 Answers1

0

If your SQL CLR stored procedure does a specific query properly (nicely parametrized) and executes it fairly frequently, then that T-SQL query will be just run once through the whole "determine the optimal execution plan" sequence and then stored in the plan cache of your SQL Server (and not evicted from it any faster than a similar T-SQL stored procedure).

As such, it will be just as "pre-compiled" as your original T-SQL stored procedure. From that point of view, I don't see any benefit.

If you could tweak your SQL statement from within your SQL CLR procedure in such a way that it would actually not even include those rows into the result set that you'll toss out in the end anyway, then your SQL-CLR stored procedure executing a properly parametrized T-SQL query might even be a bit faster than having a standard T-SQL stored procedure return too much data from which you need to exclude some rows again.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • That makes sense. I'll look into tweaking the SQL statement also. Perhaps I can pass the SIDs into the T-SQL query itself, as they are already available for the filtering on the results of that query in the CLR SP. Thanks very much for your help Marc. – Colm Dec 17 '11 at 21:16