Django 性能 - sql View 是否足够好？

Question

作为我当前项目的一部分，我恢复了交付一组在线报告的任务。

根据报告的复杂性和最佳性能，我决定在 Posgres 上编写 sql View ，然后将它们附加到只读 Django 模型。

我想问您对这种方法的看法，因为我的一些团队成员说我应该只使用 ORM，我认为这在这种情况下不是最好的。

让我知道你的想法 b/c 我真的需要争论但不是这样:“这是错误的方法 b/c 我认为它就是这样。“谢谢，

J

示例 sql View :

DROP VIEW IF EXISTS riss_all_alerts;
DROP VIEW IF EXISTS riss_state_alerts;
DROP VIEW IF EXISTS riss_zone_alerts;
DROP VIEW IF EXISTS riss_lga_alerts;

-- ZONE PART
CREATE VIEW riss_zone_alerts AS
  WITH rissAlertPart AS (
      SELECT
        zone_name      AS alert_zone,
        min(rissAlert) AS alert,
        month
      FROM (
             SELECT
               date_part('month', odk_submission_date) AS month,
               zone_name,
               CASE
               WHEN (reason_no = 'N/A' AND reason_yes = 'N/A' AND ss_to_del = 1)
                 THEN '1'
               WHEN (reason_yes <> 'N/A' OR ss_to_del = 0)
                 THEN '3'
               WHEN (reason_no <> 'N/A' OR ss_to_del = 2)
                 THEN '2'
               ELSE 'ghj'
               END                                     AS rissAlert
             FROM riss_rissdetail
             WHERE alert_id = 'zn-ssn'
             GROUP BY zone_name, reason_no, reason_yes, ss_to_del, date_part('month', odk_submission_date)
           ) AS a
      GROUP BY a.zone_name, month
      ORDER BY 1, 2
  )

  SELECT
    'zn-ssn'::TEXT                                                                AS alert_id,
    zone_name                                                               AS level_name,
    CASE WHEN greenCount :: FLOAT > 0
      THEN greenCount :: FLOAT
    ELSE 0 END / count(*)                                                   AS value,
    to_char(CASE WHEN greenCount :: FLOAT > 0
      THEN greenCount :: FLOAT
            ELSE 0 END, 'FM999MI') || ' / ' || to_char(count(*), 'FM999MI') AS extra_desc,
    alert                                                                   AS alert_level,
    date_part('month', odk_submission_date) :: INT                          AS alert_date
  FROM riss_rissdetail
    LEFT JOIN (
                SELECT
                  date_part('month', odk_submission_date) AS green_month,
                  zone_name                               AS green_zone,
                  count(*)                                AS greenCount
                FROM riss_rissdetail
                WHERE alert_id = 'zn-ssn' AND ss_to_del = 0
                GROUP BY zone_name, date_part('month', odk_submission_date)
              ) AS g ON g.green_zone = zone_name AND g.green_month = date_part('month', odk_submission_date)
    LEFT JOIN rissAlertPart AS r
      ON r.alert_zone = zone_name AND r.month = date_part('month', odk_submission_date)
  WHERE alert_id = 'zn-ssn'
  GROUP BY zone_name, g.green_zone, greenCount, alert, date_part('month', odk_submission_date);


--LGA PART
CREATE VIEW riss_lga_alerts AS
  WITH rissAlertPart AS (
      SELECT
        lga_name       AS alert_lga,
        min(rissAlert) AS alert,
        month
      FROM (
             SELECT
               date_part('month', odk_submission_date) AS month,
               lga_name,
               CASE
               WHEN (reason_no = 'N/A' AND reason_yes = 'N/A' AND ss_to_del = 1)
                 THEN '1'
               WHEN (reason_yes <> 'N/A' OR ss_to_del = 0)
                 THEN '3'
               WHEN (reason_no <> 'N/A' OR ss_to_del = 2)
                 THEN '2'
               ELSE 'ghj'
               END                                     AS rissAlert
             FROM riss_rissdetail
             WHERE alert_id = 'lg-ssn'
             GROUP BY lga_name, reason_no, reason_yes, ss_to_del, date_part('month', odk_submission_date)
           ) AS a
      GROUP BY a.lga_name, month
      ORDER BY 1, 2
  )

  SELECT
    'lg-ssn'::TEXT                                                                AS alert_id,
    lga_name                                                                AS level_name,
    CASE WHEN greenCount :: FLOAT > 0
      THEN greenCount :: FLOAT
    ELSE 0 END / count(*)                                                   AS value,
    to_char(CASE WHEN greenCount :: FLOAT > 0
      THEN greenCount :: FLOAT
            ELSE 0 END, 'FM999MI') || ' / ' || to_char(count(*), 'FM999MI') AS extra_desc,
    alert                                                                   AS alert_level,
    date_part('month', odk_submission_date) :: INT                          AS alert_date
  FROM riss_rissdetail
    LEFT JOIN (
                SELECT
                  date_part('month', odk_submission_date) AS green_month,
                  lga_name                                AS green_lga,
                  count(*)                                AS greenCount
                FROM riss_rissdetail
                WHERE alert_id = 'lg-ssn' AND ss_to_del = 0
                GROUP BY lga_name, date_part('month', odk_submission_date)
              ) AS g ON g.green_lga = lga_name AND g.green_month = date_part('month', odk_submission_date)
    LEFT JOIN rissAlertPart AS r
      ON r.alert_lga = lga_name AND r.month = date_part('month', odk_submission_date)
  WHERE alert_id = 'lg-ssn'
  GROUP BY lga_name, g.green_lga, greenCount, alert, date_part('month', odk_submission_date);


