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-issue-importer opened this issue Nov 15, 2010 · 15 comments
Closed

Slow compilation of stored procedures [CORE3237] #3608

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 15, 2010

Commented by: @dyemanov

Please provide the script.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2010

Commented by: Jesus Garcia (jegaza)

The script is prepared for be executed from IBExpert.

I send you the password by email

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2010

Modified by: Jesus Garcia (jegaza)

Attachment: metadata.zip [ 11831 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 16, 2010

Modified by: Jesus Garcia (jegaza)

Attachment: firebird.conf [ 11832 ]

Attachment: fbtrace.conf [ 11833 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2010

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 17, 2010

Modified by: @dyemanov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 19, 2010

Modified by: @dyemanov

Link: This issue is related to CORE2933 [ CORE2933 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 25, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 25, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 13, 2011

Modified by: @dyemanov

security: Developers [ 10012 ] =>

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 17, 2011

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 4, 2011

Modified by: @pcisar

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2016

Modified by: @pavel-zotov

QA Status: No test

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Feb 15, 2016

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
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants