Adding your own index in the EPiServer database

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:

  1. Marginally slower inserts (not a real problem)
  2. 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
Vote:
 

Not really, that would be the obvious side effects.

 

 

#28736
Mar 20, 2009 8:59
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
This thread is locked and should be used for reference only. Please use the Episerver CMS 7 and earlier versions forum to open new discussions.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.