We've been struggling with CPU performance issues on our website for some time and it appears as though a lot of our problem is the result of abusing the crap out of the .Prefetch feature in LLBLGen Pro. To be clear, this problem is 100% our own fault and not the problem of LLBLGen Pro -- we just didn't know what we were doing and wrote a lot of bad queries like this.

Here is an example query that we think performs really badly due to the complexity of the query, the amount of data returned from the database, and the amount of deserialization which has to happen when we pull back results:

return q.WithPathAny(x => x.Prefetch<MasterContentEntity>(version => version.TitleContent)
                                            .SubPath(titlePre => titlePre.Prefetch<TranslatedContentEntity>(title => title.TranslatedContents))
                                        .Prefetch<MasterContentEntity>(version => version.ShortTitleContent)
                                            .SubPath(shortTitlePre => shortTitlePre.Prefetch<TranslatedContentEntity>(shortTitle => shortTitle.TranslatedContents))
                                        .Prefetch<MasterContentEntity>(version => version.PageTitleContent)
                                            .SubPath(pageTitlePre => pageTitlePre.Prefetch<TranslatedContentEntity>(pageTitle => pageTitle.TranslatedContents))
                                        .Prefetch<MasterContentEntity>(version => version.MetaKeywordsContent)
                                            .SubPath(metaKeyPre => metaKeyPre.Prefetch<TranslatedContentEntity>(metaKey => metaKey.TranslatedContents))
                                        .Prefetch<MasterContentEntity>(version => version.MetaDescriptionContent)
                                            .SubPath(metaDescPre => metaDescPre.Prefetch<TranslatedContentEntity>(metaDesc => metaDesc.TranslatedContents))
                                        .Prefetch<MasterContentEntity>(version => version.CustomHtml)
                                            .SubPath(customHtmlPre => customHtmlPre.Prefetch<TranslatedContentEntity>(customHtml => customHtml.TranslatedContents))
                                        .Prefetch<ContentSectionEntity>(version => version.ContentSections)
                                            .SubPath(sectionPre => sectionPre.Prefetch<ContentSubsectionEntity>(section => section.ContentSubsections)
                                                .SubPath(subsectionPre => subsectionPre.Prefetch<ProductContentEntity>(subsection => subsection.ProductContents)
                                                    .SubPath(productContentPre => productContentPre.Prefetch<MasterContentEntity>(productContent => productContent.HeaderContent)
                                                        .SubPath(headerPre => headerPre.Prefetch<TranslatedContentEntity>(header => header.TranslatedContents)))
                                                    .SubPath(productContentPre => productContentPre.Prefetch<MasterContentEntity>(productContent => productContent.BodyContent)
                                                        .SubPath(bodyPre => bodyPre.Prefetch<TranslatedContentEntity>(body => body.TranslatedContents)))))
                                        .Prefetch<ProductVersionToEndItemEntity>(version => version.ProductVersionToEndItems)
                                        .Prefetch<MediaItemToProductVersionEntity>(version => version.MediaItemToProductVersions)
                                            .SubPath(mePre => mePre.Prefetch<MediaItemEntity>(me => me.MediaItem)
                                                    .SubPath(titPre => titPre.Prefetch<MasterContentEntity>(title => title.TitleContent)
                                                        .SubPath(tranPre => tranPre.Prefetch<TranslatedContentEntity>(tran => tran.TranslatedContents))))
                                            .SubPath<MediaImageItemToProductVersionEntity>(mediaPre => mediaPre.Prefetch<MasterContentEntity>(media => media.AltTextContent)
                                                .SubPath(altPre => altPre.Prefetch<TranslatedContentEntity>(alt => alt.TranslatedContents)))
                                            .SubPath<MediaImageItemToProductVersionEntity>(mediaPre => mediaPre.Prefetch<MasterContentEntity>(media => media.CaptionContent)
                                                .SubPath(captionPre => captionPre.Prefetch<TranslatedContentEntity>(caption => caption.TranslatedContents)))
                                        .Prefetch<RegionalProductEntity>(version => version.RegionalProducts)
                                                .SubPath(crtrp => crtrp.Prefetch<ContactRequestTypeToRegionalProductEntity>(rp => rp.ContactRequestTypeToRegionalProducts))
                                        .Prefetch<ProductTableEntity>(version => version.ProductTables)
                                            .SubPath(tablePre => tablePre.Prefetch<ProductTableNameEntity>(table => table.ProductTableName))
                                            .SubPath(tablePre => tablePre.Prefetch<ProductTableFooterEntity>(table => table.ProductTableFooters)
                                                .SubPath(footerPre => footerPre.Prefetch<MasterContentEntity>(footer => footer.MasterContent)
                                                    .SubPath(footerContentPre => footerContentPre.Prefetch<TranslatedContentEntity>(footerContent => footerContent.TranslatedContents))))
                                            .SubPath(tablePre => tablePre.Prefetch<ProductTableLayoutEntity>(table => table.ProductTableLayout)
                                                .SubPath(layoutPre => layoutPre.Prefetch<ProductTableColumnEntity>(layout => layout.ProductTableColumns)))
                                            .SubPath(tablePre => tablePre.Prefetch<ProductTableFootnoteEntity>(table => table.ProductTableFootnotes)
                                                .SubPath(footnotePre => footnotePre.Prefetch<MasterContentEntity>(footnote => footnote.MasterContent)
                                                    .SubPath(footnoteContentPre => footnoteContentPre.Prefetch<TranslatedContentEntity>(footnoteContent => footnoteContent.TranslatedContents)))
                                                .SubPath(footnotePre => footnotePre.Prefetch<ProductTableCellToProductTableFootnoteEntity>(footnote => footnote.ProductTableCellToProductTableFootnotes)
                                                    .SubPath(cellPre => cellPre.Prefetch<ProductTableToEndItemEntity>(cell => cell.ProductTableToEndItem))
                                                    .SubPath(cellPre => cellPre.Prefetch<ProductTableColumnEntity>(cell => cell.ProductTableColumn))))
                                            .SubPath(tablePre => tablePre.Prefetch<ProductTableToEndItemEntity>(table => table.ProductTableToEndItems))
                                        .Prefetch<ProductCountryExclusionEntity>(version => version.CountryExclusions))
                .First(x => x.ID == versionID);

