Performance issues on heavy ContentItems numbers

[HermesSbicego-Laser]

Hi all, I think it would a must have some optimization in DB Tables in order to be more efficient in serving data in the ContentItem List. Recently I've done a massive import of data from an external DB (100,000 rows, so 100,000 ContentItems, draftable). Now Orchard Content List (/Admin/Contents/List) show items ordered by Modified/Published/Creation date descending, and the query fires in 10/15 seconds per page... too much, let me say, for 10 records... Analysing the query execution plan, I noticed 33% cost depends on ordering by ModifiedUtc column. So I tried to index the column, but without benefits. I think we should find a solution on that in order to make Orchard efficient. Removing the order by clause, query fires in less than 1 second Any idea or solution?

The Query

 SELECT TOP 10 this_.Id as Id1404_4_, this_.Number as Number1404_4_, this_.Published as Published1404_4_, this_.Latest as Latest1404_4_, this_.Data as Data1404_4_, 
 this_.ContentItemRecord_id as ContentI6_1404_4_, titlepartr4_.Id as Id1606_0_, titlepartr4_.Title as Title1606_0_, titlepartr4_.ContentItemRecord_id as ContentI3_1606_0_, contentite1_.Id as Id1398_1_, 
 contentite1_.Data as Data1398_1_, contentite1_.ContentType_id as ContentT3_1398_1_, commonpart3_.Id as Id1426_2_, commonpart3_.OwnerId as OwnerId1426_2_, commonpart3_.CreatedUtc as CreatedUtc1426_2_,
  commonpart3_.PublishedUtc as Publishe4_1426_2_, commonpart3_.ModifiedUtc as Modified5_1426_2_, commonpart3_.Container_id as Container6_1426_2_, contenttyp2_.Id as Id1410_3_, 
  contenttyp2_.Name as Name1410_3_ FROM Orchard_Framework_ContentItemVersionRecord this_ inner join 
  Title_TitlePartRecord titlepartr4_ on this_.Id=titlepartr4_.Id 
  inner join Orchard_Framework_ContentItemRecord contentite1_ on this_.ContentItemRecord_id=contentite1_.Id 
  inner join Common_CommonPartRecord commonpart3_ on contentite1_.Id=commonpart3_.Id 
  inner join Orchard_Framework_ContentTypeRecord contenttyp2_ on contentite1_.ContentType_id=contenttyp2_.Id 
  WHERE contenttyp2_.Name = 'Contacts' 
  and this_.Latest = 1 
  ORDER BY commonpart3_.ModifiedUtc

The Execution Plan

[jakejgordon]

:+1:

[sebastienros]

You should run the SQL Server Tuning tool with this specific load and it will give you what index to create to make it faster. Also, if you set the correct MAX PAGE count in the global settings it will prevent users from accessing too far away pages, and remove a Count(*).

[alberthajdu]

Hi @HermesSbicego-Laser,

I think this has been solved. I tested it on the latest dev, on my laptop, with 150k content items and the page reloaded in ~1.7s. Maybe the infoset solved this problem but I don't know for sure.

The query

SELECT TOP (10) this_.Id AS Id739_3_
        ,this_.Number AS Number739_3_
        ,this_.Published AS Published739_3_
        ,this_.Latest AS Latest739_3_
        ,this_.Data AS Data739_3_
        ,this_.ContentItemRecord_id AS ContentI6_739_3_
        ,contentite1_.Id AS Id738_0_
        ,contentite1_.Data AS Data738_0_
        ,contentite1_.ContentType_id AS ContentT3_738_0_
        ,commonpart2_.Id AS Id782_1_
        ,commonpart2_.OwnerId AS OwnerId782_1_
        ,commonpart2_.CreatedUtc AS CreatedUtc782_1_
        ,commonpart2_.PublishedUtc AS Publishe4_782_1_
        ,commonpart2_.ModifiedUtc AS Modified5_782_1_
        ,commonpart2_.Container_id AS Container6_782_1_
        ,contenttyp6_.Id AS Id740_2_
        ,contenttyp6_.NAME AS Name740_2_
