Perf: WidgetContainer

[MatteoPiovanelli-Laser]

https://github.com/LaserSrl/Laser.Orchard.Platform/blob/593edf00dd4c47bdd9d45af35681121c8b038a3a/src/Modules/Contrib.Widgets/Services/WidgetManager.cs#LL26C31-L26C31

What I think this is doing:

• SQL query to find the Ids of the widgets (thanks to the join with WidgetExPartRecord, and the fact the default implementation will itself join ContentItemRecord and ContentItemVersionRecord)
• One contentManager.Get for each of those
  • In every Get, during the Loading handlers, we are setting delegates to launch further queries for each record involved in the content (https://github.com/LaserSrl/Orchard/blob/95a5782d725f00dc168e4e7f53bd5a88102eb7f2/src/Orchard/ContentManagement/Handlers/StorageFilter.cs#LL57C27-L57C28), so if the widget has a TitlePart and a BodyPart that's two additional queries ready to be fired.
• Extract the WidgetExPart from each of the resulting ContentItems.

We should test whether any of those additional Loading queries is firing. I suppose so, and some of this may be partly responsible for the slowdowns we are seeing, because we tend to use a lot of these widgets everywhere. In WidgetService (https://github.com/LaserSrl/Orchard/blob/95a5782d725f00dc168e4e7f53bd5a88102eb7f2/src/Orchard.Web/Modules/Orchard.Widgets/Services/WidgetsService.cs#L74) They useQueryHintsto expand theCommonPart`. It may be wort to test whether that helps our cases.

[HermesSbicego-Laser]

We can use SQL profiler in order to confirm if it fires too many queries. Then if confirmed we could try to reduce their number if possible. Large use of widgets can impact on performance, but definitely we have not another way to compose pages. So, we need to reduce SQL queries execution time