如何改进这个存储过程？

Question

我有一个问题：

假设一条装配线上的自行车经过了一些测试，然后设备将有关测试的信息发送到我们的数据库(在oracle中)。我创建了这个存储过程；它按照我的要求正确工作，即：

它获得自行车已经通过的第一个测试(每种类型的测试)的列表。例如，如果一辆自行车有两个相同类型的测试，它只显示第一个测试，并且仅当第一个测试在用户指定的日期之间时才显示它。另外，我从2个月前开始考虑，因为一辆自行车不能在流水线上花费超过2个月(我可能高估了)，但如果用户搜索2天，而我只在这两天之间查看，我可以让3天前或4天前对自行车进行的测试结果之外，如果他们在两个小时之间搜索，情况就更糟了。正如我之前所说的，sp运行得很好，但我想知道是否有优化它的方法。另外，考虑到表到年底大约有700万条记录，所以我不能查询全年，因为它可能会变得丑陋。

下面是存储过程的主要部分：

SELECT pid                                AS "bike_id",             
         TYPE                               AS "type",
         stationnr                          AS "stationnr",
         testtime                           AS "testtime",
         rel2.releasenr                     AS "releasenr",
         placedesc                          AS description,
         tv.recordtime                      AS "recordtime",
         To_char(tv.testtime, 'YYYY.MM.DD') AS "dategroup",
         testcounts                         AS "testcounts",
         tv.result                          AS "result",
         progressive                        AS "PROGRESIVO"
  FROM   (SELECT l_bike_id                                        AS pid,
                 l_testcounts                                 AS testcounts,
                 To_char(l_testtime, 'yyyy-MM-dd hh24:mi:ss') AS testtimes,
                 testtime,
                 pl.code                                      AS place,                     
                 t2.recordtime,
                 t2.releaseid,
                 t2.testresid,                     
                 t2.stationnr,
                 t2.result,                                          
                 v.TYPE,
                 v.progressive,
                 v.prs,
                 pl.description                               AS placeDesc
          FROM   (SELECT v.bike_id             AS l_bike_id,
                         v.TYPE            AS l_type,
                         Min(t.testtime)   AS l_testtime,
                         Count(t.testtime) AS l_testcounts
                  FROM   result_test t
                         inner join bikes v
                                 ON v.bike_id = t.pid
                         inner join result_release rel
                                 ON t.releaseid = rel.releaseid
                         inner join resultconfig.places p
                                 ON p.place = t.place
                  WHERE  t.testtime >= Add_months(Trunc(p_startdate), -2)
                  GROUP  BY v.bike_id,
                            v.TYPE,
                            p.code)p_bikelist
                 inner join result_test t2
                         ON p_bikelist.l_bike_id = t2.pid
                            AND p_bikelist.l_testtime = t2.testtime
                 inner join resultconfig.places pl
                         ON pl.place = t2.place
                 inner join bikes v
                         ON v.bike_id = t2.pid
                 inner join result_release rel2
                         ON t2.releaseid = rel2.releaseid
          ORDER  BY t2.pid)tv
         inner join result_release rel2
                 ON tv.releaseid = rel2.releaseid
  WHERE   tv.testtime BETWEEN p_startdate AND p_enddate             
  ORDER  BY testtime;

感谢您的回复！！

Answer 1

我很难从你给出的英文描述中理解业务需求。措辞暗示，此程序旨在工作每自行车，但我没有看到任何明显的bike_id参数提供，相反，您似乎是返回最早的结果为所有自行车测试之间给定的日期。这就是目的吗？如果它被设计为在每辆自行车上运行，那么确保自行车id被传入并在早期使用:)

您的数据类型有些混乱。在testtime子查询中，将result_test (可能是日期或时间戳列)中的p_bikelist转换为字符串，然后与TV子查询中的原始值进行比较。您还可以进一步使用(假设是有类型的参数) p_startdate和p_enddate来过滤结果。我强烈怀疑p_bikelist中的转换是不必要的，并且可能是避免索引的原因。

最后，我不明白add_months的逻辑。一定要及时延长窗口，以获得在窗口内完成但在开始日期之前2个月开始的测试，但正如所写的那样，由于tv.testtime上的条件，您无论如何都会排除较早的开始。最有可能的做法是在存储过程的早期用如下所示的代码伪造startdate

l_assumedstart := add_months(p_startdate, -2);    

然后在查询本身中使用l_assumedstart。