July 4, 2008 at 3:24 am
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
July 7, 2008 at 6:50 am
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