Pricing File Creator

Question

Problem We have a number of different customers getting a number of different price files, each customer can then also have different styles of pricing files for example customers can have just two columns, product and price. Some customers can have up to 20-30 fields each of these fields can exist in different tables in the database. Also the number of products within the file can vary also some with only a couple of hundred some with over 10k. These files have to then be run every hour to keep stock and pricing current. On average theres about 50 different file formats for 180 pricefiles so in total its 9000 odd files to be written.

Current solution My current solution for this is two tables. One table with all the products within each of the price files along with there prices in various currency's. And another table/view with all the possible product attributes. Primary key is the product code, then other fields for example short description, long description, alternative product codes etc etc.

The program then loads both these tables in to memory:

ConcurrentDictionary<PRODUCT_CODE, ConcurrentDictionary<COLUMN_NAME, VALUE>>

I then have the pricefile definition table. The table then details the price file format for eample, price file x has column 'Product_Code', 'Price_GBP', 'Description' the program then iterates (parallel) through the products within the current pricefile and grabs the columns from the concurrent dictionary's in memory. As well as columns in the dictionary there are all 'Programmable' columns for example 'RRP - price_gbp *1.4' These just go off to a switch case to see what it needs to do with these columns.

Issue My issue is that the number of product attributes is becoming very large and the number of price files and price file formats and customers wanting these files has almost 10 folded in the last couple of years since i write this system. So as you can imagine its run time is no where as quick as it was.

I cant think of anyway of making this quicker, neater and 'future proof'.

Answer 1

Loading all these tables into memory seems very expensive, at least if the in-memory data structure is not optimized.

You could instead build a query object for each file to generate, based on the price_file_definition_table. The query object would SELECT the relevant columns in the relevant tables and join it with your product_for_customer table restricted to the given customer. This would let your RDBMS organize and optimize data access. You would then just iterate over the result set to produce each file.

This will produce far better performance than interpret yourself the formulas and perform the required transformation, or the search of the right coloumn for each line.

Other possibilities, such as the use of a price file strategy could be used as well, but it is not clear to me if the price file format is related to a set of products or to a customer, nor if several customers share the same format, nor if the formats evolve frequently.

Answer 2

Create a view for each format
I know it seems like a bit of work but it would execute quickly

On your Dictionary not clear that you need concurrent. I assume you load it first. You don't need concurrent for parallel reads. Concurrent is slower.