Lukasvan3L

A pragmatic programmer

3 notes &

Speeding up Orchard: Database Indexes in SQL Server

I’m currently doing some performance tweaking of my Orchard setup. One of the things I found was 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 setup screen.┬áThe query I got from here: http://blog.brianhartsock.com/2008/12/16/quick-and-dirty-sql-server-slow-query-log/.

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.execution_count, 
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,
qs.last_execution_time,
qp.query_plan
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_ContentItemVersionRecord.ContentItemRecord_id. This can be achieved by adding to “FrameworkDataMigration.cs” the following:

SchemaBuilder.AlterTable(
  "ContentItemVersionRecord",
  table => table
    .CreateIndex("Index_Orchard_Framework_ContentItemVersionRecord_ContentItemRecord_id",
    "ContentItemRecord_id"));

That way it’ll be included if you re-recipe. Be advised, the core modules will not run database migrations, so if you have a live site it’s better to just update the database tables themselves, or put this code in another module that’s not part of the core.

  1. developer3l posted this