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 compilation of stored procedures [CORE3237] #3608

Closed
firebird-automations opened this issue Nov 15, 2010 · 15 comments
Closed

Slow compilation of stored procedures [CORE3237] #3608

firebird-automations opened this issue Nov 15, 2010 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Jesus Garcia (jegaza)

Is related to CORE2933

Attachments:
metadata.zip
firebird.conf
fbtrace.conf

We have a database with 1000 tables, 1400 stored procedures and mor that 3000 triggers. I have extracted the database metadata and executed it ( with IBExpert) against Firebird 2.1 and Firebird 2.5. In firebird 2.1 the script takes 6 minutes in execute but in FB 2.5 more than 5 hours. The difference between both is the time FB 2.5 takes to compile the stored procedures, while version 2.1 takes milliseconds, 2.5 takes more than 10 seconds.

Once tha database is created, i have connected to both and recompile some stored procedures, but with the same results. While 2.1 takes milliseconds, 2.5 takes more than 10 seconds.

I can attach the database script.

Commits: 1f5e2c7 83f36de

====== Test Details ======

Test database is encrypted.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please provide the script.

@firebird-automations
Copy link
Collaborator Author

Commented by: Jesus Garcia (jegaza)

The script is prepared for be executed from IBExpert.

I send you the password by email

@firebird-automations
Copy link
Collaborator Author

Modified by: Jesus Garcia (jegaza)

Attachment: metadata.zip [ 11831 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Jesus Garcia (jegaza)

This is my firebird configuration files, that uses port 3053. I have tested the same processs in one machine that only has fb2.5 installed and the performance is good.

@firebird-automations
Copy link
Collaborator Author

Modified by: Jesus Garcia (jegaza)

Attachment: firebird.conf [ 11832 ]

Attachment: fbtrace.conf [ 11833 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE2933 [ CORE2933 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Commented by Jesus Garcia:

I have backed up and restored the database and now everything runs fine with good results in compiling stored procedures. Then the problem, i think is with running scripts for creating databases.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

This is because the restore creates the database with up-to-date index statistics. But with a script, indices on system tables keep having zero selectivity and this causes the optimizer to choose bad plan in the case of one system query, which slows down alterations.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Not enough information

Test Details: Test database is encrypted.

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