FROM Orchard_Framework_ContentItemVersionRecord this_
INNER JOIN Orchard_Framework_ContentItemRecord contentite1_ ON this_.ContentItemRecord_id = contentite1_.Id
INNER JOIN Common_CommonPartRecord commonpart2_ ON contentite1_.Id = commonpart2_.Id
LEFT OUTER JOIN Orchard_Framework_ContentTypeRecord contenttyp6_ ON contentite1_.ContentType_id = contenttyp6_.Id
WHERE contentite1_.ContentType_id = 12
        AND this_.Latest = 1
ORDER BY commonpart2_.ModifiedUtc DESC

The execution plan As you can see the sort costs 94% now but it's fast.

[HermesSbicego-Laser]

@alberthajdu Thanks I will try with my 600K items

[BenedekFarkas]

@HermesSbicego-Laser please close the issue if you can confirm it's fixed.

[HermesSbicego-Laser]

I tried with my 600K Contents tenant; and I can see an improvement in perf. But the query still run in more than 10 seconds when I try to list the last pages of the list...

see the below query: the lower @p8 is, the faster is the query. I suppose the problem is the ROW_NUMBER() function used to page results: SELECT TOP (@p0) ... WHERE ... query.__hibernate_sort_row > @p8

DECLARE @p0 as int = 10
  DECLARE @p1 as int = 5
  DECLARE @p2 as int = 11
  DECLARE @p3 as int = 12
  DECLARE @p4 as int = 13
  DECLARE @p5 as int = 14
  DECLARE @p6 as int = 10
  DECLARE @p7 as bit = 1
  DECLARE @p8 as int = 653200

  SELECT TOP (@p0) Id907_3_, Number907_3_, Published907_3_, Latest907_3_, Data907_3_, ContentI6_907_3_, Id906_0_, Data906_0_, ContentT3_906_0_, 
  Id972_1_, OwnerId972_1_, CreatedUtc972_1_, Publishe4_972_1_, Modified5_972_1_, Container6_972_1_, Id908_2_, Name908_2_ FROM 
  (SELECT this_.Id as Id907_3_, this_.Number as Number907_3_, this_.Published as Published907_3_, this_.Latest as Latest907_3_, 
  this_.Data as Data907_3_, this_.ContentItemRecord_id as ContentI6_907_3_, contentite1_.Id as Id906_0_, contentite1_.Data as Data906_0_, 
  contentite1_.ContentType_id as ContentT3_906_0_, commonpart2_.Id as Id972_1_, commonpart2_.OwnerId as OwnerId972_1_, 
  commonpart2_.CreatedUtc as CreatedUtc972_1_, commonpart2_.PublishedUtc as Publishe4_972_1_, commonpart2_.ModifiedUtc as Modified5_972_1_, 
  commonpart2_.Container_id as Container6_972_1_, contenttyp6_.Id as Id908_2_, contenttyp6_.Name as Name908_2_, 

ROW_NUMBER() OVER(ORDER BY commonpart2_.ModifiedUtc DESC) as __hibernate_sort_row FROM 

Orchard_Framework_ContentItemVersionRecord this_ 
  inner join Orchard_Framework_ContentItemRecord contentite1_ WITH (NOLOCK) on this_.ContentItemRecord_id=contentite1_.Id 
  inner join Common_CommonPartRecord commonpart2_ WITH (NOLOCK) on contentite1_.Id=commonpart2_.Id 
  left outer join Orchard_Framework_ContentTypeRecord contenttyp6_ on contentite1_.ContentType_id=contenttyp6_.Id 
  WHERE contentite1_.ContentType_id in (@p1, @p2, @p3, @p4, @p5, @p6) and this_.Latest = @p7) as query 
  WHERE query.__hibernate_sort_row > @p8 ORDER BY query.__hibernate_sort_row;

[sebastienros]

when I try to list the last pages of the list

There are custom settings that let you define how many pages of items to display in the admin. This should be used in the lists (and it is in the content items one) to prevent the use of COUNT. And it make the pages much faster.

[BenedekFarkas]

I added this to 1.11 so we don't forget to investigate - might be an easy fix worth including.