Issue Details (XML | Word | Printable)

Key: CORE-3237
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Dmitry Yemanov
Reporter: Jesus Garcia
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
Firebird Core

Slow compilation of stored procedures

Created: 15/Nov/10 07:24 PM   Updated: 15/Feb/16 04:25 AM
Component/s: Engine
Affects Version/s: 2.5.0
Fix Version/s: 2.5.1, 3.0 Alpha 1

File Attachments: 1. File fbtrace.conf (5 kB)
2. File firebird.conf (27 kB)
3. Zip Archive (1.25 MB)

Environment: Windows 7 and Firebird 2.5 superserver
Issue Links:

QA Status: Not enough information
Test Details: Test database is encrypted.

 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 15/Nov/10 08:11 PM
Please provide the script.

Jesus Garcia added a comment - 16/Nov/10 03:59 PM
The script is prepared for be executed from IBExpert.

I send you the password by email

Jesus Garcia added a comment - 16/Nov/10 09:35 PM
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.

Dmitry Yemanov added a comment - 25/Nov/10 09:08 AM
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.

Dmitry Yemanov added a comment - 25/Nov/10 09:08 AM
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.