+972 52-548-6969
Looking at nopCommerce
I’m preparing for a talk about using multiple databases in a single application, and I wanted to show how this looks like in a real application. I wanted to take a look at nopCommerce as the target application.
To be fair, I haven’t really worked with enterprise applications in a while, but I found some interesting stuff there. Note that I’m not doing anything like a full review. My goal is to show the concept, not explore the full implications of nopCommerce. The code in questions is from commit 1286b4f8d4c0ed2a5d6441db7cbd5398821d32f2.
nopCommerce is using Entity Framework for accessing SQL Server, so it was a simple matter to install the Entity Framework Profiler and take a peek into what was going on there. I then did the simplest thing I could think of, and searched for gift in the site:
Here is what happened:
Now, there are 25 queries, and a total of 40KB of SQL executed. Here is one such query:
SELECT [Project4].[Id] AS [Id],
[Project4].[Name] AS [Name],
[Project4].[Description] AS [Description],
[Project4].[CategoryTemplateId] AS [CategoryTemplateId],
[Project4].[MetaKeywords] AS [MetaKeywords],
[Project4].[MetaDescription] AS [MetaDescription],
[Project4].[MetaTitle] AS [MetaTitle],
[Project4].[ParentCategoryId] AS [ParentCategoryId],
[Project4].[PictureId] AS [PictureId],
[Project4].[PageSize] AS [PageSize],
[Project4].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
[Project4].[PageSizeOptions] AS [PageSizeOptions],
[Project4].[PriceRanges] AS [PriceRanges],
[Project4].[ShowOnHomePage] AS [ShowOnHomePage],
[Project4].[IncludeInTopMenu] AS [IncludeInTopMenu],
[Project4].[HasDiscountsApplied] AS [HasDiscountsApplied],
[Project4].[SubjectToAcl] AS [SubjectToAcl],
[Project4].[LimitedToStores] AS [LimitedToStores],
[Project4].[Published] AS [Published],
[Project4].[Deleted] AS [Deleted],
[Project4].[DisplayOrder] AS [DisplayOrder],
[Project4].[CreatedOnUtc] AS [CreatedOnUtc],
[Project4].[UpdatedOnUtc] AS [UpdatedOnUtc]
FROM (SELECT [Limit1].[Id] AS [Id],
[Limit1].[Name] AS [Name],
[Limit1].[Description] AS [Description],
[Limit1].[CategoryTemplateId] AS [CategoryTemplateId],
[Limit1].[MetaKeywords] AS [MetaKeywords],
[Limit1].[MetaDescription] AS [MetaDescription],
[Limit1].[MetaTitle] AS [MetaTitle],
[Limit1].[ParentCategoryId] AS [ParentCategoryId],
[Limit1].[PictureId] AS [PictureId],
[Limit1].[PageSize] AS [PageSize],
[Limit1].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
[Limit1].[PageSizeOptions] AS [PageSizeOptions],
[Limit1].[PriceRanges] AS [PriceRanges],
[Limit1].[ShowOnHomePage] AS [ShowOnHomePage],
[Limit1].[IncludeInTopMenu] AS [IncludeInTopMenu],
[Limit1].[HasDiscountsApplied] AS [HasDiscountsApplied],
[Limit1].[SubjectToAcl] AS [SubjectToAcl],
[Limit1].[LimitedToStores] AS [LimitedToStores],
[Limit1].[Published] AS [Published],
[Limit1].[Deleted] AS [Deleted],
[Limit1].[DisplayOrder] AS [DisplayOrder],
[Limit1].[CreatedOnUtc] AS [CreatedOnUtc],
[Limit1].[UpdatedOnUtc] AS [UpdatedOnUtc]
FROM (SELECT [Distinct1].[Id] AS [Id]
FROM (SELECT DISTINCT [Extent1].[Id] AS [Id]
FROM [dbo].[Category] AS [Extent1]
LEFT OUTER JOIN [dbo].[AclRecord] AS [Extent2]
ON ([Extent1].[Id] = [Extent2].[EntityId])
AND (N'Category' = [Extent2].[EntityName])
LEFT OUTER JOIN [dbo].[StoreMapping] AS [Extent3]
ON ([Extent1].[Id] = [Extent3].[EntityId])
AND (N'Category' = [Extent3].[EntityName])
WHERE ([Extent1].[Published] = 1)
AND ([Extent1].[Deleted] <> cast(1 as bit))
AND (([Extent1].[SubjectToAcl] <> cast(1 as bit))
OR (([Extent2].[CustomerRoleId] IN (4))
AND ([Extent2].[CustomerRoleId] IS NOT NULL)))
AND (([Extent1].[LimitedToStores] <> cast(1 as bit))
OR (1 /* @p__linq__0 */ = [Extent3].[StoreId]))) AS [Distinct1]) AS [Project2]
OUTER APPLY (SELECT TOP (1) [Filter2].[Id1] AS [Id],
[Filter2].[Name] AS [Name],
[Filter2].[Description] AS [Description],
[Filter2].[CategoryTemplateId] AS [CategoryTemplateId],
[Filter2].[MetaKeywords] AS [MetaKeywords],
[Filter2].[MetaDescription] AS [MetaDescription],
[Filter2].[MetaTitle] AS [MetaTitle],
[Filter2].[ParentCategoryId] AS [ParentCategoryId],
[Filter2].[PictureId] AS [PictureId],
[Filter2].[PageSize] AS [PageSize],
[Filter2].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
[Filter2].[PageSizeOptions] AS [PageSizeOptions],
[Filter2].[PriceRanges] AS [PriceRanges],
[Filter2].[ShowOnHomePage] AS [ShowOnHomePage],
[Filter2].[IncludeInTopMenu] AS [IncludeInTopMenu],
[Filter2].[HasDiscountsApplied] AS [HasDiscountsApplied],
[Filter2].[SubjectToAcl] AS [SubjectToAcl],
[Filter2].[LimitedToStores] AS [LimitedToStores],
[Filter2].[Published] AS [Published],
[Filter2].[Deleted] AS [Deleted],
[Filter2].[DisplayOrder] AS [DisplayOrder],
[Filter2].[CreatedOnUtc] AS [CreatedOnUtc],
[Filter2].[UpdatedOnUtc] AS [UpdatedOnUtc]
FROM (SELECT [Extent4].[Id] AS [Id1],
[Extent4].[Name] AS [Name],
[Extent4].[Description] AS [Description],
[Extent4].[CategoryTemplateId] AS [CategoryTemplateId],
[Extent4].[MetaKeywords] AS [MetaKeywords],
[Extent4].[MetaDescription] AS [MetaDescription],
[Extent4].[MetaTitle] AS [MetaTitle],
[Extent4].[ParentCategoryId] AS [ParentCategoryId],
[Extent4].[PictureId] AS [PictureId],
[Extent4].[PageSize] AS [PageSize],
[Extent4].[AllowCustomersToSelectPageSize] AS [AllowCustomersToSelectPageSize],
[Extent4].[PageSizeOptions] AS [PageSizeOptions],
[Extent4].[PriceRanges] AS [PriceRanges],
[Extent4].[ShowOnHomePage] AS [ShowOnHomePage],
[Extent4].[IncludeInTopMenu] AS [IncludeInTopMenu],
[Extent4].[HasDiscountsApplied] AS [HasDiscountsApplied],
[Extent4].[SubjectToAcl] AS [SubjectToAcl],
[Extent4].[LimitedToStores] AS [LimitedToStores],
[Extent4].[Published] AS [Published],
[Extent4].[Deleted] AS [Deleted],
[Extent4].[DisplayOrder] AS [DisplayOrder],
[Extent4].[CreatedOnUtc] AS [CreatedOnUtc],
[Extent4].[UpdatedOnUtc] AS [UpdatedOnUtc]
FROM [dbo].[Category] AS [Extent4]
LEFT OUTER JOIN [dbo].[AclRecord] AS [Extent5]
ON ([Extent4].[Id] = [Extent5].[EntityId])
AND (N'Category' = [Extent5].[EntityName])
WHERE ([Extent4].[Published] = 1)
AND ([Extent4].[Deleted] <> cast(1 as bit))
AND (([Extent4].[SubjectToAcl] <> cast(1 as bit))
OR (([Extent5].[CustomerRoleId] IN (4))
AND ([Extent5].[CustomerRoleId] IS NOT NULL)))) AS [Filter2]
LEFT OUTER JOIN [dbo].[StoreMapping] AS [Extent6]
ON ([Filter2].[Id1] = [Extent6].[EntityId])
AND (N'Category' = [Extent6].[EntityName])
WHERE (([Filter2].[LimitedToStores] <> cast(1 as bit))
OR (1 /* @p__linq__0 */ = [Extent6].[StoreId]))
AND ([Project2].[Id] = [Filter2].[Id1])) AS [Limit1]) AS [Project4]
ORDER BY [Project4].[ParentCategoryId] ASC,
[Project4].[DisplayOrder] ASC
For reference, here is the query plan for this:
Note that all of this is generated for the products is not done via EntityFramework. That is done via the ProductLoadAllPaged stored procedure. That is 620 lines of code, includes dynamic SQL generation and several temp tables.
At that point, I actually looked at the code, and it isn’t something that I can actually make easy modifications to, at least not without spending way too long trying to understand what is going on for it to be worth it for a single presentation. So I’m going to leave this aside and look at another app. Probably a sample site, nopCommerce has 44 projects and Orchard has 77 projects. That is too big to actually be able to talk about concisely in a talk.
Comments
Hi Ayende, take a look at MVC Forum (http://www.mvcforum.com/) maybe it's a good candidate.
Luiz, I already use the Contoso University sample app.
I was very surprised to hear you say that SQL wasn't generated by EF with all the Project4, Limit1, etc... in there. That piqued my curiosity, so I took a look at the history, and it looks like the ProductService was optimized between 10/1/2011 and 10/3/2011 to be able to use procs if supported & configured in. My guess is that EF originally generated the SQL, and it was copied & pasted into the ProductLoadAllPaged proc. I never used EF back then, but my coworker told me it could be slow. Presuming it was slow at generating SQL, this seems like it could be reasonable. From a maintenance perspective, it seems like a PITA though. I would not want to add a new parameter to that proc and figure out what else I needed to change. Added to that is that there are now two supported paths, one with proc and one without.
This proc is also a good argument for not using your domain model for reads. The SQL would probably be greatly simplified by putting some views on top of the tables that look more like the result structures. That may not be an option for them depending on what the supported databases are. I'm only familiar with SQL server, but I assume it is possible that some databases don't support views.
Chris, The SP for searching products is doing dynamic SQL generation and using multiple temp tables and many joins. That isn't going to be very high performance on large dbs.
It's why I'm thinking that every hand-rolled e-Commerce could benefit from something like Solr or ElasticSearch.
With the current requirements of a storefront... SQL is just not the right tool nowadays.
Maxime, Check out what RavenDB can do for you in that arena.
Hey Ayende,
Yeah I know that RavenDB has implemented Lucene and does basically the same work. :)
All of them can be used to display a Presentation Model and do search.
Have you tried ElasticSearch for the fun of it?
If you use a relational database and you want great performace you need to go far away from EF and NHibernate. I wrote a micro ORM called EntityLite that is orders of magnitude faster than EF. EntityLite gives you great oportunities to optimize relational database access.
Mr. Lopez, I think blaming a framework for bad performance is probably not the answer. I'm sure your ORM is great, and I'm sure that people could likely make it behave poorly too. Also EF & NHIbernate have likely solved many more edge cases than your lite framework. The problem win ORMs in general in my opinion, is it allows devs to have false confidence when working with relational databases. Even if you are using an ORM you have to care what SQL is being generated. Hence the popularity of Ayende's profilers. Bad performance usually means bad code, sometimes it can be in a framework of course, but more often than not its how a developer uses or abuses a framework that causes perf issues.
Does someone pay these guys for doing such stupid things as dynamic SQL in an SP? Learn sql and end that insanity! Relational databases used to be quite efficient in 1990s and modern servers are million times faster, and yet some people don't give up until they neutralize all that performance gain with their bloatware.
Mr. Wright.
I'm sure EF and NHibernate do a great work abstrating the database and solving the impedance mismatch problem. But by solving that problem they introduce a new problem: poor performance. And this problem arise even in simple and mainstream cases. You don't need to go to edge cases. But in edge and complex cases the performance problem is even worse.
hi ayende, it would be better if you take a look at dashCommerce project.
@Dolly:
Either you construct your (paged) search query dynamically via your ORM of choice or you go the dynamic SQL inside SP route using the key seek method by Paul White. Static SQL with OR predicates and unnesccesary JOINs spells doom for performance, as decribed by Erland Sommarskog.
This will ensure you get the optimal plan for each permutation of your included search predicates and tables (make sure you test all paths and index accordingly).
The plan cache and 'optimize for ad-hoc workloads' setting should take care of the rest.
@ENOTTY The plan depicted here doesn't look very optimal - maybe you're talking about some other query.
Comment preview