Hi guys,
For a customer we once build a webservice which imported all products to the DW CMS. This went all very well and there where no real issues importing the products. The database grew and grew and one day we started encountering timeout exceptions when importing the products. First it was a onetimer a week, nowadays it's everyday so I started investigating what caused the problems.
In our application we load all products in cache using this statement: Product.GetProductBySql("SELECT * FROM [dbo].[EcomProducts] WHERE ProductActive = 1", false); Somewhere in code DW calls the Load method on ProductCollection, which calls LoadPricesOnProducts on the Price class. At this point we have a big set of Product items (22000+) which DW uses to calculate the Prices for products. DW creates the following SQL query based on the products: SELECT EcomPrices.* FROM EcomPrices WHERE (PriceUserCustomerNumber='' OR PriceUserCustomerNumber IS NULL) AND PriceProductID IN( [ALL 22000+ PRODUCT ID's as strings] ) ORDER BY EcomPrices.PriceProductID.
When running this query directly in SQL Management studio it takes around three minutes to finish. De DataReader object which is created by the LoadPricesOnProduct method uses a IDbCommand object with a default command timeout of 30 seconds, which is less than the 180 secounds I need. I can't change this timeout property myself because it's all DW code. In the end the application throws a Command Timeout error and fails to import the products.
I understand that my initial query for products could be better, but if I have bigger sets of products this would always be a problem, right? Or is there a better way of retrieving Products and ProductCollections? This is the code from our application which is executed before the error occurs:
public override void BeforeExecute() { _groupsCache = Group.GetAllGroups(); _productCache = Product.GetProductBySql("SELECT * FROM [dbo].[EcomProducts]", false); _productsToSave = new ProductCollection(); }
Cheers,
Tom
