Commented by: @mrotteveel

Did you create the procedure or view before you populated the database? Then try recreating the stored procedure and view after populating it.

Commented by: Sean Leyne (seanleyne)

While this issue may be a blocker for you/your firm, it is not (implicitly) a blocker for the project.

Commented by: Sean Leyne (seanleyne)

Edited for readbility

Commented by: @luronumen

Hi Mark, Thank you very much for your prompt reply!

This is the steps that we did in our attempt for migrate on of our system :

1- Create a Firebird SQL 3.0.4 database
2- Run the Metadata Script to recreate Domains, Exceptions, Generators, Procedures, Tables, Triggers and Views
3- Disable all database triggers:
FOR SELECT RDB$TRIGGER_NAME FROM RDB$TRIGGERS WHERE (RDB$SYSTEM_FLAG=0) ORDER BY RDB$TRIGGER_NAME INTO :V_TRIGGER_NAME DO
EXECUTE STATEMENT 'ALTER TRIGGER ' || :V_TRIGGER_NAME || ' INACTIVE;';
4- Run the SYNCHRONIZE_DATABASE procedure to load all data from the production database (http://CINMOTO.MOT.COM)
5- Recalculate all INDICES
FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES INTO :V_INDEX_NAME DO
EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :V_INDEX_NAME || ';';
6- Enable all database triggers again
FOR SELECT RDB$TRIGGER_NAME FROM RDB$TRIGGERS WHERE (RDB$SYSTEM_FLAG=0) ORDER BY RDB$TRIGGER_NAME INTO :V_TRIGGER_NAME DO
EXECUTE STATEMENT 'ALTER TRIGGER ' || :V_TRIGGER_NAME || ' ACTIVE;';

These steps above works fine on Firebird 2.5 and this issue is only reproducible on Firebird 3.0

Commented by: Sean Leyne (seanleyne)

Please provide the PLANs for the SELECT in the SP and the VIEW from v2.5 and v3.0

Commented by: @luronumen

HI Sean,

I have shared in this picture:
http://tracker.firebirdsql.org/secure/attachment/13335/Capture.PNG

Commented by: Sean Leyne (seanleyne)

Luciano,

I was looking for the PLAN for the SELECT which is *inside the SP*.

Commented by: @luronumen

=================== SP QUERY=====================
SELECT
DISTINCT V_PROTOTYPES.PROTOTYPE_VERSION
FROM V_PROTOTYPES
JOIN USERS ON (V_PROTOTYPES.CURRENT_OWNER=USERS.FULL_NAME)
JOIN TEAMS ON (USERS.TEAM_ID=http://TEAMS.ID)
JOIN PROJECTS ON (TEAMS.PROJECT_ID=http://PROJECTS.ID)
JOIN SITES ON (PROJECTS.SITE_ID=http://SITES.ID)
WHERE
(
(V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')
OR (SITES.SITE='CIN/MOTOROLA')
) AND (
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING ''
)
ORDER BY V_PROTOTYPES.PROTOTYPE_VERSION
=====================FIREBIRD 2.5 PLAN=========================
PLAN SORT (MERGE (SORT (JOIN (SITES NATURAL, PROJECTS INDEX (FK_PROJECTS_1), TEAMS INDEX (FK_TEAMS_1), USERS INDEX (FK_USERS_1))), SORT (SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1))))))
=====================FIREBIRD 3.0 PLAN=========================
PLAN SORT (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1))), SITES NATURAL, SITES INDEX (UK_SITES_1)), PROJECTS INDEX (FK_PROJECTS_1)), TEAMS INDEX (FK_TEAMS_1)), USERS INDEX (FK_USERS_1)))
=====================CONCLUSION=========================
I have ran the above query directly and the Firebird 3 is still 20 times slower than in Firebird 2.5. It means that there is a issue on the Firebird 3.0 Plan once that it is completely different than the Firebird 2.5 Plan

