PiMané (9/19/2012)
If the product's storage table has over 1.000.000 rows, cause there are over 100 storage and 10.000 products (it's a kind of Walmart company, with many products and stores) I believe it's better do "denormalize" the table and put a total column on the product's table. There are few insert and updates but lot's of list so it's best this way...
Just to give an example from the data numbers above a SELECT Product, Description, TotalStock FROM Products WHERE Product = 10 is twice as fast as the SELECT from the view and has less reads since it only uses one table...
The difference is even greater when applying paging (BETWEEN 10 AND 30) where the table select is 5 times faster and even less reads..
Thanks,
Pedro