Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1375
  • Last Modified:

LINQ-to-SQL Bulk insert optimisation

Hi,

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
CustomerId
FName
LName

Order
OrderId
OrderDateTime

Item
ItemId
ItemDescription

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:
Pseudo code
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)
        {
               ExportItem(i);       
        }
    }
}

Open in new window


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:
1500 customers
5 orders per customer
10 items per order

this would still add up to 75000 SQL calls.

Thank you

Edit
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.
0
capcap
Asked:
capcap
  • 6
  • 4
  • 2
1 Solution
 
Bhavesh ShahLead AnalysistCommented:
Hi,

As you working with large data,I would like to suugest you to insert data on procedure level.

It will be very easy to handle it in backend.
0
 
Bhavesh ShahLead AnalysistCommented:
Sory,

Forget to ask u, why you cant use procedures......?
0
 
capcapAuthor Commented:
At the present there are time constraints that don't allow us to re-write the whole of export.

Stored procedures would work in the similar way, I wouldn't be calling GetTable<DataType> each time and I will have an advantage of SQL execution plan caching, but apart from that I don't see much benefit. Am I missing something out?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Bhavesh ShahLead AnalysistCommented:

Please correct me if I'm wrong.
I'm assuming you are trying to Insert Data????

In that case,i'm not finding you should find any big problem.

Sory,if m wrong......
0
 
capcapAuthor Commented:
Mainly it's inserts. 30% of the time it's a conditional insert. E.g. Insert only if the object's ID is 0.

There are few updates but I think I can work around that.
0
 
Bhavesh ShahLead AnalysistCommented:
gud going sir.i suggest dat.all d best.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi....

Does your issue is over??
did you get the answer?
0
 
capcapAuthor Commented:
Hi,

None of the answers above address the issue of running bulk exports through LINQ-to-SQL

Thank you
0
 
Bhavesh ShahLead AnalysistCommented:
Ok.


If u need more assistance from high level experts, please click on Request Attention Link.
0
 
Vadim RappCommented:
I think the practical alternative is to export first into a file, can be xml, and then import the file into the database.
0
 
capcapAuthor Commented:
Hi Vadim,

This means that data will be flattened into XML and than I'll have to read that XML and build bulk SQL inserts. Isn't this twice as much work?
0
 
Vadim RappCommented:
(sorry for the delay, for some reason apparently not all notifications work)

I think you will still get big advantage in performance because sql utility will import the file in one bulk operation, contrary to insane opening separate connection per each line. It's probably impossible to positively tell how it's going to work without trying, but that would be the first thing I'd try - primarily because it's known fact that sql bulk import utility has high performance.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now