sub-optimal query execution inside PSQL block using parameters
--------------------------------------------------------------
Key: CORE-4318
URL: http://tracker.firebirdsql.org/browse/CORE-4318
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 Alpha 2
Environment: Firebird 3.0.0.30837, isql
Reporter: Simonov Denis
SET ECHO ON;
SET NAMES WIN1251;
SET STAT ON;
SET COUNT ON;
SET AUTODDL ON;
CONNECT 'localhost/3051:test' USER 'sysdba' PASSWORD 'masterkey';
CREATE TABLE T1 (
ID INTEGER NOT NULL
);
CREATE TABLE T2 (
ID INTEGER NOT NULL,
T1_ID INTEGER
);
SET TERM ^;
EXECUTE BLOCK
AS
DECLARE VARIABLE I INTEGER = 0;
BEGIN
WHILE (I < 1000) DO
BEGIN
I = I + 1;
INSERT INTO T2(ID, T1_ID) VALUES(:I, MOD(:I, 10));
MERGE INTO T1
USING (
SELECT MOD(:I, 10) AS F
FROM RDB$DATABASE
) SRC ON T1.ID = SRC.F
WHEN NOT MATCHED THEN
INSERT (ID) VALUES(SRC.F);
END
END^
SET TERM ;^
COMMIT;
ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT FK_T2_REF_T1 FOREIGN KEY (T1_ID) REFERENCES
T1(ID);
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1;
Select Expression
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan
S
=====================
49600
Records affected: 1
Current memory = 279233856
Delta memory = 8600
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310
SET TERM ^;
EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;
SUSPEND;
END^
Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan
S
============
49600
Records affected: 1
Current memory = 279241576
Delta memory = 7720
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310
EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
DECLARE VARIABLE V INTEGER = 1;
BEGIN
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = :V
INTO :S;
SUSPEND;
END^
Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan
S
============
49600
Records affected: 1
Current memory = 279244168
Delta memory = 2592
Max memory = 279289456
Elapsed time= 0.01 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 3010 <---------------------- This value is very
large
EXECUTE BLOCK
RETURNS (
S INTEGER
)
AS
BEGIN
WITH T AS (
SELECT
T1_ID AS T1_ID,
SUM(ID) AS S
FROM T2
GROUP BY 1
)
SELECT S
FROM T
WHERE T1_ID = 1
INTO :S;
SUSPEND;
END^
Select Expression
-> Singularity Check
-> Filter
-> Aggregate
-> Filter
-> Table "T T2" Access By ID
-> Index "FK_T2_REF_T1" Scan
S
============
49600
Records affected: 1
Current memory = 279242200
Delta memory = -1968
Max memory = 279289456
Elapsed time= 0.00 sec
Buffers = 16384
Reads = 0
Writes 0
Fetches = 310
SET TERM ;^
QUIT;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
