Ordered recursive CTE performance

  • My shopping aplications allows for definition of sales over products. For examle, if you buy products A and B, you only pay for one of them.

    I am developing a shopping basket procedure that accepts a list of products and attempts to find the best match. I am using recursive CTE to produce all permutations. This CTE is quick and works well.

    The problem is it generates many many rows to select from. For examle, having only 8 items in the basket, I get over 64,000 rows. Selecting top 1 from the CTE order by Price ASC takes a huge amount of time (6 seconds).

    If I add the 9th product, it jumps to 30 seconds since there are now 200,000 available combinations.

    Any help reducing the query execution time will be appreciated.

    Thanks

    Tal

  • You'd have to show sample code and structure (and preferably some sample data) in order to get really detailed help.

    Read this:

    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply