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 performance when executing SQL scripts as non-SYSDBA user [CORE5966] #6220

Closed
firebird-issue-importer opened this issue Nov 18, 2018 · 16 comments

Comments

@firebird-issue-importer

Submitted by: @reevespaul

Attachments:
prep.sh

Votes: 2

When a set of approxiamtely 30-50 SQL scripts containing updates of single records in various tables is executed as non-SYSDBA user granted rights to all required objects it takes 3-4 times more time compared with SYSDBA.

Commits: 2bf6198

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2018

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2018

Modified by: @AlexPeshkoff

reporter: Alexander Peshkov [ alexpeshkoff ] => Paul Reeves [ paul_reeves ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2018

Commented by: @AlexPeshkoff

Fixing this issue for FB4 should be done together with fixing security classes cache in order to make it support 'SQL SECURITY DEFINER' correctly, therefore I reassign it.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2018

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ] => Roman Simakov [ roman-simakov ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Nov 18, 2018

Modified by: @AlexPeshkoff

Fix Version: 3.0.5 [ 10885 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 2, 2019

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

Roman, I have test case for this bug (database+script).
https://www.sql.ru/forum/1295388-1/proizvoditelnost-na-zapis-fb-3-0-3
Write to me, if you need a tester.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 3, 2019

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

I have tested on build Firebird-3.0.5.33100 which included patch for this issue.
422 000 SQL scripts containing updates of single records in various tables is executed as non-SYSDBA user granted rights to all required objects it takes less then 20% more time compared with SYSDBA.
~10 minutes for SYSDBA, ~12 minutes for non-SYSDBA
I think, it is good result.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 24, 2019

Commented by: @romansimakov

Fix in 3058fa4

All other tests were passed OK, including my tests for SQL SECURITY.

Ivan, could you to test as well? This should be fixed in snapshot 4.0.0.1566 or higher.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 24, 2019

Commented by: @romansimakov

Attached http://prep.sh is my script I used to test.

The results are:
BEFORE:
roman:bin$ time ~/prep.sh 100000 sysdba

real 0m37,112s
user 0m8,534s
sys 0m10,293s
roman:bin$ time ~/prep.sh 100000 u

real 0m47,582s
user 0m9,598s
sys 0m11,884s

NOW:
roman:bin$ time ~/prep.sh 100000 sysdba

real 0m37,139s
user 0m8,807s
sys 0m10,407s
roman:bin$ time ~/prep.sh 100000 u

real 0m37,043s
user 0m8,915s
sys 0m10,307s

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 24, 2019

Modified by: @romansimakov

Attachment: http://prep.sh [ 13363 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 30, 2019

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

>Ivan, could you to test as well? This should be fixed in snapshot 4.0.0.1566 or higher.

Two weeks later.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 13, 2019

Commented by: @dyemanov

Any status update on this ticket? Ivan, did you have a chance to test the v4 snapshot?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Dec 14, 2019

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

I'm so sorry... I didn't test v4. migration 2.5 to 3.0 in progress. only one server on 2.5 remained and then test v4.

@romansimakov
Copy link
Collaborator

romansimakov commented Apr 25, 2021

Can we close this issue?

@AlexPeshkoff
Copy link
Member

AlexPeshkoff commented Apr 26, 2021

Suggest to close - no reply from author for a lo-o-o-ong time, and existing tests show that it's solved.

@romansimakov
Copy link
Collaborator

romansimakov commented Apr 26, 2021

Agreed

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment