Issue Details (XML | Word | Printable)

Key: CORE-2827
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pedro Rodrigues
Votes: 4
Watchers: 6

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

Very slow prepare for complex interrelated metadata with many triggers indirectly involved in the operation being prepared

Created: 26/Jan/10 06:05 PM   Updated: 04/Feb/11 11:40 AM
Component/s: Engine
Affects Version/s: 2.0.0, 1.5.4, 2.0.1, 2.0.2, 2.0.3, 1.5.5, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1, 2.0.5, 2.1.2, 2.5 Beta 1, 2.5 Beta 2, 2.1.3, 1.5.6, 3.0 Initial, 2.5 RC1
Fix Version/s: 2.1.4, 2.5.1, 3.0 Alpha 1

Time Tracking:
Not Specified

File Attachments: 1. Zip Archive (317 kB)
2. File GIA.7z (6.92 MB)
3. Zip Archive (5 kB)

Environment: Windows and Linux

Planning Status: Unspecified

 Description  « Hide
In triggers and procedures conditions are not analize and all instructions bellow that condition are prepared,.

Ex. I have a trigger and I put there the following condition in a trigger after update in a table2:

    if ( 1=2) then
         exception teste;
         update table1 set field1=field1+1;

table1 have 1.000.000 of records and when I change a record in table2 takes a long time and exception is never show but if I put the triggers like

    if (1=2) then
         exception teste;
         /*update table1 set field1=field1+1; */

it is fast.

Please help me all me databases are very slow because this and I swear you that this is true.
Analyze and fix this , Please .....................

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 26/Jan/10 06:22 PM
Pedro, this is the fifth time you create tickets about the same issue without providing any test case and you seem to ignore all the comments made, so all these tickets are getting closed. I'm getting tired of this. Please post a reproducible test case or use a firebird-support list for asking questions.

Pedro Rodrigues added a comment - 26/Jan/10 08:09 PM
I send you a test with with permission few days ago. If you wan I send you another. I only don't send you my database because it have 1Gb but I will send you another test . In the test with permission , Adriano says that with permission all expressions are ignore and all code in triggers are analyzed , my question is and in other ? statements below a condition are prepared but not executed even when condition is not verified.

Vlad Khorsun added a comment - 27/Jan/10 10:43 AM
Pedro, you can use prepared statements and not re-prepare it every time.

Dmitry Yemanov added a comment - 27/Jan/10 11:02 AM
Pedro, please attach a metadata only backup (gbak -m) for your big database. And provide the real queries that are a problem for you.

Pedro Rodrigues added a comment - 27/Jan/10 07:51 PM
File GIA.7z is a backup my database , extract it and then make a restore. is a udf library used in the database. it works in windows, but if you want a send you a version for linux. contains 2 files:
           script1.sql that when you run it it is slow.
           script2.sql that when you run it is very fast and the unique diference bettwen them is that in the 2 the update below a IF condition is comented, but the condition is never verified has you can see.

Why this happend ? I think that in this way you can test my teory .

Best regards

Pedro Rodrigues

Dmitry Yemanov added a comment - 29/Jan/10 07:55 AM
First of all, thanks for the test case. I can really confirm the problem. The database has very complex interrelated metadata. And when you add usage of another table into the trigger, it in turn adds a lot of dependencies (hundreds of objects get indirectly involved), thus significantly delaying the prepare process. As already explained, this is not a bug per se. All permissions (including nested) must be verified during prepare and this requirement is defined in the SQL specification. However, the underlying implementation seems being quite sub-optimal, making the prepare much slower than expected. I already foresee some room for improvement there. Also, as already suggested, it would make a lot of sense to use prepared statements to speed the things up (maybe impossible in the SQL script, but surely possible inside your application).

So, I'm changing this ticket to represent the performance issue instead of the incorrect behavior.

Pedro Rodrigues added a comment - 12/Feb/10 12:06 PM
Hi again,

