Issue Details (XML | Word | Printable)

Key: CORE-5966
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Roman Simakov
Reporter: Paul Reeves
Votes: 2
Watchers: 9
Operations

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

Slow performance when executing SQL scripts as non-SYSDBA user

Created: 18/Nov/18 07:03 PM   Updated: 30/Jul/19 11:59 AM
Component/s: Engine
Affects Version/s: 4.0 Alpha 1, 3.0.3, 3.0.4
Fix Version/s: 3.0.5

File Attachments: 1. File prep.sh (0.5 kB)

Environment: Database should be located on RAM filesystem in order to easier reproduce an issue.

QA Status: No test


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


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Peshkov added a comment - 18/Nov/18 07:08 PM
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.

Ivan Pisarevsky added a comment - 02/Feb/19 07:30 AM
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.

Ivan Pisarevsky added a comment - 03/Feb/19 04:39 PM
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.

Roman Simakov added a comment - 24/Jul/19 07:09 AM - edited
Fix in https://github.com/FirebirdSQL/firebird/commit/3058fa48d35b24fcbb77df2605616eeedcf831cc

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.

Roman Simakov added a comment - 24/Jul/19 07:11 AM - edited
Attached 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

Ivan Pisarevsky added a comment - 30/Jul/19 11:59 AM
>Ivan, could you to test as well? This should be fixed in snapshot 4.0.0.1566 or higher.

Two weeks later.