A little improvement of the tsf_user() function

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.

 

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.