I like to know if you have a prevision date to resolve this core, because our clients are very upset about this. Has you kwow I work in a company that produces software that is ALIDATA ( and we are probably the company that uses more Firebird Databases in Portugal, so because of this lost of performance our concurrency is take advantage of it and denigrating Firebird Databases image.
We are very concerned with this, so if you can tell us a date to sove this core we be grateful.

Best Regards,

Pedro Rodrigues

Dmitry Yemanov added a comment - 12/Feb/10 06:06 PM
It depends on what solution would be acceptable to you. IIRC, the difference in time between those extreme cases (trivial trigger vs the one causing dependencies) was about 100 times (0.2sec vs 20sec or something like that). I've managed to reduce that difference to 10 times (about 2sec). With some luck, these improvements could be committed into v2.1.4 which is expected to appear in a couple of months (cannot promise about v2.0.6 though). I suppose some further optimizations could also be possible, but unlikely in the v2.x versions. Also, I wouldn't expect the timings being nearly the same anyway.

Jorge Santos added a comment - 12/Apr/10 04:46 PM
Hello Dmitry,

Can you tell us if this improvements could be committed into v2.1.4 ?

I hope so ! We are looking forward for this improvements !

Best regards,

Jorge Santos

Dmitry Yemanov added a comment - 16/Apr/10 03:49 AM
Hopefully, it will be available in v2.1.4.

Pedro Rodrigues added a comment - 20/Jul/10 11:50 AM
Hi again,

Can you tell me when this improvement will be available ? I know thar you have mutch work to do, but this is very important to me.
I have many clients complaining the performance of the Database and I like to give them good news.
Can you give a date to this issue be resolved ?

Best Regards,

Pedro Rodrigues

Philip Williams added a comment - 29/Jul/10 02:47 PM
Pedro, here's what I got from Dmitry Yemanov on 15/Jul/10, when I asked about this on firebird-devel: "It's scheduled for v2.1.4 but not yet implemented. And CORE-2827 shows just one side of the issue but there are others."

I've no idea if this has been committed (partially?) and is part of any snapshot build? (I wouldn't mind testing, but am not setup to build FB.)

Dmitry Yemanov added a comment - 29/Jul/10 03:37 PM
It wasn't committed yet, because the current top priority is finalizing v2.5. You may expect some movement regarding this ticket in August (fingers crossed).

Philip Williams added a comment - 20/Sep/10 10:57 PM
Dmitry, I can provide another test case if you need it. My situation is a little different from Pedro's:

My calls to the database follow this format: an AJAX call initiates a few individual row updates here and there in the database, and then I have an on-commit trigger that calls stored procedures that potentially touch a large number of tables; those in turn have triggers that affect each other. This only happens once per transaction, so I can't make advantageous use of PreparedStatements here. The triggers do affect the speed of the individual row updates, but not horribly (from a user perspective) so optimizing those with re-use of PreparedStatements wouldn't help much (assuming I even could -- every row I update can have a variable number of fields being updated, and I always touch as few fields as I can, so that doesn't really work out.) The cost for committing, however, can be quite noticeable: somewhere around 10 seconds, even on a really nice new server, which from a user-experience standpoint isn't great.

I was able to mitigate the cost somewhat with the following approaches: (I think I mentioned this on firebird-devel, but someone might get some ideas from this for their own situation)

- The on-commit trigger only conditionally calls the stored procedures, via EXECUTE STATEMENT, if it knows that procedure will have something to do; my understanding is that this prevents each procedure's preparation from helping the others (any cached security info gets thrown away) but at least some of the time, not every SP call needs to be prepared, helping speed things up. But the more "important" the updates being made, the more SP's will need to be called, and so the prepare time goes way up. I'm logging the time spent inside the SP's, and most of them can complete in a matter of 15 or 30ms. The total time spent "doing" something is far, far less than the apparent time to execute the outermost SP. I wouldn't mind undoing the EXECUTE STATEMENT changes if this fix makes them obsolete. I check for "stuff to do" both inside and outside the SP, so it's a bit redundant at the moment.

- I tried having my triggers perform their updates via EXECUTE STATEMENT themselves; that helped the speed on individual row updates; it made on-commit speed somewhat unpredictable but usually faster; but it made nightly import jobs that touch lots of rows absolutely impossibly slow, so I had to roll back that idea. (The job couldn't even get far enough along to test on-commit speed, because of the row-update speed decrease.)

I can privately provide you with a nearly-blank database for testing, or I can test builds locally. I'd like to help however I can.

Dmitry Yemanov added a comment - 12/Oct/10 07:24 PM
Some good news for this thread :-) I seem to have found a better solution than the one tested priorly and it can be surely included into v2.1.4. As I said before, it solves only a half of the overall problem, but I believe it's worth testing anyway (as it provides a 8x performance boost for the test case). The second half is still under investigation. If anybody here is still waiting for a fix, please let me know.

Philip Williams added a comment - 12/Oct/10 08:18 PM
I would *love* to help test this, yes -- I'm excited about 2.5, but I'm even more excited about 2.1.4!

Jorge Santos added a comment - 13/Oct/10 09:12 AM - edited
Good to know that the solution of this problem will be included in v2.1.4. Although the solution is not final, an 8x increase in performance is very good ! :) And, of course, we (Alidata) would like to help test !

