Firebird 3: Strange case of select performance degradation
----------------------------------------------------------
Key: CORE-5708
URL: http://tracker.firebirdsql.org/browse/CORE-5708
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.2, 3.0.1
Environment: Windows 7 64Bit
Linux Debian 64 Bit
Reporter: Rajko Thon
In a project we encounter some strange behaviour which seems to be a bug.
We insert many rows into a database table.
Before the insert we use a PSQL-procedure to check if the entity (row) to be
inserted already exists.
The procedure is very simple. It checks for existence using a three-table join
which uses only indexed columns.
We insert 2888 elements in a cycle. The demo uses three cycles to demonstrate
the effect.
The strange behaviour can be seen in the attached picture "BugDemo.jpg".
The inserts get slower and slower the more elements are inserted.
The picture shows that the average duration for inserting elements is growing
linearly.
The second cycle continues that behaviour.
At the start of the 3rd cycle we disconnect and then reconnect the database.
>From then on the average duration for inserting elements is as low as at the
>start of cycle 1 and stays there.
It then only grows logarithmically, as it should.
Further context:
* The average duration only grows linearly if a procedure as in
"dok_elem_p_exists" with a three-table-join is used
(see attached database example).
* If the same join is used outside of the procedure, the duration only grows
logarithmically, as it should.
* If the join inside the procedure is disjointed and executed as three single
selects, the duration only grows logarithmically.
* If the database is disconnected and reconnected the duration only grows
logarithmically.
Guesswork: some offset related to the db session where joins are used in
procedures is only reset when the database is disconnected and reconnected.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