=================== VIEW QUERY=====================
SELECT
http://PROTOTYPES.ID, PROTOTYPEMODELS.PROTOTYPE_MODEL, PROTOTYPEPARTNUMBERS.PROTOTYPE_PART_NUMBER,
PROTOTYPEVERSIONS.PROTOTYPE_VERSION,
IIF( PROTOTYPES.SCRAPPING_DATE IS NULL,
IIF( PROTOTYPES.MISSING_DATE IS NULL,
IIF( CURRENT_DATE-PROTOTYPES.INVENTORYING_DATE < 10,
IIF( PROTOTYPES.AVAILABILITY_DATE < CURRENT_TIMESTAMP, PROTOTYPESTATUS.PROTOTYPE_STATUS,'BUSY'),'NOT INVENTORIED'
), 'MISSED'
), 'SCRAPPED'
),
SITES.SITE, PROJECTS.PROJECT, TEAMS.TEAM, USERS.FULL_NAME,
PROTOTYPES.DATE_ASSIGNED, PROTOTYPES.AVAILABILITY_DATE, PROTOTYPES.SERIAL_NUMBER, PROTOTYPES.SPEC1, PROTOTYPES.SPEC2, PROTOTYPES.SPEC3,
PROTOTYPES.EXTERNAL_ID, PROTOTYPES.OTHER_INFO, PROTOTYPES.LOCATION, PROTOTYPES.INVOICE,
PROTOTYPES.REGISTRATION_DATE, PROTOTYPES.INVENTORYING_DATE, PROTOTYPES.MISSING_DATE, PROTOTYPES.SCRAPPING_DATE, PROTOTYPES.PID
FROM PROTOTYPES
JOIN PROTOTYPEMODELS ON (PROTOTYPES.PROTOTYPE_MODEL_ID=http://PROTOTYPEMODELS.ID)
JOIN PROTOTYPEPARTNUMBERS ON (PROTOTYPES.PROTOTYPE_PART_NUMBER_ID=http://PROTOTYPEPARTNUMBERS.ID)
JOIN PROTOTYPEVERSIONS ON (PROTOTYPES.PROTOTYPE_VERSION_ID=http://PROTOTYPEVERSIONS.ID)
JOIN PROTOTYPESTATUS ON (PROTOTYPES.PROTOTYPE_STATUS_ID=http://PROTOTYPESTATUS.ID)
JOIN TEAMS ON (PROTOTYPES.TEAM_ID=http://TEAMS.ID)
JOIN PROJECTS ON (TEAMS.PROJECT_ID=http://PROJECTS.ID)
JOIN SITES ON (PROJECTS.SITE_ID=http://SITES.ID)
JOIN USERS ON (PROTOTYPES.USER_ID=http://USERS.ID)
WHERE
(PROTOTYPES.VISIBLE='1')
ORDER BY http://PROTOTYPES.ID;
=====================FIREBIRD 2.5 PLAN=========================
PLAN JOIN (PROTOTYPES ORDER PK_PROTOTYPES_1, PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), TEAMS INDEX (PK_TEAMS_1), PROJECTS INDEX (PK_PROJECTS_1), SITES INDEX (PK_SITES_1), PROTOTYPEVERSIONS INDEX (PK_PROTOTYPEVERSIONS_1), PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), USERS INDEX (PK_USERS_1), PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1))
=====================FIREBIRD 3.0 PLAN=========================
PLAN SORT (JOIN (PROTOTYPESTATUS NATURAL, PROTOTYPES INDEX (FK_PROTOTYPES_4), TEAMS INDEX (PK_TEAMS_1), PROJECTS INDEX (PK_PROJECTS_1), SITES INDEX (PK_SITES_1), PROTOTYPEVERSIONS INDEX (PK_PROTOTYPEVERSIONS_1), PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1), USERS INDEX (PK_USERS_1)))
=====================CONCLUSION=========================
I was not able to see any performance issue when ran the VIEW QUERY above. It means that issue is on SP QUERY Plan

Commented by: @livius2

It is really wrong to put order by in the View.
Remove it and try again.

Commented by: @luronumen

=================== SP QUERY (Remove ORDER BY)=====================
SELECT
DISTINCT V_PROTOTYPES.PROTOTYPE_VERSION
FROM V_PROTOTYPES
JOIN USERS ON (V_PROTOTYPES.CURRENT_OWNER=USERS.FULL_NAME)
JOIN TEAMS ON (USERS.TEAM_ID=http://TEAMS.ID)
JOIN PROJECTS ON (TEAMS.PROJECT_ID=http://PROJECTS.ID)
JOIN SITES ON (PROJECTS.SITE_ID=http://SITES.ID)
WHERE
(
(V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')
OR (SITES.SITE='CIN/MOTOROLA')
) AND (
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING ''
)
=====================FIREBIRD 3.0 PLAN=========================
PLAN SORT (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1))), SITES NATURAL, SITES INDEX (UK_SITES_1)), PROJECTS INDEX (FK_PROJECTS_1)), TEAMS INDEX (FK_TEAMS_1)), USERS INDEX (FK_USERS_1)))
=====================CONCLUSION=========================
The issue is still reproducible

Commented by: @livius2

Can you alias sites in your sp and show the plan then?
Also show uk_sites_1 definition

Commented by: @luronumen

Hi Karol,

How can I "alias sites in your sp"?

SITES is a table that has only one record and this is its definition:

CREATE TABLE SITES (ID BIGINT NOT NULL,
SITE VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
ADDRESS VARCHAR(128) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
VISIBLE VISIBLE DEFAULT '1' NOT NULL,
IID COMPUTED BY (('I' || ID) COLLATE WIN_PTBR),
CONSTRAINT PK_SITES_1 PRIMARY KEY (ID),
CONSTRAINT UK_SITES_1 UNIQUE (SITE));

Commented by: @luronumen

I have attached the FB25_vs_FB30.mp4 video to show how one of our internal system works when using Firebird 2.5 (left side) and Firebird 3.0 (right side). This slowness is not only in the view and SP that I mentioned here but in practically the whole system.

Commented by: @livius2

By aliasing i mean simple "SITES S" but as it contain only one record i have no more questions about it.

Instead i have question about query stats comparision.
Can you show us this difference in time execution?
Show query stats from isql or e.g flamerobin. I ask because i see that you use web which can involve slowdown in many places and this can be not releated to firebird core itself.

Commented by: @luronumen

Hi Karol,

The difference in time execution to run the below query that returns 72 records only is:
- Firebird 2.5 ~ 0.5 seconds
- Firebird 3.0 ~ 47 seconds

============================QUERY============================
SELECT
DISTINCT
V_PROTOTYPES.PROTOTYPE_VERSION
FROM
V_PROTOTYPES
JOIN USERS ON (V_PROTOTYPES.CURRENT_OWNER=USERS.FULL_NAME)
JOIN TEAMS ON (USERS.TEAM_ID=http://TEAMS.ID)
JOIN PROJECTS ON (TEAMS.PROJECT_ID=http://PROJECTS.ID)
JOIN SITES ON (PROJECTS.SITE_ID=http://SITES.ID)
WHERE
(
(V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')
OR
(SITES.SITE='CIN/MOTOROLA')
)
AND
(
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING ''
)

I noticed that the issue on Firebird 3.0 is combining the following in the WHERE query:
(V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')
OR
(SITES.SITE='CIN/MOTOROLA')

Because when I run:
WHERE
(
(V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')
)
AND
(
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING ''
)

or If I run:
WHERE
(
(SITES.SITE='CIN/MOTOROLA')
)
AND
(
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING ''
)

Firebird 3.0 is too fast than Firebird 2.5

In other words, there is a issue to t find the best query plan when combine:
(V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')
OR
(SITES.SITE='CIN/MOTOROLA')

Commented by: @luronumen

====================================================
WHERE ((V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA') OR (SITES.SITE='CIN/MOTOROLA')) AND (V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING '')

Firebird 2.5 (FAST)
PLAN SORT (MERGE (SORT (JOIN (SITES NATURAL, PROJECTS INDEX (FK_PROJECTS_1), TEAMS INDEX (FK_TEAMS_1), USERS INDEX (FK_USERS_1))), SORT (SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1))))))

Firebird 3.0 (SLOW)
PLAN SORT (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1))), SITES NATURAL, SITES INDEX (UK_SITES_1)), PROJECTS INDEX (FK_PROJECTS_1)), TEAMS INDEX (FK_TEAMS_1)), USERS INDEX (FK_USERS_1)))

