I'm working with a relatively large data export where I read data from the COM objects, store them in classes generated by LINQ and then run inserts.
For example, I have the following data structure.
Customer can have 1-to-many orders.
Order must have at least 1 item.
I have a list of 100 customers, where each customer has 500 orders and each order contains 10 items.
The current implementation is as follows:
foreach(Customer c in COMObject.Customers)
ExportCustomer(c); // This will generate customer Id that can be used in the next foreach loop
foreach(Order o in c.Orders)
ExportOrder(o); // This will generate order id that can be usedi n the next foreach loop
foreach(Item i in o.Items)
Each Export method opens a SQL connectino through LINQ DataContext and submits changes to the underlying database.
First of all the problem is that this export runs in O^3 which is already a problem - but I don't think there is a way to overcome this since I need to have a primary key within each loop.
This adds up to half a million SQL calls which is nasty, especially when GetTable<DataType> does a lot through generics and reflection.
What I'm considering is storing the entire export in memory and then running a bulk insert where GetTable<DataType> will be called few times at the most.
My question is where there are any alternatives to this? I can't avoid using LINQ and I can't use stored procedures either.
Also I'd like to know what is a reasonable amount of data that I can store in memory and how much data is stored behind the scenes. For example, the customer table contains only 3 members, but how much is stored behind the scenes to map that to the SQL table?
Example that I have provided is very basic but hopefully it explains my concerns. If I were to associate my example with the system that we are working on, than in the worse case scenariod we'd have:
5 orders per customer
10 items per order
this would still add up to 75000 SQL calls.
As I was reading the question again, I realised that the data in the COM object is already in memory, so my guess is that it's not that much data that I'm storing.
The second idea that I had is to leave the data context opened to avoid calling GetTable<DataType> on each loop iteration.