People often talk that Firebird performs badly in networks with high latency. I did some tests to see how the new protocol has been improved compared to the old one, and to see how it could be improved even more. My tests only cares about latency, and not about bandwidth.
It simulate network latency in Linux using Netem. With the tc utility, we can add a delay for outbound traffic. Since I did it for lo interface, it happens that it works for in/out traffic. I set delay to 100ms (that is more or less a value I found doing it in the internet with my horrible Telefonica speedy connection), using this command:
And here is the result of a ping:
To later remove the delay, the command is:
The test uses Firebird library present on (my till unknown) CppSys project (more on CppSys in a future post). The CppSys database library has interfaces and semantics based on JDBC. The relevant test code is here:
The println function buffers the text and prints it in its next run, with the current time (in minus) milliseconds the time when the text was buffered. Here is its code:
As you see, the test does what every client/server database developer must knows that he/she shouldn’t do: a main query with nested queries for each record. If you ever run isql over the internet, you had see it performs very slow, and that is the reason. isql does just that. It does that because it’s written using embedded SQL with gpre and sometimes this is needed to access new columns that may not be presented on a database of an older ODS. And due to embedded SQL nature, “details” of where and when to prepare statements are just ignored.
So far, here is the results.
Result for the old protocol, with 2.0 client and 2.5 server:
Result for the new protocol, with 2.5 client and 2.5 server:
FWIW, I did tested the old protocol on real network with similar latency too. The result was very resemblant.
The total times are:
New Protocol minus round-trip times of rs3
So we may see that:
The old protocol is a crap.
The new protocol does a good job removing some “unnecessary” round-trips.
It may be improved.
The first interesting problem is the series of statement preparations. The preparation of a statement may result in an error, so it necessary involves a round-trip so we can get the status from the server. But does it really needs it? Using Oracle JDBC driver, a prepareStatement with a wrong statement does not throw an exception. It’s deferred. In fact, this is a contract, and a change in a contract would require some way for the client applications to enable or disable it.
The second interesting thing is that executing a statement that needs fetch does not involves a round-trip. Does it happens too when selecting from a SP? Won’t this be a contract change as well, since selectable SPs could write to the database? I didn’t did further tests… Anyway, that’s what allows the optimizations I talk below.
Then we start the fetches. The first fetch for rs1 necessarily involves a round-trip and buffers some records in the client. Subsequents fetches are resolved locally.
The third interesting thing is about the nested queries. Since queries are executed, a round-trip would be inevitable, but it does one for each query. A more intelligent approach would be to fetch rs3 too when asked to fetch rs2, since it’s already executed. In this test, it would run ~8s faster. And a much more intelligent approach would do it in the background (and transmitting asynchronous), without putting some overhead on the rs2 call.
With a super intelligent server/client/protocol, the client and the server may cache prepared statements, and the server may asynchronous send statements invalidations to the client. Then, preparation of statements would have no round-trip (when already prepared one time), being sure that the statement is not wrong.
So what you may do now?
Do not talk with the server in this poor (and common) way. Use selectable SPs to run multiple queries.
Do not prepare/close/prepare your common statements. Cache them in your application.