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

Jump to bottom

Same SELECT with 3.0.2 uses different PLAN as 2.0.3 (and takes much longer) [CORE5557] #5824

Closed
firebird-automations opened this issue Jun 6, 2017 · 6 comments
Closed

Same SELECT with 3.0.2 uses different PLAN as 2.0.3 (and takes much longer) [CORE5557] #5824

firebird-automations opened this issue Jun 6, 2017 · 6 comments
Labels
affect-version: 3.0.2 priority: major resolution: wontfix type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Pósa József (posaj)

Executing SELECT * FROM KIADT WHERE KIADF_SIF IN (SELECT SIF FROM TMPSYNC) command take 125 msec on 2.0.3 and 1 min 13 sec on 3.0.2.
The two databases are identical, with same data and indexes.

Firebird 2.0.3:

Plan
PLAN (TMPSYNC INDEX (RDB$PRIMARY94))
PLAN (KIADT NATURAL)

Adapted Plan
PLAN (TMPSYNC INDEX (INTEG_387))
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 15ms
Execute time = 125ms
Avg fetch time = 5,00 ms
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 269 713

Firebird 3.0.2:

Plan
PLAN (TMPSYNC NATURAL)
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 15ms
Execute time = 1m 13s 828ms
Avg fetch time = 36 914,00 ms
Current memory = 1 189 906 648
Max memory = 1 218 517 920
Memory buffers = 50 000
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 228 297 158
@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

What are the data types of KIADT.KIADF_SIF and TMPSYNC.SIF? Are they same or different?

@firebird-automations
Copy link
Collaborator Author

Commented by: Pósa József (posaj)

Dear Dmitry,
Thank you for your fast answer.

KIADT.KIADF_SIF: INTEGER
TMPSYNC.SIF: VARCHAR(32)

Earlier I made a test with SELECT * FROM KIADT WHERE KIADF_SIF IN (SELECT CAST(SIF AS INTEGER) SIF FROM TMPSYNC), without acceptable result.
After your response I made a new table TMPSYNC2 with INTEGER data type, and the performance is much better (234 msec):

Plan
PLAN (TMPSYNC2 INDEX (RDB$PRIMARY1053))
PLAN (KIADT NATURAL)

Adapted Plan
PLAN (TMPSYNC2 INDEX (INTEG_3685))
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 31ms
Execute time = 234ms
Avg fetch time = 9,36 ms
Current memory = 870 499 888
Max memory = 1 218 517 920
Memory buffers = 50 000
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 203 512

thanks again.

@firebird-automations
Copy link
Collaborator Author

Modified by: Pósa József (posaj)

description: Executing SELECT * FROM KIADT WHERE KIADF_SIF IN (SELECT SIF FROM TMPSYNC) command take 125 mses on 2.0.3 and 1 min 13 sec on 3.0.2.
The two databases are identical, with same data and indexes.

Firebird 2.0.3:

Plan
PLAN (TMPSYNC INDEX (RDB$PRIMARY94))
PLAN (KIADT NATURAL)

Adapted Plan
PLAN (TMPSYNC INDEX (INTEG_387))
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 15ms
Execute time = 125ms
Avg fetch time = 5,00 ms
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 269 713

Firebird 3.0.2:

Plan
PLAN (TMPSYNC NATURAL)
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 15ms
Execute time = 1m 13s 828ms
Avg fetch time = 36 914,00 ms
Current memory = 1 189 906 648
Max memory = 1 218 517 920
Memory buffers = 50 000
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 228 297 158

=>

Executing SELECT * FROM KIADT WHERE KIADF_SIF IN (SELECT SIF FROM TMPSYNC) command take 125 msec on 2.0.3 and 1 min 13 sec on 3.0.2.
The two databases are identical, with same data and indexes.

Firebird 2.0.3:

Plan
PLAN (TMPSYNC INDEX (RDB$PRIMARY94))
PLAN (KIADT NATURAL)

Adapted Plan
PLAN (TMPSYNC INDEX (INTEG_387))
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 15ms
Execute time = 125ms
Avg fetch time = 5,00 ms
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 269 713

Firebird 3.0.2:

Plan
PLAN (TMPSYNC NATURAL)
PLAN (KIADT NATURAL)

------ Performance info ------
Prepare time = 15ms
Execute time = 1m 13s 828ms
Avg fetch time = 36 914,00 ms
Current memory = 1 189 906 648
Max memory = 1 218 517 920
Memory buffers = 50 000
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 228 297 158

summary: Same SELECT with 3.0.2 uses different PLAN as 2.0.3 (and takes much loner) => Same SELECT with 3.0.2 uses different PLAN as 2.0.3 (and takes much longer)

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

So it's not a bug but expected (for incompatible data types). Your original query should look like:

SELECT * FROM KIADT WHERE CAST(KIADF_SIF AS VARCHAR(32)) IN (SELECT SIF FROM TMPSYNC)

to be fast, but double check that your values inside TMPSYNC.SIF are really simple numbers and not something like '1.0' or '01' (which will break the comparison).

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 3.0.2 priority: major resolution: wontfix type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations