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

Execution of multiple stored procedures in some case is slow [CORE5782] #6045

Closed
firebird-automations opened this issue Mar 21, 2018 · 14 comments
Closed

Execution of multiple stored procedures in some case is slow [CORE5782] #6045

firebird-automations opened this issue Mar 21, 2018 · 14 comments
Labels
affect-version: 3.0.3 affect-version: 3.0.4 component: engine priority: major resolution: duplicate type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Is related to CORE4823
Duplicates CORE4823

Attachments:
Interbase.png
Firebird3.png
SLOWPROC.fbk

We are in point of migration of database from Interbase to Firebird and encountered slownes.
Below query run slow ~1s. With same shema this query run instant on Interbase.
We have analysed all procedures execution one by one and all looks fast with good plans.
But as whole it is slow compared to Interbase.

SELECT
D.DYR_ID
, D.DYR_NAZWA
, D2.DYR_ID AS MA_DYR_UPR
FROM DYREKCJA D
LEFT JOIN Pracownik_dyr_akcja(49, 0, 'ARozrachunkiKsiegowane', '') D2 ON D2.DYR_ID = D.DYR_ID
WHERE
D.OBSZAR_ID = 1
AND D.DYR_ID <> 0
ORDER BY D.DYR_NAZWA COLLATE PXW_PLK ASC

one point in comparision is that Firebird for above query use
PLAN SORT (JOIN (D INDEX (FK_DYREKCJA__OBSZAR), D2 NATURAL))
but Interbase
PLAN SORT (MERGE (SORT (D INDEX (RDB$FOREIGN323)),SORT ()))

on Firebird there are 561598 fetches, whereas Interbase there are only 30199 fetches

I have attached difference with stats and sample database to test
@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Attachment: Interbase.png [ 13228 ]

Attachment: Firebird3.png [ 13229 ]

Attachment: SLOWPROC.fbk [ 13230 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

One point to look into is when we change above query from LEFT to inner - of course totally different plan and resultset - then query run also quite instant on Firebird

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE4823 [ CORE4823 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

FB is not able to use MERGE plan for outer joins (yet).

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Thank you for the info.
Is there a plan for support this in some roadmap?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

v4 (fingers crossed)

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: We are in point of migration of database from Interbase to Firebird and encountered slownes.
Below query run slow ~1s. With same shema this query run instant on Interbase.
We have analysed all procedures execution one by one and all looks fast with good plans.
But as whole it is slow compared to Interbase.
SELECT
D.DYR_ID
, D.DYR_NAZWA
, D2.DYR_ID AS MA_DYR_UPR
FROM
DYREKCJA D
LEFT JOIN Pracownik_dyr_akcja(49, 0, 'ARozrachunkiKsiegowane', '') D2 ON D2.DYR_ID = D.DYR_ID
WHERE
D.OBSZAR_ID = 1
AND D.DYR_ID <> 0
ORDER BY
D.DYR_NAZWA COLLATE PXW_PLK ASC

one point in comparision is that Firebird for above query use
PLAN SORT (JOIN (D INDEX (FK_DYREKCJA__OBSZAR), D2 NATURAL))
but Interbase
PLAN SORT (MERGE (SORT (D INDEX (RDB$FOREIGN323)),SORT ()))

on Firebird there are 561598 fetches
when on Interbase there is only 30199 fetches

I have attached difference with stats and sample database to test

=>

We are in point of migration of database from Interbase to Firebird and encountered slownes.
Below query run slow ~1s. With same shema this query run instant on Interbase.
We have analysed all procedures execution one by one and all looks fast with good plans.
But as whole it is slow compared to Interbase.

SELECT
D.DYR_ID
, D.DYR_NAZWA
, D2.DYR_ID AS MA_DYR_UPR
FROM DYREKCJA D
LEFT JOIN Pracownik_dyr_akcja(49, 0, 'ARozrachunkiKsiegowane', '') D2 ON D2.DYR_ID = D.DYR_ID
WHERE
D.OBSZAR_ID = 1
AND D.DYR_ID <> 0
ORDER BY D.DYR_NAZWA COLLATE PXW_PLK ASC

one point in comparision is that Firebird for above query use
PLAN SORT (JOIN (D INDEX (FK_DYREKCJA__OBSZAR), D2 NATURAL))
but Interbase
PLAN SORT (MERGE (SORT (D INDEX (RDB$FOREIGN323)),SORT ()))

on Firebird there are 561598 fetches, whereas Interbase there are only 30199 fetches

I have attached difference with stats and sample database to test

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

The Subject of this case seems wrong. It seems the issue is about the handling of SPs in JOINs, not about "multiple SP executions".

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

When i have created this ticket i do not supposed that this is the reason
Funny that i have reported CORE4823 3 years ago and it is realeated strongly

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Karol,

Then it seems that this case is a duplicate, no?

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Now it looks like.
When CORE4823 will be implemented i will check it against this one.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue duplicates CORE4823 [ CORE4823 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

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

resolution: Duplicate [ 3 ]

@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.3 affect-version: 3.0.4 component: engine priority: major resolution: duplicate type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations