Django Mass Update/Insert Performance

Question

I'm receiving financial data for approximately 5000 instruments every 5 seconds, and need to update the respective entries in the database. The model looks as follows:

class Market(models.Model):
    market = models.CharField(max_length=200)
    exchange = models.ForeignKey(Exchange,on_delete=models.CASCADE) 
    ask = models.FloatField()
    bid = models.FloatField()
    lastUpdate = models.DateTimeField(default = timezone.now)

What needs to happen is the following:

• After new financial data is received, check if an entry exists in the database.
• If the entry exists, update the ask, bid and lastUpdate fields
• If the entry does not exist, create a new entry

My code looks as follows:

bi_markets = []
for item in dbMarkets:
    eItem = Market.objects.filter(exchange=item.exchange,market=item.market)
    if len(eItem) > 0:
        eItem.update(ask=item.ask,bid=item.bid)
    else:
        bi_markets.append(item)

#Bulk insert items that does not exist
Market.objects.bulk_create(bi_markets)  

However executing this takes way too long. Approximately 30 seconds. I need to reduce the time down to 1 second. I know this can be done as I do the same wth custom SQL code in .NET in under 100ms. Any idea how to improve the performance in Django?

Answer 1

If it’s this kind of performance you’re going for, I don’t see why you wouldn’t just break out into raw SQL. Bulk creating things that don’t exist yet sounds like the advanced SQL querying that Django isn’t really made for.

https://docs.djangoproject.com/en/2.0/topics/db/sql/

You can also do (sorry on mobile):

bi_markets = []
for item in dbMarkets:
  rows = Market.objects.filter(exchange=item.exchange, market=item.market).update(ask=item.ask, bid=item.bid)
  if rows == 0:
    bi_markets.append(item)

Market.objects.bulk_create(bi_markets)

Maybe that combination will generate some better SQL and it sidesteps the exists() call as well (update returns how many rows it changed).