MS SQL Provider performance optimization (foxnetsoft.com)

Description

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 many improvements. Plugin for each nopcommerce release has own improvements.
There are a most important:

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

Note 2.
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 and your nopcommerce versions. Try plugin before purchase free.
Read customer's reviews about plugin and install trial version and try.
It will be the best benchmark (your nop store and your data).

I have additive service Custom Performance Optimisation

Plugin works ONLY with MS SQL Server and Azure.