--STATE PART
CREATE VIEW riss_state_alerts AS
  WITH rissAlertPart AS (
      SELECT
        state_name     AS alert_state,
        min(rissAlert) AS alert,
        month
      FROM (
             SELECT
               date_part('month', odk_submission_date) AS month,
               state_name,
               CASE
               WHEN (reason_no = 'N/A' AND reason_yes = 'N/A' AND ss_to_del = 1)
                 THEN '1'
               WHEN (reason_yes <> 'N/A' OR ss_to_del = 0)
                 THEN '3'
               WHEN (reason_no <> 'N/A' OR ss_to_del = 2)
                 THEN '2'
               ELSE 'ghj'
               END                                     AS rissAlert
             FROM riss_rissdetail
             WHERE alert_id = 'st-ssn'
             GROUP BY state_name, reason_no, reason_yes, ss_to_del, date_part('month', odk_submission_date)
           ) AS a
      GROUP BY a.state_name, month
      ORDER BY 1, 2
  )

  SELECT
    'st-ssn'::TEXT                                                                AS alert_id,
    state_name                                                              AS level_name,
    CASE WHEN greenCount :: FLOAT > 0
      THEN greenCount :: FLOAT
    ELSE 0 END / count(*)                                                   AS value,
    to_char(CASE WHEN greenCount :: FLOAT > 0
      THEN greenCount :: FLOAT
            ELSE 0 END, 'FM999MI') || ' / ' || to_char(count(*), 'FM999MI') AS extra_desc,
    alert                                                                   AS alert_level,
    date_part('month', odk_submission_date) :: INT                          AS alert_date
  FROM riss_rissdetail
    LEFT JOIN (
                SELECT
                  date_part('month', odk_submission_date) AS green_month,
                  state_name                              AS green_state,
                  count(*)                                AS greenCount
                FROM riss_rissdetail
                WHERE alert_id = 'st-ssn' AND ss_to_del = 0
                GROUP BY state_name, date_part('month', odk_submission_date)
              ) AS g ON g.green_state = state_name AND g.green_month = date_part('month', odk_submission_date)
    LEFT JOIN rissAlertPart AS r
      ON r.alert_state = state_name AND r.month = date_part('month', odk_submission_date)
  WHERE alert_id = 'st-ssn'
  GROUP BY state_name, g.green_state, greenCount, alert, date_part('month', odk_submission_date);

CREATE VIEW riss_all_alerts AS
  SELECT *
  FROM riss_zone_alerts
  UNION ALL
  SELECT *
  FROM riss_lga_alerts
  UNION ALL
  SELECT *
  FROM riss_state_alerts;

我的 django 模型在这里是只读的:

class RissAlertView(models.Model):
    alert_id = models.CharField(max_length=255, primary_key=True)
    value = models.FloatField()
    alert_date = models.PositiveSmallIntegerField(null=True)
    alert_level = models.PositiveSmallIntegerField(null=True)
    level_name = models.CharField(max_length=255, null=True)
    extra_desc = models.CharField(max_length=255, null=True)

    class Meta:
        db_table = u'riss_all_alerts'
        managed = False
        verbose_name = "Riss Alerts View"
        verbose_name_plural = "Riss Alerts View"
        permissions = (("can_access_riss_alerts", "Can access RISS alerts"),)

    def __unicode__(self):
        return u'%s %s %s' % (self.alert_id, self.levelname, self.alert_date)

    def _percenatge_value(self):
        """Returns the percenateg value."""
        return self.value * 100

    percent = property(_percenatge_value)

Answer 1

如果您不想得到“这是错误的方法 b/c 我认为它就是这样。”，那么只有一种方法可以获得关于什么是最好的客观公正的答案在性能方面 - 衡量标准。

当性能至关重要时，值得花一些时间对不同的方法进行基准测试。

总的来说，Django 和它的 ORM 已经被广泛使用多年，它背后的人试图尽可能地优化它，所以使用它的部分应该不会那么糟糕。考虑检查 docs 的这一部分.另外，请注意 Django 是模块化的，因此如果您不喜欢 Django ORM，您可以将其换成其他东西，即 SQLALchemy .