I found 2 problems with the tsf_user function;
- If I use the function as DBA (with a direct login to the database) the function gives a guid.
- The function can confuse the SQL-Server query optimizer with the result that the function gets executed multiple times.
As a workaround I replaced the function by a view, which checks if the CONTEXT_INFO value isn’t a GUID, and an additional check whether the user returned is actually present in our user table.
I did not investigate what it would do with the performance when I made the function schema bound, but the view is much faster… and I think that making the function schema bound might give the same result. ( It would not bind to anything, but SQL-Server might understand that there is no need to evaluate the function more then once ).
And a small improvement to this platform would be to allow users to edit their last post, as I just spelled GUI instead of GUID. :-)
And, sorry, I just checked and the function is already schema bound … so that does not help. But in the queries I checked a view still gave a much faster result then a function, it seems that SQL server is not good at optimizing UDF’s.
And a small improvement to this platform would be to allow users to edit their last post, as I just spelled GUI instead of GUID. :-)
Sadly the platform doesn't have that ability. We (and many others) have submitted an idea for it over at Gainsight; the platform supplier.
May I ask which platform version you are on and which SQL Server version you are using?; or are you using Azure SQL Server?
We haven't heard any performance degradation about this function so I'm quite surprised. Making it a View may work in your case but not for others. What kind of performance difference are you experiencing?
We are using Azure. There where a lot of unoptimized Queries, so I spend a lot of time looking at the execution plans. I noticed that the function sometimes entered up at a location where the function was executed for every row, while that was not needed.
We have a medium big fact table, and about 90% of the users can only see 1% of the table. In our scenario, it makes a huge difference where SQL Server places the user rights in the execution plan. Even a further unoptimized query rus quit good, if you are able to dismiss 99% of the records at the start.
The way things where build, using the tsf_user function, the optimizer from Azure did not understand this. When I changed it to a view, the optimizer did.
And this optimisation made that I also changed something else in the functionality.
I made sure that the views behaved the way they should behave based on my identity when I used them from SSMS.
If I run tsf_user while I’m logged in as “daan”, I get a guid. I changed the logic a little, so that the view I created returns “daan”. It is also possible to simulate other users while executing queries in SSMS. My slightly altered implementation makes debugging easier, and makes that fields like “updated_by”, show my name instead of a guid.