Now we are in a position where the performance of our site has degraded quite a bit and we want to identify methods that have the worst impact on CPU performance. Do you have any recommendations for profilers or tools that can help us measure the amount of CPU load that a particular method generates when calling LLBLGen Pro code? Ideally we'd be able to identify the hottest CPU methods and get some objective score/weight on how bad it is. Then we could implement some changes (e.g. project out the few fields we want and stop doing a .Prefetch), and then profile again to see the score/weight go down.

I realize this isn't necessarily an LLBLGen Pro question, but I'd love to hear about how you are profiling to optimize CPU performance. Maybe we can try something similar

There are a lot of Memory Profilers out there. I used the one from Jetbrains, but now there are a lot of them.

There is also our ORMProfiler (form Solutions Design) which helps you in detecting DB connections activity, query performance, process timing, etc.

Additionally, you really should bring down the # of prefetch path elements there. Every path element is a query after all. As it's a website, chances are you deal with readonly data most of the time, i.e. you read the data, you build pages with it (e..g through mvc and client side js) and the data is gone. Altered data coming in is then updated in a subset of the entities used to build the pages.

So check what data you need for a given page or view, create a typed list for that, and fetch that. You can generate the typedlist as a poco element, which fetches very fast.

E.g. this forum uses that a lot, as the data used is mainly readonly.

The documentation contains a long article about performance tuning: http://www.llblgen.com/documentation/4.2/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/gencode_performancetuning.htm

Thanks for the responses! Will look into the links you've listed.

@Otis -- I agree this query is done entirely wrong. In reality, there may be only 15-20 fields we actually need to pull back and we could do it in a projection. We rarely really need to do a Prefetch. Given that we have dozens of these kinds of queries already in production (or maybe even hundreds) and CPU is our bottleneck at the moment, I'm just looking to find out which queries are the most CPU intensive. I suspect it's the all of the deserialization that is a problem but you would probably have a better instinct for what are the most CPU intensive operations in LLBLGen Pro. For example, in many cases we are pulling back hundreds of thousands of fields when we really only need to pull back a couple thousand. The SQL queries themselves seem to perform pretty well but our CPU is maxed out at the moment.

Thanks @Otis for sharing the really nice write-up on performance tuning. I like the practical tips on improving performance within LLBLGen Pro.

I think in our situation we just need to whip out ANTS and find the biggest pain points by hitting all of the major tasks within the website and drilling down from there.

Thanks!