Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create bulk database entries efficiently?

Tags:

People also ask

How can I speed up bulk insert?

Below are some good ways to improve BULK INSERT operations : Using TABLOCK as query hint. Dropping Indexes during Bulk Load operation and then once it is completed then recreating them. Changing the Recovery model of database to be BULK_LOGGED during the load operation.

Is bulk insert faster than insert?

Bulk insert is generally much faster.

How does bulk insert work?

BULK INSERT runs in-process with the database engine of SQL Server and thus avoids passing data through the network layer of the Client API - this makes it faster than BCP and DTS / SSIS.


I'm trying to create an Activty object for a large (300+ at a time) list of Inquiry objects. I have a single ModelForm which is being posted back, and I need to create seperate instances, and attach them to my Inquiry via a GenericForeignKey. Let's get to some code:

models.py:

class InquiryEntry(models.Model):
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField() 
    entry = generic.GenericForeignKey('content_type', 'object_id')

class Inquiry(models.Model):
    entries = models.ManyToManyField('InquiryEntry')
    # And many more fields.
    def add_entry(self, obj):
        entry = self.entries.create(entry=obj)
        self.save()
        return entry

class Activity(models.Model):  
    ts = models.DateTimeField(auto_now_add=True)                  
    due_date = models.DateField(auto_now=False)
    ## And many more fields.

views.py:

def bulk_create_activities(request):
    activity_form = ActivityForm()
    if request.method == "POST":
        activity_form = ActivityForm(request.POST)
        if activity_form.is_valid():    
            pks = [int(x) for x in request.POST.get('pks', '').split(',')]
            for inquiry in Inquiry.objects.filter(pk__in=pks):
                instance = ActivityForm(request.POST).save()
                inquiry.add_entry(instance)     
                inquiry.save()  

What I am looking for is a way to insert these into the database, preferably in one pass so that the request can be processed faster. I prefer not to drop to the database level as this application is deployed across multiple database vendors, but if that is the only way to proceed, so be it (examples for MySQL and Postgres would be awesome).


Note: I know that there is a bulk_create in the development version, but that is out of the question until there is a stable release.