NopMSSQLProvider V2 (Performance)

Plugin to speed up website performance. Performance Optimization.

Using this plugin you will increase website page loading speed and increase your profits. Your website will be more user friendly. You can enable ACL rules, “limit per store”, you can show the number of distinct products besides each category.  After it you will not have any performance degradations.

How does it work?
We made 3 improvements:

Improvement 1.
We moved logic for getting data into MS SQL Stored Procedures for heavy queries.
By default NopCommerce uses Entity Framework for getting data from databases. It is very good framework. Using  Entity Framework developers can write one code for all databases (ex. MS SQL Server, Oracle, MySQL, etc.). But we pay performance for our comfort and versatility. NopCommerce generates C# commands (Linq) for getting products, categories, etc.  Entity Framework converts these commands to SQL commands (TSQL). Entity Framework generates good SQL code for simple commands. But for non-simple commands it generates non-optimized code. Ex. For getting records from 2, 3 or more tables with many conditions. Entity Framework sends these “heavy” SQL commands to MS SQL Server.  MS SQL Server analyzes them and makes SQL query plan for getting records from tables. If SQL command is very difficult MS SQL Server can't use optimization for getting records from tables. At last we have 2 problem places “generation SQL command into Entity Framework” and  “getting records from table in MS SQL Server”. If your databases isn't large you will not have these problems. You will see them only for big tables and difficult queries. We removed standard mechanism for retrieving data. We wrote SQL commands inside MS SQL Stored Procedures manually. MS SQL Server saves SQL optimization plan for retrieving data for each Stored Procedure too.

Improvement 2.
We try to skip load excess data.
Plugin tries to filter all data on server side.
Example. You want to load order with some GUID. EF doesn't support casting of GUID to string. NopCommerce must read all orders. If your store has many orders, you will wait some time.

Improvement 3.
Category, manufacturer and vendor pages were rewritten.
If customer opens category page NopCommerce reads categories for breadcrumb, featured products, products from these categories, etc. For each group of object NopCommerce generates new request to database. Our plugin makes all manipulations with all objects inside Stored Procedures into MS SQL Server and gets all data in one request. It's important improvement.

Plugin will increase website page loading speed, but you must use only MSSQL Server. You can't go to another SQL databases. It's your payment for performance. You can see that each new version of NopCommerce increases its performance, but we try to make our plugin better too.

All customer who bought this plugin have been satisfied. If you have new ideas how to improve this plugin we can do them. You can download and try this plugin without any restrictions. Unregistered version is fully operational. It has only one limitation – trial period 30 days. After the expiration, plugin will be disabled.

How many times the speed increases?
Increase depends on data size.

Benchmarks for NopCommerce version 3.50.
240 products in the same category. Each product has 20 subproducts (ProductVariant). 60 products per page. Store has 2 languages. User opens the category (CatalogController: Category).

Settings: Acl, StoreLimitations, Discounts, disabled Cache product prices.
NopCommerceFirst launch00:00:01.8339843
NopCommerceSecond launch00:00:00.9091797
NopMSSQLProviderFirst launch00:00:00.9169922
NopMSSQLProviderSecond launch00:00:00.3916016
Settings: Acl StoreLimitations IgnoreDiscounts, disabled Cache product prices
NopCommerceFirst launch00:00:01.7158203
NopCommerceSecond launch00:00:00.5781250
NopMSSQLProviderFirst launch00:00:00.9218750
NopMSSQLProviderSecond launch00:00:00.3427734
Settings: IgnoreAcl IgnoreStoreLimitations IgnoreDiscounts, disabled Cache product prices
NopCommerceFirst launch00:00:01.5937500
NopCommerceSecond launch00:00:00.5888672
NopMSSQLProviderFirst launch00:00:00.8867188
NopMSSQLProviderSecond launch00:00:00.3935547
Settings: IgnoreAcl IgnoreStoreLimitations IgnoreDiscounts, enabled Cache product prices
NopCommerceFirst launch00:00:01.5976563
NopCommerceSecond launch00:00:00.4365234
NopMSSQLProviderFirst launch00:00:00.9501953
NopMSSQLProviderSecond launch00:00:00.3740235


Plugin works ONLY on MS SQL Server 2005/2008/2008R2/2012/2014 + Azure.

Compatible for NopCommerce 3.20, 3.30, 3.40, 3.50, 3.60, 3.70, 3.80, 3.90.

How to Install and Configure plugin.