====================================================
WHERE ((V_PROTOTYPES.SITE_OWNER='CIN/MOTOROLA')) AND (V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING '')

Firebird 2.5 (FAST)
PLAN SORT (MERGE (SORT (JOIN (SITES NATURAL, PROJECTS INDEX (FK_PROJECTS_1), TEAMS INDEX (FK_TEAMS_1), USERS INDEX (FK_USERS_1))), SORT (SORT (JOIN (V_PROTOTYPES SITES INDEX (UK_SITES_1), V_PROTOTYPES PROJECTS INDEX (FK_PROJECTS_1), V_PROTOTYPES TEAMS INDEX (FK_TEAMS_1), V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_5), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES PROTOTYPEVERSIONS INDEX (PK_PROTOTYPEVERSIONS_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1))))))

Firebird 3.0 (FAST)
PLAN SORT (HASH (JOIN (SITES NATURAL, PROJECTS INDEX (FK_PROJECTS_1), TEAMS INDEX (FK_TEAMS_1), USERS INDEX (FK_USERS_1)), SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1)))))

====================================================
WHERE ((SITES.SITE='CIN/MOTOROLA')) AND (V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING '')

Firebird 2.5 (FAST)
PLAN SORT (MERGE (SORT (JOIN (SITES INDEX (UK_SITES_1), PROJECTS INDEX (FK_PROJECTS_1), TEAMS INDEX (FK_TEAMS_1), USERS INDEX (FK_USERS_1))), SORT (SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1))))))

