I'm building a Django (1.8.12) application that parses a .bc3 file (Standard Interchange Format for Databases of Construction and Real State) and loads all the data into the database (PostgreSQL 9.3.9).A .bc3 file looks like this, and a common one has more than 2000 concepts (those records that start with ~C).To sum up, the user uploads the file and the webapp in a short period of time is able to insert the data into the database to start working on it.Modelsclass Concept(models.Model):    code = models.CharField(_('code'), max_length=20, primary_key=True)    root = models.BooleanField(_('is it root'), default=False)    chapter = models.BooleanField(_('is it chapter'), default=False)    parent = models.BooleanField(_('is it parent'), default=False)    unit = models.CharField(_('unit'), blank=True, max_length=3)    summary = models.CharField(_('summary'), blank=True, max_length=100)    price = models.DecimalField(_('price'), max_digits=12, decimal_places=3,                                null=True, blank=True)    date = models.DateField(_('creation date'), null=True, blank=True)    concept_type = models.CharField(_('concept type'), max_length=3, blank=True)    def __str__(self):        return '%s: %s' % (self.code, self.summary)class Deco(models.Model):    parent_concept = models.ForeignKey(Concept, null=True, blank=True,                                       related_name='decos')    concept = models.ForeignKey(Concept, null=True, blank=True)    factor = models.DecimalField(max_digits=12, decimal_places=3,                                 default=Decimal('0.000'))    efficiency = models.DecimalField(max_digits=12, decimal_places=3,                                     default=Decimal('0.000'))    def __str__(self):        return '%s: %s' % (self.parent_concept, self.concept)bc3parser.py#!/usr/bin/env python# -*- coding: utf-8 -*-Parses bc3 files and insert all the data into the database.import refrom enkendas.models import Version, Concept, Deco, Textfrom .utils import optional_codes, parse_dates# regex stuff# parsers stuffconcepts = {}decos = {}# decos = {'PER02': [('Qexcav', '1', '231.13'), ('Qzanj', '1', '34.5'),#                    ('Qexcav2', '1', '19.07'), ('Qrelltras', '1', '19.07')],# ...#          'Qexcav': [('MMMT.3c', '1', '0.045'), ('O01OA070', '1', '0.054'),#                     ('M07CB030', '1', '0.036'), ('%0300', '1', '0.03')]}def dispatch_record(record):        Dispatch every record.    Check the first character of the record and send it to the proper function.        if record.startswith('D'):        parse_decomp(record)    elif record.startswith('V'):        parse_version(record)    elif record.startswith('C'):        parse_concept(record)    elif record.startswith('T'):        parse_text(record)    else:        passdef parse_file(file):        Parse the whole file.    file is a generator returned by file.chunks(chunk_size=80000) in views.py.        while True:        try:            record = ''            incomplete_record = ''            # Iterates over the file sent by the user.            byte_string = next(file)            byte_stripped_string = byte_string.strip()            string = byte_stripped_string.decode(encoding='ISO-8859-1')            # List of records.            durty_strings_list = string.split('~')            # Check if one chunk in chunks is complete.            if durty_strings_list[-1] != '' and incomplete_record != '':                incomplete_record = incomplete_record + durty_strings_list.pop(-1)                dispatch_record(incomplete_record)                incomplete_record = ''            elif durty_strings_list[-1] != '' and incomplete_record == '':                incomplete_record = durty_strings_list.pop(-1)            for durty_string in durty_strings_list:                stripped_string = durty_string.strip()                if durty_string == '':                    record = record + ''                # TODO: I didn't create a regex for 'M' and 'E' records yet.                elif durty_string[0] == 'M' or durty_string[0] == 'E':                    continue                if record != '':                    # Dispatch the previous record.                    dispatch_record(record)                    # Reset the used record.                    record = ''                    # Assign the current record.                    record = stripped_string                else:                    record = record + stripped_string        except StopIteration as e:            dispatch_record(record)            break    concept_instances = []    for key_code, data in concepts.items():        code = key_code        root = chapter = parent = False        if len(key_code) > 2 and key_code[-2:] == '##':            root = True            code = key_code[:-2]        elif len(key_code) > 1 and key_code[-1:] == '#':            chapter = True            code = key_code[:-1]        if code in decos:            parent = True        concept = Concept(code=code, root=root, chapter=chapter, parent=parent,                          unit=data['unit'], summary=data['summary'],                          price=data['price'], date=data['date'],                          concept_type=data['concept_type'])        concept_instances.append(concept)    Concept.objects.bulk_create(concept_instances)    deco_instances = []    cobjs_storage = {}    for concept in Concept.objects.all():        if concept.parent is False:            continue        dec = decos[concept.code]        for child, factor, efficiency in dec:            if child == '':                continue            if factor == '':                factor = '0.000'            if efficiency == '':                efficiency = '0.000'            # To avoid extra queries.            if child in cobjs_storage:                cobj = cobjs_storage[child]            else:                cobj = Concept.objects.get(code=child)                cobjs_storage.update({child: cobj})            deco = Deco(parent_concept=concept, concept=cobj,                        factor=float(factor), efficiency=float(efficiency))            deco_instances.append(deco)            decos.pop(concept.code, None)    Deco.objects.bulk_create(deco_instances)ProcessParsing the .bc3 file uploaded by the user.Everything is working as expected.Instantiating the Concept model.I save the instances in concept_instances = [c1, c2, c3... cn].Inserting Concept instances into the database.In order to speed up the load I use the bulk_create(concept_instances) method.Instantiating the Deco model.I save the instances in deco_instances = [d1, d2, d3... dn]. But, to do that I need to retrieve each Concept object from the database because of the parent_concept and concept fields.Inserting Deco instances into the database.As before, to speed up the load I use the bulk_create(deco_instances) method.BottleneckThe whole process on the .bc3 file mentioned earlier is taking too much (95230 ms) because I'm doing 1278 SQL queries, but inserting 1276 Concept objects just takes 693 ms and 2826 Deco objects 289 ms.ResearchI read some Stack Overflow questions and the Django official documentation about Database access optimization, but I didn't find any useful improvement for this case.My AssumptionI think this line of code is the main problem, but in my opinion it is absolutely necessary.QuestionsIs it possible to create Deco objects without getting every Concept object?Is running tasks in the background the only approach to follow?Am I missing something?
Loading data from a text file into the database as fast as possible
python;parsing;file;database;django
An important aspect when doing optimisations is profiling. You should really start with that instead of asking random strangers on the internet.Anyhow, let me take a quick look.Filteringfor concept in Concept.objects.all():    if concept.parent is False:        continue    ...This seems a bit redundant, why not justfor concept in Concept.objects.filter(parent=True):    ...Many queriesI took a close look at the line you indicated might be troublesome. You have not profiled (I assume), but it looks suspicious because it performs a query in a loop.So basically the code looks like this:for concept in Concept.objects.all():    ...    for child, factor, efficiency in dec:        ...        if child in cobjs_storage:            cobj = cobjs_storage[child]        else:            cobj = Concept.objects.get(code=child)            cobjs_storage.update({child: cobj})        ...So, ideally, you'd want to make sure that cobjs_storage contains as much as possible. One way to do that would be to add the following before the first for loop above:# Pre-fetch required objects.needs_prefetch = set(child for child, __, __ in decos.values())for cobj in Concept.objects.filter(code__in=needs_prefetch):    cobjs_storage[cobj.code] = codjIt's a bit hacky, perhaps, but it should lower the number of queries, and as such improve results.[edit: I just found a better way] Using in_bulk (https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.in_bulk) you can rewrite it a bit:# Pre-fetch required objects.needs_prefetch = set(child for child, __, __ in decos.values())cobjs_storage.update(Concept.objects.in_bulk(needs_prefetch))Also, make sure to add any created Concept object to the cobjs_storage after creating, so that you don't incur a database hit for that.Dispatchdef dispatch_record(record):        Dispatch every record.    Check the first character of the record and send it to the proper function.        if record.startswith('D'):        parse_decomp(record)    elif record.startswith('V'):        parse_version(record)    elif record.startswith('C'):        parse_concept(record)    elif record.startswith('T'):        parse_text(record)    else:        passThis is not as expensive as a database hit, but it's still someplace that might need some optimisation, or at least a bit of a refactoring to make it cleaner.def dispatch_record(record):    dispatch_table = {        'D': parse_decomp,        'V': parse_version,        'C': parse_concept,        'T': parse_text,    }    try:        parser = dispatch_table[record[0]]    except (IndexError, KeyError):        return    parser(record)This makes it easier to add extra parsers, and the .startswith() is no longer called multiple times.Parsing filesThe following piece of code is quite suspect.while True:    try:        record = ''        incomplete_record = ''        # Iterates over the file sent by the user.        byte_string = next(file)        byte_stripped_string = byte_string.strip()        string = byte_stripped_string.decode(encoding='ISO-8859-1')        # List of records.        durty_strings_list = string.split('~')        # Check if one chunk in chunks is complete.        if durty_strings_list[-1] != '' and incomplete_record != '':            incomplete_record = incomplete_record + durty_strings_list.pop(-1)            dispatch_record(incomplete_record)            incomplete_record = ''        elif durty_strings_list[-1] != '' and incomplete_record == '':            incomplete_record = durty_strings_list.pop(-1)        for durty_string in durty_strings_list:            stripped_string = durty_string.strip()            if durty_string == '':                record = record + ''            # TODO: I didn't create a regex for 'M' and 'E' records yet.            elif durty_string[0] == 'M' or durty_string[0] == 'E':                continue            if record != '':                # Dispatch the previous record.                dispatch_record(record)                # Reset the used record.                record = ''                # Assign the current record.                record = stripped_string            else:                record = record + stripped_string    except StopIteration as e:        dispatch_record(record)        breakFirst of all, it's quite long, but there is one thing I would very much like to comment on. If possible, do not use while loops when a for loop suffices. But there is actually a lot more going on. Let me walk you through a few refactorings I'd like to suggest.First, the code just before the except StopIteration:            if record != '':                # Dispatch the previous record.                dispatch_record(record)                # Reset the used record.                record = ''                # Assign the current record.                record = stripped_string            else:                record = record + stripped_stringIn the else, you know record == '', and '' + stripped_string is always the same as stripped_string.            if record != '':                # Dispatch the previous record.                dispatch_record(record)                # Reset the used record.                record = ''                # Assign the current record.                record = stripped_string            else:                record = stripped_stringIn both branches, the last line is the same, so we can move it out, and drop the else which is now empty.            if record != '':                # Dispatch the previous record.                dispatch_record(record)                # Reset the used record.                record = ''            # Assign the current record.            record = stripped_stringThis makes the record = '' in the if redundant.            if record != '':                # Dispatch the previous record.                dispatch_record(record)            # Assign the current record.            record = stripped_stringAlready so much cleaner.        for durty_string in durty_strings_list:            stripped_string = durty_string.strip()            if durty_string == '':                record = record + ''            # TODO: I didn't create a regex for 'M' and 'E' records yet.            elif durty_string[0] == 'M' or durty_string[0] == 'E':                continue            if record != '':                # Dispatch the previous record.                dispatch_record(record)            # Assign the current record.            record = stripped_stringThe record = record + '' is a bit useless. Because we already know it's a string, we can modify the elif a bit.        for durty_string in durty_strings_list:            stripped_string = durty_string.strip()            if durty_string and durty_string[0] == 'M' or durty_string[0] == 'E':                continue            if record != '':                # Dispatch the previous record.                dispatch_record(record)            # Assign the current record.            record = stripped_string(I broke PEP8 here, but I'm going to fix that now.)        for durty_string in durty_strings_list:            stripped_string = durty_string.strip()            if durty_string and durty_string[0] in ('M', 'E'):                continue            if record != '':                # Dispatch the previous record.                dispatch_record(record)            # Assign the current record.            record = stripped_stringMarginally better. I have a bit more overview now, and I'd really like to get rid of the try/except, so let me see what's necessary for that.while True:    try:        ...1        byte_string = next(file)        ...2    except StopIteration as e:        dispatch_record(record)        breakI did the hard work at looking over the rest of the code (the ...1 and ...2), and I feel confident that those parts won't throw a StopIteration. So let's factor those out.while True:    ...1    try:        byte_string = next(file)    except StopIteration as e:        dispatch_record(record)        break    ...2Now, to continue I need to elaborate on ...1 a bit, filling it in againwhile True:    record = ''    incomplete_record = ''    try:        # Iterates over the file sent by the user.        byte_string = next(file)    except StopIteration as e:        dispatch_record(record)        break    ...2We can move incomplete_record to after the try/except.while True:    record = ''    try:        # Iterates over the file sent by the user.        byte_string = next(file)    except StopIteration as e:        dispatch_record(record)        break    incomplete_record = ''    ...2I'd like to do the same for the record, but it's used in the except clause. But, it's still '' at that point, so let's cheat a bit and substitute that by hand.while True:    try:        # Iterates over the file sent by the user.        byte_string = next(file)    except StopIteration as e:        dispatch_record('')        break    record = ''    incomplete_record = ''    ...2Looking at dispatch_record we see that '' is handled as pass. So it does nothing. Let's remove that call.while True:    try:        # Iterates over the file sent by the user.        byte_string = next(file)    except StopIteration as e:        break    record = ''    incomplete_record = ''    ...2And this is a fairly common pattern, so common in fact that this is the basis of the for loop.for byte_string in file:    record = ''    incomplete_record = ''    ...2Let me zoom out again.for byte_string in file:    record = ''    incomplete_record = ''    byte_stripped_string = byte_string.strip()    string = byte_stripped_string.decode(encoding='ISO-8859-1')    # List of records.    durty_strings_list = string.split('~')    # Check if one chunk in chunks is complete.    if durty_strings_list[-1] != '' and incomplete_record != '':        incomplete_record = incomplete_record + durty_strings_list.pop(-1)        dispatch_record(incomplete_record)        incomplete_record = ''    elif durty_strings_list[-1] != '' and incomplete_record == '':        incomplete_record = durty_strings_list.pop(-1)    for durty_string in durty_strings_list:        stripped_string = durty_string.strip()        if durty_string and durty_string[0] in ('M', 'E'):            continue        if record != '':            # Dispatch the previous record.            dispatch_record(record)        # Assign the current record.        record = stripped_stringBecause incomplete_record = '' is inside the loop, it always gets reset. Are you sure you have tried the algorithm with larger files? (And tested it is correct)?There are more reasons why I think your code is broken, for instance the handling of dispatch_record, and where the assignments take place.Rewriting parse_file.What parse_file should do is the following:Iterate over all the records in file (separated by ~), and call parse_record on all of them.Assuming memory was infinite (or just 'large enough'), you could just dofor record in file.read().split('~'):    dispatch_record(record)But from your code, I assume it's not 'large enough', and we get it in chunks.def parse_file(chunks):    partial_record = ''    for chunk in chunks:        stripped_chunk = byte_string.strip()        string = stripped_chunk.decode(encoding='ISO-8859-1')        records = chunk.split('~')        # Prepend the partial record to the first record         records[0] = partial_record + records[0]        # Get the last         partial_record = records.pop(-1)        for record in records:            dispatch_record(record)    # If we still have data left, it's a full record, but just at    # the end of the file.    if partial_record != '':        dispatch_record(partial_record)Ideally, you'd split out the parsing of the ~-chunked blocks from the iteration, but this is good enough for now, I think.