Product search performance optimisation

I downloaded nopcommerce 2.2 a couple days ago and found that product search has been moved to the stored procedure against v2.0

I've analyzed the code of sp ProductLoadAllPaged and optimized it.

My general suggestions about TSQL are:

1. Dirty read. It's enough to add "... READ UNCOMMITTED" at the beginning of sp and you shouldn't add NOLOCK hint to your selects. It will set dirty read for all the queries executed in the sp. BTW I guess that NOLOCK for temporary tables is extra.

2. OR clause kills performance. If a query has a lot of OR clauses, SQL optimizer may not consider table indexes for execution plans.
In our case ORs are used for flexibility of applying of filtering params.
Another way to reach it is dynamic sql.
It allows you include only necessary tables in the query and apply only significant params. And obviously to avoid extra ORs :)

3. Dividing complex queries into more simple queries. In our case I've take out keyword search in the separate query.


Here is my optimized version of ProductLoadAllPaged:
http://dl.dropbox.com/u/45465534/nopcommerce/ProductLoadAllPaged.sql

2 Developers:
Could you perform load testing of my version of ProductLoadAllPaged on "real" amount of products and localized data?

Thanks. I'll review it a bit later

When you say "analyzed", does that mean you actually compared Execution plans (for various input params)?  It would be difficult to say that your rewrite is more "optimized" without such a comparison.

I'm not totally against Dynamic SQL, but there are some caveats

1) Dynamic SQL is vulnerable to SQL injection
2) You should parameterize your queries. Without parameterization, your query will be compiled each time, except when a user performs a search with exactly the same search conditions

and I've some comments/thoughts:

A) I think your DISTINCT in your UNION queries is redundant

B) UNION for the same table will do two full scans compared to OR for many of your queries, because there is no index to use

For example

SELECT DISTINCT pv.ProductId
FROM ProductVariant pv
WHERE PATINDEX(@Keywords, pv.name) > 0
UNION
SELECT DISTINCT pv.ProductId
FROM ProductVariant pv
WHERE PATINDEX(@Keywords, pv.sku) > 0    

Versus

SELECT DISTINCT pv.ProductId
FROM ProductVariant pv
WHERE PATINDEX(@Keywords, pv.name) > 0
   OR PATINDEX(@Keywords, pv.sku) > 0  

C) I've not looked at the original query in tremendous detail, but I don't think the ORs are as bad as you might think, because there are no indexes to use (at least right now, and I don't know that it would make much sense to add them on  fields like Price, etc.)
What may be a more optimal rewrite is not not join the other tables (category, manufacturer, tags - mappings), but rather use NOT EXISTS similar to what is used for

        --filter by specs
        @SpecAttributesCount = 0
        OR
        (
          NOT EXISTS(

D) Another thought is that if you're using Temp tables is to maybe use TABLE variables instead (they are in-memory)

E) The sp's query does not have to be so "generic" all the time.  An understanding and re-writing with respect to how it's typically used by the calling application can lead to significant improvement - for example, the HOME page (the first one seen by all your customers :), looks for "featured products".  If that's the ONLY param being passed in, then it may make sense to put an index on that field, and an IF statement would just execute a query with only that WHERE clause.

E) Finally, Nolock (READ UNCOMMITTED) should not really be considered a "performance tool". It controls the accuracy of the data read; if you don't mind queries occationally returning incorrect data, then sure, use it :)   There are better ways to deal with performance (rewrite query, indexes, etc.), and are are typically applied AFTER you determine there is a performance problem.

Alexandr,

I've just integrated new stored procedure into official releases. Great contribution. Thanks a lot!
https://www.nopcommerce.com/boards/t/15021/24-performance.aspx?p=3#62494