Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

slow changes on domain [CORE5602] #5868

Closed
firebird-automations opened this issue Sep 4, 2017 · 8 comments
Closed

slow changes on domain [CORE5602] #5868

firebird-automations opened this issue Sep 4, 2017 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Holger Klemt (klemmo)

i reported this issue directly to vlad based on a customer database that we can not upload here

if have no idea why but executing these statements, each take about 2 minutes on a fast machine

ALTER DOMAIN BOOL DROP CONSTRAINT;

ALTER DOMAIN BOOL ADD CHECK (VALUE IN ('T', 'F'));

(checked with with another db with 500 generated tables and each
using a simlar domain 10 times, but this db much faster)

Vlad already told me that he found some not good defined system queries.

additional problem perhaps:
i do not know if it is the same problem, but for example on rdb$procedures, we do no longer have an index on rdb$procedure_name, only a combined index on
RDB$PACKAGE_NAME,RDB$PROCEDURE_NAME

Some old code did a select on rdb$procedures joined it on RDB$PROCEDURE_NAME with a user based table for special userrights.
In fb <=25 it was very fast, in fb>=30 this part create a very bad crossjoin and when you have several thousands stored procs in the
database, it does not work well.

perhaps for compatibility reasons, there should be not only the combined index on tables that now have rdb$package

tables are

RDB$DEPENDENCIES,RDB$FUNCTIONS,RDB$FUNCTION_ARGUMENTS,RDB$PROCEDURES,RDB$PROCEDURE_PARAMETERS,RDB$VIEW_RELATIONS

perhaps all systemqueries need a review based on this, and i also found that RDB$DEPENDENCIES does have a rdb$package column, but it has no index at all

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Holger Klemt (klemmo)

description: i reported this issue directly to vlad based on a customer database that we can not upload here

if have no idea why but executing these statements, each take about 2 minutes on a fast machine

ALTER DOMAIN BOOL DROP CONSTRAINT;

ALTER DOMAIN BOOL ADD CHECK (VALUE IN ('T', 'F'));

(checked with with another db with 500 generated tables and each
using a simlar domain 10 times, but this db much faster)

Vlad already told me that he found some not good defined system queries.

additional problem perhaps:
i do not know if it is the same problem, but for example on rdb$procedures, we do no longer have an index on rdb$procedure_name, only a combined index on
RDB$PACKAGE_NAME,RDB$PROCEDURE_NAME

Some old code did a select on rdb$procedures joined it on RDB$PROCEDURE_NAME with a user based table for special userrights.
In fb <=25 it was very fast, in fb>=30 this part create a very bad crossjoin and when you have several thousands stored procs in the
database, it does not work well.

perhaps for campatibility reasons, there should be not only the combined index on tables that now have rdb$package

tables are

RDB$DEPENDENCIES,RDB$FUNCTIONS,RDB$FUNCTION_ARGUMENTS,RDB$PROCEDURES,RDB$PROCEDURE_PARAMETERS,RDB$VIEW_RELATIONS

perhaps all systemqueries need a review based on this, and i also found that RDB$DEPENDENCIES does have a rdb$package column, but it has no index at all

=>

i reported this issue directly to vlad based on a customer database that we can not upload here

if have no idea why but executing these statements, each take about 2 minutes on a fast machine

ALTER DOMAIN BOOL DROP CONSTRAINT;

ALTER DOMAIN BOOL ADD CHECK (VALUE IN ('T', 'F'));

(checked with with another db with 500 generated tables and each
using a simlar domain 10 times, but this db much faster)

Vlad already told me that he found some not good defined system queries.

additional problem perhaps:
i do not know if it is the same problem, but for example on rdb$procedures, we do no longer have an index on rdb$procedure_name, only a combined index on
RDB$PACKAGE_NAME,RDB$PROCEDURE_NAME

Some old code did a select on rdb$procedures joined it on RDB$PROCEDURE_NAME with a user based table for special userrights.
In fb <=25 it was very fast, in fb>=30 this part create a very bad crossjoin and when you have several thousands stored procs in the
database, it does not work well.

perhaps for compatibility reasons, there should be not only the combined index on tables that now have rdb$package

tables are

RDB$DEPENDENCIES,RDB$FUNCTIONS,RDB$FUNCTION_ARGUMENTS,RDB$PROCEDURES,RDB$PROCEDURE_PARAMETERS,RDB$VIEW_RELATIONS

perhaps all systemqueries need a review based on this, and i also found that RDB$DEPENDENCIES does have a rdb$package column, but it has no index at all

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Please, test next snapshot build

@firebird-automations
Copy link
Collaborator Author

Commented by: Holger Klemt (klemmo)

tested with Firebird-3.0.3.32801-0_Win32 embedded, takes now less than 400ms, so all ok
(commit still took around 6 seconds, for whatever reasons ...)

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.3 [ 10810 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Not enough information

Test Details: Could not get valuable difference in elapsed ime on 3.0.3.32798 vs 2.5.8.27070.
Waiting for reply from Vlad, letter 13-sep-2017 21:54.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Not enough information => Done successfully

Test Details: Could not get valuable difference in elapsed ime on 3.0.3.32798 vs 2.5.8.27070.
Waiting for reply from Vlad, letter 13-sep-2017 21:54.

=>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants