Django批量更新/插入性能 [英] Django Mass Update/Insert Performance
问题描述
我每5秒钟接收大约5000种工具的财务数据,并且需要更新数据库中的相应条目.该模型如下所示:
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)
需要发生的事情如下:
- 在收到新的财务数据后,检查是否存在条目数据库.
- 如果该条目存在,请更新"ask","bid"和"lastUpdate"字段
- 如果该条目不存在,请创建一个新条目
我的代码如下:
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)
但是执行此过程花费的时间太长.大约30秒.我需要将时间减少到1秒.我知道可以做到这一点,因为我可以在100毫秒内在.NET中执行相同的自定义SQL代码.知道如何提高Django的性能吗?
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?
推荐答案
如果您要获得这种性能,我不明白为什么您不只是闯入原始SQL.批量创建尚不存在的东西听起来像是不是真正针对Django的高级SQL查询.
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/topic/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)
也许这种组合会生成更好的SQL,并且也避开了 exists()
调用( update
返回它更改了多少行).
Maybe that combination will generate some better SQL and it sidesteps the exists()
call as well (update
returns how many rows it changed).
这篇关于Django批量更新/插入性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!