使用Django和sqlite查询过多的运行时问题

Question

我在运行时遇到以下代码问题：

# filter start and end date
matches = database.objects.filter(start__gte= Start, end__lte= End)    
# iterate through matches
for Item in matches:
        # filter database again
        test = database.objects.filter(cm1=Item.cm1, cm2=Item.cm2, cm3=Item.cm3)
        # compare first item of filter with iterated Item
        if test[0] == Item:
            TrueRun = True
        else:
            TrueRun = False

我有一个大约8万行的数据库。在第一步中，我过滤我想要查看的行，通常应该在8k左右。在第二步中，我迭代所有这些项，检查它们是唯一的还是第一个具有特定属性的行（cm1、cm2、cm3）。你知道吗

现在的问题是，我要进行8k个数据库查询，总共需要15分钟。有没有什么方法可以加速这个过程，例如在循环之前使用dict，它包含cm1及其“匹配行”的所有可能性？你知道吗

谢谢你的帮助！你知道吗

\uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu

评论后编辑

我的模型的默认顺序与这里的使用方式不同。在程序中，我有大约25个模型，并检查其中12个是否相等。你知道吗

循环的其余部分应该不有趣，因为之前有另一种检查TrueRun的方法，大约需要2分钟。唯一改变的是内部#-----#请看这里：

equalnessList = ['cm1','cm2', 'cm3']
for idx, Item in enumerate(matches):
    #-----------------#
    TrueRun = True
    listTrue = []
    for TrueIdx,TrueItem in enumerate(listTrue):
        EqualCount = 0
        for equCrit in equalnessList:
            if getattr(Item,equCrit)!=getattr(matches[TrueItem],equCrit):
                EqualCount += 1
        if EqualCount == len(equalnessList):
            TrueRun = False
            break
    #------------------#
    # Some stuff in here, that can't be changed
    if TrueRun:
        resDict[getattr(Item,'id')] = [True]
        listTrue.append(idx)
    else:
        resDict[getattr(Item,'id')] = [False]

这里的问题是，它工作不正常，没有使用筛选日期之外的数据库条目进行检查。你知道吗

Answer 1

# filter start and end date
matches = database.objects.filter(start__gte=Start, end__lte=End)

# iterate through matches
for item in matches:
    # filter database again and get the id of the first element matched
    first_item = database.objects.filter(cm1=item.cm1, cm2=item.cm2, cm3=item.cm3).values('id').first()
    # compare first item id with the id of filtered "item"
    if first_item['id'] == item.id:
        TrueRun = True
    else:
        TrueRun = False

Answer 2

您可能需要调整它以符合您的要求。特别是，您需要在每个cm1, cm2, cm3组中保持原始排序顺序。你知道吗

matches = database.objects.filter(start__gte=Start, end__lte=End)
all_objects = database.objects.all().order_by('cm1', 'cm2', 'cm3', 'sequence')
# replace 'sequence' by field(s) that model is sorted on by default
results_dict = {}
cm1 = None
cm2 = None
cm3 = None
first = False
for obj in all_objects:
    if (obj.cm1 != cm1) or (obj.cm2 != cm2) or (obj.cm3 != cm3):
        cm1 = obj.cm1
        cm2 = obj.cm2
        cm3 = obj.cm3
        first = True
    if obj.start >= Start and obj.end <= End:
        results_dict[obj.id] = first
    first = False