NopMSSQLProvider V2 (Performance)

Plugin to speed up website performance. Performance Optimization.
$50.00

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.

Note.
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.

Note2.
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.
NopCommerce First launch 00:00:01.8339843
NopCommerce Second launch 00:00:00.9091797
NopMSSQLProvider First launch 00:00:00.9169922
NopMSSQLProvider Second launch 00:00:00.3916016
 
Settings: Acl StoreLimitations IgnoreDiscounts, disabled Cache product prices
NopCommerce First launch 00:00:01.7158203
NopCommerce Second launch 00:00:00.5781250
NopMSSQLProvider First launch 00:00:00.9218750
NopMSSQLProvider Second launch 00:00:00.3427734
 
Settings: IgnoreAcl IgnoreStoreLimitations IgnoreDiscounts, disabled Cache product prices
NopCommerce First launch 00:00:01.5937500
NopCommerce Second launch 00:00:00.5888672
NopMSSQLProvider First launch 00:00:00.8867188
NopMSSQLProvider Second launch 00:00:00.3935547
 
Settings: IgnoreAcl IgnoreStoreLimitations IgnoreDiscounts, enabled Cache product prices
NopCommerce First launch 00:00:01.5976563
NopCommerce Second launch 00:00:00.4365234
NopMSSQLProvider First launch 00:00:00.9501953
NopMSSQLProvider Second launch 00: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.

 

How to Install and Configure plugin.

You can see how this plugin works at Youtube

http://youtu.be/qPZkgG7EUe0

 

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.

Note.
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.

Note2.
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.
NopCommerce First launch 00:00:01.8339843
NopCommerce Second launch 00:00:00.9091797
NopMSSQLProvider First launch 00:00:00.9169922
NopMSSQLProvider Second launch 00:00:00.3916016
 
Settings: Acl StoreLimitations IgnoreDiscounts, disabled Cache product prices
NopCommerce First launch 00:00:01.7158203
NopCommerce Second launch 00:00:00.5781250
NopMSSQLProvider First launch 00:00:00.9218750
NopMSSQLProvider Second launch 00:00:00.3427734
 
Settings: IgnoreAcl IgnoreStoreLimitations IgnoreDiscounts, disabled Cache product prices
NopCommerce First launch 00:00:01.5937500
NopCommerce Second launch 00:00:00.5888672
NopMSSQLProvider First launch 00:00:00.8867188
NopMSSQLProvider Second launch 00:00:00.3935547
 
Settings: IgnoreAcl IgnoreStoreLimitations IgnoreDiscounts, enabled Cache product prices
NopCommerce First launch 00:00:01.5976563
NopCommerce Second launch 00:00:00.4365234
NopMSSQLProvider First launch 00:00:00.9501953
NopMSSQLProvider Second launch 00: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.

 

How to Install and Configure plugin.

You can see how this plugin works at Youtube

http://youtu.be/qPZkgG7EUe0

 

 Version 4.30 - November 28, 2016

+ added new feature 'Load all categories on startup' link.
+ added new feature 'Load all manufacturers on startup' link.

 Version 4.28 - Septemper 29, 2016

+ added support SmartSEO plugin.
+ the store's owner can disable optimization for PriceCalculationService.

 Version 4.26 - January 30, 2016

- fixed bug with case-sensitive databases (OrderService).

 Version 4.24 - July 24, 2015

- fixed bug with compare products button (link) (NopCommerce 3.60).

 Version 4.22 - May 23, 2015

+ added new parameter Default Product Sorting (NopCommerce 3.40/3.50 and newer).

 Version 4.20 - February 10, 2015

- fixed bug with TOPMenu categories (NopCommerce 3.50).

Version 4.16 - January 15, 2015

+ replace action TopMenu from CatalogController.

Version 4.15 - Nonember 10, 2014

+ added support Special Price Offers plugin.

Version 4.13 - May 30, 2014

+ added parameter Hide Filters Block

Version 4.12 - May 29, 2014

+ added support Azure

Version 4.09 - May 19, 2014

+ added fix for "bad" url-records.

Version 4.08 - May 7, 2014

+ added caching to Widget service

Version 4.07 - April 28, 2014

- fixed bug with redirection for MSSQLCategoryNavigation action

Release: Version 4.06 on April 25th, 2014