Dmitry Yemanov added a comment - 13/Oct/10 12:07 PM
Please test the next (tomorrow's) snapshot of v2.1.4, it should already contain the first part of the fix.

Pedro Rodrigues added a comment - 13/Oct/10 12:08 PM

Finally good news , I still waiting for resolution of this issue, and this is very important for me.
An 8x increase in performance is very good , and when the problem be completed solved it will be excelent.
But now I'm available to test this increase in performance, it will accelerate our application 8x.

Good work.

Best regards,

Pedro Rodrigues

Dmitry Yemanov added a comment - 13/Oct/10 02:18 PM - edited
The second half of the solution has just been committed as well. The final performance boost is 20x in my tests.

Jorge Santos added a comment - 13/Oct/10 04:02 PM
20x !? Great news! After performing our tests, we will inform the results obtained in our databases. Thank you!

Philip Williams added a comment - 13/Oct/10 06:47 PM
Dmitry, could we get a brief description of the "two halves" of the problem, and maybe a bit about the solutions, so we know what to look for when testing, what the limits to the improvements are (or should be)? (And for great release notes later!) Thanks!

Dmitry Yemanov added a comment - 14/Oct/10 05:08 AM
Here we talk about the prepare operation being slow. Analysis shows that 99% of time spent during permission checking, as every upper level insert indirectly touches a lot of other metadata (through triggers) and thus the whole chain of objects to be checked is quite long. The problems found are:

1) Permission checking for triggers require two queries against the system tables per every field accessed by that trigger. With many triggers or many fields, this takes a lot of time even provided that the system tables reside in the page cache.

2) Generic permission checking is algorithmically sub-optimal. It affects all kinds of queries being prepared, not only the ones involving triggers. You may find some related details in CORE-1775.

3) The test database was a kind of logically corrupted. A lot of metadata objects have references to their permissions but they are physically missing (RDB$SECURITY_CLASSES and RDB$USER_PRIVILEGES are nearly empty). As a result, the engine tried to lookup the ACLs again and again, without success. I don't know how it happened, but this isn't normal. So this is not the engine's problem and I had to fix it by zapping (nullifying) those invalid references inside the database.

Philip Williams added a comment - 14/Oct/10 03:27 PM
Thanks for the details. Were any of the inefficiencies affecting SP's or SP calls? (i.e. because lots of triggers use tables and call procedures, which themselves use tables, ...) I would assume SP's look up table/field permissions the same way triggers do?

Also, looks like the windows snapshot builds are currently unavailable, for those who might be waiting to test them:
10/12/2010 (recent) - firebird-devel list - Alex Peshkoff - "Also windows build is broken, but Vlad was going to fix this."

Dmitry Yemanov added a comment - 14/Oct/10 03:40 PM
Part (1) is related to triggers only, SPs are not affects. But other issues affect all kinds of objects.

Only v3.0 build is broken. v2.1 snapshot should be available tomorrow (it seems today the build system missed my yesterday's changes because the change log is automagically updated by another host nearly at the same time :-).

Philip Williams added a comment - 18/Oct/10 08:10 PM
So far, so good. For some of the worst cases, nearly 10s were spent preparing. Upgrading alone got me down to 3s; then I stripped out the "if (exists(...)) then execute statement 'execute procedure ...'" hacks I had put in place to prevent preparing procedures that were only conditionally called, and that got me down to about 1s (as we discussed, hiding calls prevents your solution from making use of permissions cache.) 10x improvement there.

Then I got down to about 500ms total time (prepare + run) with some further optimizations. I estimate I've got 200ms to 300ms left based on comparing the time it takes to do a connection.commit() (jaybird) vs. the timestamps I'm recording (UDFs writing to a log file) as the on-commit trigger runs: from start to finish, the actual SP code only thinks it took 200ms. But again, my situation involves both lots of SPs and lots of triggers, plus on the on-commit thing, so I may have other problems.

For individual operations (before committing) where triggers were also slowing things down, I'm seeing a dramatic improvement as well. No errors yet, security still seems to be in place. I've deployed to my test environment for users to play with, I'll keep an eye on it there.