Firebird 3.0 (FAST)
PLAN SORT (HASH (JOIN (SITES INDEX (UK_SITES_1), PROJECTS INDEX (FK_PROJECTS_1), TEAMS INDEX (FK_TEAMS_1), USERS INDEX (FK_USERS_1)), SORT (JOIN (V_PROTOTYPES PROTOTYPEVERSIONS NATURAL, V_PROTOTYPES PROTOTYPES INDEX (FK_PROTOTYPES_3), V_PROTOTYPES PROTOTYPESTATUS INDEX (PK_PROTOTYPESTATUS_1), V_PROTOTYPES TEAMS INDEX (PK_TEAMS_1), V_PROTOTYPES PROJECTS INDEX (PK_PROJECTS_1), V_PROTOTYPES SITES INDEX (PK_SITES_1), V_PROTOTYPES PROTOTYPEMODELS INDEX (PK_PROTOTYPEMODELS_1), V_PROTOTYPES PROTOTYPEPARTNUMBERS INDEX (PK_PROTOTYPEPARTNUMBERS_1), V_PROTOTYPES USERS INDEX (PK_USERS_1)))))

Commented by: @luronumen

Does anyone have any status on this issue? Do you need any further information about this issue?
Firebird 2.5 is being deprecated and the Firebird 3.0 query algorithm is still not meeting the same technical criteria we have in Firebird 2.5 and our company has not yet been able to migrate to the latest version of Firebird because of that issue.

Commented by: @luronumen

One important information about this issue is that it is noticeable when the string length is less than 3 characters:

CONTAINING 'Luciano' > Fast (0.8 sec)
CONTAINING 'Lucian' > Fast (0.8 sec)
CONTAINING 'Lucia' > Fast (0.8 sec)
CONTAINING 'Luci' > Fast (0.8 sec)
CONTAINING 'Luc' > Fast (1 sec)
CONTAINING 'Lu' > Slow (2.5 sec)
CONTAINING 'L' > Too Slow (38.0 sec)
CONTAINING '' > Too Slow (38.0 sec)

Commented by: Sean Leyne (seanleyne)

Luciano,

The query performance is likely related to the number of rows matching the criteria (and the accompanying grouping/sorting), how many rows match:

CONTAINING 'Luc'
CONTAINING 'Lu'
CONTAINING 'L'
CONTAINING ''

Commented by: Sean Leyne (seanleyne)

Luciano,

A review of your SPs SQL finds that the following:

