In my opinion the expression: SUBSTRING(p.permlist,t.n+1, CHARINDEX(',',p.permlist,t.n+1)-t.n-1) permelement caused the problem, and the the reason behind this is that the optimizer decided to evaluate the join condition: p.permelement=d.elemid

I think that the test data provided so far make a good base of a good test data. But I have to say that the data do not reflect a real life data. I do not usally see a purchase of 100000 item and a quantity of 2000 per item. In my opinion the scripts shoul