Adding your own index in the EPiServer database

henriknystrom

Vote:

A client of ours is looking into adding a custom index to the EPiServer 5 R1 database to speed up the response from one specific query that is quite slow. (Yes, it's a FindPagesWithCriteria). I would normally recommend that they instead cache the result but in this case they where after a quick fix (as we all are at some point) and by adding an optimized index on tblProperty the query came down from ~1000ms to 14ms.

I know that R2 added new indexes but I suspect that they won't beat an index optimized for this exact query.

What I now wanted some input on was what potential side effects this might have. I could only come up with two:

Marginally slower inserts (not a real problem)
Could create problems when doing an upgrade, so the index needs to be dropped before an upgrade and recreated afterwards if still needed.

Can anyone add (or remove) anything to this list?

Cheers
Henrik

#28726

Mar 20, 2009 0:15

Per Bjurström

Vote:

Not really, that would be the obvious side effects.

#28736

Mar 20, 2009 8:59

Steve Celius

Vote:

Someone will forget about the index after some time, and you will get in trouble during upgrades (I recently spent half a day cleaning up indexes that crashed the migration tool.)

Go with the index as a real short term solution, and rewrite the FPWC code to perform better (limit the number of pages it returns). Also, R2 SP1 added substantial performance improvements to FPWC which is worth checking out. Just don't forget to remove that index!

/Steve

#28741

Mar 20, 2009 10:15