SQL Server : Scalar Function Affecting Performance

Created 22 January 2016 12:20, updated 18 June 2017 13:49

I was looking into poor performance issue on a data warehouse report today, and noticed this line as one of the columns being returned in the SELECT statement:


The query as a whole has around 7 joins and returns 5500 records. This is a fairly small data set, but without this line the query was running in under 2 seconds - with the scalar function being called it was taking 14 - 20 seconds. However, when I looked into the function it really wasn't doing very much - it wasn't very well written but it didn't have any hidden SELECT statements or anything that would cause an obvious performance issue. All it was doing, was formatting the date being passed to it.

The function itself wasn't the problem - it was the recursive call of the scalar function that was the issue. Basically, this speaks to the way SQL Server optimises - it just cannot optimise that function as part of its query plan, and executes it 5500 times - once for every row.

So what's the solution? If the function encapsulates logic that you don't want spread across reports, you can't remove the function. Your best option is to use a TABLE valued function instead of a scalar. Firstly, you need to rewrite your function as follows:

CREATE FUNCTION [cadi].[MyFunction] (@Date as smalldatetime)
       select right(convert(varchar, @Date, 106), 8) AS MyDate

Then, you need to add remove the function from the SELECT portion of your query and CROSS APPLY it - as follows:

SELECT MyTableResult.MyDateColumn, 
FROM dbo.MainTable t
INNER JOIN dbo.OtherTable o ON t.Id = o.ForeignKeyId
CROSS APPLY dbo.MyFunction(t.MyDateColumn) AS MyTableResult
WHERE t.Status = 1

In my particular scenario, applying the TABLE function as opposed to SCALAR resulted in performance going down to 2 seconds i.e. no appreciable hit on performance at all.

MediaWiki on IIS working with no styles

Created 04 January 2016 19:58

I decided to install MediaWiki today on one of our web servers, and set it up within IIS. However, although the site immediately served pages, all of them were displaying without any styles. The stylesheet request showed with a 200 response within Chrome so I didn't think there was anything wrong there and resorted to Google. However, I couldn't find anything on Google with an answer, so after an hour of trial and error I eventually ended up back in Chrome looking at the request.

It turns out the 200 response was incorrect - by following the link there was actually an error occurring when I looked at the contents of the response itself. It turns out, there was an error in the response: C:\Windows\TEMP not writable. The less engine being used was trying to dynamically compile the stylesheet and was failing as the IIS user under which the site was running didn't have access to the folder. I added write permissions to the user, and boom, it started working.