Database Indexes

Topics: Core, Customizing Orchard, General
Oct 7, 2011 at 7:56 PM

Hi Chaps,

I would like to get some information from the community around indexing Orchard CMS's database (think SQL Server).

Has anyone done it? Has anyone got any suggestions to which columns need indexing? etc....

Cheers, Nick

Nov 8, 2011 at 2:56 PM

I'm currently doing some performance tweaking of my Orchard setup. One of the things I found was indeed that a few core database indexes are missing.

The way I'm going at it is using a query that outputs the most read-intensive queries, and then having a look at their execution plan. I'm doing it on a Sql Server instance, not the file-based version that's default selected in the recipe screen.

The query I got from here:


SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC


Now, if you take the top query (so this is one that's used very often or it's extremely slow, or both if you're unlucky), and execute it with the "execution plan" option enabled (Ctrl+M), you'll see how it executes. What I found, for instance, was this query:


SELECT this_.Id as Id57_2_, this_.Number as Number57_2_, this_.Published as Published57_2_, this_.Latest as Latest57_2_, this_.Data as Data57_2_, this_.ContentItemRecord_id as ContentI6_57_2_, contentite1_.Id as Id54_0_, contentite1_.Data as Data54_0_, contentite1_.ContentType_id as ContentT3_54_0_, advancedme2_.Id as Id35_1_, advancedme2_.Text as Text35_1_, advancedme2_.Url as Url35_1_, advancedme2_.Position as Position35_1_, advancedme2_.MenuName as MenuName35_1_, advancedme2_.SubTitle as SubTitle35_1_, advancedme2_.Classes as Classes35_1_, advancedme2_.DisplayText as DisplayT8_35_1_, advancedme2_.DisplayHref as DisplayH9_35_1_, advancedme2_.RelatedContentId as Related10_35_1_ 
FROM Orchard_Framework_ContentItemVersionRecord this_ 
inner join Orchard_Framework_ContentItemRecord contentite1_ on this_.ContentItemRecord_id=contentite1_.Id 
inner join Szmyd_Orchard_Modules_Menu_AdvancedMenuItemPartRecord advancedme2_ on contentite1_.Id=advancedme2_.Id 
WHERE advancedme2_.MenuName = @p0 and this_.Published = @p1


The execution plan shows me that the slowest part is the mapping of Orchard_Framework_ContentItemRecord to Szmyd_Orchard_Modules_Menu_AdvancedMenuItemPartRecord.
And thus I found out it would be smart to include an index on the field Orchard_Framework_ContentItemRecord.ContentItemRecord_id. This can be achieved to add to "FrameworkDataMigration.cs" the following:

  table => table

That way it'll be included if you re-recipe.

@OrchardTeam, could you include this index in the core module itself?

Nov 8, 2011 at 3:23 PM
lukasvan3l wrote:

@OrchardTeam, could you include this index in the core module itself?

Well done for digging into this. You should know that the Performance Team ( are looking into indices and your input (particularly on Execution Plans) could perhaps be useful there! 

Nov 8, 2011 at 3:37 PM

I tried the fix you suggested in FrameworkDataMigrations.cs - but I don't seem to be able to apply the update. The modules screen shows "Some features need to be upgraded: Orchard.Framework" but the link doesn't work because the module isn't listed anywhere (and so there's no Update button to click). How did you get this migration to run? This is in Orchard 1.3.

Nov 8, 2011 at 4:58 PM

Right now the Core features can't run migrations, but it's just a UI limitation. I overloaded it for the Title part specifically in 1.3 (for updated, not fresh install which don't need it then). If we intend to add indexes in next version, then we'll do it again.

Can you please do some follow ups on the Performance thread, and you might also create a fork and do it for more tables. Like any FK with ContentItem and ContentItemVersionRecord for instance.