在嵌套循环中优化Django查询

Question

我正在开发一个Django应用程序，它可以帮助分析来自不同地点的发射机的无线电信号传播。原始数据是从MySQL数据库获得的，MySQL数据库记录了报告器接收到的每个信号及其来源和时间戳。你知道吗

其思想是在特定的时间间隔/块期间检查接收器相对于目标发射器的状态。过滤后的结果需要作为JSON文件返回。你知道吗

# All records within the time frame in question
qs_spots = Spot.objects.filter(unix_time__range = (starttime, endtime))
qs_spots = qs_spots_all.values()

# the focus is on a particular transmitter tx
tx_dict = {}
tx_dict['tx_callsign'] = tx

interval_range = 120

# list of all unique receivers - the field is called 'reporter' in the model
rx_list = qs_spots.values_list('reporter', flat=True).distinct()
tx_dict['rx_total'] = len(rx_list)
tx_dict['receivers'] = []

# goes through each reporter in the list
for rx in rx_list:
    rx_dict = {}
    rx_dict['rx_callsign'] = rx

    rx_spots = qs_spots.filter(reporter=rx)

    rx_dict['time_state'] = []

    current_time = starttime
    while current_time <= endtime:
        next_time = current_time + interval_range # increment next time interval
        time_dict = {}
        time_dict['datetime'] = current_time

        rx_time = rx_spots.filter(unix_time__range = (current_time, next_time))
        # first check if any records exist for the particular interval
        if rx_time.exists():                        
            # then check the transmitter status
            if rx_time.filter(transmitter=tx).exists():
                time_dict['status'] = 0 # reporter is receiving from target transmitter
            else:
                time_dict['status'] = 1 # reporter is not receiving from target transmitter, but is receiving from other transmitters
        else:
            # if no records exists for the particular interval
            # i.e reporter is not receiving at all during this interval
            time_dict['status'] = 2

        current_time = next_time # set to next interval

        rx_dict['time_state'].append(time_dict.copy())  

    tx_dict['receivers'].append(rx_dict.copy())

response = json.dumps(tx_dict, cls=DjangoJSONEncoder)
return HttpResponse(response, content_type='application/json')

我自己测试了外环，它工作得很好，尽管速度很慢——在30秒到2分钟之间返回一个包含50-100个接收器的JSON列表。当我添加内环来检查目标发射器的状态时，问题就出现了。结果永远不会回来，即使在几个小时之后。你知道吗

我已经尝试了不同的时间范围，但我的“默认”是大约50个接收器在1小时的窗口间隔120秒（2分钟）。50*（60/2）=总共1500个回路。对于计算机来说，这听起来不太容易，但是有什么东西导致它锁定了。你知道吗

编辑：添加了我的型号.py如果有帮助的话

class Spot(models.Model):
    spot_id = models.IntegerField(db_column='Spot_ID', primary_key=True)  # Field name made lowercase.
    unix_time = models.IntegerField(db_column='UNIX_time', blank=True, null=True)  # Field name made lowercase.
    reporter = models.CharField(db_column='Reporter', max_length=15, blank=True, null=True)  # Field name made lowercase.
    transmitter = models.CharField(db_column='Transmitter', max_length=15, blank=True, null=True)  # Field name made lowercase.

class Meta:
    managed = False
    db_table = 'SPOT'

编辑2: 尝试将查询集转换为字典并限制字段

qs_spots = qs_spots.values('unix_time', 'reporter', 'transmitter')

rx_spots = qs_spots.filter(reporter=rx).values('unix_time', 'reporter', 'transmitter')

rx_time = rx_spots.filter(unix_time__range = (current_time, next_time)).values('transmitter')

外环似乎运行得更快，但内环仍然被锁定。你知道吗

示例预期的JSON响应

{
  "tx_callsign": "EA4DUT",
  "rx_total": 3,
  "receivers": [
    {
      "rx_callsign": "G1RWT"
      "time_state": [
        { "datetime": 1443963600, "status": 0 },
        { "datetime": 1443963720, "status": 1 },
        { "datetime": 1443963840, "status": 2 }
        ]
    },
    {
      "rx_callsign": "PI4THT"
      "time_state": [
        { "datetime": 1443963600, "status": 1 },
        { "datetime": 1443963720, "status": 0 },
        { "datetime": 1443963840, "status": 2 }
        ]
    },
    {
      "rx_callsign": "EI6KD"
      "time_state": [
        { "datetime": 1443963600, "status": 2 },
        { "datetime": 1443963720, "status": 0 },
        { "datetime": 1443963840, "status": 0 }
        ]
    },
    ]
}