FOR
SELECT
DISTINCT V_PROTOTYPES.PROTOTYPE_VERSION
FROM V_PROTOTYPES
JOIN USERS ON (V_PROTOTYPES.CURRENT_OWNER=USERS.FULL_NAME)
JOIN TEAMS ON (USERS.TEAM_ID=http://TEAMS.ID)
JOIN PROJECTS ON (TEAMS.PROJECT_ID=http://PROJECTS.ID)
JOIN SITES ON (PROJECTS.SITE_ID=http://SITES.ID)
WHERE
(
(V_PROTOTYPES.SITE_OWNER=:V_SITE)
OR (SITES.SITE=:V_SITE)
) AND (
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING :Q
)

could be restated as:

FOR
SELECT
DISTINCT V_PROTOTYPES.PROTOTYPE_VERSION
FROM V_PROTOTYPES
WHERE
V_PROTOTYPES.CURRENT_OWNER = V_PROTOTYPES.USERS.FULL_NAME
AND (
(V_PROTOTYPES.SITE_OWNER=:V_SITE)
OR (V_PROTOTYPES.SITE=:V_SITE)
) AND (
V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING :Q
)

The JOINs are redundant, they are already present in the V_PROTOTYPES VIEW definition.

Commented by: @luronumen

Hi Sean Leyne,

Thank you very much for your reply! I really appreciate it!

The database has less than 25000 records and this should not be the root cause.

Other than that, as I mentioned in the description of this bug, this is not reproducible in version 2.5 which is a strong indication that the version 3.0 query algorithm cannot identify the "JOIN redundancy" described by you.

I updated Stored Procedure as described below and query performance was again similar to version 2.5.

Please keep this bug open because there are other bottlenecks in our software that are even worse than the one I described in the bug. Once I have identified and fixed them all I will come back here to inform you. In the meantime, you can try to identify why in version 3.0 the query algorithms are failing to identify this supposed JOIN redundancy once that the version 2.5 is able to do since other Firebird users migrating to version 3.0 may also face similar issue.

============ I_PROTOTYPE_SEARCH_VERSION stored procedure ============
SET TERM ^ ;
ALTER PROCEDURE I_PROTOTYPE_SEARCH_VERSION (
USERID BIGINT,
Q VARCHAR(32) )
RETURNS (
PROTOTYPE_VERSION VARCHAR(32) )
AS
DECLARE V_SITE VARCHAR(32);
DECLARE V_FULL_NAME VARCHAR(100);
BEGIN
Q=COALESCE(Q,'');
SELECT V_USERS.SITE,V_USERS.FULL_NAME FROM V_USERS WHERE (V_USERS.USER_ID=:USERID) INTO :V_SITE,:V_FULL_NAME;
FOR
SELECT
DISTINCT V_PROTOTYPES.PROTOTYPE_VERSION
FROM
V_PROTOTYPES
WHERE
(
(V_PROTOTYPES.SITE_OWNER=:V_SITE) OR
(V_PROTOTYPES.CURRENT_OWNER=:V_FULL_NAME)
)
AND
(V_PROTOTYPES.PROTOTYPE_VERSION CONTAINING :Q)
ORDER BY
V_PROTOTYPES.PROTOTYPE_VERSION
INTO
:PROTOTYPE_VERSION
DO
SUSPEND;
END^
SET TERM ; ^

Commented by: Sean Leyne (seanleyne)

Luciano,

Another suggestion would be to modify the VIEW and expose the http://USERS.ID, USERS.Site and USER.FULL_Name as new USER_ID, USER_SITE, USER_FULL_NAME columns.

This would allow you to eliminate the SP and use the View directly (which would allow the engine to determine the best PLAN, because it can 'push' the SELECT criteria to the VIEW tables).

The SQL would become:

SELECT
DISTINCT PROTOTYPE_VERSION
FROM
V_PROTOTYPES
WHERE
USER_ID = :User_ID
AND PROTOTYPE_VERSION CONTAINING COALESCE( :Q, '')
AND (
SITE_OWNER = USER_SITE
OR CURRENT_OWNER = USER_FULL_NAME
)
ORDER BY PROTOTYPE_VERSION

Commented by: @luronumen

Hi Sean

I redesigned all 63 queries on our system that were experiencing performance issues in Firebird 3.0. Now they all no longer join views with tables that are already within those views using other join criteria.

So for my part, this issue is no longer a blocker for migrating our system to Firebird 3.0.

Feel free to lower its priority but I still find it interesting that you continue to investigate why this query has become so slow in Firebird 3.0.

In addition I would like you to take a look at these two bugs I found during a migration simulation for firebird 3.0: CORE6116 and CORE6118
Is there any possibility of them being fixed in the version 3.0.5?

Thanks in advanced,
Luciano

Commented by: @luronumen

Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - NONBLOCKER (#⁠nonmotorolablocker)