Dynamic Properties performance revisited

Everyone knows dynamic properties are slow and have been so through the ages.

We speeded it up by a factor x100 with a single statement. Read on to know how and why.

My colleague Måns Hjertstrand and myself sat down today with the intent to review options to remove use or work around the slowness of dynamic properties on a site we’re working on.

The site has limited use of dynamic properties, but fairly large number of pages, ca 100 000. It’s an EPiServer CMS 5 site.

We’ve previously analyzed the issue, and found that the Stored Procedure netDynamicPropertyTree was the culprit.

The behavior is very unfortunate, because every time the page cache is cleared (which is frequently) or a page is moved, the cached result of netDynamicPropertyTree is invalidated.

Every time a dynamic property is referred to, it starts by calling netDynamicProperty tree when the cache has been cleared. This only happens the first time between cache invalidations, but it’s much worse than it appears.

We’re running a web farm with 3 servers and 6 enterprise sites. this adds up to 18(!) calls to netDynamicProperty tree every time the cache is cleared.

The last time we visited this issue, a clean call of netDynamicPropertyTree took about 20 seconds. When we had up to 18 instances running at the same time, the total time took so long that the cache often was cleared again before we were done etc etc – basically crashing the server.

The solution then was to hack into the EPiServer code and change the way the cache was reloaded, using the old data in the cache until the new data was delivered from the database. This sort of worked, but the database server still had to work pretty hard, and I had to do some pretty disgusting things in C# with reflection.

Now, with more pages in the database, a clean call of netDynamicProperty tree takes about 133 seconds… It’s simply not working any more!

So Måns hit upon the idea to sneak a peak at the Stored Procedure itself and check what really was the problem and to analyze just what factors affected it.

Surprise! When we ran the code from netDynamicProperties from a SQL Query window it ran in 2-3 seconds. Not 133. Hmm…

To make a long story short – the problem is that a temporary table is created, and when run from a Stored Procedure, SQL Server never figures out it needs an index.

Googling turned up many similar situations where SQL Server is slow when running a stored procedure, but fast when running from a query window. Many solutions were found where the problem was with parameter sniffing (we don’t have any), or ANSI NULL (we’re ok there), recompilation (nope, didn’t help) or suggested use a table variable instead of temp table (nope, slower).

So – the following addition to netDynamicProperties speeded up use of dynamic properties by a factor of almost x100, and totally changed the responsiveness of the whole site.

create clustered index idx_tmpprop_fkParentID on #tmpprop ( fkParentID )

That’s it. Running time down to about 1700 milliseconds instead of 133000. Not quite 100x but when several are running it’s much more!

One would have thought this would have been addressed, considering that dynamic properties have been such a problem for so many years. I guess we struck lucky, considering that neither of us really knows anything about SQL Server.

Oh, I also did the corresponding change to netDynamicPropertiesLoad – it can’t hurt I guess.

Oh, oh – I did say we don’t know much about SQL Server? I have no idea why it apparently does not matter what column we index… Comments appreciated.

Of course we do not recommend you try this at home, after all it does mean voiding your warranty. You might want to ask for this, or something better, to be retrofitted in a hotfix for your version of EPiServer though.

Disclaimer: Your mileage may vary.