I'm having a runtime problem with the following code:
# 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
I have a database with around 80k rows. In a first step I filter the rows I want to look at, should be normally around 8k. In a second step I iterate over all of these items and check if they're unique or the first ones with some specific attributes (cm1, cm2, cm3).
The Problem is now, that I do 8k database queries, which all together take about 15 minutes. Is there any way to accelerate this, for example using a dict before the loop which contains all possibilities of cm1 and its' matching rows?
Thanks for your help!
__________________________
Edit after Comment
The default order of my models is different to how it's used here. In program I have about 25 models and check about 12 of them for equalness.
The rest of the loop should not be interesting, because there was another way of checking TrueRun before, which took about 2 minutes. Only thing changed is inside #-----# See here:
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]
Problem here was, that it wasn't working right and didn't use database entries outside of filtered dates for the check.
# 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
You may need to tweak this to fit your requirements. In particular, you need to maintain the original sort order within each cm1, cm2, cm3 group.
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