代码之家 › 专栏 › 技术社区 › Jeldrik

可选择的程序非常慢,带有时间戳

firebird stored-procedures sql

Jeldrik · 技术社区 · 7 年前

我有以下选择声明:

SELECT
    "TRIP"."ID" as "ID",
    "TRIP"."KEY" as "TRIP_KEY",
    "ROUTE"."KEY" as "ROUTE_KEY",
    "ROUTE"."AGENCY_ID" as "AGENCY_ID",
    "TRIP"."BLOCK" as "BLOCK",
    "TRIP"."DIRECTION" as "DIRECTION",
    "CALENDAR"."MASK" as "MASK",
    "CALENDAR"."START_DATE",
    "CALENDAR"."END_DATE",
    "TRIP"."CALENDAR_ID" as "CALENDAR_ID"
    FROM "STOP_TIME" 
    JOIN "FEED" ON("STOP_TIME"."FEED_ID"="FEED"."ID") 
    JOIN "TRIP" ON ("STOP_TIME"."TRIP_ID"="TRIP"."ID") 
    JOIN "CALENDAR" ON("TRIP"."CALENDAR_ID"="CALENDAR"."ID") 
    JOIN "ROUTE" ON("TRIP"."ROUTE_ID"="ROUTE"."ID")
WHERE 
    "FEED"."REALTIME_FEED"='TrainFeed' AND 
    "FEED"."ACTIVE" = true AND 
    "CALENDAR"."START_DATE" <= '15.03.2019 11:00' AND 
    "CALENDAR"."END_DATE" >= '10.03.2019 11:00' AND
    "STOP_TIME"."DEPARTURE_TIME" >= 0 AND 
    "STOP_TIME"."DEPARTURE_TIME" < 39600000

这给了我以下运行时行为(FlameRobin的输出):

正在执行语句。。。
已执行语句(运行时间:0.000s)。
4287次读取,0次标记,1847次读取,0次写入。
0次插入,0次更新,0次删除,722个索引,3个序列。
增量内存:1415008字节。
总执行时间:0.322s
脚本执行完成。

然而,如果我把这个查询放在一个可选的过程中:

SET TERM ^ ;
CREATE PROCEDURE SELECT_TIMETABLE (
    FEEDNAME varchar(100),
    CALENDAR_TIME timestamp,
    TIME_FROM integer,
    TIME_TO integer)
RETURNS (
    ID bigint,
    TRIP_KEY varchar(100),
    ROUTE_KEY varchar(100),
    AGENCY_ID bigint,
    "BLOCK" varchar(100),
    DIRECTION smallint,
    MASK smallint,
    CALENDAR_ID bigint)
AS
DECLARE VARIABLE valid_trip BOOLEAN;
BEGIN 
    FOR SELECT
        "TRIP"."ID" as "ID",
        "TRIP"."KEY" as "TRIP_KEY",
        "ROUTE"."KEY" as "ROUTE_KEY",
        "ROUTE"."AGENCY_ID" as "AGENCY_ID",
        "TRIP"."BLOCK" as "BLOCK",
        "TRIP"."DIRECTION" as "DIRECTION",
        "CALENDAR"."MASK" as "MASK",
        "TRIP"."CALENDAR_ID" as "CALENDAR_ID"
    FROM "STOP_TIME" 
        JOIN "FEED" ON("STOP_TIME"."FEED_ID"="FEED"."ID") 
        JOIN "TRIP" ON ("STOP_TIME"."TRIP_ID"="TRIP"."ID") 
        JOIN "CALENDAR" ON("TRIP"."CALENDAR_ID"="CALENDAR"."ID") 
        JOIN "ROUTE" ON("TRIP"."ROUTE_ID"="ROUTE"."ID")
    WHERE 
        "FEED"."REALTIME_FEED"=:feedName AND 
        "FEED"."ACTIVE" = true AND 
        "CALENDAR"."START_DATE" <= :calendar_time AND 
        "CALENDAR"."END_DATE" >= :calendar_time AND 
        "STOP_TIME"."DEPARTURE_TIME" >= :time_from AND 
        "STOP_TIME"."DEPARTURE_TIME" < :time_to
    INTO :id, :trip_key, :route_key, :agency_id, :block, :direction, :mask, :calendar_id DO 
    BEGIN
        SUSPEND;
    END
END^
SET TERM ;

并用相同的参数调用它:

SELECT 
    p.ID, p.TRIP_KEY, 
    p.ROUTE_KEY, 
    p.AGENCY_ID, 
    p."BLOCK", 
    p.DIRECTION,
    p.MASK, p.CALENDAR_ID
FROM SELECT_TIMETABLE (
    'TrainFeed', 
    '15.03.2019 11:00', 
    0, 
    39600000) p;

我遇到了更糟糕的运行时行为:

正在执行语句。。。
已执行语句(运行时间:0.000s)。
3248968次回迁,0次标记,52540次读取,0次写入。
0次插入,0次更新,0次删除,1338852索引,42序列。
增量内存:784字节。
总执行时间:3.477秒
脚本执行完成。

我已经把范围缩小到筛选日历表的开始日期和结束日期。如果省略过滤器,执行时间将下降到半秒。除此之外,上面的可选过程也不会给出任何结果(使用开始日期/结束日期筛选),因为查询是这样做的。

所以我的可选程序似乎有问题。但我看不出问题所在。

Firebird版本在Windows上是3.0.4.33054(x64)。我目前使用Firebird作为嵌入式服务器。

0 回复 | 直到 7 年前